View Full Version : PHP Syntax for "GROUP BY" queries

06-22-2006, 02:01 PM
I'm looking for the PHP syntax to display records grouped by a column. For example:

record 1
record 2
record 3

New York:
record 4
record 5

record 6

Here's my MySQL query:

SELECT idx, site_name FROM links WHERE (site_name LIKE '%$q%' OR city LIKE '%$q%' OR body_short LIKE '%$q%' OR body LIKE '%$q%' OR country LIKE '%$q%') AND live = 'yes' ORDER BY site_name, city, country, date_created DESC

I want to group results by the column category

06-22-2006, 02:17 PM
refer to here:

their example helps give you the order of the query.. the group by would go before the order by.

06-22-2006, 02:47 PM
I know the mySQL syntax. I am actually looking for the PHP syntax to output the result of the query.

06-22-2006, 03:15 PM
something like this?


$dbh = mysql_connect('localhost', 'mysql_user', 'mysql_password');

$q = mysql_real_escape_string($q);
$rs = mysql_query("
SELECT idx, site_name, city
FROM links
WHERE (site_name LIKE '%$q%'
OR city LIKE '%$q%'
OR body_short LIKE '%$q%'
OR body LIKE '%$q%'
OR country LIKE '%$q%')
AND live = 'yes'
ORDER BY site_name, city, country, date_created DESC");

$header = '';
while ($row=mysql_fetch_assoc($rs)) {
if ($header!=$row['city']) {
print $row['city'].'<br />';
$header = $row['city'];
print '<a href="sites.php?idx='.$row['idx'].'">'.htmlentities($row['site_name']).'</a><br />';




If you are more familiar with OOP follow the code examples from here

Kid Charming
06-22-2006, 05:43 PM
The PHP syntax is completely separate from the MySQL -- a GROUP BY or any other query-language structure has no effect on the PHP code. If you're running any of the selected columns through a function (like MAX(), MIN(), etc.), you should alias it in your query so you can use the alias to refer to it:

MAX(foo) AS foo

Then you can still refer to that column as $row['foo'] in your code.

06-22-2006, 06:43 PM
Thanks fci, that's what I was looking for.

Do you care to explain to a novice each step of your code from $header = ''; on? Thanks.

06-22-2006, 06:52 PM
// initialize header and set it to an empty string
$header = '';

// mysql_fetch_assoc will return each row from your result set
// when there are no more results, the while loop ends
while ($row=mysql_fetch_assoc($rs)) {

// we only want to print the header if it hasn't been seen in the result yet
if ($header!=$row['city']) {
print $row['city'].'<br />';

// means the current header display is for $row['city']
$header = $row['city'];

// print a link to your site name
print '<a href="sites.php?idx='.$row['idx'].'">'.htmlentities($row['site_name']).'</a><br />';


06-23-2006, 12:26 PM
Thanks, much appreciated!

06-23-2006, 12:49 PM
I would like to count the returned rows to this, what would be the syntax? I tried to add COUNT(fields......) AS rowcount but I get an error.

06-23-2006, 01:09 PM
echo mysql_num_rows($rs);

would give you the number of rows in the recordset.

06-30-2006, 12:11 PM
I will leave the rowcount aside for now.

What I would like to do now is store the categories in a separate table. So I created a "categories" table:

idx int(11)
category varchar(50)
label varchar(50)
type varchar(50)

So I want to do the exact same thing, sorting my results by category, but this time time pulling the category label from the categories table.

The match between the 2 tables is on categories.idx and links.category1.

I tried to add the new elements to the query but then I had trouble modifying the PHP code that group the results.

Can someone help with the syntax?

Here's my current code:

$connection = mysql_connect($host,$user,$password)
or die ("couldn't connect to server");
$db = mysql_select_db($database,$connection)
or die ("Couldn't select database");

$q_esc = mysql_real_escape_string($q);
$query_news = mysql_query("
SELECT idx, site_name, city, category1
FROM links
site_name LIKE '%$q_esc%'
OR city LIKE '%$q_esc%'
OR body_short LIKE '%$q_esc%'
OR body LIKE '%$q_esc%'
OR country LIKE '%$q_esc%'
AND live = 'yes'
ORDER BY category1, site_name, city, country, date_created DESC");

if ($q)
echo "<p class=\"header_plus\" style=\"color:#c60\">";
echo "Search results for <i><u>".stripslashes($q)."</u></i>";
echo "</p>";

$header = '';
while ($row=mysql_fetch_assoc($query_news))

if ($header!=$row['category1'])
print "<div class=\"header\" style=\"margin-top:20px\"><u>".$row['category1']."</u></div><br />\n";
$header = $row['category1'];
print "<div style=\"margin-bottom:8px\"><a href=\"javascript:openItem('item_link.php?idx=".$row['idx']."')\">".$row['site_name']."</a>";
if ($row['city'])
print " (".$row['city'].")";
print "</div>\n";

echo "<p>";
include ("includes/find_links.inc");
echo "</p>";

05-23-2010, 06:08 PM
$header = '';

$query2 = mysql_query("select your_title from your_table where your_title='$your_variable' order by your_title") or die(mysql_error());

while ($row = mysql_fetch_array($query2)){

if ($header!=$row['your_title']) {

echo " " . $row[your_title] . ".<br>";

$your_variable= $row[your_title];
$header = $row[your_title];

It's very good!!!! it's work!! thanks!!! thanks!!! thanks!!!!!