mysql_query in PHP containing variables returning mysql error

Question

My code, which archives old members from a membership database, when run, returns

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DELETE FROM members WHERE member_ref = 155' at line 5.

It should copy the entry to the identical 'archive' table from 'members' and delete the original in members.

$ref = '155';

$leave = mysql_query("  INSERT INTO archive 
                        SELECT *
                        FROM members
                        WHERE member_ref = ".$ref.";
                        DELETE FROM members
                        WHERE member_ref = ".$ref ) or die(mysql_error());

I know I should be using mysqli but I haven't had time to standardise the whole system (~120 pages) from mysql to mysqli.

It's parsing 155 into the second $ref

Using the phpmyadmin's SQL window and entering the following works perfectly.

INSERT INTO archive 
                        SELECT *
                        FROM members
                        WHERE member_ref = 155;
                        DELETE FROM members
                        WHERE member_ref = 155

What am I missing?

You cannot have two seperate queries in one statement. Unless you specify a different separator than the default one (`;`). Its best practice however to have one statement for one job. — paskl, Aug 01 '15 at 18:24
See the manual, http://php.net/manual/en/function.mysql-query.php. `mysql_query() sends a unique query (multiple queries are not supported)` — chris85, Aug 01 '15 at 18:28

score 2 · Answer 1 · edited Aug 01 '15 at 18:34

2

Use Separate Call, like this, See Details About mysql_query

$leave = mysql_query("  INSERT INTO archive 
                        SELECT *
                        FROM members
                        WHERE member_ref = '$ref' ") or die(mysql_error());

$leave = mysql_query("  DELETE FROM members
                        WHERE member_ref = '$ref'") or die(mysql_error());

edited Aug 01 '15 at 18:34

Parash Nath Mishra

63
6

answered Aug 01 '15 at 18:32

Suresh KUMAR Mukhiya

598
7
24

score 0 · Answer 2 · edited Aug 01 '15 at 18:29

0

If you go to the PHP Manual, it is written right on the first line:

mysql_query() sends a unique query (multiple queries are not supported)

http://php.net/manual/en/function.mysql-query.php

Therefore you should put your queries in separate mysql_query() calls.

edited Aug 01 '15 at 18:29

chris85

23,846
7
34
51

answered Aug 01 '15 at 18:29

Edu

2,354
5
32
36

mysql_query in PHP containing variables returning mysql error

2 Answers2