Reputation: 256
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:
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:
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!
Upvotes: 1
Views: 241
Reputation:
$PlaceName = $_GET['townName'];
$PlaceName = mysql_real_escape_string($PlaceName);
// note - added order to the query
$sql="SELECT * from $database1 LEFT JOIN $database2 on $database1.ID = $database2.ID WHERE PlaceName='$PlaceName'
ORDER BY SurnameBeingSearched, MemberSurname, MemberPersonalName";
$query = mysql_query($sql);
echo '<h1>People who are searching for '.$PlaceName.':</h1>';
echo '<ul>';
$cntr = mysql_num_rows($query);
if ($cntr > 0) {
$i = 0;
$srchd = mysql_result($query, $i, 'SurnameBeingSearched');
$mbr = mysql_result($query, $i, 'MemberPersonalName');
$mbr = $mbr . " " . mysql_result($query, $i, 'MemberSurname');
$mbr = $mbr . " (id #" . mysql_result($query, $i, 'ID') . ")";
$lin = $srchd . " is being searched by " . $mbr;
$prev = $srchd;
if ($cntr == 1) {
echo "<li>" . $lin . "</li>";
} else {
for ($i = 1; $i< $cntr; $i++) {
$srchd = mysql_result($query, $i, 'SurnameBeingSearched');
$mbr = mysql_result($query, $i, 'MemberPersonalName');
$mbr = $mbr . " " . mysql_result($query, $i, 'MemberSurname');
$mbr = $mbr . " (id #" . mysql_result($query, $i, 'ID') . ")";
if ($srchd == $prev) { // common search
$j = $i + 1;
if ($j < $cntr) { // still have data
$nxt = mysql_result($query, $j, 'SurnameBeingSearched');
if ($prev == $nxt) { // another one coming -- use the comma
$lin = $lin . ", " . $mbr;
} else {
$lin = $lin . ", and " . $mbr; // last member add the 'and' - line is done
echo "<li>" . $lin . "</li>";
}
$prev = $srchd;
} else { // ran out of data - need to finish the line
$lin = $lin . ", and " . $mbr; // last member add the 'and' - line is done
echo "<li>" . $lin . "</li>";
} else { // new search - need to print this line and start a new one
echo "<li>" . $lin . "</li>";
$lin = $srchd . " is being searched by " . $mbr;
$prev = $srchd;
} // test searched = previous
} // next i
} // only one row
} // cntr > 0
echo '</ul>';
/* note: this is not tested
I would recommend using table1 and table2 instead of database1 and database2
or better give the tables meaningful names
I would use active voice instead of passive voice
*/
Upvotes: 0
Reputation: 4755
You might also be able to use the MySQL GROUP_CONCAT()
function.
It would look something like this...
SELECT places_tbl.name, GROUP_CONCAT(people_tbl.name)
FROM places_tbl
LEFT JOIN people_tbl ON (places_tbl.id = people_tbl.id)
GROUP BY places_tbl.id
GROUP_CONCAT()
by default returns the values as comma delimited. You can probably split them up to get the formatting as you need it or use the SEPARATOR
keyword. GROUP_CONCAT(fieldname SEPARATOR '-')
Upvotes: 1
Reputation: 625485
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:
$rows = array();
$last = '';
while ($row = mysql_fetch_array($query)) {
$surname = $row['SurnameBeingSearched'];
$id = $row['ID'];
$name = $row['MemberPersonalName'];
if ($last != $surname) {
$last = $surname;
$rows[] = array();
}
$rows[count($rows)-1][$id] = $name;
}
foreach ($rows as $row) {
// now display each group of names
}
Upvotes: 2