I have two MySQL tables, $database1 and $database2. Both have a field in them called ID. I am passing the name of a town to the file using GET (i.e. it's in the URL of the PHP file that holds this code).
I can run this query...
$PlaceName = $_GET['townName'];
$PlaceName = mysql_real_escape_string($PlaceName);
$sql="SELECT * from $database1 LEFT JOIN $database2 on $database1.ID = $database2.ID WHERE PlaceName='$PlaceName'";
$query = mysql_query($sql);
echo '<h1>People who are searching for '.$PlaceName.':</h1>';
echo '<ul>';
while ($row = mysql_fetch_array($query)) {
echo "<li>ID #",$row['ID'],": ",$row['MemberPersonalName']," ",$row['MemberSurname']," -- searching for ",$row['SurnameBeingSearched'],"</li>";
}
echo '</ul>';
...and it works and all is well. Right now the output looks like this...
People who are searching for Hogwarts:
- ID #137: Hermione Granger -- searching for Stern
- ID #137: Hermione Granger -- searching for Engelberg
- ID #503: Harry Potter -- searching for Kreindler
- ID #549: Ron Weasley -- searching for Kreindler
- ID #1062: Draco Malfoy -- searching for Engelberg
- ID #1155: Ginny Weasley -- searching for Kreindler
- ID #1155: Ginny Weasley -- searching for Streisand
But the output needs tweaking, and I'm having trouble writing my SQL query statement to reflect the changes. What I really want is for the output to look like this...
People who are searching for Hogwarts:
- Engelberg is being searched by Hermione Granger (id #137) and Draco Malfoy (id #1062)
- Kreindler is being searched by Harry Potter (id #503), Ron Weasley (id #549), and Ginny Weasley (id #1155)
- Stern is being searched by Hermione Granger (id #137)
- Streisand is being searched by Ginny Weasley (id #1155)
In other words, I need to group the output together by the field 'SurnameBeingSearched', I need to list the names of the people doing the searching in an "X, Y, and Z" output format (where it knows where to add a comma, if necessary, depending on the number of results), and I need to order the results by the 'SurnameBeingSearched' field.
Help? Thanks!
You need to list the names so this isn't an aggregation (in the SQL sense) problem. Keep your current query. You're going to have to do the grouping in code.
So something like: