Re: For each loop from an array's contents

Let's say the tables looked like this: create table people( SSN char(11) NOT NULL, pID int(11) UNSIGNED NOT NULL, PRIMARY KEY (SSN), UNIQUE ID (SSN), KEY (pID) ); create table history( pID int(11) UNSIGNED NOT NULL, visitID int(11) UNSIGNED NOT NULL, KEY (pID), KEY (visitID) ); creat table info( visitID int(11) UNSIGNED NOT NULL, visit_time datetime default '0000-00-00 00:00:00' NOT NULL, purchase_amount float(10,2) NOT NULL, PRIMARY KEY (visitID), UNIQUE ID (visitID) KEY (visit_time) ); now just do this query to get all from info with SSN '555-55-5555' SELECT info.* FROM people AS P, history AS H, info AS I WHERE P.SSN='555-55-5555' && P.pID=H.pID && H.visitID=I.visitID That will return all from history where SSN = 555-55-5555 (you really don't need the history table if you don't want). If this isn't what you wanted then it can be a small tutorial on "joins" :O) --Joe On Fri, Oct 06, 2000 at 04:32:46PM -0700, Michael Conley wrote: > My goal: To look in one table to get a value of the "peoplenumber" field > from the people table given the SSN (also in the people database). This > works just fine. I enter an SSN and I can retrieve all of the information > about a user. This should only come up with one row. > > >From there, I want to go to another table (called "history") and search > through the "peoplenumber" column for any entries with the persons > peoplenumber (which we got from the above query). This will likely come up > with multiple rows. From this, the field "historynumber" will be the one we > use for the next query. > > >From this query, I need to go to another table (info) and search it for > instances of each "historynumber". There will likely be multiple rows for > each historynumber. I need to keep track of which hits in the "info" table > relate to which "historynumber" they were searching. > > The process will go like this: > > Enter "111-22-3333" for a social. This searches the "ssn" field of the > people table and comes up with a match of: [peoplenumber]=123, > [firstname]=john, [lastname]=doe. > >From there, I search the history table to find any matches of > people.peoplenumber to history.peoplenumber. (there should be multiple > hits). > >From there, I search the info table to find any matches of > history.historynumber to info.historynumber (there should be multiple hits > for each). > Display a list which will show all of the hits in the history table. Next > to each history table hit will be info on each hit in the info table for > that historynumber. > So for one SSN, there will be multiple historynumber hits. Each > historynumber hit will have multiple info hits under it. > > I have the search working fine for matching the SSN with a peoplenumber. > I am having a tough time taking that peoplenumber and finding all of the > entries in the history table. > I have no idea how to then take each of those historynumber results and > search the info table for them. > > I started messing with "foreach" to go through the array, but I get an error > stating "Invalid argument supplied for foreach()". > > Any help is sure appreciated. The script is below. > > > > <html> > <head> > <title>Search Results</title> > </head> > <? > $user = "someuser"; > $password = "somepassword"; > $database = "somedatabase"; > $link = mysql_connect("localhost", $user, $password); > if ( ! $link) die("Couldn't connect to MySQL"); > mysql_select_db( $database, $link) or die("Couldn't open $database: > ".mysql_error()); > > $result = mysql_query("select * from people where ssn = '$ssn'"); > $number_of_rows = mysql_num_rows($result); > > print "There are $number_of_rows matches in the $database database"; > print "<table border=1>\n"; > > while ($row = mysql_fetch_array($result)) > { > print "<tr>\n"; > print > > "<td>$row[lastname]</td><td>$row[firstname]</td><td>$row[peoplenumber]</td>\ > n"; > print "</tr>\n"; > > foreach ($result as $entry) > { > $history = mysql_query("select * from history where peoplenumber = > $row[peoplenumber]"); > print "<tr>\n"; > print > > "<td>$entry[pnumber]</td><td>$entry[midate]</td><td>$entry[modate]</td>\n"; > print "</tr>\n"; > } > } > > print "</table>\n"; > mysql_close( $link); > ?> > </body> > </html> /*****************************\ * Joe Stump * * www.Care2.com * * Office: 650.328.0198 * * Extension: 122 * \*****************************/ http://www.miester.org/Joe/Stump/Joe_Stump_resume.html -----BEGIN GEEK CODE BLOCK----- Version: 3.12 GB/E/IT d- s++:++ a? C++++ UL++$ P+ L+++$ E----! W+++$ N+@ o? K? w---! O-@ M+@ V-! P(++) PE(+) Y+@ PGP+++@ t+@ 5? R-! tv@ b+ DI++@ D(++++) G++@ e+@ h@ r+! z(+++++**)! ------END GEEK CODE BLOCK------