Multi-update query...

[clubbedougan]

Hello,

I have a list of checkboxes in my PHP page and a pic and link associated with each.

Now, I need to be able to update all records checked/unchecked.

The following is a small portion of my script:

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

$id = $_POST['current']; 

if (($_SESSION['user_id'] == 1) || ($_SESSION['user_id'] == 2)) { 

foreach($_POST['current'] as $key=>$value) 
{ 
if($key) 
$checkValue="Y"; 
else $checkValue="N"; 

$query = "UPDATE footer_logos SET visible = '".$checkValue."' WHERE id = '".$value."' "; 

if (!mysql_query($query)) 
{ 
  // The code, if the query not executed.. 

echo 'query was not executed'; 

} 
} 

} 

$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_array($result1)) { 

        $visible = $row1['visible']; 
        $id = $row1['id']; 

echo '<tr><td>'; 

if ($visible=='Y') {$checked="checked";} 
else{$checked="";} 

echo '<input type="checkbox" name="current[]" id="current[]" value="' . $id . '" class="maillistadd" '.$checked.' /></td>'; 

echo '<td><img src="../images/' . $row1[1] . '" /></td> 
<td>' . $row1[2] . '</td><td>' . $row1[4] . '</td>'; 
echo '</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><? 

include ('../include_files/afooterfile.html'); 

?> 

Is it easy to basically update all records(there's only 5 just now) and set 'visible' to either 'N' or 'Y' depending on whether the checkbox is checked or not.
Upon submitting I get the following error:

Error!
Notice: Query: 
MySQL Error: Query was empty

I've been racking my brains on this one for a few weeks now.

Many thanks!

[BlueKai]

What does name="current[]" id="current[]" mean? what are the []'s for?

[clubbedougan]

Hi!
Thanks for the reply.

I'm not sure what I meant by having the square brackets like that.

However, when the query outputs the checkboxes, those that are set at 'Y' in the db are checked and those at 'N' are unchecked - which is great!

It's just when I try to run an UPDATE query to update all records, it doesn't do it.I'd like to update all records(there's only 5) in the 'footer_logos' table so they change to either Y or N.
Can you help?

[BlueKai]

Try taking the brackets out. You're submitting name="current[]", but you're accepting $_POST['current']. Not sure if you're trying to do something with an array here or not...

[clubbedougan]

I basically want to update every record in the table.

If the checkbox associated with record 1 is checked I'd like it to update the 'visible' column as 'Y', if it is unchecked, I'd like it to update the 'visible' column as 'N'.

What can I do to fix this?

Sorry to be such a newb at this.

[Weedpacket]

BlueKai wrote:

Try taking the brackets out. You're submitting name="current[]", but you're accepting $POST['current']. Not sure if you're trying to do something with an array here or not...

The [] will make $POST['current'] an array, containing the values of all of the fields that were named "current[]".

It is a mistake to have 'id="current[]"', of course, because ids have to be unique in a page.

The other mistake is in not realising (print_r($_POST) to see what is actually being submitted - that often helps) that unchecked checkboxes are not submitted, only checked ones.

So to work out which ones were unchecked, it's necessary to have a list of all of the checkbox values and remove those that are checked.

[clubbedougan]

Hi,

OK. So I have 5 checkboxes.

The first one has a value of 'N' in the db, therefore is unchecked. The remaining 4 are checked.

When I submit my form having unchecked 3,4 and 5. The $POST['current'] value is '2'. When I uncheck 4 and 5, the $POST['current'] value is '3' and so on.

Can someone please help me then with an update query which will update every record in that table to either N or Y based on whether it is checked or unchecked.

Thanks.

[BlueKai]

Because you're using a while loop, it echoes every checkbox with the same name. Upon submission, it will only accept the last one with that name. Putting a unique value on the input name will fix that.

I'm not sure how to go about that doing the way you're doing it since I'm not 100% clear on what you're doing with arrays here (both with mysql_fetch_array and current[]).

I'd use mysql_fetch_assoc() instead of mysql_fetch_array() and tack on the id at the end of the input name...

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[1]}' /></td><td>{$row1[2]}</td><td>{$row1[4]}</td>
</tr>";
}     

It's very possible that I'm not understanding this correctly and using an array of some sort makes more sense.

[clubbedougan]

Thanks for the help with this BlueKai.

It shows the correct checked/unchecked boxes, however, does not display record data.

Could be a problem with this line:

<td><img src='../images/{$row1[1]}' /></td><td>{$row1[2]}</td><td>{$row1[4]}</td>

And the query still seems to be 'empty' 🙁

I wonder, is the query empty, because this part may be wrong?

foreach($_POST['current'] as $key=>$value)
{
if($key)
$checkValue="Y";
else $checkValue="N";

$query = "UPDATE footer_logos SET visible = '".$checkValue."' WHERE id = '".$value."' ";

if (!mysql_query($query))
{
  // The code, if the query not executed..

echo 'query was not executed';

}
}

}

[BlueKai]

You'd have to accept what you're submitting. So, if you have id's 1,2,3,4,5 and your while loop is generating current_1, current_2, current_3, current_4, and current_5 with values on or null, you'd then need to accept and update each.

if ($_POST['current_1'] == "on")
	mysql_query("update table set visible='T' where id=1")

You can put this in a loop and make the id a variable, or you can use an array (which in this case might be better but I don't use arrays enough to be comfortable helping someone with them). Either way, you have to look for each id's checkbox and find out if a value of 'on' was submitted and update accordingly.

[clubbedougan]

So would something likes this do?

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

if (($_SESSION['user_id'] == 1) || ($_SESSION['user_id'] == 2)) {

$id = $_POST['current'];

foreach($_POST['current'] as $key=>$value) 
{

if ($_POST['current_$id'] == "on")
    mysql_query("update footer_logos set visible='Y' where id=$id")
}

[BlueKai]

Foreach is for an array. Are you submitting an array? If you're using what I put above, you'd need to use a for loop:

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

This will only work if your id's are 1-5.

If the id's are more random, or you imagine that they might change, putting them into an array, submitting the array, then reading the array in a foreach might make more sense.

[clubbedougan]

Hmm...don't seem to be getting anywhere with this. Going around in circles(!)

Anyone else fancy giving this a shot?

<?php # second stage of editing offers

require_once('../dbc/connect.php');

$page_title = "Footer Logos";

include ('../include/aheadit.html');

if (($_SESSION['usercat']!='a')) {
	echo 'Sorry, you are not logged in';
	exit;
}


// change what logos are displayed on site
if (isset($_POST['submitted'])) {

if (($_SESSION['user_id'] == 1) || ($_SESSION['user_id'] == 2)) {

$current = $_POST['current'];

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

if (!mysql_query($query)) { 
  // The code, if the query not executed..

echo 'query was not executed';

} 
}

}

if ($result = mysql_query($query)) { // Everything Worked.

	echo $query;

	echo '<div class="success"><b>Footer Logos updated !</b>';

	echo '<p>The Footer Logos have been updated on the database.</p></div><br />';

	echo '<ul><li><a href="index.php">Go back to Admin</a></li>';

	echo '<li><a href="javascript:history.go(-1)">Go back to the previous page</a></li></ul>';     

	include ('../include/afootit.html');

    exit;   

}

else {

	echo 'Error!';

	// echo '<br />Sorry, you have to be one of the main Administrators to make changes here.<br />';

	trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());
}

}


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


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

echo $_POST['current'];

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>

	<hr size="2" />

Just to say again, this form is used so that a user can check/uncheck checkboxes which will then update the table in my database to either make the value in the visible field, 'Y'(when checked) or 'N'(unchecked).

Right now I'm getting an error from the piece BlueKai gave me with the FOR Loop.

Any help would be brilliant.