Page 1 of 1

last number from a column of numbers?

Posted: Sun Oct 30, 2011 11:41 pm
by newToThis
Hi,
I tried asking this question at another forum and received negative and quite contemptuous answers so I'll try to explain why I don't want to use an auto insert id field.

I have two databases to juggle: one is a subset of the other (the products on the website), while 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.

So, I want the web database to accept updated IDs from the master database. (I could have two 'ID' columns in the web database but it seems redundant).

My solution was to get the last number from a column of numbers (ID), 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.

I tried to use:
SELECT IDCOL FROM prods ORDER BY IDCOL DESC LIMIT 1
This works as SQL in phpMyAdmin, but once i write it as php...

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

//it 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) then...

$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, all i want is the last value from a column!
Thanks
Steve

Re: last number from a column of numbers?

Posted: Mon Oct 31, 2011 1:20 am
by manohoo
Just do a "SELECT column FROM table"... don't do any sorting, put results in an array:

Code: Select all

// $result is an array containing all column values from table, for example:
$result = array(3, 7, 99, 2);
// now get the last element in the array:
echo end($result); // 2

Re: last number from a column of numbers?

Posted: Mon Oct 31, 2011 2:30 am
by newToThis
Thanks manohoo,
while I was coding your solution, someone on the other site came up with this (pointing out that mysql_query returns a result resource, not the actual result.):

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

It works so I'm happy.
Thanks again
Steve

Re: last number from a column of numbers?

Posted: Mon Oct 31, 2011 10:06 am
by dsnraju
<?php

$res = mysql_query('SELECT IDCOL FROM prods ORDER BY IDCOL');

while($row = mysql_fetch_array($res))
{
$newid = $row['IDCOL'];
}
echo $newid;
$insertid = $newid + 1;

// insert this $insertid to your table
?>