last number from a column of numbers?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
newToThis
Forum Newbie
Posts: 6
Joined: Tue Sep 13, 2011 1:44 am

last number from a column of numbers?

Post 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
User avatar
manohoo
Forum Contributor
Posts: 201
Joined: Wed Dec 23, 2009 12:28 pm

Re: last number from a column of numbers?

Post 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
newToThis
Forum Newbie
Posts: 6
Joined: Tue Sep 13, 2011 1:44 am

Re: last number from a column of numbers?

Post 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
dsnraju
Forum Newbie
Posts: 10
Joined: Sun Oct 30, 2011 3:39 am

Re: last number from a column of numbers?

Post 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
?>
Post Reply