Page 1 of 1

PHP Newbie: Only last query result showing

Posted: Sun Feb 28, 2010 9:48 am
by CMcP
pickle | Please use [ code=php ], [ code=text ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.


I hope someone can help me with this ... I'm at wit's end here.

I am trying to show results in a page based on information from 2 tables. Since I'm relatively new to PHP, I've decided to use 2 queries to get the results. This approach works fine if only 1 result is being returned. However, if the 1st query returns multiple results, only the last result is processed and displayed in the 2nd query.

Is there some way to show ALL of my desired results using this format? Please help!

--------------- (sample code below) ---------------

Code: Select all

$query = "SELECT Locale FROM Therapists WHERE Available='yes' ";
$result = mysql_query($query)
              or die ("Could Not Execute Query.");
 
while ($row = mysql_fetch_assoc($result))
{
extract($row);
 
echo" <br>Locale: $Locale</br>\n";  // displays 2 results
}
?>
 
<?php
$query = "SELECT * FROM Locations WHERE Locale='$Locale' ";
$result = mysql_query($query)
              or die ("Could Not Execute Query.");
 
while ($row = mysql_fetch_assoc($result))
{
extract($row);
 
echo" <br>Locale: $Locale</br>\n";
         <br>Locale: $Address</br>\n";  // displays only last record
}
?>
 

pickle | Please use [ code=php ], [ code=text ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.

Re: PHP Newbie: Only last query result showing

Posted: Sun Feb 28, 2010 10:10 am
by davex
Hi,

Your first query returns numerous rows but the $Locale variable will just be one at a time - when the first query finishes it's just the last result returned as you have discovered.

You need to update your second query to search for all the Locales either using ORs in the WHERE query or (my preference) the IN option.

Code: Select all

 
<?php
$query = "SELECT Locale FROM Therapists WHERE Available='yes' ";
$result = mysql_query($query)
or die ("Could Not Execute Query.");
 
$locale_list=array();
 
while ($row = mysql_fetch_assoc($result))
{
extract($row);
 
echo" <br>Locale: $Locale</br>\n"; // displays 2 results
$locale_list[]=$Locale;
}
 
$query = "SELECT * FROM Locations WHERE Locale IN(";
$first=true;
foreach($locale_list as $a_locale)
 {
 if ($first) $first=false;
 else $query.=","; // if not the first entry put a comma in
 $query.="'".$a_locale."'";
 }
$query.=")";
$result = mysql_query($query)
or die ("Could Not Execute Query.");
 
while ($row = mysql_fetch_assoc($result))
{
extract($row);
 
echo" <br>Locale: $Locale</br>\n";
<br>Locale: $Address</br>\n"; // displays only last record
}
?>
In this way your second query ends up being something like:

SELECT * FROM Locations where Locale IN('first_locale','second_locale');

Hope that helps,

Dave.

Re: PHP Newbie: Only last query result showing

Posted: Sun Feb 28, 2010 11:55 am
by CMcP
I've tried the method that you described and I still can't get ti to work. If possible could you please explain the previous method you mentioned of using ORs in the WHERE clause? I'd greatley appreciate any help you could give me.

Re: PHP Newbie: Only last query result showing

Posted: Sun Feb 28, 2010 12:02 pm
by davex
Hi,

Can you maybe echo the query it is generating and post that? I probably made a mistake in the code.

As for the OR's it would work pretty much the same way with code like:

Code: Select all

<?php
$query = "SELECT Locale FROM Therapists WHERE Available='yes' ";
$result = mysql_query($query)
or die ("Could Not Execute Query.");
 
$locale_list=array();
 
while ($row = mysql_fetch_assoc($result))
{
extract($row);
 
echo" <br>Locale: $Locale</br>\n"; // displays 2 results
$locale_list[]=$Locale;
}
 
$query = "SELECT * FROM Locations WHERE ";
$first=true;
foreach($locale_list as $a_locale)
 {
 if ($first) $first=false;
 else $query.=" OR "; // if not the first entry put a comma in
 $query.="Locale='".$a_locale."'";
 }
$query.=")";
$result = mysql_query($query)
or die ("Could Not Execute Query.");
 
while ($row = mysql_fetch_assoc($result))
{
extract($row);
 
echo" <br>Locale: $Locale</br>\n";
<br>Locale: $Address</br>\n"; // displays only last record
}
?>
This should build a query of:

SELECT * FROM Locations WHERE Locale='x' OR Locale='y' OR Locale='z'

Personally I always prefer the IN as sometimes this can generate a really really long OR OR OR query.

Please post the generated query from the IN example and maybe I can see what I messed up in my example!

Cheers,

Dave.

Re: PHP Newbie: Only last query result showing

Posted: Sun Feb 28, 2010 1:27 pm
by CMcP
As with the previous script, the first query works beautifully (I separated it into 2 queries to do some testing). However, when the 2nd query is added, the output is just a blank screen. I've tried to decipher the 2nd query to figure out what might be causing it, but because I'm a newbie at this I wasn't able to find the cause.

Can you help?

Re: PHP Newbie: Only last query result showing

Posted: Sun Feb 28, 2010 1:35 pm
by davex
Please echo the query from either the IN or the OR solution like this:

Code: Select all

<?php
$query = "SELECT Locale FROM Therapists WHERE Available='yes' ";
$result = mysql_query($query)
or die ("Could Not Execute Query.");
 
$locale_list=array();
 
while ($row = mysql_fetch_assoc($result))
{
extract($row);
 
echo" <br>Locale: $Locale</br>\n"; // displays 2 results
$locale_list[]=$Locale;
}
 
$query = "SELECT * FROM Locations WHERE Locale IN(";
$first=true;
foreach($locale_list as $a_locale)
 {
 if ($first) $first=false;
 else $query.=","; // if not the first entry put a comma in
 $query.="'".$a_locale."'";
 }
$query.=")";
 
echo "<br />Query: ".$query."<br />";
 
$result = mysql_query($query)
or die ("Could Not Execute Query.");
 
while ($row = mysql_fetch_assoc($result))
{
extract($row);
 
echo" <br>Locale: $Locale</br>\n";
<br>Locale: $Address</br>\n"; // displays only last record
}
?>
What is the query shown?

Cheers,

Dave.

Re: PHP Newbie: Only last query result showing

Posted: Sun Feb 28, 2010 2:01 pm
by CMcP
Nothing is shown ... the result (as with the other attempts) is a blank web page. :(

Re: PHP Newbie: Only last query result showing

Posted: Sun Feb 28, 2010 2:18 pm
by davex
Try this:

** Updated to fix syntax error I noticed **

Code: Select all

<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);
 
$query = "SELECT Locale FROM Therapists WHERE Available='yes' ";
$result = mysql_query($query)
or die ("Could Not Execute Query.");
 
$locale_list=array();
 
while ($row = mysql_fetch_assoc($result))
{
extract($row);
 
echo" <br>Locale: $Locale</br>\n"; // displays 2 results
$locale_list[]=$Locale;
}
 
$query = "SELECT * FROM Locations WHERE Locale IN(";
$first=true;
foreach($locale_list as $a_locale)
 {
 if ($first) $first=false;
 else $query.=","; // if not the first entry put a comma in
 $query.="'".$a_locale."'";
 }
$query.=")";
 
echo "<br />Query: ".$query."<br />";
 
$result = mysql_query($query)
or die ("Could Not Execute Query.");
 
while ($row = mysql_fetch_assoc($result))
{
extract($row);
 
echo "<br>Locale: $Locale</br>\n";
echo "<br>Locale: $Address</br>\n"; // displays only last record
}
?>

Re: PHP Newbie: Only last query result showing

Posted: Sun Feb 28, 2010 2:51 pm
by davex
Ok just actually tried this in PHP and the following works fine for me:

Code: Select all

<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);
 
// Set these to your credentials:
$sql=mysql_connect("MYSQL_SERVER i.e. 127.0.0.1","MYSQL_USERNAME i.e. root","MYSQL_USER_PASSWORD");
mysql_select_db("MYSQL_DATABASE i.e. therapistdb");
 
$query = "SELECT Locale FROM Therapists WHERE Available='yes' ";
$result = mysql_query($query)
or die ("Could Not Execute Query.");
 
$locale_list=array();
 
while ($row = mysql_fetch_assoc($result))
{
extract($row);
 
echo" <br>Locale: $Locale</br>\n"; // displays 2 results
$locale_list[]=$Locale;
}
 
$query = "SELECT * FROM Locations WHERE Locale IN(";
$first=true;
foreach($locale_list as $a_locale)
 {
 if ($first) $first=false;
 else $query.=","; // if not the first entry put a comma in
 $query.="'".$a_locale."'";
 }
$query.=")";
 
//echo "<br />Query: ".$query."<br />";
 
$result = mysql_query($query)
or die ("Could Not Execute Query.");
 
while ($row = mysql_fetch_assoc($result))
{
extract($row);
 
echo " <br>Locale: $Locale</br>\n";
echo "<br>Address: $Address</br>\n"; // displays only last record
}
?>

Re: PHP Newbie: Only last query result showing

Posted: Tue Mar 09, 2010 3:44 am
by CMcP
Everything works beautifully now ... thanks for all of your help!

:D

Re: PHP Newbie: Only last query result showing

Posted: Tue Mar 09, 2010 3:46 am
by davex
Glad to hear it - good luck with your project.

Cheers,

Dave.