PHP For Loop to build insert string

Question

I'm trying to insert several values into a database.

First I add these values to array (function/other code omitted):

$name = (isset($_POST['name']) ? ($_POST['name']) : "name");
$email = (isset($_POST['email']) ? ($_POST['email']) : "email"); 
$username = $mysqli->real_escape_string($_POST['username']);
$password = $mysqli->real_escape_string(md5($_POST['password']));

Like so:

$formvars = array();
array_push($formvars, $name, $email, $username, $password);

Then call function to insert:

    $sql = array(); 
        for ($i = 0; $i < count($formvars);$i++) {
            $sql[] = $formvars[$i];
        }

        print_r(implode(',', $sql));

        $qry = 'INSERT INTO chinesegame (name, email, username, password) VALUES '. implode(',' , $sql);

        if(!$mysqli->query($qry))
        {
            echo "Error inserting data to the table \nquery:$qry";
            return false;
        }        

    return true;

My DB structure is as such:

enter image description here

It's giving me an insert error:

Error inserting data to the table query:INSERT INTO chinesegame (name, email, username, password) Dan,dthusky@gmail.com,danman,827ccb0eea8a706c4c34a16891f84e7b

Can I not build an insert string like that?

Strings ought to be quoted in SQL context. Also have you considered bound parameters? — mario, Jan 02 '14 at 01:12
@mario I have trouble using those, so I went with real_escape_string. And can you elaborate on the string quotes in the SQL context — user3871, Jan 02 '14 at 01:13
Yes, `mysqli` is pretty awkward to use correctly. Check out [`pdo_query()`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php/20767765#20767765). — mario, Jan 02 '14 at 21:15

Damien Black · Accepted Answer · 2014-01-02T01:28:59.567

1

You forgot your "VALUES" keyword. and parenthesis help too. You also need all of the values to be in quotes, here is the correct version:

$qry = 'INSERT INTO chinesegame (name, email, username, password) VALUES ("'. implode('","' , $sql). '")';

Also, mysqli->query takes a connection variable first which has the connection information to the database, it should look like this:

$connection = mysqli_connect("localhost","my_user","my_password","my_db");
if(!$mysqli->query($connection, $qry))

Also, for the record,

    $sql = array(); 
    for ($i = 0; $i < count($formvars);$i++) {
        $sql[] = $formvars[$i];
    }

could be replaced with

$sql = $formvars;

there is no reason to loop through the whole thing getting them one at a time.

edited Jan 02 '14 at 01:28

answered Jan 02 '14 at 01:12

Damien Black

5,579
18
24

Okay oops haha. So it's still producing the insert error even with the quotes fixed – user3871 Jan 02 '14 at 01:17
Can you print $qry before it is run and paste it here? – Damien Black Jan 02 '14 at 01:19
Do I need to account for the autoincrement Primary key for the id? Also, print gives: INSERT INTO chinesegame (name, email, username, password) VALUES ("Dan","dthusky@gmail.com","danman","827ccb0eea8a706c4c34a16891f84e7b") – user3871 Jan 02 '14 at 01:20
1

print mysqli_error(); after you do the query in the if statement before you return. Copy that here. – Damien Black Jan 02 '14 at 01:25
The auto increment will work automatically, no worries there. – Damien Black Jan 02 '14 at 01:31
Yeah I've already included a connection php file at the top of this php file – user3871 Jan 02 '14 at 01:32
Well use that connection variable, it has to be sent to the mysqli->query function. – Damien Black Jan 02 '14 at 01:34

score 0 · Answer 2 · answered Jan 02 '14 at 01:10

0

It looks like you're missing a few things in the insert statement:

$qry = 'INSERT INTO chinesegame (name, email, username, password) '. implode(',' , $sql);

Try changing the insert statement to something like:

$qry = 'INSERT INTO chinesegame (name, email, username, password) VALUES ('. implode(',' , $sql).')';

answered Jan 02 '14 at 01:10

Mr. Meeseeks

1,841
2
21
37

1

The values need to be in quotes, I got you a correct version above. – Damien Black Jan 02 '14 at 01:17

PHP For Loop to build insert string

2 Answers2