MySQL Update with PHP variables in a loop

Question

I've been stuck on this for an hour.

I've got a series of variables ($recordsQuestion_1, $recordsQuestion_2, etc). Since the total number of these variables changes, I want to update my DB in a loop. However, I can't figure out how to actually store the variable. Just $l or "recordsQuestion_1 (2, 3, etc)" into the DB.

Here's what I've got, tried grasping variable variables (not even sure if that's how to do it), but couldn't get anything to work. Maybe an array?

Suggestions?

$l = 1;
while ($l <= $num_rows) {
    $query = "UPDATE records SET recordListingID = $recordsQuestion_" . $l . " WHERE recordID = " . $l;
    mysql_query($query) or die(mysql_error());
    $l++;   
};

The mysql extension is outdated and on its way to deprecation. Use mysqli or PDO and prepared statements instead. Prepared statements are more efficient when executing a query multiple times. Also, prepared statement parameters aren't vulnerable to SQL injection; no need to escape values and no worry that you'll forget. — outis
– outis, Commented Aug 19, 2011 at 1:28
Outputting database error messages to non-admin users discloses too much information. Instead, log the MySQL error message. For some errors (such as those related to missing or invalid values), output your own error message to the user and what action the user can take to address it. For the rest, inform the user that there was an internal error. — outis
– outis, Commented Aug 19, 2011 at 1:38

Luke Stevenson · Accepted Answer · 2011-08-19 01:17:28Z

3

If you are going to have an varying number of variables ($recordsQuestion_1, $recordsQuestion_2 ... $recordsQuestion_n), look at using an array instead, as this will be far easier to work with.

Which could then result in a cleaner loop like:

$recordsQuestion = array(
  'Zero' , # PHP Arrays are zero-indexed, so the first element will have a key of 0
  'One' ,
  'Two' ,
  ...
);

$sqlTpl = 'UPDATE records SET recordListingID = "%s" WHERE recordID = %s';
foreach( $recordsQuestion as $key => $value ){
  $sqlStr = sprintf( $sqlTpl , mysql_real_escape_string( $value ) , (int) $key );
  if( !mysql_query( $sqlStr ) ){
    # Row Update Failed
  }else{
    # Row Updated OK
  }
}

answered Aug 19, 2011 at 1:17

Luke Stevenson

10.3k2 gold badges28 silver badges41 bronze badges

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

1 Comment

Dillon Doyle Over a year ago

Yes. I found the solution below, and I'll change to an array. I'm going to try using .serializeArray (posting with jquery)

Petr Peller · Accepted Answer · 2011-08-19 01:04:55Z

0

You probably want use someting like this:

$var1 = "foo";
$i = 1;

echo "${"var$i"} boo"; //foo boo

Why don't you just use array instead of the series of variables?

answered Aug 19, 2011 at 1:04

Petr Peller

8,86610 gold badges55 silver badges66 bronze badges

2 Comments

Dillon Doyle Over a year ago

Yup this worked. Thanks! Tried pasting the updated code but it didn't work :( I'm probably going to switch to an array, this is being posted dynamically with jQuery. I'll probably have to change to .serializeArray

Dillon Doyle Over a year ago

$l = 1; 	while ($l <= $num_rows) { 		$temp = "${"recordsQuestion_$l"}"; 		mysql_query("UPDATE records SET recordQuestion = '$temp' WHERE recordID = $l"); 		mysql_query($query) or die(mysql_error()); 		$l++;	 	};

Robert Martin · Accepted Answer · 2011-08-19 01:09:49Z

To clarify, your case would be:

$l = 1;
while ($l <= $num_rows) {
    $query = "UPDATE records SET recordListingID = " . ${"recordsQuestion_$l"} . " WHERE recordID = " . $l;
    mysql_query($query) or die(mysql_error());
    $l++;   
};

However, it occurs to me your problem may be to do with SQL. You can't have a variable number of columns in standard SQL. To store a variable number of things, like you're suggesting, you can use an additional column to represent the number you're calling $l. For instance,

recordId | questionId | questionText
---------+------------+-------------
       1 |          1 | "Why?"
       1 |          2 | "Who?"
       1 |          3 | "When?"
       1 |          4 | "How?"
       2 |          1 | "How long?"
       2 |          2 | "Which?"
       3 |          1 | "Wherefore?"
       4 |          1 | "Really?"

In this case, each recordId can have a different number of questions.

outis · Accepted Answer · 2011-08-19 01:34:37Z

0

For reference, here's what the loop would look like using PDO, with a little more error handling added:

$updateRecords = $db->prepare('UPDATE records SET recordListingID = :listing WHERE recordID = :id';
$failed = array();
try {
    foreach ($questions as $id => $listing) {
        if (!$updateRecords->execute(array(':id' => $id, ':listing' => $listing))) {
            # record failure message
            $failed[$id] = ...;
        }
    }
} catch (PDOException $exc) {
    # DB error; handle it
    ...
}

answered Aug 19, 2011 at 1:34

outis

77.7k23 gold badges155 silver badges228 bronze badges

Collectives™ on Stack Overflow

MySQL Update with PHP variables in a loop

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related