p2p.wrox.com Forums

Need to download code?

View our list of code downloads.

  Return to Index  

beginning_php thread: Arrays, Queries and Select-boxes

Message #1 by "Peter Simard" <peter@p...> on Tue, 30 Apr 2002 03:20:23
Am I on the right track here?

The jist of this is that ultimately I want to set up a select box that's 
pre-selected with data already in the DB.

The reason for the 2 queries is this:
i only want to allow my user to choose what's available in the DB 
obviously, but also, a new election will need to update the database with 
pricing data specific to the selected ingredient.  As pricing data is 
stored in only one place, the "ingredient" table, that data needs to go 
into the UPDATE.

new MyConnect();
$theCode = "id3cc575193";//will be $_POST['recCode']

$sql = "SELECT rec_ingredient FROM recipe_ingredient_data WHERE rec_code 
='" . $theCode . "'";
$rs = mysql_query($sql) or die(mysql_error());

$sql2 = "SELECT ingredient, gross_cost FROM ingredient";
$rss = mysql_query( $sql2 ) or die( mysql_error());

//declare global arrays
$GLOBALS["ALL"] = array();
$GLOBALS["REC"] = array();

//declare counter variable
$counterVal = 0;

//place this recipes' ingredients into an array with GLOBAL SCOPE

while($rec_ing = mysql_fetch_array( $rs ))
 $REC[$counterVal] = $rec_ing;

//re-set counter variable
$counterVal = 0;

//place ALL ingredients into an array with GLOBAL SCOPE

while($all_ing = mysql_fetch_array( $rss)) {
	$ALL[$counterVal] = $all_ing;

//determine the number of elements in each array
$numIngreds = count($ALL);
$numRecIngreds = count($REC);

// determine the elements common to both arrays
$rCode = array_intersect($REC, $ALL);

//print out the values from the intersection so we can see what we have 
print_r( $rCode);

//access a specififc element of the intersection
echo("<BR><BR>rCode[5][0]== " .$rCode[5][0]);

//spit out some test data
echo("<BR><BR>Rec Array Sample:  " . $REC[4][0] . "\n");
echo("<BR>All ingredients array sample: " . $ALL[0][0] . "<BR><BR>\n");

//diagnostic data
Number of ingredients is: <?= $numIngreds ."<BR>\n"?>
Number of Recipe Ingredients is: <?= $numRecIngreds ."<BR>\n"?>
Message #2 by "Nikolai Devereaux" <yomama@u...> on Mon, 29 Apr 2002 21:34:13 -0700
Hi Peter,

Sorry I've been too busy to take a more in-depth look at the site and code,
but I've got an observation -- if you're making two separate SQL selects and
you expect the result arrays from each of these queries to have overlapping
data, then you should just write an SQL query that does the matching for
you.  After all, that's what database engines are designed to do well, no?

SELECT rid.rec_ingredient, ing.gross_cost
  FROM recipe_ingredient_data rid,
       ingredient             ing
 WHERE rid.rec_code = $theCode
   AND rid.rec_ingredient = ing.ingredient

(I'm assuming that rid.rec_ingredient and ing.ingredient are matching keys.
You can also write this query using a join, though I expect david cameron
will correct my bugs:

SELECT rid.rec_ingredient, ing.gross_cost
  FROM recipe_ingredient_data rid,
       ingredient             ing
 WHERE rid.rec_code = $theCode
  JOIN recipe_ingredient_data ON
       recipe_ingredient_data.rec_ingredient = ingredient.ingredient

Anyway, after this query, you'll have all the ingredients (and their costs)
for a particular recipe.

hope this helps,


Message #3 by Peter Simard <pasimard@v...> on Tue, 30 Apr 2002 00:54:53 -0400
Hey Nik;

I maybe making more work here than is necessary, and your suggestion
may be the way to go, if so I already have the query for that.

What I absolutely must have here is the ability to show the user their
current ingredient for each record of a menu/recipe as a pre-selected value in a drop
a select box.

as an aside: (
The cost data for any ingredient is stored only in the ingredient
table and values for each recipe are calculated/generated on the fly.

So, when the user sees the update form, ALL potential ingredients in
the ingredient table are available in the select box, and, the
ingredient which corresponds/matches the value in their record is pre-selected.

Sorry if I'm:
1) too brain dead to get it at the moment
2) being obtuse

Thanks in advance Nik,

Pete ~Sleepless in New Jersey~

Message #4 by "Peter Simard" <peter@p...> on Tue, 30 Apr 2002 06:14:51
This gives me the pre-selected ingredient i want:

for($i=0; $i < $numIngreds; $i++) 
	if($ALL[$i][0] == $REC[1][0]){
	<OPTION name="selUp" value="<?php echo($ALL[$i][1] . ":" . $ALL
[$i][0]) ?>" SELECTED><?php echo($ALL[$i][0])?></OPTION>

		?><OPTION name="selUp" value="<?php echo($ALL[$i]
[1] . ":" . $ALL[$i][0]) ?>" ><?php echo($ALL[$i][0])?></OPTION>

By the way:

Homestarrunner.com ROCKS!!!


Laugh out loud funny


  Return to Index