PHP Newbie: Only last query result showing

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
CMcP
Forum Newbie
Posts: 5
Joined: Sun Feb 28, 2010 9:29 am

PHP Newbie: Only last query result showing

Post 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.
davex
Forum Contributor
Posts: 101
Joined: Sat Feb 27, 2010 4:10 pm
Location: Namibia

Re: PHP Newbie: Only last query result showing

Post 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.
CMcP
Forum Newbie
Posts: 5
Joined: Sun Feb 28, 2010 9:29 am

Re: PHP Newbie: Only last query result showing

Post 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.
davex
Forum Contributor
Posts: 101
Joined: Sat Feb 27, 2010 4:10 pm
Location: Namibia

Re: PHP Newbie: Only last query result showing

Post 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.
CMcP
Forum Newbie
Posts: 5
Joined: Sun Feb 28, 2010 9:29 am

Re: PHP Newbie: Only last query result showing

Post 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?
davex
Forum Contributor
Posts: 101
Joined: Sat Feb 27, 2010 4:10 pm
Location: Namibia

Re: PHP Newbie: Only last query result showing

Post 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.
CMcP
Forum Newbie
Posts: 5
Joined: Sun Feb 28, 2010 9:29 am

Re: PHP Newbie: Only last query result showing

Post by CMcP »

Nothing is shown ... the result (as with the other attempts) is a blank web page. :(
davex
Forum Contributor
Posts: 101
Joined: Sat Feb 27, 2010 4:10 pm
Location: Namibia

Re: PHP Newbie: Only last query result showing

Post 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
}
?>
davex
Forum Contributor
Posts: 101
Joined: Sat Feb 27, 2010 4:10 pm
Location: Namibia

Re: PHP Newbie: Only last query result showing

Post 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
}
?>
CMcP
Forum Newbie
Posts: 5
Joined: Sun Feb 28, 2010 9:29 am

Re: PHP Newbie: Only last query result showing

Post by CMcP »

Everything works beautifully now ... thanks for all of your help!

:D
davex
Forum Contributor
Posts: 101
Joined: Sat Feb 27, 2010 4:10 pm
Location: Namibia

Re: PHP Newbie: Only last query result showing

Post by davex »

Glad to hear it - good luck with your project.

Cheers,

Dave.
Post Reply