Multiple Inserts PHP query

[xavcor75]

Hi, I'm using php script referenced in Connect Android to MySQL Database Tutorial to perform querys to a remote mysql database and works fine, but I need also insert multiple records into the database so I used the same php script to send multiple "INSERT" commands, but it wont work for all the records. The code I'm using is something like this:

 Sub CmdInserts_Click
   Dim numero As Int 
   Dim Fecha As String , CodCliente As String , CodVendedor As String , Total As Float , Iva As Float 
   Dim Detalle As String , Estado As Int , Aprobado As Int 
   
   HCCabeceraPedidos.Initialize ("HCCabeceraPedidos")
   For i=0 To 1000
      numero=i   
      Fecha="2012-01-01"
      CodCliente="00000"
      CodVendedor="03"
      Total=0
      Iva=0
      Detalle="'prueba ingreso registro"
      Estado=0
      Aprobado=0
   
      SQL="Insert into pedidocab values('" & numero & "','" & Fecha & "','" & CodCliente & "','" & CodVendedor & "'," &Total & "," &Iva & ",'" &Detalle & "'," & Estado & "," & Aprobado & ")"
      Log(SQL)
      ExecuteRemoteQuery(SQL,i)
   Next 

Sub ExecuteRemoteQuery(Query As String, TaskId As Int)
    ' servertest.com is for example purpose only

   req.InitializePost2("http://www.servertest.com/querydat.php", Query.GetBytes("UTF8"))
       HCCabeceraPedidos.Execute(req, TaskId)
   End If
End Sub

Sub HCCabeceraPedidos_ResponseSuccess (Response As HttpResponse, TaskId As Int)
   Log("Insertado" & TaskId)
End Sub

The log shows this:

Insert into pedidocab values('0','2012-01-01','00000','03',0,0,'prueba ingreso registro',0,0)
Insert into pedidocab values('1','2012-01-01','00000','03',0,0,'prueba ingreso registro',0,0)
Insert into pedidocab values('2','2012-01-01','00000','03',0,0,'prueba ingreso registro',0,0)
.
.
.
Insert into pedidocab values('1000','2012-01-01','00000','03',0,0,'prueba ingreso registro',0,0)

But the log in the responsesuccess event only show this:
Inserted 1
Inserted 5
Inserted 3
Inserted 2
Inserted 0
Inserted 6
Inserted 52
Inserted 54
** Activity (main) Pause, UserClosed = false **
sending message to waiting queue (hccabecerapedidos_responsesuccess)
sending message to waiting queue (hccabecerapedidos_responsesuccess)
sending message to waiting queue (hccabecerapedidos_responsesuccess)
sending message to waiting queue (hccabecerapedidos_responsesuccess)
sending message to waiting queue (hccabecerapedidos_responsesuccess)
running waiting messages (5)
(*** after a while ****)
Inserted 7
Inserted 62
Inserted 48
Inserted 63
Inserted 65
** Activity (main) Pause, UserClosed = false **
sending message to waiting queue (hccabecerapedidos_responsesuccess)
sending message to waiting queue (hccabecerapedidos_responsesuccess)
sending message to waiting queue (hccabecerapedidos_responsesuccess)
sending message to waiting queue (hccabecerapedidos_responsesuccess)
sending message to waiting queue (hccabecerapedidos_responsesuccess)
running waiting messages (5)
(*** after a while ****)
Inserted 8
Inserted 67
Inserted 57
Inserted 68
Inserted 70

The Mysql Table database query show this (image below).


How can i fix this? I need insert properly all the records in the remote mysql database (the order is not a problem) and in a reasonable time.

Thanks in advanced.

 

[Penko]

Normally, in PHP, you have to put ";" after each insert if you are going to execute multiple ones.

Haven't checked the Server-side code, try this first.

Edit:
But now I read that only part of your inserts fail. Then, is it possible that you do not use the quotes properly in some of the queries? You know it but all strings should be surrounded by '. Double(Int) values do not need quotes. Can you print all the queries on the server side(store them to a file) and try to manually execute them via PHPMyAdmin for example?

Are you checking if the query has been successfully executed and if not, do you store the mysql_error() value?

What you need to do is understand what is the reason for certain records not to get saved to the table.

 

[xavcor75]

Thanks for you response, the php code i'm using is posted in 'Connect Android to MySQL Database Tutorial', and when I send multiple inserts in only one string (insert into...; insert into...;insert into...etc) I get a "sql syntax " error. I read in the forum that the php script not support multiple inserts in only one command and you need to call the php query multiple times.

On other hand, all the sentences are the same since all them are in a for loop with the same syntax and are correctly structured and I don't have any errors. The file option is not a valid solution for my app because all the process must be automatic.

thanks

 

[Roger Garstang]

PHP has some Multi Query Functions (mysqli_multi_query {Not sure if it or you have used the mysqli side, but it is better than the normal mysql commands}) that will take multiple commands.

 

[xavcor75]

Thanks Roger, I'm not familiar with php but I could modify the php script to use mysqli commands and now the script can execute multiple update/ insert queries on a single connection. I post the modified php script below. Thanks.

<?

$databasename = "xxxx";
$databaseusername ="xxxx";
$databasepassword = "xxxx";

$mysqli = new mysqli ("localhost", $databaseusername , $databasepassword , $databasename );

if (mysqli_connect_errno()) {
    header("HTTP/1.1 500 Internal Server Error");
    echo $query.'\n';
    echo mysqli_connect_error();
    exit();
}

$query = file_get_contents("php://input"); 

if ($mysqli->multi_query($query)) {
    do {
        /* almacenar primer juego de resultados */
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                print json_encode($row[]);
            }
            $result->free();
        }
    } while ($mysqli->next_result());
}

/* cerrar conexión */
$mysqli->close();
?>

 

[Mahares]

I tried to use the PHP code in the previous post since its author 'xavcor75' claims it works, but I got the following error:
Fatal error cannot use [] for reading in line :
print json_encode($row[]);
I removed the 2 brackets and reran it. This time the error is: 500 Internal Server error.
Could someone with PHP knowledge, please check the code and determine its accuracy, particularly when the author said it worked for him.
Thank you.

 

[Mahares]

mc73 notes: True. One should use the mysqli multiple query functionality

@mc73: I have tried the code from 'xavcor75' post which uses the multiple query, but it does not work despite the issuer claims it works. With your expertise, please take a look at it yourself or someone else with PHP knowledge.
Thank you

 

[mc73]

@mc73: I have tried the code from 'xavcor75' post which uses the multiple query, but it does not work despite the issuer claims it works. With your expertise, please take a look at it yourself or someone else with PHP knowledge.
Thank you

I am not near a pc right now but when back, I'll post a php file for testing. Hope it'll help.