How to neatly "build" large sql queries made up of a large number of variables

Question

First off, Sorry if this has already been asked.
I looked around but couldn't find any answers for it, Or maybe I was searching using the wrong words.

I have a long SQL query that I need to execute using PHP. It requires a large number of variables to be updated.

This is what I mean:

$user = json_decode($stringWithJson);

$reallyLongSqlQuery = "UPDATE `profile` SET `userid` = '{$user->userid}', `name` = '{$user->username}', `lastlogoff` = '{$user->userlastlogoff}', `profileurl` = '{$user->userprofileurl}', `avatar` = '{$user->useravatar}', `avatarmedium` = '{$user->useravatarmedium}', `useravatarfull` = '{$user->useravatarfull}', `state` = '{$user->userprofilestate}', `realname` = '{$user->userrealname}', `timecreated` = '{$user->userprofilecreatedunix}' WHERE `id` = 1;";

mysql_query($reallyLongSqlQuery);

This works fine and all, but It's a lot of code for a single line. Is there any way I can tidy this up?

Example:

$reallyLongSqlQuery = "UPDATE `profile` SET `userid` = '" . $user->userid . 
    "', `name` = '" . $user->username . 
    "', `lastlogoff` = '" . $user->userlastlogoff . 
    "', `profileurl` = '" . $user->userprofileurl . 
    "', `avatar` = '" . $user->useravatar .  
    "', `avatarmedium` = '" . $user->useravatarmedium . 
    "', `useravatarfull` = '" . $user->useravatarfull . 
    "', `state` = '" . $user->userprofilestate . 
    "', `realname` = '" . $user->userrealname . 
    "', `timecreated` = '" . $user->userprofilecreatedunix . 
    "' WHERE `id` = 1;";

This doesn't fly off the screen in one giant line, but it looks even messier in my opinion.

Another way I've though of is predefining all the variables beforehand, Like so:

$userid = $user->userid;
$username = $user->username;
$userlastlogoff = $user->userlastlogoff;
$userprofileurl = $user->userprofileurl;
$useravatar = $user->useravatar;
$useravatarmedium = $user->useravatarmedium;
$useravatarfull = $user->useravatarfull;
$userprofilestate = $user->userprofilestate;
$userrealname = $user->userrealname;
$userprofilecreatedunix = $user->userprofilecreatedunix;

$reallyLongSqlQuery = "UPDATE `profile` SET `userid` = '{$userid}', `name` = '{$username}', `lastlogoff` = '{$userlastlogoff}', `profileurl` = '{$userprofileurl}', `avatar` = '{$useravatar}', `avatarmedium` = '{$useravatarmedium}', `useravatarfull` = '{$useravatarfull}', `state` = '{$userprofilestate}', `realname` = '{$userrealname}', `timecreated` = '{$userprofilecreatedunix}' WHERE `id` = 1;";

Once again, This works fine but there must be an easier (and tidier) way to do it.
Anyone have a solution?

A neater and safer way to do it is prepared statements, take a look at (http://php.net/manual/en/pdo.prepared-statements.php) — Hans Dubois, Dec 12 '14 at 08:01
One can never `Neatly build` queries with string interpolation anymore. http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php is neat — Hanky Panky, Dec 12 '14 at 08:02
@HansDubois I just took a look at the link you posted, This is exactly what I was looking for. Thank you! — Kain, Dec 12 '14 at 08:03
You should have a look at [object-relational mapping](http://en.wikipedia.org/wiki/Object-relational_mapping). — Gumbo, Dec 12 '14 at 08:07
@Gumbo Also useful, But that looks like C#, Not php. Thanks though. — Kain, Dec 12 '14 at 08:10
@InuKenshi ORM is language independent. Have a look at [Good PHP ORM Library?](http://stackoverflow.com/q/108699/53114) — Gumbo, Dec 12 '14 at 08:12

KIKO Software · Accepted Answer · 2014-12-12T08:40:08.667

1

Of course you should be using bindings, not a plain query string, but an array can be helpful in your case:

$data['userid']         = $user->userid;
$data['name']           = $user->username;
$data['lastlogoff']     = $user->userlastlogoff;
$data['profileurl']     = $user->userprofileurl;
$data['avatar']         = $user->useravatar; 
$data['avatarmedium']   = $user->useravatarmedium;
$data['useravatarfull'] = $user->useravatarfull;
$data['state']          = $user->userprofilestate;
$data['realname']       = $user->userrealname;
$data['timecreated']    = $user->userprofilecreatedunix;

foreach ($data as $column => $value)
{
  $updates[] = "$column = '$value' "; // value should be escaped!
}

$reallyLongSqlQuery = 'UPDATE profile SET '.
                      implode(',',$updates).
                      ' WHERE id = 1';

edited Dec 12 '14 at 08:40

answered Dec 12 '14 at 08:16

KIKO Software

10,480
2
13
28

I'll give this a shot, Thanks! – Kain Dec 12 '14 at 08:18
I'm getting an `Warning: implode(): Invalid arguments passed `. I've never used implode() before, So I'm not too sure what argument would be 'invalid'. Any ideas? – Kain Dec 12 '14 at 08:27
If I use `echo $updates;` right after the foreach statement, It simply returns `timecreated = '1349995769'` I tried using `$updates[$column] = "$column = '$value' ";` and this seemed to give the correct result – Kain Dec 12 '14 at 08:33
1

Sorry, yes, I didn't run the code. You're right that's a bug. But you found it, and that's good! :-) You don't need to use `$column`, just `[]` will do. Don't forget to escape the value, that's really important. The method depends on the way you access the database (mysql, mysqli, etc). – KIKO Software Dec 12 '14 at 08:39
Thanks for the edit, I've marked your answer as correct :) I've tested this and it works perfectly. – Kain Dec 12 '14 at 08:43

How to neatly "build" large sql queries made up of a large number of variables

1 Answers1