Page 1 of 1

[SOLVED] Trouble altering a MySQL query depending on $_POST

Posted: Mon Feb 05, 2007 5:13 am
by impulse()
I have a table which retrieves data from a MySQL DB and then displays it in a table. Next to each column in a text field to alter the DB entry and at the bottom of the table a submit button to update the form. The first problem I had is that the empty text boxes were being submitted as values and altering the entries to blanks. So then I decided to run through a loop like the following:

Code: Select all

if ($_POST) {

  $query = "UPDATE reminders SET ";

  if (!empty($_POST["type"])) {
    $query .= "type = '$_POST[type]',";
  }

  if (!empty($_POST["view"])) {
    $query .= "view = '$_POST[view]',";
  }

  if (!empty($_POST["message"])) {
    $query .= "message = '$_POST[message]',";
  }

  if (!empty($_POST["preRemMess"])) {
    $query .= "preRemMessage = '$_POST[preRemMess]',";
  }

  $query .= "WHERE id = '$_POST[id]'";

  echo $query;
  mysql_query($query) or die(mysql_error());
}
But this is proving difficult to get working because of the placement of the commas. How could I make PHP identify if the $query .= is the last one so it doesn't put the comma before "WHERE ID = '$_POST[id]'".
This is what my typical failing query looks like:

Code: Select all

UPDATE reminders SET type = 'yo',WHERE id = '105'
I hope that makes sense to you.

Regards, Stephen

Posted: Mon Feb 05, 2007 5:18 am
by louie35
try this:

Code: Select all

if ($_POST) { 

  $query = "UPDATE reminders SET "; 

  if (!empty($_POST["type"])) { 
    $query .= "type = '$_POST[type]',"; 
  } 

  if (!empty($_POST["view"])) { 
    $query .= "view = '$_POST[view]',"; 
  } 

  if (!empty($_POST["message"])) { 
    $query .= "message = '$_POST[message]',"; 
  } 

  if (!empty($_POST["preRemMess"])) { 
    $query .= "preRemMessage = '$_POST[preRemMess]',"; 
  } 

  $query = str_replace(",,"," ",$query);//replace duplicate commas
  $query = str_replace(", WHERE"," WHERE",$query);//replace commas before WHERE
  $query = str_replace(",WHERE"," WHERE",$query);//replace commas before WHERE

  $query .= "WHERE id = '$_POST[id]'"; 

  echo $query; 
  mysql_query($query) or die(mysql_error()); 
}

Posted: Mon Feb 05, 2007 5:22 am
by impulse()
Cool. Worked a treat.

Thank you.

Posted: Mon Feb 05, 2007 5:24 am
by louie35
you welcome.

yu should mark the tread as solved.

Posted: Mon Feb 05, 2007 6:16 am
by mickd
By the way, not sure if it was intended but your array elements aren't quoted.

Posted: Mon Feb 05, 2007 6:29 am
by impulse()
It was intended due to PHP using the 2nd " ' " as the end of string. I assume I can backslash it out though? How would my code benefit from quoting array elements?

Regards,

Posted: Mon Feb 05, 2007 6:59 am
by m3mn0n
As a side note, I would highly recommend doing some variable sanitizing.

Passing user input directly into a query is highly insecure.

A rule of thumb for PHP is never trust user input & always sanitize variables.

Posted: Mon Feb 05, 2007 8:10 am
by impulse()
This will only be used by company employees. Unless we have a rogue employee there's no need to worry :)

Note taken that it's good practice though.

Posted: Mon Feb 05, 2007 9:12 am
by feyd
impulse() wrote:This will only be used by company employees. Unless we have a rogue employee there's no need to worry :)

Note taken that it's good practice though.
All the values require escaping still... unless you don't think employees will every use apostrophes.