I have a db with a table. Take for example "un_names". Table "registered_names". Everything is clear. Someone is registered, he is in the table "un_names", after confirmation in "registered_names". Here I use such a query, to determine who bounced, we take a request for a random sample.

We insert into the "registered_names" 1 a random name from "un_names":

INSERT INTO registered_names SELECT DISTINCT * FROM un_names WHERE un_name.name = name ORDER BY RAND( ) LIMIT 1

We infer who bounced:

 echo $row["name"]." Перенесен ";

And delete from the old table:

 DELETE FROM un_names WHERE id = un_name.name = name

Php code

 $stmt = $db->query(' INSERT INTO registered_names SELECT DISTINCT * FROM names WHERE names.name= name ORDER BY ORDER BY RAND( ) LIMIT 1 '); echo $row["name"]." Перенесен "; $stmt = $db->query(' DELETE FROM names WHERE names.name= name ');

Please tell me why it does not work? I get this error

  • SQLSTATE [HY000]: General error
  • 1) do you have one server or is it replicated? If it is replicated, it is better not to use INSERT ... SELECT at all ... 2) You have these queries wrapped in a transaction. 3) Can not cite the full text of the error? - cheops

1 answer 1

I understand that the value of the field for which operations are made is in the variable $row["name"] . But it is not used in the database queries. What most likely mysql notifies in its error messages.

The variable must be passed to the request. And it is better to do this as follows.

 $stmt = $db->prepare('INSERT INTO registered_names SELECT DISTINCT * FROM names WHERE names.name=? ORDER BY ORDER BY RAND() LIMIT 1'); if ( $stmt->execute(array($row["name"])) ) { echo $row["name"]." Перенесен "; $stmt = $db->prepare('DELETE FROM names WHERE names.name=?'); $stmt->execute(array($row["name"])); }