-3

hi guys I am trying to run multiple queries thru mySQL and php and have not so far been able to figure it out... I read that mysqli_multi_query might be what I want and tried it as well with only the first query being run because I had them in this format. "query";"query";"query" etc...so my last attempt was like this "query;query;query" etc and nothing happens can someone please figure out the syntax to get all 4 queries to run consecutively. here is the queries first then the php code.

INSERT INTO queue (SELECT NULL,item1, log, Active FROM shipping WHERE Active = 1);
DELETE FROM shipping ORDER BY id ASC LIMIT 1;
UPDATE shipping SET Active ='0' ORDER BY id DESC LIMIT 1;
UPDATE shipping SET Active ='1' ORDER BY id ASC LIMIT 1;

and here is the php..

<?php

$servername = "localhost";
$database = "logistics";
$username = "root";
$password = "";

$conn = mysqli_connect($servername, $username, $password, $database);

 if (!$conn) {

die("Connection failed: " . mysqli_connect_error());
}

$sql = "INSERT INTO queue (SELECT NULL,item1, log, Active FROM shipping WHERE Active = 1);
   DELETE FROM shipping ORDER BY id ASC LIMIT 1;
    UPDATE shipping SET Active ='0' ORDER BY id DESC LIMIT 1;
    UPDATE shipping SET Active ='1' ORDER BY id ASC LIMIT 1"


 if (mysqli_multi_query($conn, $sql)) {
  echo "New record created successfully";
 } else {
  echo "Error: " . $sql . "<br>" . mysqli_error($conn);
  }

?>

Thanx in advance.

Jon
  • 115
  • 1
  • 9
  • ` UPDATE shipping SET Active ='0' ORDER BY id DESC LIMIT 1; UPDATE shipping SET Active ='1' ORDER BY id ASC LIMIT 1"` what is the point in this? – treyBake Aug 08 '18 at 09:24
  • 2
    UPDATE does not have a ORDER BY or LIMIT ????? Correct your SQL syntax – RiggsFolly Aug 08 '18 at 09:25
  • You also need to look up how to look for and react to potentially 4 success messages or an error being returned from one of these multi queries. – RiggsFolly Aug 08 '18 at 09:31
  • I would suggest you do this inside a TRANSACTION as 4 seperate single queries – RiggsFolly Aug 08 '18 at 09:32
  • I guess what I was asking since every one is complaining about my queries instead of what I was asking was how to run multiple queries regardless of what they think the point of it is. – Jon Aug 08 '18 at 09:41

1 Answers1

1

Other than the wrong syntax in UPDATE statement as already commented, you should consider pulling those SQL code into a Stored Procedure and call that procedure from your client code instead like

create procedure usp_doSome
as
begin
INSERT INTO queue (SELECT NULL,item1, log, Active FROM shipping WHERE Active = 1);
DELETE FROM shipping ORDER BY id ASC LIMIT 1;
UPDATE shipping SET Active ='0' ORDER BY id DESC LIMIT 1;
UPDATE shipping SET Active ='1' ORDER BY id ASC LIMIT 1;
end

Well on how to call a stored procedure from php, there are many example present. See below

http://www.mysqltutorial.org/php-calling-mysql-stored-procedures/

How to call a MySQL stored procedure from within PHP code?

Rahul
  • 76,197
  • 13
  • 71
  • 125
  • If you can add a comment as to how to call a (the) Stored Procedure from PHP that would be great. – Martin Aug 08 '18 at 09:31
  • Thanks @Rahul will look into that. I was hoping for a simpler answer like syntax adjusted but this is a starting point...real noob at all this thanx again. – Jon Aug 08 '18 at 09:51