PHP Syntax for "GROUP BY" queries

[ro1960]

I'm looking for the PHP syntax to display records grouped by a column. For example:

Paris:
record 1
record 2
record 3

New York:
record 4
record 5

Moscow:
record 6
etc...

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

[fci]

refer to here:
http://dev.mysql.com/doc/refman/5.0/en/select.html

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

[ro1960]

I know the mySQL syntax. I am actually looking for the PHP syntax to output the result of the query.

[fci]

something like this?

<?php

$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 />';

}

?>


http://us2.php.net/manual/en/function.mysql-connect.php
http://us2.php.net/manual/en/function.mysql-query.php
http://us2.php.net/manual/en/function.mysql-fetch-assoc.php
http://us2.php.net/manual/en/function.mysql-real-escape-string.php

If you are more familiar with OOP follow the code examples from here
http://pear.php.net/package/MDB2

[Kid Charming]

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:


SELECT
MAX(foo) AS foo
FROM
...etc


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

[ro1960]

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.

[fci]

// 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 />';

}

[ro1960]

Thanks, much appreciated!

[ro1960]

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.

[raf]

echo mysql_num_rows($rs);

would give you the number of rows in the recordset.
http://www.php.net/manual/nl/function.mysql-num-rows.php

[ro1960]

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
WHERE
(
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>";
}

//*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($query_news))
{

//*we*only*want*to*print*the*header*if*it*hasn't*been*seen*in*the*result*yet
if ($header!=$row['category1'])
{
print "<div class=\"header\" style=\"margin-top:20px\"><u>".$row['category1']."</u></div><br />\n";
//*means*the*current*header*display*is*for*$row['category1']
$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>";
?>

[johip]

$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!!!!!