Problems using max()

Anon

OK so I've search the forums on several sites and I've yet to find a solution to my problem.

I have a table where I wish to extract the highest value from an auto-incrementing field.

Sounds simple, and should be simple but I can't get it to work.

I've tried:
$result = mysql_query("SELECT MAX(call_id)FROM calls");
$r = mysql_fetch_array($result);
$found = $r[call_id];

But $found comes up empty.

I've tried using '$result = mysql_query("SELECT MAX(call_id) AS call_id FROM calls");' but still no joy. I also tried '$result = mysql_query("SELECT call_id, MAX(call_id) FROM calls");' but that gave me errors, any body have any ideas?

Thanks,

Nick Gushlow.

Anon

Scratch that it works now, and I don't know why. Using 'max() as' now produces the expected result. I didn't change anything else, but it definitely didn't work before!

Oh well at least it works now.

Anon

This is a MySQL problems anyway, contrary to popular beliefe, MySQL and PHP aren't one in the same.. :-)

Glad you found and fixed the problem though..

Anon

Now you see I would have said that this was a PHP problem. You see mysql handles max statements exactly as it should according to standard SQL (if run at the mysql command line); however you have to use a different statement when parsing via PHP.

Rather than just saying that the results of the max query is variable N, you have to define variable N within the select statement.

So there fore instead of the expected (and true to norm) way:

$N = mysql_query("SELECT MAX(call_id) FROM calls");

You have to do:

mysql_query("SELECT MAX(call_id) AS N FROM calls");

That's why I got a bit confused.

Anon

I've been wrestling with this same beastie, thanks for the 'AS' tip, but how did you pull the data out of the query result? What do you do with the 'N' afterwards?

Any help would be greatly appreciated!

gushy

You need to reference the data as if the N was the column name. SO for instance in the example below I replace N with the name thingy.

So you'd do something like:

$result = mysql_query("SELECT MAX(call_id) AS thingy FROM calls");

$row = mysql_fetch_array($result);

echo $row[thingy];

Hope that makes sense.

-- Nick Gushlow.

Anon

Thanks! Doing the happy Snoopy dance over here! I needed to get the product code so as to return search results on that particular product. I don't suppose you know of a one step method of retrieving that info, do you? I tried messing around with the SELECT statement and just couldn't get it do anything fancier so I could get it all done in one go. I could quite easily be overlooking something very simple and obvious. I DO need to use the MAX function for this code.

Thanks again for that help, you're my hero of the day! :o)

gushy

If you could supply more info as to table strucutures, required data, etc. I'll be glad to help.

-- Nick Gushlow

gryfon

Well, basically I'm just using the MAX function to find the newest added file (it will have the highest number). I was wondering if there was some way to pull ALL of the info for that file from the database using just the results of the MAX() search? I've tried the standard method of fetching the row into a variable and pulling the data out of the array with it's named key, but that doesn't seem to work with MAX. Does it just give us back that one result?

eg./ to get the file ID with MAX I did this (as per your suggestion):
$query = "SELECT MAX(file_num) AS NUM FROM " . DBTABLE ;

(...dbconnection stuff here...)

$data=mysql_fetch_array($queryResultHandle);

$file_num = $data["NUM"];

That is what you suggested, and it works well for retrieving the file number. But in order to get the rest of the info on the file (such as file_name, file_pic, file_num_downloads), I have to do another complete query to the database using $file_num as my search key to select * (the whole row).

$newquery = "SELECT * FROM " . DBTABLE . " WHERE file_num = '$file_num'";

Then I do the array thing again as per usual. Is there ANY way to access the other data relating to that file with the original result given by MAX()?

There are actually three files in total I'm trying to retrieve, two of which use the MAX function, and the third will use a random function. I forsee this making at least 4 queries to the database using the above method. Is there some slick method of using the max result to get that info? Or perhaps another way to combine the $file_num, $file_highest_download and $random_file into one query to give me the three separate resultant files? Geez, even I'm getting confused writing this, so I apologize in advance if you get confused reading it!

As it stand right now, it IS doing everything I want it to do, I just wanted to cut down the number of queries if possible.

Many, many thanks! :-)

gushy

Sorry I haven't replied sooner Fara, been a bit busy. Bloody clients, who do they think they are giving me work?! :-)

The easy way to do it would be with sub-selects, but MySQL doesn't support them, so I think you are stuck doing it with seperate queries, but then you seem to want 3 seperate unrelated result sets anyway (highest id, downloads and rand), so this is probably the best and easiest way.

I'll have athink about it, if I can come up with a better solution, I'll let you know.

-- Nick Gushlow

gryfon

Doggone clients, anyways!

LOL! Thanks Nick, I'd come to the same realization that MySQL doesn't support sub-selects (found that in some documentation somewhere). I DO appreciate the attempt to help, though. Hey, when I'm done this monster, I'll send you the url.

Have a happy holiday season!
Fara

gushy

You will be pleased to know that sub-selects are planned for the next version of MySQL.

I'm gonna spend some time today having a crack at your problem and seeing if I can think of a solution, I'll let you know if I have success.

Why is it always more motivating and fun solving someone else's problem than it is doing your own work? And why doens't the boss understand?!

Happy Holidays!

-- Nick