PDA

View Full Version : Updating Multiple Rows at once


m1a2x3x7
11-08-2007, 11:21 PM
Hey guys,

A client of mine would like to be able to turn on and off zip codes for certain states. So on the admin side of things I have two radio buttons on and off one has a value of 1 on the other has a value of off. I want them to be able to set all the zip codes to on and off at one time with one submit.

I have everything working except for multiple zip code changes it will only update the first one.

Here is my code.

<?php

ob_start();

//CONNECT TO DATABASE//
require_once("connection.php");



//PULL INFORMATION FROM THE DATABASE//
$query = sprintf("SELECT * FROM zipCodes");
$result = @mysql_query($query);
$row = @mysql_fetch_array($result);

//VARIABLES//
$active ="green.jpg";
$deactive ="red.jpg";
$radio1 = $_POST['radio1'];
$radio2 = $_POST['radio2'];

//UPDATES ZIPCODE STATUS//
if (isset($_POST['submit']) && $radio1 == 1) {
$query =sprintf("UPDATE zipCodes SET active = 1 where test_id = '$zipID[$i]'");
$result = @mysql_query($query);

$query = sprintf("SELECT * FROM zipCodes");
$result = @mysql_query($query);
$row = @mysql_fetch_array($result);

}elseif (isset($_POST['submit']) && $radio2 == 0) {
$query =sprintf("UPDATE zipCodes SET active = 0 where test_id = '$zipID[$i]'");
$result = @mysql_query($query);

$query = sprintf("SELECT * FROM zipCodes");
$result = @mysql_query($query);
$row = @mysql_fetch_array($result);

}



?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Admin</title>
<script src="SpryAssets/SpryTabbedPanels.js" type="text/javascript"></script>
<link href="SpryAssets/SpryTabbedPanels.css" rel="stylesheet" type="text/css" />
<style type="text/css">
<!--
.font {
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 12px;
}
#wrapper {
margin: auto;
height: auto;
width: 800px;
}
.tableItems {
margin: auto;
height: auto;
width: 410px;
}
.items {
height: auto;
width: 410px;
margin-top: 10px;
margin-right: auto;
margin-bottom: auto;
margin-left: auto;
clear: both;
}
.submit {
text-align: right;
height: auto;
width: 80px;
margin-left: 490px;
margin-bottom: 10px;
}
.footer {
clear: both;
height: auto;
width: 600px;
}
.status {
height: auto;
width: 80px;
float: left;
margin-left: 30px;
text-align: center;
margin-bottom: 10px;
}
.onOff {
height: auto;
width: 80px;
float: left;
margin-left: 30px;
text-align: center;
margin-bottom: 10px;
}
.zipcode {
text-align: center;
height: auto;
width: 80px;
float: left;
margin-bottom: 10px;
}
-->
</style>
</head>

<body>
<div id="wrapper">
<div id="TabbedPanels1" class="TabbedPanels">
<ul class="TabbedPanelsTabGroup">
<li class="TabbedPanelsTab" tabindex="0">Indiana</li>
<li class="TabbedPanelsTab" tabindex="0">Kentucky</li>
<li class="TabbedPanelsTab" tabindex="0">Ohio</li>
<li class="TabbedPanelsTab" tabindex="0">Illinois</li>
</ul>
<div class="TabbedPanelsContentGroup">
<div class="TabbedPanelsContent">
<div id="TabbedPanels2" class="TabbedPanels">
<ul class="TabbedPanelsTabGroup">
<li class="TabbedPanelsTab" tabindex="0">Zip Code</li>
<li class="TabbedPanelsTab" tabindex="0">County</li>
</ul>
<div class="TabbedPanelsContentGroup">
<div class="font"> <br />
Indiana Zip Codes<br />
<br />
<div class="tableItems"><div class="zipcode">Zip Code</div><div class="status">Status</div><div class="onOff">On
</div><div class="onOff">Off</div>
<div class="footer"></div></div><form action="<?php $_SERVER['PHP_SELF'];?>" method="post">
<?php do { ?> <div class="items"><div class="zipcode"><?php echo $row['zip_code'];?></div>
<div class="status"><img src="images/<?php if ($row['active'] == 1) {
echo $active;
}elseif ($row['active'] == 0) {
echo $deactive;
}?>" /></div>
<div class="onOff">
<label>
<input name="radio<?php echo $row['test_id'];?>" type="radio" id="radio1" value="1" <?php if ($row['active'] == 1) {
echo "checked = \"checked\"";}?> />
</label>
</div>
<div class="onOff">
<label>
<input name="radio<?php echo $row['test_id'];?>" type="radio" id="radio2" value="0" <?php if ($row['active'] == 0) {
echo "checked = \"checked\"";}?> />
</label>
</div>
<div class="footer">
</div></div><?php } while ($row = @mysql_fetch_array($result));?><br /><div class="submit">
<input name="hiddenField" type="hidden" value="1" />
<input name="submit" type="submit" id="submit" value="Submit" />
</div></form>
</div>
<div class="font">Indiana Counties</div>
</div>
</div>
<p>&nbsp;</p>
</div>
<div class="TabbedPanelsContent">
<div id="TabbedPanels3" class="TabbedPanels">
<ul class="TabbedPanelsTabGroup">
<li class="TabbedPanelsTab" tabindex="0">Zip Code</li>
<li class="TabbedPanelsTab" tabindex="0">County</li>
</ul>
<div class="TabbedPanelsContentGroup">
<div class="font"><br />
Kentucky Zip Codes</div>
<div class="font"><br />
Kentucky Counties</div>
</div>
</div>
<p>&nbsp;</p>
</div>
<div class="TabbedPanelsContent">
<div id="TabbedPanels4" class="TabbedPanels">
<ul class="TabbedPanelsTabGroup">
<li class="TabbedPanelsTab" tabindex="0">Zip Code</li>
<li class="TabbedPanelsTab" tabindex="0">County</li>
</ul>
<div class="TabbedPanelsContentGroup">
<div class="font"><br />
Ohio Zip Codes</div>
<div class="font"><br />
Ohio Counties</div>
</div>
</div>
<p>&nbsp;</p>
</div>
<div class="TabbedPanelsContent">
<div id="TabbedPanels5" class="TabbedPanels">
<ul class="TabbedPanelsTabGroup">
<li class="TabbedPanelsTab" tabindex="0">Zip Code</li>
<li class="TabbedPanelsTab" tabindex="0">County</li>
</ul>
<div class="TabbedPanelsContentGroup">
<div class="font"><br />
Illinois Zip Codes</div>
<div class="font"><br />
Illinois Counties
</div>
</div>
</div>
<p>&nbsp;</p>
</div>
</div>
</div>
<script type="text/javascript">
<!--
var TabbedPanels1 = new Spry.Widget.TabbedPanels("TabbedPanels1");
var TabbedPanels2 = new Spry.Widget.TabbedPanels("TabbedPanels2");
var TabbedPanels3 = new Spry.Widget.TabbedPanels("TabbedPanels3");
var TabbedPanels4 = new Spry.Widget.TabbedPanels("TabbedPanels4");
var TabbedPanels5 = new Spry.Widget.TabbedPanels("TabbedPanels5");
//-->
</script>
</div>
</body>
</html>
and here what it looks like right now.

http://www.schultzstudio.com/freelance/index.php

I'm pretty sure I need to use an array but I can say I've really experimented with them so any help would be great.

davidj
11-09-2007, 07:19 AM
are you wanting to update multiple zip codes at once where the address ID's are different

m1a2x3x7
11-09-2007, 10:57 AM
yes each zip code has a separate id.

My database is set like this.

-----------------------------------
| test_id | zip_code | state | active |
-----------------------------------

davidj
11-09-2007, 12:49 PM
ok first perform a select on what you want to update

your result will return in the usual array format


$row['test_id']


then just do your do while loop with your update statement inside that using the $row['test_id'] in your WHERE clause.

m1a2x3x7
11-09-2007, 01:19 PM
ok first perform a select on what you want to update
your result will return in the usual array format


Your talking about this?

//PULL INFORMATION FROM THE DATABASE//
$query = sprintf("SELECT * FROM zipCodes");
$result = @mysql_query($query);
$row = @mysql_fetch_array($result);and this?

<?php do { ?>
<div class="items"><div class="zipcode"><?php echo $row['zip_code'];?></div>
<div class="status"><img src="images/<?php if ($row['active'] == 1) {
echo $active;
}elseif ($row['active'] == 0) {
echo $deactive;
}?>" /></div>
<div class="onOff">
<label>
<input name="radio<?php echo $row['test_id'];?>" type="radio" id="radio1" value="1" <?php if ($row['active'] == 1) {
echo "checked = \"checked\"";}?> />
</label>
</div>
<div class="onOff">
<label>
<input name="radio<?php echo $row['test_id'];?>" type="radio" id="radio2" value="0" <?php if ($row['active'] == 0) {
echo "checked = \"checked\"";}?> />
</label>
</div>
<div class="footer">
</div></div><?php } while ($row = @mysql_fetch_array($result));?>then just do your do while loop with your update statement inside that using the $row['test_id'] in your WHERE clause.

Like this?

$zipID = $row['test_id'];

if (isset($_POST['submit']) && $radio1 == 1) {

do {

$query =sprintf("UPDATE zipCodes SET active = 1 where test_id = '$zipID'");
$result = @mysql_query($query);

$query = sprintf("SELECT * FROM zipCodes");
$result = @mysql_query($query);
$row = @mysql_fetch_array($result);

} while ($row = @mysql_fetch_array($result));

}elseif (isset($_POST['submit']) && $radio2 == 0) {

do {

$query =sprintf("UPDATE zipCodes SET active = 0 where test_id = '$zipID'");
$result = @mysql_query($query);

$query = sprintf("SELECT * FROM zipCodes");
$result = @mysql_query($query);
$row = @mysql_fetch_array($result);

} while ($row = @mysql_fetch_array($result));

}

davidj
11-09-2007, 03:12 PM
notice the select is above the do while

your do while is not outputting anything. Its just there to loop through your results from the select statement.

notice how i use the $row['test_id'] in the SQL. Its this that will change every time it loops


$query = sprintf("SELECT * FROM zipCodes");
$result = @mysql_query($query);
$row = @mysql_fetch_array($result);

do {

$query =sprintf("UPDATE zipCodes SET active = 1 where test_id = '%s'", $row['test_id']);
$resultUpdate = @mysql_query($query);


} while ($row = @mysql_fetch_array($result));

m1a2x3x7
11-09-2007, 03:57 PM
Thanks for taking the time to help David.

ok so I have this now.

if (isset($_POST['submit']) && $radio1 == 1) {


$query = sprintf("SELECT * FROM zipCodes");
$result = @mysql_query($query);
$row = @mysql_fetch_array($result);

do {
$query =sprintf("UPDATE zipCodes SET active = 1 where test_id = '%s'", $row['test_id']);
$resultUpdate = @mysql_query($query);



} while ($row = @mysql_fetch_array($result));

$query = sprintf("SELECT * FROM zipCodes");
$result = @mysql_query($query);
$row = @mysql_fetch_array($result);

}elseif (isset($_POST['submit']) && $radio2 == 0) {


$query = sprintf("SELECT * FROM zipCodes");
$result = @mysql_query($query);
$row = @mysql_fetch_array($result);

do {

$query =sprintf("UPDATE zipCodes SET active = 1 where test_id = '%s'", $row['test_id']);
$resultUpdate = @mysql_query($query);



} while ($row = @mysql_fetch_array($result));

$query = sprintf("SELECT * FROM zipCodes");
$result = @mysql_query($query);
$row = @mysql_fetch_array($result);


}

but it still isnt working. what does the "%s" do?

m1a2x3x7
11-09-2007, 04:21 PM
ok with the code above it wouldnt even update the first zipcode this code now will update the first no but not the second one.

if (isset($_POST['submit']) && $radio1 == 1) {

do{

$query =sprintf("UPDATE zipCodes SET active = 1 where test_id = '%s'", $zipID);
$result = @mysql_query($query);


} while ($row = @mysql_fetch_array($result));

$query = sprintf("SELECT * FROM zipCodes");
$result = @mysql_query($query);
$row = @mysql_fetch_array($result);

}elseif (isset($_POST['submit']) && $radio2 == 0) {

do{

$query =sprintf("UPDATE zipCodes SET active = 0 where test_id = '%s'", $zipID);
$result = @mysql_query($query);

} while ($row = @mysql_fetch_array($result));

$query = sprintf("SELECT * FROM zipCodes");
$result = @mysql_query($query);
$row = @mysql_fetch_array($result);


}

m1a2x3x7
11-09-2007, 04:46 PM
ok I have a break through...kinda. I have this...

if (isset($_POST['submit']) && $radio1 == 1) {

do{

$query =sprintf("UPDATE zipCodes SET active = 1 where test_id = '%s'", $row['test_id']);
$resultUpdate = @mysql_query($query);


} while ($row = @mysql_fetch_array($result));

$query = sprintf("SELECT * FROM zipCodes");
$result = @mysql_query($query);
$row = @mysql_fetch_array($result);

}elseif (isset($_POST['submit']) && $radio2 == 0) {

do{

$query =sprintf("UPDATE zipCodes SET active = 0 where test_id = '%s'", $row['test_id']);
$resultUpdate = @mysql_query($query);

} while ($row = @mysql_fetch_array($result));

$query = sprintf("SELECT * FROM zipCodes");
$result = @mysql_query($query);
$row = @mysql_fetch_array($result);


}

but say i set the first one to on they all go on. I set it to off the all go off.

m1a2x3x7
11-09-2007, 08:19 PM
ok David I've shrunk the php down some.

But I still am getting the same result as the one above.

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


do{

$query =sprintf("UPDATE zipCodes SET active = $radio1 where test_id = '%s'", $row['test_id']);
$resultUpdate = @mysql_query($query);


} while ($row = @mysql_fetch_array($result));

$query = sprintf("SELECT * FROM zipCodes");
$result = @mysql_query($query);
$row = @mysql_fetch_array($result);

}

davidj
11-09-2007, 08:43 PM
you are performing your select after the update

you need your select first

m1a2x3x7
11-09-2007, 09:24 PM
ok i copied the select and placed it above to dowhile

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

$query = sprintf("SELECT * FROM zipCodes");
$result = @mysql_query($query);
$row = @mysql_fetch_array($result);
do{

$query =sprintf("UPDATE zipCodes SET active = $radio1 where test_id = '%s'", $row['test_id']);
$resultUpdate = @mysql_query($query);


} while ($row = @mysql_fetch_array($result));

$query = sprintf("SELECT * FROM zipCodes");
$result = @mysql_query($query);
$row = @mysql_fetch_array($result);

}


If i change the first zip code they both change but if i try to change to second zip code nothing happens.

you can see it here.

http://www.schultzstudio.com/freelance/index.php