asked on

Help with PHP, MYSQL query to get MAX ID of a Table

I need a little help figuring out how to get the max id of a table. My Table has Column ID set to AUTO_INCREMENT and is the Primary Keyname. I need to get the MAX ID and assign variables to some of the columns on that row.

The following successfully give me the value of "Column3" & "Column4" Where ID is 10.

$con=mysqli_connect("host","user","pass","MY_DB");
$result = mysqli_query($con,"SELECT * FROM Table WHERE ID='10'");
$row1 = mysqli_fetch_array($result);
$C3 = $row1['Column3'];
$C4 = $row1['Column4'];
echo $C3;
echo $C4;

Open in new window

But when I try to select the MAX ID my page is blank. I have tried several examples I found online and nothing seems to work.

$con=mysqli_connect("host","user","pass","MY_DB");
$result = mysqli_query($con,"SELECT * FROM Table WHERE ID='(SELECT MAX(ID) FROM Table)'";
$row1 = mysqli_fetch_array($result);
$C3 = $row1['Column3'];
$C4 = $row1['Column4'];
echo $C3;
echo $C4;


$con=mysqli_connect("host","user","pass","MY_DB");
$result = mysqli_query($con,"SELECT * FROM Table WHERE ID=MAX(ID)";
$row1 = mysqli_fetch_array($result);
$C3 = $row1['Column3'];
$C4 = $row1['Column4'];
echo $C3;
echo $C4;

Open in new window

Can someone show me the correct way to get the Max ID?

SOLUTION

Richard Davis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Haris Dulic

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mlsbraves

ASKER

Both give me a blank page.

$con=mysqli_connect("host","user","pass","MY_DB");
$result = mysqli_query($con,"SELECT row FROM table ORDER BY id DESC LIMIT 1");
$row1 = mysqli_fetch_array($result);
$C3 = $row1['Column3'];
$C4 = $row1['Column4'];
echo $C3;
echo $C4;

Open in new window

$con=mysqli_connect("host","user","pass","MY_DB");
$result = mysqli_query($con,"SELECT * FROM Table WHERE ID=(SELECT MAX(ID) FROM Table)";
$row1 = mysqli_fetch_array($result);
$C3 = $row1['Column3'];
$C4 = $row1['Column4'];
echo $C3;
echo $C4;

Open in new window

Haris Dulic

Hi,

can you check like this and then see if there is anything in that last row?

$con=mysqli_connect("host","user","pass","MY_DB");
$result = mysqli_query($con,"SELECT * FROM Table WHERE ID=(SELECT MAX(ID) FROM Table)";
$row1 = mysqli_fetch_array($result);
$C3 = $row1['Column3'];
$C4 = $row1['Column4'];
$C5 = $row1['ID'];
echo $C3;
echo $C4;
echo $C5;

Open in new window

Richard Davis

Sounds like you have something wrong with your data. Perhaps an empty row?
Have you tried my suggestion I originally posted also yet?

Brian Tao

samo4fun's code should work, except that it's missing the closing ")" in line 2. So, by copying and modifying his code:

$con=mysqli_connect("host","user","pass","MY_DB");
$result = mysqli_query($con,"SELECT * FROM Table WHERE ID=(SELECT MAX(ID) FROM Table)");
$row1 = mysqli_fetch_array($result);
$C3 = $row1['Column3'];
$C4 = $row1['Column4'];
echo $C3;
echo $C4;

Open in new window

Please try.

mlsbraves

ASKER

Ok just tried this on a new DB I created and I'm getting the same result and the row is not empty.

DB Name: TEST_DB
Table: my_data

When I use the following I get the correct results for the variables:

<?php
$con=mysqli_connect("host","user","password","TEST_DB");
$result = mysqli_query($con,"SELECT * FROM my_data WHERE ID='5'");
$row1 = mysqli_fetch_array($result);
$C3 = $row1['Status'];
$C4 = $row1['Money'];
$C5 = $row1['ID'];
echo $C3;
echo $C4;
echo $C5;
?>

Open in new window

But the other two codes give me a black page:

<?php
$con=mysqli_connect("host","user","password","TEST_DB");
$result = mysqli_query($con,"SELECT row FROM my_data ORDER BY id DESC LIMIT 1");
$row1 = mysqli_fetch_array($result);
$C3 = $row1['Status'];
$C4 = $row1['Money'];
$C5 = $row1['ID'];
echo $C3;
echo $C4;
echo $C5;
?>

Open in new window

<?php
$con=mysqli_connect("host","user","password","TEST_DB");
$result = mysqli_query($con,"SELECT * FROM my_data WHERE ID=(SELECT MAX(ID) FROM my_data)";
$row1 = mysqli_fetch_array($result);
$C3 = $row1['Status'];
$C4 = $row1['Money'];
$C5 = $row1['ID'];
echo $C3;
echo $C4;
echo $C5;
?>

Open in new window

Haris Dulic

can you do this sql directly in the database and post the result .. if any

ASKER CERTIFIED SOLUTION

Brian Tao

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Haris Dulic

Nice catch ;) Missed that parentheses twice...:)

mlsbraves

ASKER

Thanks for all the help. Both Codes work after reading taoyipai post. I was thinking row was apart of the statement for some reason lol. Once I put the code in NetBeans it quickly showed me the syntax error as well.

Richard Davis

I posted what I did because I figured I didn't need to explain that it was meant to be pseudo code and that the author needed to modify the query to fit his/her needs. Lesson learned here, "never assuming anything".

Ray Paseur

The "blank page" is a symptom of a misconfigured PHP installation. When you're in a deployed environment it might make sense, but it makes no sense at all for a development environment. You want to be able to see what you're doing. Look into error_reporting(), log_errors() and display_errors(). These are all documented at http://php.net and once you have the right settings you will be able to catch and correct errors much more easily!