Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    New Coder
    Join Date
    Dec 2005
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    PHP Syntax for "GROUP BY" queries

    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:
    Code:
    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

  • #2
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #3
    New Coder
    Join Date
    Dec 2005
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I know the mySQL syntax. I am actually looking for the PHP syntax to output the result of the query.

  • #4
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    something like this?
    PHP Code:
    <?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/functio...etch-assoc.php
    http://us2.php.net/manual/en/functio...ape-string.php

    If you are more familiar with OOP follow the code examples from here
    http://pear.php.net/package/MDB2
    Last edited by fci; 06-22-2006 at 03:17 PM.

  • #5
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:

    Code:
    SELECT
     MAX(foo) AS foo
    FROM
    ...etc
    Then you can still refer to that column as $row['foo'] in your code.

  • #6
    New Coder
    Join Date
    Dec 2005
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #7
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    PHP Code:
    // 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 />';
            


  • #8
    New Coder
    Join Date
    Dec 2005
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, much appreciated!

  • #9
    New Coder
    Join Date
    Dec 2005
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #10
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    PHP Code:
    echo mysql_num_rows($rs); 
    would give you the number of rows in the recordset.
    http://www.php.net/manual/nl/functio...l-num-rows.php
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #11
    New Coder
    Join Date
    Dec 2005
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:

    Code:
    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:

    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>";
    		?>

  • #12
    New to the CF scene
    Join Date
    May 2010
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Smile Thanks! you're a genius!!!

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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •