How to get max of an ID as a PHP variable and insert it into another table where the ID is also max

Question

I have two tables, Requests & Accounting_Fundscenter_Request

I'm creating a SQL query in PHP that updates

Request_ID from Accounting_Fundscenter_Request WHERE ID is max
to
the max Request_ID from Requests

So far I have gotten the max(Request_ID) rom Requests, but I don't know how to take that value in php & sql and update the other Request_ID to equal that value.

Also, I cannot use the syntax "max(id)" because the "max" function will not work in my first query and I don't know why.

Here's what I have so far:

/* GET MAX ID FROM REQUESTS */
     $selectMaxID = 'SELECT Request_ID FROM Requests ORDER BY Request_ID DESC LIMIT 1';
     $maxIdResult = mysqli_query($conn, $selectMaxID); //run query



     if (mysqli_num_rows($maxIdResult) > 0) {  
        while($maxid = mysqli_fetch_assoc($maxIdResult)) {
           echo "Max Request ID: " . $maxid["Request_ID"]. "<br>";
        }   //echo result of 

      } 


     $insertFundsCenterMaxId = "INSERT INTO `Accounting_Fundscenter_Request` (

     `Request_ID`, 


     VALUES (


     $maxid["Request_ID"], 

     )


      WHERE MAX(`ID`);";


     /* RUN THE QUERY */
     $insertFundsCenterMaxId = mysqli_query($conn, $insertFundsCenterMaxId);

This does not work. Is there a way to fix this or maybe do it in one query?

EDIT: with your help I found the solution:

What do you mean by "does not work"? What happens instead? Is there any error message given? — Nico Haase, May 13 '19 at 15:52
I get an error like: mywebsite.com is currently unable to handle this request. HTTP ERROR 500 which can happen when my php is messed up. — Audrey, May 13 '19 at 15:53
If that happens, what have you tried to locate the error? On such a message, there is something written to your server's error log nearly all the time. If not, try to debug the problem by checking which line triggers that error — Nico Haase, May 13 '19 at 15:54
Try `VALUES ( '$maxid["Request_ID"]' )` in the insert statement. Note the single quotes and removed comma. — Anurag Srivastava, May 13 '19 at 15:55
This is definitely a php syntax problem because when I comment out the "bad" code, it works perfectly for everything else. — Audrey, May 13 '19 at 15:56
There is a trailing comma after `Request_ID` that should be removed as well. — Anurag Srivastava, May 13 '19 at 15:58

score 0 · Answer 1 · answered May 13 '19 at 16:13

You have many options here: You can fix the syntax error you have in you insert query execution like this:

$insertFundsCenterMaxIdQuery = sprintf('INSERT INTO Accounting_Fundscenter_Request (Request_ID) VALUES (%d)', $maxid["Request_ID"]);
/* RUN THE QUERY */
$insertFundsCenterMaxId = mysqli_query($conn, $insertFundsCenterMaxIdQuery);

This way you use string formatting to replace the variable instead of directly using $maxid["Request_ID"] in a string. Please replace %d with %s in case the Request_ID is supposed to be string/varchar.

Or you can follow another approach and just use one query to do the work like this:

INSERT INTO Accounting_Fundscenter_Request (Request_ID) 
SELECT MAX(Request_ID) FROM Requests

And just execute this query

This is great and the query works, but it does not insert where the Accounting_Fundscenter_Request Request_ID is max. Rather, it just creates a new row in the table and inserts only that. Do you know how to add that to the query? — Audrey, May 13 '19 at 17:09
Not sure I got your comment right, but if you want the RequestId in the table where you are inserting to be incremented, the best way to that is to setup auto_increment for that column. check the following page. https://stackoverflow.com/questions/4795382/mysql-add-auto-increment-to-primary-key — , May 13 '19 at 20:27

Nico Haase · Answer 2 · 2019-05-13T16:04:36.433

You're facing a syntax error in the update query:

$insertFundsCenterMaxId = "INSERT INTO `Accounting_Fundscenter_Request` (

 `Request_ID`, 

 VALUES (
  $maxid["Request_ID"], 
 )

 WHERE MAX(`ID`);";

Using the double quotes in that variable hiding in the VALUES part, you are ending the string contained in insertFundsCenterMaxId. Following it is a raw string containing Request_ID which cannot be parsed by PHP. That's simply invalid code.

To solve it, you could start using prepared statements. They will also help you to secure your application against SQL injection.

There is also a solution to the syntax error problem alone - but that will leave your application vulnerable. That's why I haven't included a fix for that, but by checking how to build strings you might find it on your own. But please, please do not use it for this problem. Please.

It still doesn't work but I appreciate the help with the syntax error! — Audrey, May 13 '19 at 16:01

How to get max of an ID as a PHP variable and insert it into another table where the ID is also max

2 Answers2