[RESOLVED] Update Table where the same.

[SoulAssassin]

I have 2 table
locations
- location_id
- location_code
- box_number

boxes
- box_id
- box_number
- location_code

Somehow I managed to set all location_code in boxes to 0.
How do I select all locations, select all boxes, then Update boxes set locations_code = location_code where box_number is the same.
I need to get those values back into my db urgently.
Here is what I've tried, but it's obviously not right.
And I still havn't done the Update query.
Any help guys?

<? 
$boxselect = mysql_query("Select * from boxes",$con);
$boxs = mysql_num_rows($boxselect);
while($box = mysql_fetch_array($boxselect, MYSQL_ASSOC))
{


$location = mysql_query("Select * from location where box_number = ".$box['location_code']."",$con);
$locationr = mysql_num_rows($location);
while($loc = mysql_fetch_array($location, MYSQL_ASSOC))
{


if ($loc)
{

?>
<form action="" method="post" ">
<table width="62%" border="0">
    <tr> 
      <td width="45%"><div align="right">Location Code from Location Table:</div></td>
      <td width="55%"> <div align="left"> 
  <input name="location_code" type="text" value="<?php echo $loc['location_code'];?>" size="40" />
        </div>
        </td>
    </tr>
    <tr> 
      <td height="24"><div align="right">Box Number:</div></td>
      <td><div align="left"> <input type="text" name="box_number" value="<?php echo $box['box_number'];?>" size="40"></div></td>
    </tr>
  </table>
  <input type="Submit" name="Edit" value="Edit">
</form>
<?
}
}
}
?>

Thanks

[SoulAssassin]

Ok, I figured out that I can do the Select like this:

<?
$selectsame = mysql_query("Select * from boxes, locations WHERE boxes.box_number = locations.box_number",$con);
?>

Now I need to somehow to the Update, Maybe this will work:

<?
$updatebox = mysql_query("Update boxes set location_code='locations.location_code' where location_code='locations.location_code''",$con);
?>

How would I do this?

[phattymatty]

Are you looking for something like this?

<?
if ($_POST['edit'])
{
	$boxselect = mysql_query("Select * from boxes",$con);
	$box_row = mysql_fetch_assoc($boxselect);

$location = mysql_query("Select * from location where box_number = '$box['location_code']'",$con);
$loc_row = mysql_fetch_assoc($location)

if ($loc_row)
{?>
	<form action="" method="post" ">
	<table width="62%" border="0">
	 <tr>
  		<td width="45%"><div align="right">Location Code from Location Table:</div></td>
  		<td width="55%"> <div align="left">
	 <input name="location_code" type="text" value="<?php echo $loc_row['location_code'];?>" size="40" />
   		 </div>
   		 </td>
	 </tr>
	 <tr>
 		 <td height="24"><div align="right">Box Number:</div></td>
  		<td><div align="left"> <input type="text" name="box_number" value="<?php echo $box_row['box_number'];?>" size="40"></div></td>
		</tr>
	</table>
	 <input type="Submit" name="Edit" value="Edit">
	</form>
<?
	}else{ echo "Query for location failed.";}
	$sql = "UPDATE boxes
			SET 
			locations_code = '$loc_row['location_code']
			WHERE box_number ='$box_row['box_number']";
	$result = mysql_query($sql);
	$num_rows = mysql_num_rows($result);
	if (!$result){ echo "Error updating boxes table setting locations code where box number's are same."; }else{ echo $num_rows." changes made."; } 
}
?> 

[laserlight]

What is the relationship between locations and boxes? It seems to me that you may not even need boxes.location_code.

[SoulAssassin]

Shot for the response guys....

Every box has a location linked to it.
There's location which are "vacant".
If a new box is loaded, I update locations box_number with new box's box_number, and insert into boxes the location_code. There's no relations specifically set up.
My boxes table set all values to 0 through some stupid query error I made, and I haven't got a recent backup. I know, slap me.

Thanks phattymatty
your way seems like it might work. Let me try that out quick.

[SoulAssassin]

Just so you know, I'm using PHP 4.
I've put the "if ($_POST['edit'])" in front of the Update query.
But when using mysql_fetch_assoc, it just gives me 1 row, so I don't even get to the Update yet.

This just gives me one row:

<?
$boxselect = mysql_query("Select * from boxes",$con);
$box_row = mysql_fetch_assoc($boxselect);

$location = mysql_query("Select * from location where box_number = ".$box_row['box_number']."",$con);
 $loc_row = mysql_fetch_assoc($location);

if ($loc_row)
{
echo "<br>";
echo $loc_row['location_code'];
echo "<br>";
}
?>

Should I create some association?
I'm sure there's a simple way.

[phattymatty]

That should only return one row because that is what you are specifying in your locations query.

$location = mysql_query("Select * from location where box_number = ".$box_row['box_number']."",$con);
 $loc_row = mysql_fetch_assoc($location);

This query is getting all of the information from locations table about BOX NUMBER x. Thats why you are getting 1 result. What are you trying to do exactly? Even with php4 those methods should work fine. I kinda ran through the example I gave you fast. I may have made a mistake so double check the work. 🆒

SoulAssassin wrote:

Just so you know, I'm using PHP 4.
I've put the "if ($_POST['edit'])" in front of the Update query.
But when using mysql_fetch_assoc, it just gives me 1 row, so I don't even get to the Update yet.

This just gives me one row:

<?
$boxselect = mysql_query("Select * from boxes",$con);
$box_row = mysql_fetch_assoc($boxselect);

$location = mysql_query("Select * from location where box_number = ".$box_row['box_number']."",$con);
 $loc_row = mysql_fetch_assoc($location);

if ($loc_row)
{
echo "<br>";
echo $loc_row['location_code'];
echo "<br>";
}
?>

Should I create some association?
I'm sure there's a simple way.

[phattymatty]

And try this update query instead of the one I posted.....

$sql = "UPDATE boxes
            SET
            boxes.locations_code = '$loc_row['location_code']
            WHERE boxes.box_number ='$loc_row['box_number']"; 

[SoulAssassin]

Got it..
thanks guys.
I thought I was going to it all with one query, but I just had to submit the form for every entry.
This is what worked eventually.

<?php

if($_POST['edit'])
{

$box_numbers = $_POST['box_number'];
$location_codes = $_POST['location_code'];

$result = mysql_query("UPDATE boxes SET location_code ='$location_code' WHERE box_number ='$box_numbers'"); 

if (!$result)
{ 
echo "Error updating boxes table setting locations code where box number's are same.";
}
else
{ 
echo "changes made to Box Number $box_number"; 
}
}

$boxselect = mysql_query("Select * from boxes where location_code = '0'",$con);
$box_row = mysql_fetch_array($boxselect);
echo "<br>"; 
$mybox = $box_row['box_number'];
echo $mybox;

$location = mysql_query("Select * from location where box_number ='$mybox'",$con);
$loca = mysql_num_rows($location);
$loc_row = mysql_fetch_array($location);
$loc_rows = $loc_row['box_number'];
echo "<br>"; 
echo $box_row['box_number'];
if ($loc_row)
{
?>
        <form action="" method="post" >
        <table width="62%" border="0">
            <tr>
              <td width="45%"><div align="right">Location Code:</div></td>
              <td width="55%"> <div align="left">
         <input name="location_code" type="text" value="<?php echo $loc_row['location_code'];?>" size="40" />
                </div>
                </td>
            </tr>
            <tr>
              <td height="24"><div align="right">Box Number:</div></td>
              <td><div align="left"> <input type="text" name="box_number" value="<?php echo $box_row['box_number'];?>" size="40"></div></td>
            </tr>
          </table>
         <input type="Submit" name="edit" value="edit">
        </form>
<? 
}
?>

Resolved....
Appreciated!