Fetching the last auto-incremented id in a table
Moderator: General Moderators
- evilmonkey
- Forum Regular
- Posts: 823
- Joined: Sun Oct 06, 2002 1:24 pm
- Location: Toronto, Canada
Fetching the last auto-incremented id in a table
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.
Thanks.
- evilmonkey
- Forum Regular
- Posts: 823
- Joined: Sun Oct 06, 2002 1:24 pm
- Location: Toronto, Canada
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
- evilmonkey
- Forum Regular
- Posts: 823
- Joined: Sun Oct 06, 2002 1:24 pm
- Location: Toronto, Canada
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:
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:
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!
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>";
}
?>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');I hope someone can help. Thanks
Cheers!
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?
Are $bullres and $bullsql declared anywhere? Should these be $fres and $fsql?
- evilmonkey
- Forum Regular
- Posts: 823
- Joined: Sun Oct 06, 2002 1:24 pm
- Location: Toronto, Canada
Thank you McGruff, I took a couple of your suggestions. This is my revised code:
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!
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>";
}
?>Cheers!
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
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
- evilmonkey
- Forum Regular
- Posts: 823
- Joined: Sun Oct 06, 2002 1:24 pm
- Location: Toronto, Canada
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!
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!
- evilmonkey
- Forum Regular
- Posts: 823
- Joined: Sun Oct 06, 2002 1:24 pm
- Location: Toronto, Canada
Yes, I did. I was looking through the MySQL manual, and found something interesting:
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).
Look at the latter sentence. So I modified my code a little: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)
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";}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);
}- evilmonkey
- Forum Regular
- Posts: 823
- Joined: Sun Oct 06, 2002 1:24 pm
- Location: Toronto, Canada
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
so then you just have a $_GET['page'] 
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);
}- evilmonkey
- Forum Regular
- Posts: 823
- Joined: Sun Oct 06, 2002 1:24 pm
- Location: Toronto, Canada
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:
Thanks again, everyone, for your help.
To anyone who will want to do a search on this topic 20 years from now
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>";
}
?>