PHP array INSERT into MySQL failing

Question

Many posts similar to mine,none of them work.

Have an array $data['date'], $data['name'], $data['value'].

Trying to insert into MySQL table MyValues (Date, Name, Value)

Have tried 7-8 different methods, none working. Would like something like

for ($a=0;$a<10;$a++) {
    mysql_query("INSERT INTO MyValues('Date','Index_Name','Index')
       VALUES ($data['date'][$a] ,$data['name'][$a], $data['value'][$a])"
}

Have also tried foreach, building a single string to give to MySQL, etc.

Get this error Warning: mysql_error() expects parameter 1 to be resource, boolean given on line 45

[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://stackoverflow.com/a/14110189/1723893). — NullPoiиteя, Feb 01 '13 at 17:47
@span you can have a table named anything, but it may have to be written as `Values` in queries. — Explosion Pills, Feb 01 '13 at 17:51
Please don't call your table `values` and [escape your SQL injections](http://bobby-tables.com/php). You **cannot** insert arbitrary data into your query without escaping. — tadman, Feb 01 '13 at 18:09

score 3 · Answer 1 · edited May 23 '17 at 12:24

3

columnName shouldn't be wrap with single quotes as they are identifiers not string literals.

INSERT INTO `Values` (Date,Index_Name,Index) VALUES (....)

one more thing, the only identifier here that needs to be wrap with backtick is the tableName VALUES because it is a Reserved Keyword.

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

edited May 23 '17 at 12:24

Community

1
1

answered Feb 01 '13 at 17:48

John Woo

258,903
69
498
492

1

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 'Values(Date,Index_Name,Index) VALUES' on line 1 – Niet the Dark Absol Feb 01 '13 at 17:51
1

^ Issue resolved. Downvote removed. – Niet the Dark Absol Feb 01 '13 at 17:53
@Kolink I overlooked it. Thanks – John Woo Feb 01 '13 at 17:54

score 0 · Accepted Answer · answered Feb 01 '13 at 17:51

Since Values is a reserved word, you can't use it as is for a table name. You must use backticks to enclose it. Similarly, it is not valid to use single quotes to name columns, you need backticks there too.

Try this:

$out = Array();
$esc = "mysql_real_escape_string";
foreach($data['date'] as $k=>$v) {
    $out[] = "('".$esc($data['date'][$k])."', '".$esc($data['name'][$k])."', "
       ."'".$esc($data['value'][$k])."')";
}
mysql_query("INSERT INTO `Values` (`Date`, `Index_Name`, `Index`) values ".implode(",",$out));

I think you've just demonstrated why using `mysql_query` is a very bad idea. While clever, this is a giant mess. — tadman, Feb 01 '13 at 18:10

score 0 · Answer 3 · answered Feb 01 '13 at 18:14

0

try this, use $a++ not $ee++

    for ($a=0;$a<10;$a++) {
mysql_query("INSERT INTO `Values` (`Date`,`Index_Name`,`Index`)
   VALUES ('".$data['date'][$a]."' ,'".$data['name'][$a]."', '".$data['value'][$a]."' ")
 }

answered Feb 01 '13 at 18:14

echo_Me

37,078
5
58
78

Close, but *still* wrong... – Niet the Dark Absol Feb 01 '13 at 19:14

score -1 · Answer 4 · edited Jan 25 '17 at 17:10

First of all, just use PDO/mysqli with prepared statements so you wont ever have any issues like this.

This will solve it though (column names with back-ticks instead of single quotes, and escaped data):

for ($a=0;$a<10;$a++) {
    mysql_query("INSERT INTO `Values` (`Date`,`Index_Name`,`Index`)
       VALUES ('".mysql_real_escape_string($data['date'][$a])."' ,
'".mysql_real_escape_string($data['name'][$a])."', 
'".mysql_real_escape_string($data['value'])[$a]."'");
}

And try to avoid reserved names for your columns like indexand values.

score -1 · Answer 5 · edited Nov 01 '15 at 12:42

-1

First, I believe you want your query values quoted, so the result is 'value' and not just value. Example:

mysql_query("INSERT INTO Values(Date,Index_Name,Index) VALUES ('$data['date'][$a]' ,'$data['name'][$a]', '$data['value'][$a]');

If you are doing multiple queries, do something like:

$q = "INSERT INTO Values(Date,Index_Name,Index) VALUES ";
for {
  // Add to the string here for each insert item
}
mysql_query($q);

Additionally, please start phasing out PHP's mysql_* library in favor of mysqli or PDO.

edited Nov 01 '15 at 12:42

marc_s

732,580
175
1,330
1,459

answered Feb 01 '13 at 17:48

Nicholas Yost

266
6
15

I just realized you put the table name of Values. Why would you name a table Values? It's a reserved word in MySql to the best of my knowledge. Try changing the name of the table if all else fails. – Nicholas Yost Feb 01 '13 at 17:57
Why are you down voting everyone? Sure, we may not hand you code on a silver platter, but please at least indicate WHY we have down votes so we can try to resolve them... – Nicholas Yost Feb 01 '13 at 18:04
sorry noob. hitting wrong buttons -> down vote. Not intentional! sorry – user2033337 Feb 01 '13 at 18:31
That's fine. Would you mind unvoting us down? Also, if my answer doesnt work, please tell me any errors or problems you have with it. – Nicholas Yost Feb 01 '13 at 18:40

score -2 · Answer 6 · answered Feb 01 '13 at 17:49

-2

This works:

for ($a=0;$a<10;$a++) {
    mysql_query("INSERT INTO Values('Date','Index_Name','Index')
    VALUES ('".$data['date'][$a]."','".$data['name'][$a]."','".$data['value'][$a]."')"
}

answered Feb 01 '13 at 17:49

Roberto

528
7
15

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 'Values(Date,Index_Name,Index) VALUES' on line 1 – Niet the Dark Absol Feb 01 '13 at 17:52
This doesn't even come close to fixing the problem. Why do people always think it's string interpolation that's the issue? It's not. – tadman Feb 01 '13 at 18:11

PHP array INSERT into MySQL failing

6 Answers6