?
Solved

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

Posted on 2014-10-13
13
Medium Priority
?
3,032 Views
Last Modified: 2014-10-14
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?
0
Comment
Question by:mlsbraves
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 12

Assisted Solution

by:Richard Davis
Richard Davis earned 400 total points
ID: 40379099
Have you considered this route?

SELECT row FROM table ORDER BY id DESC LIMIT 1;

Open in new window

1
 
LVL 15

Assisted Solution

by:Haris Djulic
Haris Djulic earned 800 total points
ID: 40379100
Can you try like this :

$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

0
 
LVL 3

Author Comment

by:mlsbraves
ID: 40379119
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

0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40379129
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

0
 
LVL 12

Expert Comment

by:Richard Davis
ID: 40379135
Sounds like you have something wrong with your data. Perhaps an empty row?
Have you tried my suggestion I originally posted also yet?
0
 
LVL 9

Expert Comment

by:Brian Tao
ID: 40379173
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.
0
 
LVL 3

Author Comment

by:mlsbraves
ID: 40379175
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

DB-Structure
DB-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

0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40379183
can you do this sql directly in the database and post the result .. if any
0
 
LVL 9

Accepted Solution

by:
Brian Tao earned 800 total points
ID: 40379185
Did you read my comment at http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28536952.html#a40379173 ?

The problem in adrian_brooks's comment (your first try) was that you don't have a "row" column in the table and he was just giving you an example on SQL but you directly copied and pasted it.

The problem in samo4fun's comment (your second try) was that you were missing the end parentheses ")" in the line
$result = mysqli_query($con,"SELECT * FROM my_data WHERE ID=(SELECT MAX(ID) FROM my_data)";

Open in new window

and it should be
$result = mysqli_query($con,"SELECT * FROM my_data WHERE ID=(SELECT MAX(ID) FROM my_data)");

Open in new window


Good luck!
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40379187
Nice catch ;) Missed that parentheses twice...:)
0
 
LVL 3

Author Closing Comment

by:mlsbraves
ID: 40379202
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.
0
 
LVL 12

Expert Comment

by:Richard Davis
ID: 40379209
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".
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40379662
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!
0

Featured Post

WordPress Tutorial 3: Plugins, Themes, and Widgets

The three most common changes you will make to your website involve the look (themes), the functionality (plugins), and modular elements (widgets).

In this article we will briefly define each again, and give you directions on how to install them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question