1

I try to execute a very simple query to delete one row. the "max_allowed_packet" equals "1073741824" that's why I'm confused why I get 

Error while sending QUERY packet. PID=2

I select just 25 rows from a table to send their data via sms and sleep(1) after deleting the row.

Query:

$stmt = $db->prepare("select * from sms limit 25");
$stmt->execute();

I loop through rows here and send row data via sms then delete it

$q = $db->prepare("delete from sms where id = '$r[id]'");
$q->execute();
sleep(1)

But sometimes (just sometimes not every time) I get that error and row doesn't get deleted so the sms message is sent so many times to the same user because it's still in the table. Any ideas to solve this?

Thanks in advance.

PHP User
  • 2,350
  • 6
  • 46
  • 87
  • Side note not related to the problem but usefull information: SQL is a unsordered set of data.. SQL also doesnt guarantee running the query `select * from TABLE limit 25` would give the same results without using ORDER BY.. – Raymond Nijland Jul 07 '18 at 11:23
  • What are you trying the do precisely.. Because you "obfuscated"the tables names it's hard to see if you use one table or two tabels. Provide table structure(s), some example records place it on sql-fiddle.com or db-fiddle.com also we need the expected results as a ascii (text formatted) table also more PHP code would be nice. – Raymond Nijland Jul 07 '18 at 11:28
  • The same table select then delete – PHP User Jul 07 '18 at 12:43
  • Why not simply use `DELETE FROM sms ORDER BY id LIMIT 25` ? – Raymond Nijland Jul 07 '18 at 12:46
  • Because I delete the row if the sms gateway responds with success otherwise I don't delete the row @Raymond Nijland – PHP User Jul 07 '18 at 12:54

1 Answers1

2

When you use a pattern like

prepare mysql query a
execute mysql query a
for each row in result set a
    prepare mysql query b
    execute mysql quer b

you must use a separate db connection for mysql query b. Why? query a can still be using its connection as long as you're reading rows from its result set.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • So you mean open connection prepare execute save id value in a parameter close connection open the same connection again prepare execute close connection again ?? – PHP User Jul 07 '18 at 12:06
  • open connection prepare and execute if message sent open a new connection prepare execute close inner connection continue loop – PHP User Jul 07 '18 at 14:38