Can't get my mysql_query() to work in my php script

Question

I am having troubles getting my php script to run via php in my LAMP server. Heres my code that I'm having issues with.

# Insert Statement
$insert     ="INSERT INTO ACRC VALUES('','','1C3-4A','$fs','$acrc1c3_4afs');
INSERT INTO ACRC VALUES('','','1C3-4A','$fi','$acrc1c3_4afi');
INSERT INTO ACRC VALUES('','','1C3-4A','$fo','$acrc1c3_4afo');
INSERT INTO ACRC VALUES('','','1C3-4B','$fs','$acrc1c3_4bfs');
INSERT INTO ACRC VALUES('','','1C3-4B','$fi','$acrc1c3_4bfi');
INSERT INTO ACRC VALUES('','','1C3-4B','$fo','$acrc1c3_4bfo');
INSERT INTO ACRC VALUES('','','1C3-12A','$fs','$acrc1c3_12afs');
INSERT INTO ACRC VALUES('','','1C3-12A','$fi','$acrc1c3_12afi');
INSERT INTO ACRC VALUES('','','1C3-12A','$fo','$acrc1c3_12afo');
INSERT INTO ACRC VALUES('','','1C3-12B','$fs','$acrc1c3_12bfs');
INSERT INTO ACRC VALUES('','','1C3-12B','$fi','$acrc1c3_12bfi');
INSERT INTO ACRC VALUES('','','1C3-12B','$fo','$acrc1c3_12bfo');";
echo "$insert";
mysql_query($insert) or die(mysql_error());
echo "$insert";
?>

Running this command returns the following result.

[root@enseva html]# php snmp.php
ACRC Unit 1C3-4A : Fan Speed 73.3 % : Fluid In Temperature 47.2 F : Fluid Out Temperature 55 F
ACRC Unit 1C3-4B : Fan Speed 74 % : Fluid In Temperature 47.8 F : Fluid Out Temperature 53.5 F
ACRC Unit 1C3-12A : Fan Speed 28.9 % : Fluid In Temperature 44.7 F : Fluid Out Temperature 46.4 F
ACRC Unit 1C3-12B : Fan Speed 28.5 % : Fluid In Temperature 47.1 F : Fluid Out Temperature 62.9 F
INSERT INTO ACRC VALUES('','','1C3-4A','Fan Speed','73.3');
INSERT INTO ACRC VALUES('','','1C3-4A','Fluid Temperature In','47.2');
INSERT INTO ACRC VALUES('','','1C3-4A','Fluid Temperature Out','55');
INSERT INTO ACRC VALUES('','','1C3-4B','Fan Speed','74');
INSERT INTO ACRC VALUES('','','1C3-4B','Fluid Temperature In','47.8');
INSERT INTO ACRC VALUES('','','1C3-4B','Fluid Temperature Out','53.5');
INSERT INTO ACRC VALUES('','','1C3-12A','Fan Speed','28.9');
INSERT INTO ACRC VALUES('','','1C3-12A','Fluid Temperature In','44.7');
INSERT INTO ACRC VALUES('','','1C3-12A','Fluid Temperature Out','46.4');
INSERT INTO ACRC VALUES('','','1C3-12B','Fan Speed','28.5');
INSERT INTO ACRC VALUES('','','1C3-12B','Fluid Temperature In','47.1');
INSERT INTO ACRC VALUES('','','1C3-12B','Fluid Temperature Out','62.9');You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO ACRC VALUES('','','1C3-4A','Fluid Temperature In','47.2');INSERT IN' at line 2

Now I've take then result from that and manually put it in my mysql and it worked as is although it did return 2 warning with each insert statement. The first value is an autoincrement id value and the second is a timestamp.

For a followup question my timestamp is returning all 0's.

Can you guys help with my question? I've been a long time lurker and this site has helped me much in the past.

[Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — Alex Gittemeier, Apr 03 '13 at 05:22

score 1 · Answer 1 · answered Apr 03 '13 at 05:24

mysql_query() does not allows you to execute multiple queries at once. You will have to execute all those one by one if you prefer to use the deprecated mysql_* extension

See this note from PHP Manual

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

Also that famous red box will say

This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used.

score 1 · Answer 2 · answered Apr 03 '13 at 05:26

1

Use Mysqli's multi_query function to run multi query.

mysql_* doesn't allow to run multi query.

answered Apr 03 '13 at 05:26

Yogesh Suthar

30,424
18
72
100

Do you have any good resources for Mysqli? I'm still fairly new to the php extensions. – Jonathon Harper Apr 03 '13 at 21:17

score 1 · Accepted Answer · answered Apr 03 '13 at 05:29

You cannot send the "INSERT INTO"s like this. But you can do the following:

$insert = "INSERT INTO ACRC VALUES 
('','','1C3-4A','$fs','$acrc1c3_4afs'),
('','','1C3-4A','$fi','$acrc1c3_4afi'),
('','','1C3-4A','$fo','$acrc1c3_4afo'),
('','','1C3-4B','$fs','$acrc1c3_4bfs'),
('','','1C3-4B','$fi','$acrc1c3_4bfi'),
('','','1C3-4B','$fo','$acrc1c3_4bfo'),
('','','1C3-12A','$fs','$acrc1c3_12afs'),
('','','1C3-12A','$fi','$acrc1c3_12afi'),
('','','1C3-12A','$fo','$acrc1c3_12afo'),
('','','1C3-12B','$fs','$acrc1c3_12bfs'),
('','','1C3-12B','$fi','$acrc1c3_12bfi'),
('','','1C3-12B','$fo','$acrc1c3_12bfo');";

As a reference: http://www.electrictoolbox.com/mysql-insert-multiple-records/

This worked for my needs now but as others suggested I need to learn mysqli for future needs. Thanks for your help. — Jonathon Harper, Apr 03 '13 at 21:27

nasonfish · Answer 4 · 2014-02-08T00:00:27.487

For your follow up questions, regarding the errors you receive: these would be caused by you using empty strings for what I assume is the id and timestamp in your query.

You'd want to include what rows you are inserting into in your INSERT query, and let the other values (like auto_incrementing ids) to fill themselves in.

For example, say your table has the rows: id, time, val1, val2, val3. Your current statement is like executing:

INSERT INTO ACRC (id, time, val1, val2, val3) VALUES('','','1C3-4A','$fi','$acrc1c3_4afi');

So MySQL is attempting to insert empty strings into the id and time, which then just inserts what is "empty" for that row, (so, for integers, it is 0). This tends to cause issues when you have rows that are supposed to be unique - auto_increment will do that for you if you don't try to insert something into the id column.

Instead, use:

INSERT INTO ACRC (val1, val2, val3) VALUES ('1C3-4A', '$fi', '$acrc1c3_4afi')

And omit the other rows to allow them to fill themselves in.

Can't get my mysql_query() to work in my php script

4 Answers4