php - Can't insert array into MySQL

Question

if ($response_array[0] == 1) {
    $table = payments_received;
    function mysql_insert_array($table, $response_array) {
    foreach ($response_array as $field=>$value) {
        $fields[] = '`' . $field . '`';
        $values[] = "'" . mysql_real_escape_string($value) . "'";
    }
    $field_list = implode(',', $fields);
    $value_list = implode(', ', $values);

    $query = "INSERT INTO `" . $table . "` (" . $field_list . ") VALUES (" . $value_list . ")";
        if (!$query) { 
        $message = mysql_error();
        die($message);
    }
    }

    include('receipt.php');
}

Any ideas why this doesn't work? I know the condition at the top is satisfied because the script goes on to include receipt.php (bottom of code). I know that $response_array has data because of this as well (plus I use the data from it in the receipt). I get no error output at all despite the condition under $query (by the way, my MySQL connection info is specified at the top of the script by including config.inc.php which is in perfect working order). I hope I'm not missing something glaringly obvious.

UPDATE #1:

As several of you pointed out I didn't actually call mysql_query(as I feared I was missing some glaringly obvious stuff that goes to show that my brain is not functioning on a high level). I took Gus' edits and tried them, fixing the unbalanced braces and adding mysql_query() (as Frank mentioned). Here's what I have:


function mysql_insert_array($table, $response_array) {
    foreach ($response_array as $field=>$value) {
    $fields[] = '' . $field . '';
    $values[] = "'" . mysql_real_escape_string($value) . "'";
    }
    $field_list = implode(',', $fields);
    $value_list = implode(', ', $values);

$field_list = rtrim($fieldlist,",");
$value_list = rtrim($value_list,",");

$query = mysql_query("INSERT INTO `" . $table . "` (" . $field_list . ") VALUES (" . $value_list . ")");
if (!$query) { 
$message = mysql_error();
die($message);
}


}

if ($response_array[0] == 1) {
    $table = "payments_received";
    mysql_insert_array($table, $response_array);
    include('receipt.php');
} else {
    include('declined.php');
}

I get further and at least get an error code, which is...

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 '0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,' at line 1

...I know that the problem is the trailing comma but I keep running into this problem today and have yet to figure out how to get rid of that damn extra comma in a situation like this. Ideas?

Thank you all for your help so far.

UPDATE #2

Answered my own question. I used rtrim($field_list,",") and rtrim($value_list,",") with a successful result. See updates to the code in UPDATE #1.... Or not! I just realized when I was pasting the code above that I had typoed and forgot the underscore in field_list in one place... I added it back and tested again and it didn't work. Same error as the one above. However when the typo remains in place the code works and does exactly what I want. WTF!?

FINAL UPDATE

I did what I wanted with the code below. I added ` around $field on line 3 of the code below (I don't know what that character is called).


function mysql_insert_array($table, $response_array) {
    foreach ($response_array as $field=>$value) {
    $fields[] = "$field";
    $values[] = "'" . mysql_real_escape_string($value) . "'";
    }
    $field_list = implode(',', $fields);
    $value_list = implode(', ', $values);

$field_list = rtrim($field_list,",");
echo "$field_list <br />";
$value_list = rtrim($value_list,",");
echo "$value_list <br />";

$query = mysql_query("INSERT INTO `" . $table . "` ($field_list) VALUES (" . $value_list . ")");
if (!$query) { 
$message = mysql_error();
die($message);
}


}

if ($response_array[0] == 1) {
    $table = "payments_received";
    mysql_insert_array($table, $response_array);
    include('receipt.php');
} else {
    include('declined.php');
}

@Kerin Thanks. I made another update to my question as well. — Vincent
– Vincent, Commented Apr 13, 2011 at 5:17
without seeing the rest of your code I'd have to hazard the guess that the rtrim function where you dropped the underscore behaves unpredictably when fed undefined values. That's not a real guess and I'm not happy with it, but I'll have another look in the morning. Additionally, I note that in your implode() functions you're using ',' as delimiter in the first implode, and ', ' as delimiter for the second. And thirdly, I'd like to add that the whole rtrim thing is weird and unnecessary, just do implode() with '' as your delimiter and you'll get instant concatenated strings! — Winfield Trail
– Winfield Trail, Commented Apr 13, 2011 at 5:59

Winfield Trail · Accepted Answer · 2011-04-13 04:54:15Z

5

You haven't actually executed $query, like with mysql_query($query);

:)

Ryan's probably right too, but this is specifically why your query isn't working - as far as PHP is concerned, you've built the query and then not bothered to do anything productive with it.

Edit 1: Frank?! Man, way to show the love. :)

But I jest. You can do a couple of things; either use substr() to clip the trailing comma off the end and then append a ; in its place with normal string concatenation, or alternatively switch your do while loop for a for () loop and add an if-clause that selectively drops a "," or a ";" in based on whether your counter is equal to the size of the source array minus one.

I'd do the substr one, personally.

edited Apr 13, 2011 at 4:54

answered Apr 13, 2011 at 4:17

Winfield Trail

5,7032 gold badges31 silver badges45 bronze badges

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

6 Comments

Dave Over a year ago

Oh, RYAN is right, but no one else who mentioned it is, eh? ;) You and you're "you have to actually execute the query" - ugh. Maybe he didn't WANT to execute the query - maybe it was his intention to just write a pretty query so we could admire it.

Winfield Trail Over a year ago

Nobody else had mentioned it when I said that! I mean, that I saw. Either that or I'm a hateful jerk. Ask my wife.

Ryan Over a year ago

@Dave i got you by 21 seconds, fair and square!

Winfield Trail Over a year ago

I CANT TAKE ALL THIS FIGHTING MOMMY DADDY PLEASE STOP IT

Dave Over a year ago

@Ryan - 21 seconds and 1 down-vote later, and I'm off the island. :)

|

Ryan · Accepted Answer · 2011-04-13 04:16:16Z

0

Here is your problem:

$table = payments_received;

PHP is looking for a literal named payments_received; you probably meant:

$table = "payments_received";

answered Apr 13, 2011 at 4:16

Ryan

9052 gold badges6 silver badges25 bronze badges

5 Comments

Jon Gauthier Over a year ago

This only causes a PHP notice on my machine — the language assumes by payments_received that the intended value is "payments_received".

Ryan Over a year ago

also, as pointed out by Kerin, he isn't actually executing a query! each problem, by itself, is enough to prevent data from actually being inserted

Ryan Over a year ago

@Hans very curious! thanks for pointing that out. well, as long as he's not using spaces in his literal i guess he's ok :)

Jon Gauthier Over a year ago

Still, it's a language "feature" of PHP one should probably not rely on :)

Ryan Over a year ago

the most surprising part is that the error is only notice-level

Wes · Accepted Answer · 2011-04-13 04:19:24Z

0

The first problem is

$table = payments_received;

should be

$table = "payments_received";

The next improvement can be:

"INSERT INTO `" . $table . "` ("

should be

"INSERT INTO `$table` ("

leading to the point where you need to execute

$result = mysql_query($query)

answered Apr 13, 2011 at 4:19

Wes

6,6053 gold badges24 silver badges26 bronze badges

3 Comments

Ryan Over a year ago

that second point is a non-issue, because they would evaluate to the same thing. the real problem, as Kerin pointed out, is that he's not actually executing the query

Dave Over a year ago

Your "next problem" isn't a problem. What's wrong w/ the way he wrote it? I actually like his way better, since he's not putting a variable in a string.

Wes Over a year ago

@Ryan Updated as you were typing.

Gus · Accepted Answer · 2011-04-13 04:20:50Z

0

You made two main mistakes: you forgot to add "" on that 2nd line is the small one, and the main one is that you never actually execute the function. You have to define the function first, then you can execute it in your if() statement.

Hopefully this works for you:

function mysql_insert_array($table, $response_array) {
    foreach ($response_array as $field=>$value) {
    $fields[] = '' . $field . '';
    $values[] = "'" . mysql_real_escape_string($value) . "'";
    }
    $field_list = implode(',', $fields);
    $value_list = implode(', ', $values);


    $query = "INSERT INTO `" . $table . "` (" . $field_list . ") VALUES (" . $value_list . ")";
    if (!$query) { 
    $message = mysql_error();
    die($message);
}

if ($response_array[0] == 1) {
    $table = "payments_received";
    mysql_insert_array($table, $response_array);
}

include('receipt.php');

answered Apr 13, 2011 at 4:20

Gus

1,9336 gold badges26 silver badges37 bronze badges

3 Comments

Ryan Over a year ago

you also forgot that he doesn't ever execute the query :x

Frank Farmer Over a year ago

You're right that he declares mysql_insert_array() and never calls it (which the other answers to date have missed), but you forgot to call mysql_query, and you have unbalanced curly braces, so your code is a few tweaks away from working as well.

Vincent Over a year ago

Thanks Gus, Ryan, and Frank. I made some amendments but am still having issues. See Update #1 in the question.

Collectives™ on Stack Overflow

php - Can't insert array into MySQL

4 Answers 4

6 Comments

5 Comments

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

5 Comments

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related