Updating multiple rows in MySQL table

Hi,

I’m trying to run a SQL query multiple times.



if isset($_POST['submitted'])) {

$current = $_POST['current'];

for ($i=1;$i<=25;$i++){
    if (($_POST['current_'.$i]) == "on")
        mysql_query("update footer_logos SET visible = 'Y' where id={$i}");
    }
}

}

$query1 = "SELECT * FROM footer_logos";
$result1 = @mysql_query($query1); // Run the Query.

echo '<p><b>Edit "Footer Logos"</b></p>';

echo '<form name="form1" method="POST" action="update_logos.php" class="book">';

echo '<table width="550" border="0" style="padding:5px">';
  echo '<tr style="background-color:#eaeaea;">
  	<td>Visible</td>
    <td>Image</td>
    <td>Link</td>
    <td>ALT Text</td>
  </tr>';

while ($row1 = mysql_fetch_assoc($result1)) {  // use assoc, not array

    if ($row1['visible']=='Y')
        $checked="checked";
    else
        $checked="";

    // use id to name each one differently
    echo "<tr>
        <td><input type='checkbox' name='current_{$row1['id']}' id='current_{$row1['id']}' value='{$row1['id']}' class='maillistadd' {$checked} /></td>
        <td><img src='../images/{$row1['src']}' /></td><td>{$row1['link']}</td><td>{$row1['alt']}</td>
    </tr>";
}
	
echo '</table>';
	
?>       
            <br>
            <input name="submit" type="submit" class="submit" value="Update the 'Logos' box">
            <br>
            <br>
            <input type="hidden" value="form1">
            <input type="hidden" name="submitted" value="TRUE" />
            </p>
		</form>

Can anyone help me to fix the above?

If on, it should set visible to Y.

Many thanks for any help.

No problem. :slight_smile:

Many thanks, Anthony.

I was just about to update this thread.

I managed to get a solution last night that does the trick:


if (isset($_POST['submitted'])) { 
   $sql = "UPDATE footer_logos SET visible = "; 
   if (empty($_POST['current'])) { // none checked 
      $sql.= "'N'"; 
   } 
   elseif(is_array($_POST['current'])) { 
      foreach($_POST['current'] as & $id) { 
         $id = (int)$id; // prevent SQL injection by casting to int 
          
      } 
      $id_in = implode(',', $_POST['current']); 
      $sql.= "IF(id IN($id_in), 'Y', 'N')"; 
   } 
   else { 
      die("How did that happen?"); 
   } 
   $result = mysql_query($sql); 
   if (!$result) { 
      user_error(mysql_error() . "<br />$sql"); // debug false result 
       
   } 
}

Thanks again for the help sir.


<?php
/**
 *  Sets 'active' to 'Y' if id included in colllection, else to 'N'
 */
$sql = sprintf(
  "UPDATE table SET active = IF((id IN(&#37;s)), 'Y', 'N')",
  implode(
    ',',
    array_map(
      'intval',
      $ids_from_form
    )
  )
);
?>

Can anyone help with this?

I’m still a little stuck.

Currently my code is:


if (isset($_POST['submitted'])) {

    if(is_array($_POST['current'])) // check whether any of the checkboxes has been ticked, if not - no need to perform the update 
    { 
        $id_in = implode(',', $_POST['current']); // from all the boxes that have been ticked, create a string that looks like: 1,2,3,4 so it can fit into MySQL's IN() function 
         mysql_query("update footer_logos SET visible = 'Y' where id IN($id_in)"); // run ONE query and update all the records that are specified by the checkboxes. 
    } 
     
} 


$query1 = "SELECT * FROM footer_logos"; 
$result1 = @mysql_query($query1); // Run the Query. 

echo '<p><b>Edit "Footer Logos"</b></p>'; 

echo '<form name="form1" method="POST" action="update_logos.php" class="book">'; 

echo '<table width="550" border="0" style="padding:5px">'; 
  echo '<tr style="background-color:#eaeaea;"> 
      <td>Visible</td> 
    <td>Image</td> 
    <td>Link</td> 
    <td>ALT Text</td> 
  </tr>'; 

while ($row1 = mysql_fetch_assoc($result1)) {  // use assoc, not array 

    if ($row1['visible']=='Y') 
        $checked="checked"; 
    else 
        $checked=""; 

    // use id to name each one differently 
    echo "<tr> 
        <td><input type='checkbox' name='current[{$row1['id']}]' id='current_{$row1['id']}' value='{$row1['id']}' class='maillistadd' {$checked} /></td>
        <td><img src='../images/{$row1['src']}' /></td><td>{$row1['link']}</td><td>{$row1['alt']}</td> 
    </tr>"; 
} 
     
echo '</table>'; 
     
?>        
            <input name="submit" type="submit" class="submit" value="Update the 'Logos' box" />  
            <input type="hidden" value="form1"> 
            <input type="hidden" name="submitted" value="TRUE" />  
        </form>

I’d like to set visible to N in footer_logos if the current id does not appear in the $_POST[‘current’] array.

Should I select all the IDs from the footer_logos table and compare them with the $_POST[‘current’] array ?

Thanks for any help.

Dang, that looks good.

I’m going to play about with it but this one from Blue is also pretty easy to do.

When are you heading up over the border sir?


UPDATE
    table
SET 
    active = IF(id IN (YES_IDS), 'Y', 'N')
WHERE 
    id IN (ALL_IDS)

Using Blue’s code above. I’m 90% of the way there.

And yes, potential flaw-a-mundo! It doesn’t set it to N if unticked.

Does that mean I just add a second query to set it to ‘N’ ?

e.g.


    if(is_array($_POST['current'])) // check whether any of the checkboxes has been ticked, if not - no need to perform the update
    {
        $id_in = implode(',', $_POST['current']); // from all the boxes that have been ticked, create a string that looks like: 1,2,3,4 so it can fit into MySQL's IN() function
         mysql_query("update footer_logos SET visible = 'Y' where id IN($id_in)"); // run ONE query and update all the records that are specified by the checkboxes.
    } else {
         mysql_query("update footer_logos SET visible = 'N' where id IN($id_in)"); // run ONE query and update all the records that are specified by the checkboxes.
}

Hey Anthony! How’s tricks?

Eeeek, that looks fairly complex.

What’s the best way to incorporate it into my script above?

Thanks for the help

May I interject with some SPL magic/tomfoolery ?


<?php
$_POST = array(
  'current_1' => 'Y',
  'c' => 'd'
);

class YupFilter extends FilterIterator
{
  public function accept(){
    if('Y' === parent::current()){
      if(1 === preg_match('~current_(\\d)~', $this->key())){
        return true;
      }
    }
    return false;
  }
  
  public function current(){
    list($id) = sscanf($this->key(), 'current_&#37;d');
    return (int)$id;
  }
}

$ids = array();

foreach(new YupFilter(new ArrayIterator($_POST)) as $value){
  /**
   *  Only values which meet the specification set in YupFilter::accept()
   *  reach here.
   */
  array_push($ids, $value);
}

printf(
  "UPDATE table SET active = 'Y' WHERE id IN (%s);",
  implode($ids)
);

?>

Aaaah.

I see what you mean.

I’m going to look into this in about 10 minutes time and get back shortly.

Thank you for your help

Good call, I may very well partake. I’ll check it out. Tempted to take a look in. CodeIgniter one looks decent too.
Wow, Cups too? Must be a goodin.

Good call, I may very well partake. I’ll check it out. Tempted to take a look in. CodeIgniter one looks decent too.
Wow, Cups too? Must be a goodin.

First problem you have is by constructing the names of your checkboxes.
Having your checkboxes named “current_1”, “current_2” and so on isn’t really usable later on when you send the checkbox data to your php script.

That’s why programmers invented something called an array.
Array is a construct that allows you to group variables in such way that you can easily re-use their values and do grouped operations on those variables.

In short, had you named your checkboxes such as “current[1]”, “current[2]” you’d have much easier job handling input from them.

In terms of code, it would look like this when creating checkboxes:


// use id to name each one differently

    echo "<tr>

        <td><input type='checkbox' name='current[{$row1['id']]' id='current_{$row1['id']}' value='{$row1['id']}' class='maillistadd' {$checked} /></td>

        <td><img src='../images/{$row1['src']}' /></td><td>{$row1['link']}</td><td>{$row1['alt']}</td>

    </tr>";

And it would look like this when you want to update the database with boxes that have been ticked:

if(isset($_POST['submitted']))
{
    if(is_array($_POST['current'])) // check whether any of the checkboxes has been ticked, if not - no need to perform the update
    {
        $id_in = implode(',', $_POST['current']); // from all the boxes that have been ticked, create a string that looks like: 1,2,3,4 so it can fit into MySQL's IN() function
         mysql_query("update footer_logos SET visible = 'Y' where id IN($id_in)"); // run ONE query and update all the records that are specified by the checkboxes.

    }
}

However, there’s one potential flaw - I don’t know if it was intended or not but once you check a box and update visible to Y - how can you set value of visible to something else (I am assuming other state can be N)?

Basically the second half of the above script works perfectly.

It’s just the update is no good :cry:

Is there anything obvious I’ve stuffed up on?

Thank you

Hi Sgt,

Unfortunately that didn’t seem to fix the issue.

Is there anything I can do at this end to help test it?

Off Topic:

Well, I’ll save you the boring details but I ended up meeting the client via a WebEx, so no Scotland trip for me so far! :frowning:

On a brighter note, I’ve blagged myself a training budget this year so I’m hitting as many conferences as I can.

So far, PHPNW10 is booked and prepared for. Also, Mr. Geraghty is hitting it up too, all the way from France!

Will you be partaking this year young man? There’s some nice cheap, early-bird tickets in the offing. :cool:

From memory i don’t believe you can use {$i} in mysql queries, try

        mysql_query("update footer_logos SET visible = 'Y' where id='$i'");