Looped query, assign result variable name according to array number

[matthewst]

How do I do this?

loop a query for id numbers and related info and assign the results to variables named from the array

example loop 1:$0id = id, $0related_info = related_info

example loop 2:$1id = id, $1related_info = related_info

 

 

then, on my processing page take those variables and loop 1: UPDATE table SET info = $0stuff_from_form WHERE id = $0id

then loop 2: UPDATE table SET info = $1stuff_from_form WHERE id = $1id

and so on

[Adam]

Well to start with variables cannot have a leading digit in their name.

 

Take a look into arrays.

[matthewst]

Yeah, I'm an idiot but my question is still the same. How do I get the array values or variables into my looped update query? Right now I'm using foreach to echo the results but then my $id = whatever the last result is. I need $id from loop 1 then $id from loop 2 and so on.

 

<?php
include('include/db_con.php');

if (!$radius){
$radius = '1';
}
$get_lat = "SELECT AVG(latitude) FROM zipcodes WHERE city = '$city' AND state = '$state'";
$got_lat = mysql_query($get_lat);
$rowlat = mysql_fetch_array($got_lat);
$lat = $rowlat[0];

$get_lon = "SELECT AVG(longitude) FROM zipcodes WHERE city = '$city' AND state = '$state'";
$got_lon = mysql_query($get_lon);
$rowlon = mysql_fetch_array($got_lon);
$lon = $rowlon[0];

$coords = array('latitude' =>  $lat, 'longitude' =>   $lon);

$sql = "SELECT DISTINCT city, ( 3959 * acos( cos( radians( {$coords['latitude']} ) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians( {$coords['longitude']} ) ) + sin( radians( {$coords['latitude']} ) ) * sin( radians( latitude ) ) ) ) AS distance FROM zipcodes HAVING distance <= {$radius} ORDER BY distance";
$query = mysql_query($sql);
//while($row = mysql_fetch_assoc($query)){
//$selected_cities = $row['city'];
//echo "$$" . $selected_cities . "$$<br />";
$names = array();
while ($row = mysql_fetch_array($query)) {
$names[] = $row[0];
}
$selected_cities = implode("' or '", array_unique($names));
//echo "{$row['city']} {$row['zipcode']} ({$row['distance']})<br>\n"; 
//echo $selected_cities;
$Get_City = mysql_query("SELECT rest_id FROM restaurant_info WHERE state = '$state' AND city = ('$selected_cities') ORDER BY rest_id");
$rest_ids = array();
while ($got_city = mysql_fetch_array($Get_City)) {
$rest_ids[] = $got_city[0];
}
print_r(array_values($rest_ids));
echo "<form name='mark_sold' action='mark_sold.php' enctype='multipart/form-data' method='post'><table width='1100' border='1'><tr><td><h2>Spot 1</h2>";
foreach ($rest_ids as $id){
//$rest_ids = implode("' or '", $rest_ids);
//$rest_ids = "'" . $rest_ids . "'";
$Get_Ad = mysql_query("SELECT rest_id, rest_name, city, ad_space1 FROM restaurant_info WHERE rest_id = $id");
$got_ad = mysql_fetch_assoc($Get_Ad);
$is_sold = $got_ad['ad_space1'];
$rest_id = $got_ad['rest_id'];
$rest_name = $got_ad['rest_name'];
$city = $got_ad['city'];
if($is_sold){
$is_sold = "<font color='red'>sold</font>";
}else{
$is_sold = "<font color='green'>available</font><input name='ad1' id='ad1' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo $rest_name." in ". $city." is ".$is_sold."<br />";
}
    echo "</td><td><h2>Spot2</h2>";
foreach ($rest_ids as $id){
//$rest_ids = implode("' or '", $rest_ids);
//$rest_ids = "'" . $rest_ids . "'";
echo $id;
$Get_Ad = mysql_query("SELECT rest_id, rest_name, city, ad_space2 FROM restaurant_info WHERE rest_id = $id");
$got_ad = mysql_fetch_assoc($Get_Ad);
$is_sold = $got_ad['ad_space2'];
$rest_id = $got_ad['rest_id'];
$rest_name = $got_ad['rest_name'];
$city = $got_ad['city'];
if($is_sold){
$is_sold = "<font color='red'>sold</font>";
}else{
$is_sold = "<font color='green'>available</font><input name='ad2' type='checkbox' value='1' /><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo $rest_name." in ". $city." is ".$is_sold."<br />";
}
echo "</td><td><h2>Spot 3</h2>";
foreach ($rest_ids as $id){
//$rest_ids = implode("' or '", $rest_ids);
//$rest_ids = "'" . $rest_ids . "'";
$Get_Ad = mysql_query("SELECT rest_id, rest_name, city, ad_space3 FROM restaurant_info WHERE rest_id = $id");
$got_ad = mysql_fetch_assoc($Get_Ad);
$is_sold = $got_ad['ad_space3'];
$rest_id = $got_ad['rest_id'];
$rest_name = $got_ad['rest_name'];
$city = $got_ad['city'];
if($is_sold){
$is_sold = "<font color='red'>sold</font>";
}else{
$is_sold = "<font color='green'>available</font><input name='ad3' type='checkbox' value='1' /><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo $rest_name." in ". $city." is ".$is_sold."<br />";
}
echo "</td><td><h2>Spot 4</h2>";
foreach ($rest_ids as $id){
//$rest_ids = implode("' or '", $rest_ids);
//$rest_ids = "'" . $rest_ids . "'";
$Get_Ad = mysql_query("SELECT rest_id, rest_name, city, ad_space4 FROM restaurant_info WHERE rest_id = $id");
$got_ad = mysql_fetch_assoc($Get_Ad);
$is_sold = $got_ad['ad_space4'];
$rest_id = $got_ad['rest_id'];
$rest_name = $got_ad['rest_name'];
$city = $got_ad['city'];
if($is_sold){
$is_sold = "<font color='red'>sold</font>";
}else{
$is_sold = "<font color='green'>available</font><input name='ad4' type='checkbox' value='1' /><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo $rest_name." in ". $city." is ".$is_sold."<br />";
}
echo "</td><td><h2>Spot 5</h2>";
foreach ($rest_ids as $id){
//$rest_ids = implode("' or '", $rest_ids);
//$rest_ids = "'" . $rest_ids . "'";
$Get_Ad = mysql_query("SELECT rest_id, rest_name, city, ad_space5 FROM restaurant_info WHERE rest_id = $id");
$got_ad = mysql_fetch_assoc($Get_Ad);
$is_sold = $got_ad['ad_space5'];
$rest_id = $got_ad['rest_id'];
$rest_name = $got_ad['rest_name'];
$city = $got_ad['city'];
if($is_sold){
$is_sold = "<font color='red'>sold</font>";
}else{
$is_sold = "<font color='green'>available</font><input name='ad5' type='checkbox' value='1' /><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo $rest_name." in ". $city." is ".$is_sold."<br />";
}
echo "</td></tr><tr><td><h2>Spot 6</h2>";
foreach ($rest_ids as $id){
//$rest_ids = implode("' or '", $rest_ids);
//$rest_ids = "'" . $rest_ids . "'";
$Get_Ad = mysql_query("SELECT rest_id, rest_name, city, ad_space6 FROM restaurant_info WHERE rest_id = $id");
$got_ad = mysql_fetch_assoc($Get_Ad);
$is_sold = $got_ad['ad_space6'];
$rest_id = $got_ad['rest_id'];
$rest_name = $got_ad['rest_name'];
$city = $got_ad['city'];
if($is_sold){
$is_sold = "<font color='red'>sold</font>";
}else{
$is_sold = "<font color='green'>available</font><input name='ad6' type='checkbox' value='1' />";
}
echo $rest_name." in ". $city." is ".$is_sold."<br />";
}
//blah blah blah
echo "</td></tr><tr><td colspan='5'><center><input type='submit' name='submit' value='Submit'></center></td></tr></table></form>";
mysql_close();		
?>

 

And here is my update query

 

mysql_query("UPDATE restaurant_info SET ad_space1='$ad1', ad_space2='$ad2' WHERE rest_id='$rest_id'") or die(mysql_error());

 

[Adam]

Holy crap, what's that?

 

You're not explaining the problem very well at all. Do you want to update the data you've taken from a form or a query?

[matthewst]

That's half my problem, I don't understand most of what I'm coding.

 

Here's what's going on:

query database and display results in a table like so

rest name 1 spot 1_sold___rest name 1 spot 2_avail__rest name 1 spot 3_avail

rest name 2 spot 1_sold___rest name 2 spot 2_avail__rest name 2 spot 3_sold

rest name 3 spot 1_avail__rest name 3 spot 2_avail___rest name 3 spot 3_sold

I've got that done. The problem is my $id variable is different with each loop. So when

I use the (avail)check box on "rest name 1 spot 3" my form process page gets the $id

of rest name 3 because that was what the last loop assigned.

 

I'm sure there is a better way to do this but I don't know it. Thank you for any help you

can offer.

 

I think maybe I'm misunderstanding how to use foreach.

[Adam]

I've got that done. The problem is my $id variable is different with each loop.

 

Could you point out, or post the code just for this part?

[matthewst]

Thanks for having a look.

Lets use Tulsa OK for our first query:

SELECT all rest_id's FROM the restaurant_info table WHERE state OK and city = Tulsa

and put the results in an array called rest_ids

 

Then I use foreach for table cell 1:

for array variable 0 select the related info from the database and echo it - if ad1 = '' then echo "make a check box"

then

for array variable 1 select the related info from the database and echo it - if ad1 = '' then echo "make a check box"

 

Foreach for table cell 2:

for array variable 0 select the related info from the database and echo it - if ad2 = '' then echo "make a check box"

then

for array variable 1 select the related info from the database and echo it - if ad2 = '' then echo "make a check box"

 

The problem is the name of the variable $id is the same each time the query executes. Each cell my contain 30 entries and the table may have 50 cells. How do I tell my processing page I want to tick the box for entry 1 in cell 2?

 

$Get_City = mysql_query("SELECT rest_id FROM restaurant_info WHERE state = '$state' AND city = ('$selected_cities') ORDER BY rest_id");
$rest_ids = array();
while ($got_city = mysql_fetch_array($Get_City)) {
$rest_ids[] = $got_city[0];
}
//print_r(array_values($rest_ids));
echo "<form name='mark_sold' action='mark_sold.php' enctype='multipart/form-data' method='post'><table width='1100' border='1'><tr><td><h2>Spot 1</h2>";
   foreach ($rest_ids as $id){
   //$rest_ids = implode("' or '", $rest_ids);
   //$rest_ids = "'" . $rest_ids . "'";
   $Get_Ad = mysql_query("SELECT rest_id, rest_name, city, ad_space1 FROM restaurant_info WHERE rest_id = $id");
///////////right here $id = 1, but the next time it will be 2 and so on  ---  when i process my form $id is always the last rest_id processed
   $got_ad = mysql_fetch_assoc($Get_Ad);
   $is_sold = $got_ad['ad_space1'];
   $rest_id = $got_ad['rest_id'];
   $rest_name = $got_ad['rest_name'];
   $city = $got_ad['city'];
   if($is_sold){
   $is_sold = "<font color='red'>sold</font>";
   }else{
   $is_sold = "<font color='green'>available</font><input name='ad1' id='ad1' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
   }
   echo $rest_name." in ". $city." is ".$is_sold."<br />";
   }
    echo "</td><td><h2>Spot2</h2>";