Member Avatar for Eagle.Avik

hi, i am new to mysql and also to php, i am trying to add a feature to a webpage, for showing some latest records from a mysql databse. i managed to show records from mysql database, but the problem is i only trying to show the first four records but it shows all the records i have in my databse. here is my script that shows the data from the database, so how do i managed to do that!!

<?php
$db = mysql_connect($mysql_server, $mysql_username, $mysql_password);
if (!$db)
{
   die('Failed to connect to database server!<br>'.mysql_error());
}
mysql_select_db($mysql_database, $db) or die('Failed to select database<br>'.mysql_error());
$sql = "SELECT id, name, seo_friendly_url FROM " . $mysql_table . "PAGES WHERE visible = 1 ORDER BY menu_index ASC";
$result = mysql_query($sql, $db);
while ($data = mysql_fetch_array($result))
{
   echo "<a href=\"" . basename(__FILE__) . "?page=" . $data['seo_friendly_url'] . "\" id=\"" . $data['id'] . "\">" . $data['name'] . "</a>\n";
}
mysql_close($db);
?>

thanx.

  • 3 Contributors
  • 6 Replies
  • 864 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by Eagle.Avik

Dani AI

Generated

A short recap and clarification tied to the existing replies: correctly pointed to limiting the result set, and also suggested switching away from the old mysql_* API. The main behaviour to control is the SQL ordering plus a row-limit — LIMIT only restricts how many rows are returned, it does not change which rows are “first” unless the query’s ORDER BY is chosen correctly (AUTO_INCREMENT id for newest inserts, or a timestamp column if available; menu_index is typically a manual sort key).

Two quick checks that often trip people on this thread: first, confirm the final table name built by your code (the original shows $mysql_table . "PAGES" — echo the assembled SQL before running it to make sure it matches the real table). Second, don’t trust raw DB values when printing HTML: escape output to avoid XSS and avoid numeric-only id attributes (prefix with a letter or use data- attributes).

A simple, modern approach is to use PDO (or mysqli) and UTF-8, select only the columns needed and limit the rows. Example pattern (adjust credentials and table name safely):

<?php
$pdo = new PDO('mysql:host=HOST;dbname=DB;charset=utf8mb4', USER, PASS, [PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION]);
$limit = 4;
$sql = "SELECT id,name,seo_friendly_url FROM pages WHERE visible = 1 ORDER BY menu_index DESC LIMIT " . (int)$limit;
foreach ($pdo->query($sql, PDO::FETCH_ASSOC) as $row) {
    echo '<a href="'.htmlspecialchars(basename(__FILE__).'?page='.$row['seo_friendly_url']).'" id="p'.(int)$row['id'].'">'.htmlspecialchars($row['name']).'</a>';
}
?>

Final notes: use prepared statements when user input is involved, add an index on the ORDER BY column for large tables, and migrate from the old mysql extension (removed in PHP 7+) to mysqli or PDO for security and future compatibility.

Recommended Answers

All 6 Replies

Member Avatar for Member #120589 
$sql = "SELECT id, name, seo_friendly_url FROM {$mysql_table}PAGES WHERE visible = 1 ORDER BY menu_index LIMIT 4";
$r = mysql_query($sql);
if(mysql_num_rows($r))
{
    while($d = mysql_fetch_assoc($r))
    {
        echo $d['field'];
        //do what you want
    }
}

I suggest using mysqli_* or PDO though as mysql_* is to be deprecated.

Member Avatar for Atli

Hey.

You can add a LIMIT clause to your SQL query to specify how many you want.

SELECT stuff FROM the_table 
ORDER BY id DESC 
LIMIT 4

Assuming id is an AUTO_INCREMENT field, this query would give you only the latest four records. The ORDER BY is returning the results in descendinc order (DESC), and the LIMIT is only giving you the first four of those records.

Edited by Atli
Member Avatar for Member #120589

Ah, missed the latest records :)

$sql = "SELECT id, name, seo_friendly_url FROM {$mysql_table}PAGES WHERE visible = 1 ORDER BY menu_index DESC LIMIT 4";
Member Avatar for Eagle.Avik

@diafol thnx, but can you give me the full edited code

Member Avatar for Member #120589

What do you mean full edited code?

I showed you the SQL. I demonstrated the conditional (if) and the while loop. I don't know what else you want me to do? Are you not able to insert your own code into the loop?

Member Avatar for Eagle.Avik

@diafol, sorry for asking that previously. got it working now... thanx soooo much.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.