CODEWITHSUNDEEP
phpmysqlsqlpdo
kmoney12
kmoney12

Reputation: 4490

What Is Wrong With This Particular PDO (SQL) Statement?

Very sorry to be asking one of these questions, but I have been trying for the past hour to figure this one out and I am just plain stuck.

I am trying to update a lot of columns at the same time. I am using PDO.

Some PHP code:

$db=db_connect();
$wid=$_POST['wid'];
$time=time();
$username=$_SESSION['s_username'];
$arr=explode("|", $_POST['idata']);
array_push($arr, $time,$username,$wid );

$statement = $db->prepare("UPDATE widgets SET file_id=?,delay=?,title=?,instructions=?,width=?,height=?,bg_color=?,bg_image=?,border-radius=?,offer_table_border_width=?,offer_table_border_color=?,offer_table_link_color=?,offer_table_link_hover_color=?,offer_table_bg1=?,offer_table_bg2=?,head_font_size=?,head_font_color=?,instructions_font_size=?,instructions_font_color=?,time=? WHERE username=? AND wid=?");
$statement->execute($arr);

var_dump($arr);

The code comes from an AJAX request in the format "var1|var2|var3..." etc. This information is split by the "|" delimiter and then a few variables are added, then the table should be updated.

Here is the result of the var_dump (this is what $arr equates to):

array(22) { [0]=> string(2) "40" [1]=> string(1) "0" [2]=> string(9) "My Title!" [3]=> string(16) "My Instructions!" [4]=> string(3) "700" [5]=> string(3) "500" [6]=> string(4) "#FFF" [7]=> string(27) "http://background-image.png" [8]=> string(1) "0" [9]=> string(1) "1" [10]=> string(7) "#81ABD6" [11]=> string(7) "#0588C6" [12]=> string(7) "#81ABD6" [13]=> string(4) "#FFF" [14]=> string(7) "#DDEEFF" [15]=> string(2) "24" [16]=> string(7) "#2779AA" [17]=> string(2) "18" [18]=> string(7) "#2779AA" [19]=> int(1349486490) [20]=> string(15) "myuser" [21]=> string(6) "2LvS4c" }

And once again, the PDO statement:

$statement = $db->prepare("UPDATE widgets SET file_id=?,delay=?,title=?,instructions=?,width=?,height=?,bg_color=?,bg_image=?,border-radius=?,offer_table_border_width=?,offer_table_border_color=?,offer_table_link_color=?,offer_table_link_hover_color=?,offer_table_bg1=?,offer_table_bg2=?,head_font_size=?,head_font_color=?,instructions_font_size=?,instructions_font_color=?,time=? WHERE username=? AND wid=?");

The result from this: There are NO ERRORS returned from errorInfo(). There IS a row where username="myuser" and wid="2LvS4c", but it is simply not updated. There must be some sort of syntax error that I can't see, and is not being returned with errorInfo().

I tried with a simpler query:

$statement = $db->prepare("UPDATE widgets SET file_id=? WHERE username=? AND wid=?");
$statement->execute(array($arr[0], $username, $wid));

And it works like a charm! So what is wrong with the big query? Thanks!

Upvotes: 0

Views: 101

Answers (1)

paxdiablo
paxdiablo

Reputation: 881403

This page lists the allowable characters in identifiers for MySQL, and - (minus) doesn't appear to be one of them.

First, are you certain that border-radius is the correct column name? It uses a minus sign whereas all the others use the underscore, _. At a bare minimum, you should maintain some consistency in your naming.

Other than that, start removing one column at a time from the update statement until it works. If it's a problem with a specific column (or possibly command length, however unlikely), this sort of "change one thing and retry" strategy can pinpoint the problem.

Upvotes: 2

Related Questions