MySQL Update with PHP variables in a loop

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++;   
};

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
  }
}

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?

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.

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
    ...
}