Need to reuse same query data in several fields

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
retareed
Forum Newbie
Posts: 4
Joined: Wed Sep 03, 2008 1:47 pm

Need to reuse same query data in several fields

Post by retareed »

I'm trying to display a list of hospitals/insurance companies from which to choose, but need to include that list ten times in the same form. It would be ideal to have the second and subsequent lists of hospitals display only those hospitals which have not been previously selected -- a diminishing list, if you will. I'd just like to conquer the multiple lists first. Here's my current code:

Code: Select all

        $sqlhosp = "SELECT HospInsCo FROM hospitals ORDER BY HospInsCo ASC";
        $sqlhosp_result = mysql_query($sqlhosp) or die (mysql_error());
// (snip)
<td width=\"200\"><select name=\"hospital1\">
            <option value=\"\"> -- Select -- </option>";
            while ($row = mysql_fetch_array($sqlhosp_result)) {
                    $HospInsCo = $row["HospInsCo"];
                    echo "<option value=\"$HospInsCo\">$HospInsCo</option>";
            }
            echo "
            </select></td>
 
 
I've tried duplicating the above but changing the select name to hospital2, hospital3 and so on. Currently I only get the complete list of hospitals in the first select statement. Subsequent select statements only offer the option of " -- Select --". What is the best way to accomplish my goal?
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Need to reuse same query data in several fields

Post by andyhoneycutt »

I would guess you're only re-using the code to display the hospitals and not re-running the query. I suggest you load the results into an array and use that array to display results on each subsequent go-around.

-Andy
retareed
Forum Newbie
Posts: 4
Joined: Wed Sep 03, 2008 1:47 pm

Re: Need to reuse same query data in several fields

Post by retareed »

I've toyed with that idea, but I don't think I understand arrays completely and how to really manipulate them. I gleaned the current code from examples found elsewhere. It appears the hospitals are in $row["HospInsCo"] and I've searched and tried different things like:

Code: Select all

for ($i=0; $i<count($row["HospInsCo"]);$i++)
    {
$displaydata=$row["HospInsCo"]."<br>";
echo "<option value=\"$displaydata\">$displaydata</option>";
        }
 
...but again, I must not understand arrays enough to manipulate them fully.

I shouldn't need to re-run the query, should I?
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Need to reuse same query data in several fields

Post by andyhoneycutt »

The only reason why I suggest you may need to run the query again is because you're popping elements off the result set until there is nothing left to pop in your first while loop. What happens then, later, if you try to loop through the results again? This is the loop popping elements off your result set:

Code: Select all

<option value=\"\"> -- Select -- </option>";
            while ($row = mysql_fetch_array($sqlhosp_result)) {
                    $HospInsCo = $row["HospInsCo"];
                    echo "<option value=\"$HospInsCo\">$HospInsCo</option>";
            }
One thing you could do to avoid having to pull a new result set every time you wish to display your data:

Code: Select all

$sqlhosp = "SELECT HospInsCo FROM hospitals ORDER BY HospInsCo ASC";
$sqlhosp_result = mysql_query($sqlhosp) or die (mysql_error());
$a = array();
while( $row = mysql_fetch_assoc($sqlhosp_result) )
  $a[] = $row["HospInsCo"];
 
// loop through results any time in your code later...
foreach($a as $HospInsCo)
  echo "<option value=\"$HospInsCo\">$HospInsCo</option>";
Hope that all makes sense!

-Andy
retareed
Forum Newbie
Posts: 4
Joined: Wed Sep 03, 2008 1:47 pm

Re: Need to reuse same query data in several fields

Post by retareed »

Thanks Andy! That makes more sense than the other bit I was using. I'll give it a try and let you know.
retareed
Forum Newbie
Posts: 4
Joined: Wed Sep 03, 2008 1:47 pm

Re: Need to reuse same query data in several fields

Post by retareed »

Works like a charm! Andy ROCKS! :D
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Need to reuse same query data in several fields

Post by andyhoneycutt »

Good deal buddy, glad I could help.

-Andy
zet
Forum Newbie
Posts: 1
Joined: Wed Oct 01, 2008 11:58 pm

Re: Need to reuse same query data in several fields

Post by zet »

How About reusing mysql_fetch_array for 2 arrays ?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Need to reuse same query data in several fields

Post by VladSun »

I can't agree one should use a buffer in this case ...
Take a look at this very usefull function ;)

http://bg2.php.net/manual/en/function.m ... a-seek.php
mysql_data_seek — Move internal result pointer
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply