Insert into mySQL from php foreach loop

Question

I know this has been asked so many times but I can't seem to figure the issue out. Im running the following code, but it just returns the fail. When I run the output of

$query1 --> INSERT INTO number (count,code,prize,printed) VALUES ('1','Q0stZr0g8uc4syE','','0');

straight into phpmyadmin it inserts fine. I must be doing something stupid... Any ideas?

$times_to_run = 100;
$prize='';

for($i=1;$i<=$times_to_run;$i++){

    $array[] = array(
            'count' => $i,
            'code1' => randomString(),
            'prize' => $prize
    );
}

$codes = $array;

foreach ($codes as $code){

    $query1 = "INSERT INTO number (count,code,prize,printed) VALUES ('". $code['count']."','". $code['code1']."','". $code['prize']."','0');";

    $q = mysqli_query($query1) or die (mysql_error());

}

EDIT: I changed the query to use mysqli and got the full error which is: the actual error is:

Warning: mysqli_query() expects at least 2 parameters, 1 given and the line it points to is: $q = mysqli_query($query1) or die (mysql_error());

** If I change

'". $code['prize']."'

to '0' I still get the same error.

If you would return the actual error you would know what the problem is. — Jay Blanchard, Feb 13 '15 at 19:03
Looks good. But why not use [one `INSERT` for all](https://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql)? — kero, Feb 13 '15 at 19:03
Also, your `or die ('FAIL - '.$query1)` should be changed to `or die(mysql_error())` to get the real error. — Funk Forty Niner, Feb 13 '15 at 19:17
As per your edit, the message is clear. It requires another parameter be passed. Read up on the function http://php.net/manual/en/mysqli.query.php - Plus, you can't mix `mysql_` with `mysqli_` APIs. You should show us more code and what API you're using to connect with. — Funk Forty Niner, Feb 13 '15 at 19:40
ah yes, you need to pass the connection as the first parameter -- the result you got from mysqli_connect(). See examples here: http://php.net/manual/en/mysqli.query.php — delatbabel, Feb 13 '15 at 19:42
I'd suggest that you learn about how to use prepared statements and bind parameters. It makes your SQL handling much easier without having to embed values in the statements you create. Plus, it leaves you safe from SQL injection attacks. — Andy Lester, Feb 13 '15 at 20:02
@AndyLester SQL Injection is not relevant here since all data are not coming from a form but are generated by the PHP script. But I agreed with you that prepared statements and bind parameters are better than embed values. — Fabien TheSolution, Feb 13 '15 at 21:12
I understand that SQL injection doesn't matter here. I'm just talking about good habits for the future. — Andy Lester, Feb 13 '15 at 22:07
Thanks everyone, your comments have been really helpful :) It's working now — Janey, Feb 13 '15 at 22:17

delatbabel · Accepted Answer · 2015-02-13T19:59:33.043

0

To make things clear, here is the corrected version of your code:

$link = mysqli_connect("localhost", "my_user", "my_password", "world");
$times_to_run = 100;
$prize='';

for($i=1;$i<=$times_to_run;$i++){

    $array[] = array(
            'count' => $i,
            'code1' => randomString(),
            'prize' => $prize
    );
}

$codes = $array;

foreach ($codes as $code){

    $query1 = "INSERT INTO number (count,code,prize,printed) VALUES ('". $code['count']."','". $code['code1']."','". $code['prize']."','0')";

    $q = mysqli_query($link, $query1) or die (mysqli_error($link));

}

edited Feb 13 '15 at 19:59

answered Feb 13 '15 at 19:26

delatbabel

3,601
24
29

The ";" is needed in the PHP version which is where the problem lies. – BigScar Feb 13 '15 at 19:32
1

Not according to the examples on this page it isn't: http://php.net/manual/en/mysqli.query.php – delatbabel Feb 13 '15 at 19:41

eldin zenderink · Answer 2 · 2015-02-13T19:15:59.703

-1

I might be wrong here but I think there are spaces needed after ] and after that . Like this:

'" . $code['count'] . "'

instead of

'". $code['count']."'

Oh and just something that might be handy and good to know, mysql_query is deprecated and unsafe, unles you are using it for yourself at a local host I would suggest you take a look at mysqli_, which works basicly the same.

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

edited Feb 13 '15 at 19:15

answered Feb 13 '15 at 19:08

eldin zenderink

85
9

3

Syntactically with or without the space it makes no difference. – delatbabel Feb 13 '15 at 19:26

Insert into mySQL from php foreach loop

2 Answers2