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!
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: 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!
$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: Posting Code in the Forums to learn how to do it too.
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.
<?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');
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.
<?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!
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.
<?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
}
?>
<?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
}
?>
<?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
}
?>