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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

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

Post 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
Last edited by impulse() on Mon Feb 05, 2007 5:37 am, edited 1 time in total.
User avatar
louie35
Forum Contributor
Posts: 144
Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:

Post 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()); 
}
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post by impulse() »

Cool. Worked a treat.

Thank you.
User avatar
louie35
Forum Contributor
Posts: 144
Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:

Post by louie35 »

you welcome.

yu should mark the tread as solved.
mickd
Forum Contributor
Posts: 397
Joined: Tue Jun 21, 2005 9:05 am
Location: Australia

Post by mickd »

By the way, not sure if it was intended but your array elements aren't quoted.
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post 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,
User avatar
m3mn0n
PHP Evangelist
Posts: 3548
Joined: Tue Aug 13, 2002 3:35 pm
Location: Calgary, Canada

Post 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.
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Post Reply