Page 1 of 1
Fetching the last auto-incremented id in a table
Posted: Sat May 10, 2003 10:38 am
by evilmonkey
Hello. I want to fetch the last auto-inceremented id from a table after I do the SELECT query. Mind, I can't do this with mysql_num_rows because the number of rows won't neccessarily be the same as the last id. How could I do this?
Thanks.
Posted: Sat May 10, 2003 11:43 am
by AVATAr
Posted: Sat May 10, 2003 11:55 am
by evilmonkey
No, that only works after INSERT. I need after SELECT for the last row fetched. Thanks for trying.
Cheers!
Posted: Sat May 10, 2003 11:57 am
by twigletmac
You can do a SELECT on the ID field, order it in descending order, limit it to one and you get the last ID value.
Mac
Posted: Sat May 10, 2003 2:10 pm
by evilmonkey
Thanks Mac, that helped (a little). But now, I have bigger problems. This is a script to make a gallery of pictures, filesnames (along with other info) of which are stored in a MySQL database.I want to limit the user to seeing a maximum of 20 pictures at a time from the last one, and keep going until it reacher the first one (with previous and next links). The problem is, the code below does not show anything. Here it is:
Code: Select all
//connect to database
$db = mysql_connect("localhost","", "");
mysql_select_db("pcdna", $db);
if (@!$category){
$category="*";}
//start value should be the last database value by default
if (@!$start)
{
$fsql = "SELECT * FROM pics WHERE category='$category' ORDER BY id DESC LIMIT 1";
$fres = mysql_query($bullsql, $db);
$f=mysql_fetch_assoc($bullres);
$last_id=$f['id'];
$start=$last_id;
echo $start;
}
//Query the database
$sql = "SELECT * FROM pics WHERE category='$category' ORDER BY id DESC LIMIT '$start', 20";
$result = mysql_query($sql, $db);
//loop to display the 20 pictures
while($row = mysql_fetch_assoc($result))
{
echo "<p><a href="http://127.0.0.1/pc-dna/pic.php?id=".$row['id'].""><img src="showpic.php?max_width=200&max_height=200&file=".$row['url'].""></a><br>";
echo """.$row['title']."" By: ".$row['submitter']."<br>";
}
?>
Can I do SELECT * FROM table WHERE category= *? Would this work? I tried doing this without this clause, and it still wouldn't work.
NOTE: The previous and next links have not been made yet.
Here is a dump of my database if someone needs it:
Code: Select all
CREATE TABLE `pics` (
`id` int(11) NOT NULL auto_increment,
`submitter` varchar(128) NOT NULL default '',
`category` varchar(128) NOT NULL default '',
`url` varchar(128) NOT NULL default '',
UNIQUE KEY `id` (`id`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;
#
# Dumping data for table `pics`
#
INSERT INTO `pics` VALUES (1, 'Jack', 'stuff', 'apache.jpg');
INSERT INTO `pics` VALUES (2, 'Jack', 'stuff', 'php.jpg');
There it is. I don't think it's a problem with my pictures because it worked before I started doing all that $category and $start manipulation.
I hope someone can help. Thanks
Cheers!
Posted: Sat May 10, 2003 7:38 pm
by McGruff
I don't actually know if "SELECT * FROM table WHERE category= *" would work or not, but in any case it's simpler just to write "SELECT * FROM table". Rather than using an IF clause to set $category to *, you could use an IF to define mysql query strings (with and without a WHERE clause).
Are $bullres and $bullsql declared anywhere? Should these be $fres and $fsql?
Posted: Sat May 10, 2003 8:51 pm
by evilmonkey
Thank you McGruff, I took a couple of your suggestions. This is my revised code:
Code: Select all
//connect to database
$db = mysql_connect("localhost","", "");
mysql_select_db("pcdna", $db);
//start value should be the last database value by default
if (@!$start)
{
if (@!$category){
$fsql = "SELECT * FROM pics ORDER BY id DESC LIMIT 1";}
else{
$fsql = "SELECT * FROM pics WHERE category='$category' ORDER BY id DESC LIMIT 1";}
$fres = mysql_query($fsql, $db);
$f=mysql_fetch_assoc($fres);
$last_id=$f['id'];
$start=$last_id;
echo $start;
}
//Query the database
if (@!$category){
$sql = "SELECT * FROM pics ORDER BY id DESC LIMIT '$start',20";}
else{
$sql = "SELECT * FROM pics WHERE category='$category' ORDER BY id DESC LIMIT '$start',20";}
$result = mysql_query($sql, $db);
//loop to display the 20 pictures
while($row = mysql_fetch_assoc($result))
{
echo "<p><a href="http://127.0.0.1/pc-dna/pic.php?id=".$row['id'].""><img src="showpic.php?max_width=200&max_height=200&file=".$row['url'].""></a><br>";
echo """.$row['title']."" By: ".$row['submitter']."<br>";
}
?>
The wierd part is, it can display only one picture. If I change the limit to simply 1, everything works fine (with or without a cetegory). The problem is, it just displays a blank page with no pictures (in fact it gives me a warning about the mysql_fetch_assoc) when I set the LIMIT clause to '$start', 20. Can anyone tell me what's wrong? Thanks.
Cheers!
Posted: Sun May 11, 2003 11:01 am
by Coco
edit again.... 4th time lucky? *sigh*
You are using a number for your offset in your limit which is likely higher than the number of rows in the table. You said yourself that the highest auto-inc id is probably higher than num_rows, so this means that $start is probably higher than the last row in the table
the actual id numbers have no relevance to LIMIT commands
cant you just use MAX(id) too?
http://www.mysql.com/doc/en/Group_by_functions.html
Posted: Sun May 11, 2003 11:18 am
by evilmonkey
No, that's not it...
Something is wrong with the way I use the LIMIT clause. I tried to manually set it to 2,2 (start from row 2 and display 2 pictures), that wouldn't work either. I have it echoing the id of the last row it fetches, and it is proper, so that is not my problem. I have just tried removing the LIMIT clause altogether, and it works fine, but if I have hundreds of pictures, putting them on a single page is torture for both the client and the server. I need to get this limit clause working...
Any suggestions?
Cheers!
Posted: Sun May 11, 2003 11:25 am
by Coco
did you take the '' off the offset? its a number not a string
Posted: Sun May 11, 2003 11:34 am
by evilmonkey
Yes, I did. I was looking through the MySQL manual, and found something interesting:
If two arguments are given, the first specifies the offset of the first row to return, the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1)
Look at the latter sentence. So I modified my code a little:
Code: Select all
//unchanged code
$start=$last_id-1;
//unchanged code
if (@!$category){
$sql = "SELECT * FROM pics ORDER BY id DESC LIMIT $start, 2";}
else{
$sql = "SELECT * FROM pics WHERE category='$category' ORDER BY id DESC LIMIT $start,2";}
The problem is, this only displays number 1 in the database (I only have two pictures there, I want it to display number 2 first, number 1 second.). Maybe because it is descending order I need to put -2, not 2...(tried didn't work, couldn't find an answer in the manual either).
Posted: Sun May 11, 2003 11:55 am
by ReDucTor
Code: Select all
$q=mysql_query("SELECT * FROM pics ".(@$category?"WHERE category='".$category."'":'')." ORDER BY id LIMIT 2") or die(mysql_error());
for($i=mysql_num_rows($q);$i>0;$i--) {
$row=mysql_fetch_assoc($q);
print_r($row);
}
Posted: Sun May 11, 2003 12:30 pm
by evilmonkey
Thank you very much, that worked. I got it going in descending order too. The only question I have is, if I want to start not at the last, but somewhere in the middle, how would I do that withnthis code?
Cheers!
Posted: Sun May 11, 2003 2:02 pm
by Coco
ah well you see the limit works on the
return not on the actual layout of the database.
so if you have it ordered desc, offset 0 is the HIGHEST, whereas on asc, offset 0 is the LOWEST
the main use of this would be to page lots of data
Code: Select all
$showpage = 20; //no. of items to show per page
$offset = ($page - 1) * $showpage;// -1 because page 1 starts at 0
$q=mysql_query("SELECT * FROM pics ".(@$category?"WHERE category='".$category."'":'')." ORDER BY id LIMIT $offset,$showpage") or die(mysql_error());
for($i=mysql_num_rows($q);$i>0;$i--) {
$row=mysql_fetch_assoc($q);
print_r($row);
}
so then you just have a $_GET['page']

Posted: Sun May 11, 2003 3:04 pm
by evilmonkey
Thank you very much Coco! I got it working.
To anyone who will want to do a search on this topic 20 years from now

, here is my (with the help pf others, of course) final code:
Code: Select all
<?php
//connect to database
$db = mysql_connect("localhost","", "");
mysql_select_db("db", $db);
//if it's just gallery.php, it should display all categories starting from the latest. (category is set is $q)
if (@!$page){
$page = 1;}
$showpage = 20; //no. of items to show per page
$offset = ($page - 1) * $showpage;// -1 because page 1 starts at 0
$q=mysql_query("SELECT * FROM pics ".(@$category?"WHERE category='".$category."'":'')." ORDER BY id desc LIMIT $offset,$showpage") or die(mysql_error());
for($i=mysql_num_rows($q);$i>0;$i--) {
$row=mysql_fetch_assoc($q);
echo "<p><a href="http://127.0.0.1/pc-dna/pic.php?id=".$row['id'].""><img src="showpic.php?max_width=100&max_height=100&file=".$row['url'].""></a><br>";
echo """.$row['title']."" By: ".$row['submitter']."<br>";
}
?>
Thanks again, everyone, for your help.