query last 10 records in a table

ablack

I need to display the last records in a table in the order they came in.

so.... simply adding DESC to the query string won't do it for me.

any Ideas?

stolzyboy

do you have some sort of auto_incrementing field to work with

ablack

Yes pk_user

and here is the query string.

$query = "SELECT * FROM swd_user HAVING fk_account = 2 ORDER BY pk_user ";

stolzyboy

you can use limit 10 inside your query, and either with asc or desc depending which ones you want to show, probably desc if you want the last ones??

example

$query = "SELECT * FROM swd_user HAVING fk_account = 2 ORDER BY pk_user desc limit 0,10";

ablack

I have some code that limits the number of records displayed.

I need to display the last few records in the asecnding order
so I can not use desc to get to the bottom of the db

stolzyboy

so use asc, one way or the other it will grab the first or last according to when they were input with the autoincrementing field

ablack

Ahah here is my problem

I can limmit the number of records displayed

but they are taken from the top of the query

and I need them in asc order but from the botom of the query

so 3 records from a 5 record table show up like this

rec 3
rec 4
rec 5

not like this

rec 5
rec 4
rec 3

stolzyboy

ok i see the problem now, didn't quite make sense before

$start=mysql_num_rows($result)-10;

$query = "SELECT * FROM swd_user HAVING fk_account = 2 ORDER BY pk_user desc limit $start,10";

ablack

looks good but i get this error

mysql_num_rows(): supplied argument is not a valid MySQL result resource

nnichols

For a start you should be using "WHERE" not "HAVING" as the later has a higher overhead on the db server and memory.

Try this code which returns the last 10 rows in the table and then use array_reverse() to reverse the order.

$sql = "SELECT * FROM swd_user WHERE fk_account = 2 ORDER BY USER_ID DESC LIMIT 10";
$res = mysql_query($sql)  or die('Query f****d up.<br>Error: ' . mysql_error());

while ($row = mysql_fetch_array($res)) {
$desc[] = $row;
}
$reversed = array_reverse($desc);

Hope this helps 😉

colbec

Perhaps you could create a temporary table with the required records using the necessary sort to get the right ones, then sort the temp table in the real order, make the report and drop the temp table?

nnichols

The full code for my example above, including the code to loop through the reversed array is -

<?

mysql_connect($host, $user, $pass);
mysql_select_db($db_name);
$sql = "SELECT * FROM swd_user WHERE fk_account = 2 ORDER BY USER_ID DESC LIMIT 10";
$res = mysql_query($sql)  or die('Query failed.<br>Error: ' . mysql_error());

while ($row = mysql_fetch_row($res)) {
// store rows in an array
$desc[] = $row;
}

// reverse the array
$reversed = array_reverse($desc);

$num_rows = count ($reversed);

echo '<table border=1>';
// loop through rows
for ($i=0 ; $i<$num_rows ; $i++) {
$row = $reversed[$i];

$num_fields = count ($row);
echo '<tr>';
	// loop through fields
	for ($j=0 ; $j<$num_fields ; $j++) {
	echo "<td>$row[$j]</td>";
	}
echo '</tr>';
}
echo '</table>';

?>

Hope this helps 😉

ablack

Works great thanks!

only problem is...
My color codeing is not working can you take a look...
I only need one field in the table with alternating colors

this is what I was using
$row_color = ($row_count % 2) ? $color1 : $color2;

for($i = 0; $i < $numofrows; $i++) {
$row = mysql_fetch_array($result); //get a row from our result set
if($i % 2) { //this means if there is a remainder
echo "<TR bgcolor=\"lightblue\">\n";
} else { //if there isn't a remainder we will do the else
echo "<TR bgcolor=\"white\">\n";
}
echo "<TD align=center width=100><font face=Arial,Verdana size=1>".$row['name']."</TD>\n";
echo "</TR>\n";

nnichols

Just view the source on the page where you use it and it should indicate what is going wrong

ablack

nothing shows up on the page when I run it.

What would I modify just to display the name field?

Your code is much cleaner than mine so I am going through and trying to make it like yours..
🙂

nnichols

Post the whole code as you have it now and I'll have a look.

ablack

Thanks I really appreciate the help.
This code works...
Here is a screen shot of what it does.

I can't add the reverse coding it breaks...

<?PHP
$db = mysql_connect("localhost","table_name","password") or die("Problem connecting");
mysql_select_db("database_name") or die("Problem selecting database");
$query = "SELECT * FROM swd_user WHERE fk_account = 2 ORDER BY pk_user";
$result = mysql_query($query) or die ("Query failed" . mysql_error());
//let's get the number of rows in our result so we can use it in a for loop
$numofrows = 20;

$row_color = ($row_count % 2) ? $color1 : $color2;

echo "<TABLE align=center BORDER=\"0\">\n";
//echo "<TR bgcolor=\"lightblue\"><TD align=center><font face=Arial,Verdana size=1><b>Name</b></TD>\n";
for($i = 0; $i < $numofrows; $i++) {
$row = mysql_fetch_array($result); //get a row from our result set
if($i % 2) { //this means if there is a remainder
echo "<TR bgcolor=\"lightblue\">\n";
} else { //if there isn't a remainder we will do the else
echo "<TR bgcolor=\"white\">\n";
}
echo "<TD align=center width=100><font face=Arial,Verdana size=1>".$row['name']."</TD>\n";
echo "</TR>\n";
}
//now let's close the table and be done with it
echo "</TABLE>\n";
?>

nnichols

If you only want to use "name" from the db then only select "name", otherwise you are just wasting system resources.

<?

mysql_connect($host, $user, $pass);
mysql_select_db($db_name);
// if you only want to display "name" - only select "name", NOT "*"
$sql = "SELECT name FROM swd_user WHERE fk_account = 2 ORDER BY pk_user DESC LIMIT 10";
$res = mysql_query($sql)  or die('Query failed.<br>Error: ' . mysql_error());

//change this to assoc
while ($row = mysql_fetch_row($res)) {
// store rows in an array
$desc[] = $row;
}

// reverse the array
$reversed = array_reverse($desc);

$num_rows = count ($reversed);

echo '<table border=1>';
// loop through rows
for ($i=0 ; $i<$num_rows ; $i++) {
$row = $reversed[$i];

if($i % 2)
	$color = 'white';
else
	$color = 'lightblue';

$num_fields = count ($row);
echo '<tr bgcolor="' . $color . '">';
	// loop through fields
	for ($j=0 ; $j<$num_fields ; $j++) {
	echo "<td>$row[$j]</td>";
	}
echo '</tr>';
}
echo '</table>';

?>

You could get rid of the field loop and change the mysql_fetch_row() to mysql_fetch_assoc() -

<?

mysql_connect($host, $user, $pass);
mysql_select_db($db_name);
// if you only want to display "name" - only select "name", NOT "*"
$sql = "SELECT name FROM swd_user WHERE fk_account = 2 ORDER BY pk_user DESC LIMIT 10";
$res = mysql_query($sql)  or die('Query failed.<br>Error: ' . mysql_error());

//change this to assoc
while ($row = mysql_fetch_assoc($res)) {
// store rows in an array
$desc[] = $row;
}

// reverse the array
$reversed = array_reverse($desc);

$num_rows = count ($reversed);

echo '<table border=1>';
// loop through rows
for ($i=0 ; $i<$num_rows ; $i++) {
$row = $reversed[$i];

if($i % 2)
	$color = 'white';
else
	$color = 'lightblue';

echo '<tr bgcolor="' . $color . '">';

echo "<td>$row['name']</td>";

echo '</tr>';
}
echo '</table>';

?>

Hope this helps 😉

ablack

You ARE a GOD 😃