mysql_query in PHP containing variables returning mysql error [duplicate]

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
– paskl, Commented Aug 1, 2015 at 18:24
See the manual, php.net/manual/en/function.mysql-query.php. mysql_query() sends a unique query (multiple queries are not supported) — chris85
– chris85, Commented Aug 1, 2015 at 18:28

Parash Nath Mishra · Accepted Answer · 2015-08-01 18:34:41Z

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 1, 2015 at 18:34

Parash Nath Mishra

636 bronze badges

answered Aug 1, 2015 at 18:32

Suresh KUMAR Mukhiya

6168 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

chris85 · Accepted Answer · 2015-08-01 18:29:45Z

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 1, 2015 at 18:29

chris85

23.9k7 gold badges36 silver badges51 bronze badges

answered Aug 1, 2015 at 18:29

Edu

2,7335 gold badges35 silver badges37 bronze badges

Collectives™ on Stack Overflow

mysql_query in PHP containing variables returning mysql error [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related