get last row in a column?

[completeNewb]

Hi, I'm trying to get the last number from a column of numbers, add 1 to it and use this to insert a new value in the column.

The column is not the primary key, indexed, or auto insert, it's just a plain old int field.

 

$newid = mysql_query('SELECT IDCOL FROM prods ORDER BY IDCOL DESC LIMIT 1');

 

// the above just doesn't work--it returns 0, there are over 1200 products--I'm going nuts over this.

 

//$newid += 1; I've commented this out, it works though (in that it adds 1 to 0)

 

$sql = mysql_query("INSERT INTO prods (IDCOL , Prod_Code, blah... blah..)

        VALUES('$newid','$prod_code','blah... blah...')") or die (mysql_error()); //this works (but inserts 0 as the newID)

 

You'd think this would be so easy.

Thanks

Steve

 

[silkfire]

If you have auto increment on your ID column then you don't need this meaningless procedure...

[seany123]

You should really use the Search function on this site, i found about 10 threads asking the same question...

 

assuming your using ID and with an auto increment then this should do it.

 

this was just c+p from another thread...

$sql = "SELECT * FROM prods ORDER BY IDCOL DESC limit 0,1" ;

 

if that doesnt work... you could use a fetch_num_rows to get the latest id (assuming you dont delete any rows).. then use the number of rows against the IDCOL

[completeNewb]

Thanks for your replies...

 

first, auto increment is not an option, unfortunately I have two databases to juggle, one is a subset of the other (products on the website). The master database is ms sql which won't run on a unix server (the website). The master database is on an office server with a poor connection unsuited for web users to connect to.

 

second, fetch_num_rows to get the latest id (assuming you dont delete any rows)-- but, hey, rows will get deleted.

 

So, I need a web database that will accept updated IDs from the master database.

 

I searched long and hard and tried numerous solutions. Sorry but the my meaningless procedure is the best I could come up with. Believe me, i would be overjoyed if anyone's got another.

 

$sql = "SELECT * FROM prods ORDER BY IDCOL DESC limit 0,1" ;

 

looks to me like it would select every column (?) when i only want the last ID, but I just tried it and sadly it also returns 0.

 

This sql:

SELECT IDCOL FROM prods ORDER BY IDCOL DESC LIMIT 1

works in phpMyAdmin, but once i write it as php...

 

$newid = mysql_query('SELECT IDCOL FROM prods ORDER BY IDCOL DESC LIMIT 1');

 

it returns 0.

 

I don't understand why.

 

Steve

 

 

[kicken]

mysql_query returns a result resource, not the actual result.

 

To get the field value you need to call mysql_fetch_array() and then pull the column out of that array.

 

$res = mysql_query('SELECT IDCOL FROM prods ORDER BY IDCOL DESC LIMIT 1');
$row = mysql_fetch_array($res);
$newid = $row['IDCOL'];

[completeNewb]

kicken,

not only did you explain why my efforts failed but your solution works like a charm! 

endless thanks,

Steve