[SOLVED] Please help, loop error with existing update script

wmguk · July 9, 2008

Hi Guys,

Basically I have 3 basic pages. Add, Edit and Delete.

The Add Page: (this is done and working)

<form name="create" method="post" action="scripts/create.php" enctype="multipart/form-data">
          <p> </p>
            <table width="420" border="0" align="center" cellpadding="0" cellspacing="0" class="boxes">
              <tr>
                <td class="rightmenu">Name:</td>
                <td class="rightmenu"> </td>
                <td><input name="name" type="text" class="boxes" id="name" size="21" /></td>
                <td colspan="3" align="center" valign="middle"><div align="right"><a href="#" onClick="history.go(-1)"><img src="../images/buttons/back.gif" width="42" height="30" border="0"></a></div></td>
              </tr>
              <tr>
                <td class="rightmenu">Style:</td>
                <td width="5" class="rightmenu"> </td>
                <td>
                  <select name="style" class="boxes" id="style">
                    <option value="glass">Glass</option>
                    <option value="mosaic">Mosaic</option>
                    <option value="natural">Natural</option>
                    <option value="ceramic" selected="selected">Ceramic</option>
                    <option value="designer">Designer</option>
                  </select>                </td>
                <td colspan="3" align="center" valign="middle">
                
<input type="hidden" name="MAX_FILE_SIZE" value="5000000" />
Choose a file to upload: <input name="uploadedfile" type="file" /></td>
              </tr><tr>
                <td class="rightmenu">Colour:</td>
                <td width="5" class="rightmenu"> </td>
                <td><select name="colour" class="boxes" id="colour">
                  <option value="beige" selected>Beige</option>
                  <option value="black">Black</option>
                  <option value="blue">Blue</option>
                  <option value="green">Green</option>
                  <option value="grey">Grey</option>
                  <option value="orange">Orange</option>
                  <option value="red">Red / Terr</option>
                  <option value="white">White</option>
                  <option value="yellow">Yellow</option>
                  <option value="others">Others</option>
                  </select></td>
                <td colspan="3" align="center" valign="middle"> </td>
              </tr>
              
              <tr>
                <td class="rightmenu"> </td>
                <td width="5" class="rightmenu"> </td>
                <td> </td>
                <td colspan="3" align="center" valign="middle"> </td>
              </tr>
              <tr>
                <td class="rightmenu">Clearance?</td>
                <td width="5"> </td>
                <td><input name="clear" type="checkbox" class="boxes" id="clear"></td>
                <td class="rightmenu">Special Offer?</td>
                <td width="5"> </td>
                <td><input name="special" type="checkbox" class="boxes" id="special"></td>
                </tr>
              <tr>
                <td class="rightmenu"> </td>
                <td> </td>
                <td> </td>
                <td class="rightmenu"> </td>
                <td> </td>
                <td> </td>
              </tr>
              <tr>
                <td colspan="6" class="rightmenu"><textarea name="info" cols="80" rows="6" class="boxes" id="info"></textarea></td>
                </tr>
              <tr>
                <td class="rightmenu"> </td>
                <td> </td>
                <td> </td>
                <td class="rightmenu"> </td>
                <td> </td>
                <td> </td>
              </tr>
              <tr>
                <td> </td>
                <td width="5"> </td>
                <td> </td>
                <td colspan="3" align="center" valign="middle"> </td>
              </tr>
              <tr>
                <td bgcolor="#6699DD"> </td>
                <td bgcolor="#6699DD"> </td>
                <td bgcolor="#6699DD"> </td>
                <td bgcolor="#6699DD"> </td>
                <td bgcolor="#6699DD"> </td>
                <td bgcolor="#6699DD"> </td>
              </tr>
              <tr>
                <td width="90"> </td>
                <td width="5"> </td>
                <td> </td>
                <td width="90"> </td>
                <td width="5"> </td>
                <td width="90"> </td>
              </tr>
<?PHP
echo "<input type='hidden' name='count' value='$count'>";
for($i = 1;$i <= $count;$i++)
{
?>
<tr><td class="rightmenu">Size:</td>
<td width="5"> </td>
<td><?PHP echo "<input type='text' class='boxes' name='size_$i' value=''>"; ?></td>
<td class="rightmenu">Cost:</td>
<td width="5"> </td>
<td><?PHP echo "<input type='text' class='boxes' name='cost_$i' value=''>"; ?></td></tr>
<?PHP } ?>

<tr><td colspan="6" align="center" valign="middle">
<input name="type" type="hidden" value="tile">
<input name="create" type="submit" class="buttons" value="Create Tile">
</td></tr></table>
          </form>

And the script:

<?
$type = $_POST['type'];

include ("connection.php");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }mysql_select_db($db, $con);
  
  
// Where the file is going to be placed 
$target_path = "../../images/tiles/";

// File Upload Script.
$target_path = $target_path . basename( $_FILES['uploadedfile']['name']); 
$_FILES['uploadedfile']['tmp_name']; 

if(move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $target_path)) {
    echo "";
} else{
    echo "There was an error uploading the file called: ".  basename( $_FILES['uploadedfile']['name']). 
    ", please try again!<br><br>";
} 

//Create Product
  $name = $_POST['name'];
  $style = $_POST['style'];
  $info = $_POST['info'];
  $colour = $_POST['colour'];

  $pic = basename( $_FILES['uploadedfile']['name'] );
    
if (empty($_POST['clear']))
$clear = "off"; 
else 
$clear = "on";

if (empty($_POST['special']))
$special = "off"; 
else 
$special = "on";

mysql_query("INSERT INTO products( name, style, type, lowprice, colour, clear, special, pic, info) VALUES ('$name','$style', '$type', '400.00', '$colour', '$clear', '$special', '$pic', '$info');");

$refnum = mysql_insert_id ();

$count = (int)$_REQUEST['count'];
for($i = 1; $i <= $count; $i++)
{
  $cost = number_format($_REQUEST["cost_".$i], 2, ".", "");
  $size = $_REQUEST['size_'."$i"];
if ($cost > 0 && $size > 0)
mysql_query("INSERT INTO sizes (originalprod, size, cost) VALUES ('$refnum', '$size', '$cost');");
}
//GET ORIGINAL LOWEST COST
$result2 = mysql_query("SELECT * FROM products WHERE refnum = '$refnum'");
while ($row=mysql_fetch_assoc($result2))
{
$originalcost = $row['lowprice'];

//GET LOWEST COST JUST ENTERED
$result = mysql_query("SELECT * FROM sizes WHERE originalprod = '$refnum' ORDER BY cost ASC LIMIT 1");
while ($row=mysql_fetch_assoc($result))
{
$lowprice = $row['cost'];

//COMPARE TO FIND REAL LOWEST COST & INSERT TO THE DB
if ($originalcost < $lowprice) {
$insertprice = $originalcost ; 
}else{
$insertprice =
$lowprice ;
}
$sql = "UPDATE products SET lowprice = '$insertprice' WHERE refnum = '$refnum'";
mysql_query( $sql , $con ) or die( "<strong>Query Error</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql<br><br>" );
}
}

mysql_close($con);?>
<META HTTP-EQUIV="Refresh" CONTENT="0; URL=../main.php">

The Edit Page

<?PHP
session_start(); 
if (!isset($_SESSION['username'])) {
    header("location:index.html");
    exit; } 
$user = $_SESSION['username'];

$refnum = $_GET['refnum'];
include "scripts/connection.php";
$countresult = mysql_query("SELECT * FROM sizes WHERE originalprod = '$refnum' ");
$result = mysql_query("SELECT * FROM products WHERE refnum = '$refnum' ");
$used = mysql_num_rows($countresult);
$count = 10 ;
?>
//ALL HEADER AND HTML BITS ETC
<?PHP
while ($row=mysql_fetch_assoc($result))
{
$pic = $row['pic'];

?>          <form name="create" method="post" action="scripts/edit.php" enctype="multipart/form-data">
          <p> </p>
            <table border="0" align="center" cellpadding="0" cellspacing="0" class="boxes">
              <tr>
                <td class="rightmenu">Name:</td>
                <td class="rightmenu"> </td>
                <td><input name="name" type="text" class="boxes" size="21" value="<? echo $row['name']; ?>" /></td>
                <td colspan="3" rowspan="8" align="center"><img src="../images/tiles/<?php echo $pic; ?>" width="150px" height="150px" border="0"></td>
              </tr>
              <tr>
                <td class="rightmenu"> </td>
                <td class="rightmenu"> </td>
                <td> </td>
              </tr>
              <tr>
                <td class="rightmenu">Style:</td>
                <td class="rightmenu"> </td>
                <td>
                  <select name="style" class="boxes" id="style">
                    <option value="<? echo $row['style']; ?>" selected="selected"><? echo ucwords($row['style']); ?></option>
                    <option value="glass">Glass</option>
                    <option value="mosaic">Mosaic</option>
                    <option value="natural">Natural</option>
                    <option value="ceramic">Ceramic</option>
                    <option value="designer">Designer</option>
                  </select>                </td>
                </tr>
              <tr>
                <td class="rightmenu"> </td>
                <td class="rightmenu"> </td>
                <td> </td>
                </tr>
              <tr>
                <td class="rightmenu">Colour:</td>
                <td class="rightmenu"> </td>
                <td><select name="colour" class="boxes" id="colour">
                  <option value="<? echo $row['colour']; ?>" selected="selected"><? echo ucwords($row['colour']); ?></option>
                  <option value="beige">Beige</option>
                  <option value="black">Black</option>
                  <option value="blue">Blue</option>
                  <option value="green">Green</option>
                  <option value="grey">Grey</option>
                  <option value="orange">Orange</option>
                  <option value="red">Red / Terr</option>
                  <option value="white">White</option>
                  <option value="yellow">Yellow</option>
                  <option value="others">Others</option>
                                </select></td>
                </tr>
              
              <tr>
                <td class="rightmenu"> </td>
                <td class="rightmenu"> </td>
                <td> </td>
                </tr>
              <tr>
                <td class="rightmenu">Clearance?</td>
                <td> </td>
                <td><input name="clear" type="checkbox" class="boxes" id="clear" 
                <? if ($row['clear'] == "on")
			{ 
			echo "checked" ;
			}
			else{ }
			?>></td>
                </tr>
              <tr>
                <td class="rightmenu">Special Offer?</td>
                <td> </td>
                <td><input name="special" type="checkbox" class="boxes" id="special"
			<? if ($row['special'] == "on")
			{ 
			echo "checked" ;
			}
			else{ }
			?>></td>
                </tr>
              
              <tr>
                <td colspan="3" align="left" class="rightmenu"><div align="left"></div></td>
                <td colspan="3" align="center" valign="middle"> </td>
              </tr>
              <tr>
                <td colspan="3" class="rightmenu"><input type="hidden" name="MAX_FILE_SIZE" value="5000000" />
Choose a file to upload: </td>
                <td colspan="3" align="center" valign="middle"><span class="rightmenu">
                  <input name="uploadedfile" type="file" class="boxes" />
                </span></td>
              </tr>
              <tr>
                <td> </td>
                <td> </td>
                <td> </td>
                <td colspan="3" align="center" valign="middle"> </td>
              </tr>
              <tr>
                <td colspan="6"><textarea name="info" cols="80" rows="6" class="boxes" id="info"><? echo $row['info']; ?></textarea></td>
                </tr>
              <tr>
                <td> </td>
                <td> </td>
                <td> </td>
                <td colspan="3" align="center" valign="middle"> </td>
              </tr>
              <tr>
                <td bgcolor="#6699DD"> </td>
                <td bgcolor="#6699DD"> </td>
                <td bgcolor="#6699DD"> </td>
                <td bgcolor="#6699DD"> </td>
                <td bgcolor="#6699DD"> </td>
                <td bgcolor="#6699DD"> </td>
              </tr>
              <tr>
                <td> </td>
                <td> </td>
                <td> </td>
                <td> </td>
                <td> </td>
                <td> </td>
              </tr>
<?PHP
$r = 0;
while ($row=mysql_fetch_assoc($countresult))
{
if($r <= $used) $r++;
?>
<tr><td class="rightmenu">Size:</td>
<td> </td>
<td><?
echo "<input type='text' class='boxes' name='size_$r' value='";
echo $row['size'];
echo "'>";
?> </td>
<td class="rightmenu">Cost:</td>
<td> </td>
<td><?
echo "<input type='text' class='boxes' name='cost_$r' value='";
echo $row['cost']; 
echo "'>";
  }
?> </td></tr>

<?
for($i = $used+1;$i <= $count; $i++)
{
?>
<tr><td class="rightmenu">Size:</td>
<td> </td>
<td>
<?PHP echo "<input type='text' class='boxes' name='size_$i' value=''>"; ?> </td>
<td class="rightmenu">Cost:</td>
<td> </td>
<td>
<?PHP echo "<input type='text' class='boxes' name='cost_$i' value=''>"; }?> </td></tr>
<tr><td colspan="6" align="center" valign="middle">
<input name="type" type="hidden" value="tile">
<input name="pic" type="hidden" value="<?php echo $pic; ?>">
<input name="refnum" type="hidden" value="<? echo $refnum ; ?>">
<input name="edit" type="image" src="../images/buttons/edit.gif" class="buttons" value="Edit"> 
<a href="javascript:history.back(-1);" onClick="return ConfirmBack()"><img src="../images/buttons/back.gif" width="42" height="30" border="0"></a></td>
</tr></table>
          </form><? } ?>

and edit script page

<?
include ("connection.php");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }mysql_select_db($db, $con);
  
if ($_FILES['uploadedfile']['name'] == ''){   
$pic = $_POST['pic']; 
} else {

// Where the file is going to be placed 
$target_path = "../../images/tiles/";

// File Upload Script.
$target_path = $target_path . basename( $_FILES['uploadedfile']['name']); 
$_FILES['uploadedfile']['tmp_name']; 

if(move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $target_path)) {
    $pic = basename( $_FILES['uploadedfile']['name']);
} else {
    echo "There was an error uploading the file called: ".  basename( $_FILES['uploadedfile']['name']). 
    ", please try again!<br><br>"; } 	
}
//Create Product
  $refnum = $_POST['refnum'];
  $name = $_POST['name'];
  $style = $_POST['style'];
  $colour = $_POST['colour'];
  $type = $_POST['type'];
  $info = $_POST['info'];
  

if (empty($_POST['clear']))
$clear = "off"; 
else 
$clear = "on";

if (empty($_POST['special']))
$special = "off"; 
else 
$special = "on";

//Update the product information
mysql_query("UPDATE products SET name = '$name', style = '$style', info = '$info', type='$type', colour = '$colour', clear = '$clear', special = '$special', pic = '$pic' WHERE refnum = '$refnum'");

//Find refnum for each size and cost
$sizeresult = mysql_query("SELECT * FROM sizes WHERE originalprod = '$refnum'");
while ($row=mysql_fetch_assoc($sizeresult))
{
$sizeref = $row['refnum'];


//Run the count information
$count = 10;
for($i = 1; $i <= $count; $i++)
{
  $cost = number_format($_REQUEST["cost_".$i], 2, ".", "");
  $size = $_REQUEST['size_'."$i"];
if ($cost > 0 && $size > 0)

mysql_query("UPDATE sizes SET size = '$size, cost = '$cost' WHERE refnum = '$sizeref'");
} }
mysql_close($con);*/
?>
<META HTTP-EQUIV="Refresh" CONTENT="0; URL=../main.php">

and as always the delete is the easiest, so that is done...

So my question, how can i run the update query in the sizes and cost loop?

craygo · July 9, 2008

look like it will run youjust have so syntax errors

//Run the count information
$count = 10;
for($i = 1; $i <= $count; $i++)
{
  $cost = number_format($_REQUEST["cost_".$i], 2, ".", "");
  $size = $_REQUEST['size_'."$i"];
  if ($cost > 0 && $size > 0){
   mysql_query("UPDATE `sizes` SET `size` = '$size', `cost` = '$cost' WHERE `refnum` = '$sizeref'");
   }
}
mysql_close($con);

Also how do you know the loop needs to run 10 times?? The only thing is you will be updating the same record on every loop. Why would you do this??

You may want to pass the count value from the form to the update script.

Ray

wmguk · July 9, 2008

look like it will run youjust have so syntax errors
//Run the count information
$count = 10;
for($i = 1; $i <= $count; $i++)
{
  $cost = number_format($_REQUEST["cost_".$i], 2, ".", "");
  $size = $_REQUEST['size_'."$i"];
  if ($cost > 0 && $size > 0){
   mysql_query("UPDATE `sizes` SET `size` = '$size', `cost` = '$cost' WHERE `refnum` = '$sizeref'");
   }
}
mysql_close($con);
Also how do you know the loop needs to run 10 times?? The only thing is you will be updating the same record on every loop. Why would you do this??

You may want to pass the count value from the form to the update script.

Ray

Basically on the create page there is an option to make 10 different sizes and prices - but if only 5 are entered then only 5 records will be created, however on the edit page i want 10 boxes to appear, and check the database, see the results and fill the boxes, leaving how ever many are needed to make ten - empty.

ie, 5 in database, then displays 5 empty ones.

They are called size_1, size_2 size_3 etc and cost_1, cost_2 cost_3 etc.... however in the database you get

Refnum | originalprod | size | cost

1 | 1 | 400mm | 18.99

2 | 1 | 500mm | 19.99

3 | 1 | 600mm | 20.99

4 | 1 | 700mm | 21.99

5 | 1 | 800mm | 22.99

originalprod is the id i need to update - so mysql_query("UPDATE `sizes` SET `size` = '$size', `cost` = '$cost' WHERE `originalprod` = '$originalprod'");

but as there are 5 results all with the same originalprod id, i thought be better to use the refnum and loop through.

need to create an array of $refnum WHERE originalprod = $originalprod

then do the update on the array of results or something, but i think i am overcomplicating it, and i could just do with some guidance really

wmguk · July 9, 2008

I put it in ths loop to repeat itself until there were no more results found.

//Find refnum for each size and cost
$sizeresult = mysql_query("SELECT * FROM sizes WHERE originalprod = '$refnum'");
while ($row=mysql_fetch_assoc($sizeresult))
{
$sizeref = $row['refnum'];


//Run the count information
$count = 10;
for($i = 1; $i <= $count; $i++)
{
  $cost = number_format($_REQUEST["cost_".$i], 2, ".", "");
  $size = $_REQUEST['size_'."$i"];
if ($cost > 0 && $size > 0)

mysql_query("UPDATE sizes SET size = '$size, cost = '$cost' WHERE refnum = '$sizeref'");
} 
}

was hoping it would check the database and find 5 results, then run

UPDATE sizes SET size = '$size, cost = '$cost' WHERE refnum = '1'

UPDATE sizes SET size = '$size, cost = '$cost' WHERE refnum = '2'

UPDATE sizes SET size = '$size, cost = '$cost' WHERE refnum = '3'

UPDATE sizes SET size = '$size, cost = '$cost' WHERE refnum = '4'

UPDATE sizes SET size = '$size, cost = '$cost' WHERE refnum = '5'

and so say if i wanted to edit 5 of the results and add 2 new ones, it would also then need to run and insert for however many are not already in the DB

wmguk · July 9, 2008

the other way to do it would be to:

get the current records, and show them in the relevant boxes. then delete where originalprod = "$originalprod"

then do a brand new insert, instead of the update.

Sign In

[SOLVED] Please help, loop error with existing update script

Recommended Posts

wmguk

Link to comment

Share on other sites

craygo

Link to comment

Share on other sites

wmguk

Link to comment

Share on other sites

wmguk

Link to comment

Share on other sites

wmguk

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information