[SOLVED]Getting max(column_value) into a PHP variable

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

[SOLVED]Getting max(column_value) into a PHP variable

Post by mikebr »

There is probably a very simple answer to this one but I have tried many versions and can't seem to get the value of the ID (auto_increment) column, the query runs ok but I can't get the value returned.

PHP and MySQL, heres the code I am using...

Code: Select all

<?php
$query="SELECT MAX(item_ID) AS item_ID FROM _account WHERE client = '$client' AND service='$service' LIMIT 1";

if (!mysql_query($query,$connection)) {
		echo "Error $query";
		exit;
		} else {
				
				if($num_rows == 1) {
				$row = mysql_fetch_array($query_result);
				$item_ID = $row["item_ID"];
				echo "num_rows=$num_rows<br />item_ID=$item_ID";
				}
				
		}
?>
num_rows returns 1 but $item_ID is empty, I have also tried the following query...

Code: Select all

<?php
$query="SELECT MAX(item_ID) FROM _account WHERE client = '$client' AND service='$service' LIMIT 1";
?>
but $item_ID still appears to be empty.

I am totally at a loss here, and I can't seem to find information that goes beond the query which makes me think it is getting the row that is the problem.

Thanks in advance
:cry:
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

I'm not sure how num_rows can be displayed ok as i don't see where you set it, and also you are doing LIMIT 1, so there will only ever be 0 or 1 rows returned. Try this :

Code: Select all

<?php 
$query="SELECT MAX(item_ID) AS item_ID FROM _account WHERE client = '$client' AND service='$service' LIMIT 1"; 
$result = mysql_query($query) or die(mysql_error());
if(mysql_num_rows($result) > 0){            
    $row = mysql_fetch_array($result); 
    echo 'item_ID='.$row['item_ID']; 
} else {
    echo 'No rows found to display';
}
?>
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: Getting max(column_value) into a PHP variable

Post by timvw »

mikebr wrote:.

$query="SELECT MAX(item_ID) AS item_ID FROM _account WHERE client = '$client' AND service='$service' LIMIT 1";
make that

$query = "SELECT MAX(item_ID) AS item_ID FROM _account WHERE client='{$client}' AND SERVICE='{$service}'";

In the manual there is some stuff about variable expansion in strings...
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

You'de only need to do that with arrays really, e.g
SELECT * FROM foo WHERE name='{$_POST['name']}'";
but
SELECT * FROM foo WHERE name='$name'" is fine.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

markl999 wrote:You'de only need to do that with arrays really, e.g
SELECT * FROM foo WHERE name='{$_POST['name']}'";
but
SELECT * FROM foo WHERE name='$name'" is fine.
true ;)

But the op has also:

echo "num_rows=$num_rows<br />item_ID=$item_ID";

Guess where the problem is? ;))
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

That's fine too :o
It would be a problem if it was ...
echo 'num_rows=$num_rows<br />item_ID=$item_ID';
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

markl999
I'm not sure how num_rows can be displayed ok as i don't see where you set it, and also you are doing LIMIT 1, so there will only ever be 0 or 1 rows returned. Try this :
I took it out along with my comments without realising it.
The code you posted sorted my problem, I never realised it passed the value as an Array.

Thanks for the help from you both.
Post Reply