• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

PHP/MYSQL Print Values in Groups

Hello experts,

I have a table values called 'weather':

id     city           temperature
1     London       15
2     New York   15
3     Paris           16
4     Rome          18
5     Edinburgh   12

What I would like to do is print all values in groups according to similar temperature so I would get:

12
Edinburgh

15
London
New York

16
Paris

18
Rome

Should I be using the GROUP BY command:

$query = "SELECT * FROM `weather` GROUP BY temperature";

and how wouldI just print one instance of the common temperature as a title for each group? Thanks
0
allanch08
Asked:
allanch08
  • 4
  • 4
  • 3
3 Solutions
 
Ray PaseurCommented:
I would do this in the PHP code that visualized the data, not in the query.  SELECT with ORDER BY temperature ASC, then set the PHP variable $start_temperature to something like -1000.  As you retrieve each row from the db results set, compare the temperature to the $start_temperature.  If they are different, you start a new output group and you replace $start_temperature with the new value.
0
 
allanch08Author Commented:
Thanks Ray, that's sounds like a good plan. I was always under the impression that you should do things in MYSQL first as it is quicker. but I am dealing with a small amount of data <200 rows.
0
 
Ray PaseurCommented:
I tend to think in PHP terms first, and only start thinking MySQL when more complex queries are involved.  In this case, the design pattern is obvious to me using PHP and since you want an entire results set, GROUP BY might not give you exactly what you want.  An interesting question might be the way to group the outputs so that your collected list of cities were organized by temperature ranges, rather than exact degrees.  In this case, an array made from the rows of the results set with keys being the temperature range might be the easiest to implement.  Just a thought, ~Ray
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
maeltarCommented:
This will work :

<?PHP
$user = "username";
$pass = "password";
$db = "database name";

$con = mysql_connect("localhost",$user,$pass);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db($db, $con);

// Get temps from the database using distinct, so if there are multiple of the same temp we only get 1 of it
$sql = "select distinct temp from weather";
$result = mysql_query($sql);

// check the query is ok..
if (!$result)
    {
        // oh dear, not good
        echo "Error : " . mysql_error();
        exit();
    }else{
        // All is good...
        // Itterate through the temps and get the cities...
        while ($row = mysql_fetch_array($result))
        {
            // so we know the array element $row[0] holds the temp for each city
            
            
            //  Now we need to get the cities that have the temps help in out array element $row[0]
                $city_sql = "select city from weather where temp = {$row[0]}";
                $city_result = mysql_query($city_sql);
                // again, catch errors...
                if (!$city_result)
                {
                    echo "Error with city_result : " . mysql_error();
                    exit();
                }else{
                    // count how many cities are same temp
                    $city_num = mysql_num_rows($city_result);
                    
                    // now get the city names..
                    echo "Temperature: {$row[0]}<br />";
                    echo "<p style=\"margin-left:50px;\">";
                    while ($city_row = mysql_fetch_array($city_result))
                    {
                        // now lets show the data...
                        echo "{$city_row[0]}<br />";
                    }
                    echo "</p>";
                }
                
        }
    }
    mysql_close($con);

?>

Open in new window

0
 
maeltarCommented:
Sorry, the database schema I used...

CREATE TABLE `weather` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `city` varchar(50) NOT NULL,
  `temp` double(20,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

Open in new window

0
 
Ray PaseurCommented:
I think if we are using SELECT DISTINCT we would get either London or New York but not both.

See http://www.laprbass.com/RAY_temp_allanch08.php

The important moving parts start at line 78.
<?php // RAY_temp_allanch08.php
error_reporting(E_ALL);


// CITIES GROUPED FOR PRESENTATION BY TEMPERATURE


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES



// CREATING A TABLE OF TEST DATA
$sql = "CREATE TEMPORARY TABLE my_table (
        _key int          NOT NULL AUTO_INCREMENT,
        city VARCHAR(24)  NOT NULL DEFAULT '',
        temp DECIMAL(4,1) NOT NULL DEFAULT 0.0,
        PRIMARY KEY(_key)  )";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}

// LOAD THE TEST DATA
mysql_query("INSERT INTO my_table (city, temp) VALUES ('London',    '15')") or die(mysql_error() );
mysql_query("INSERT INTO my_table (city, temp) VALUES ('New York',  '15')") or die(mysql_error() );
mysql_query("INSERT INTO my_table (city, temp) VALUES ('Paris',     '16')") or die(mysql_error() );
mysql_query("INSERT INTO my_table (city, temp) VALUES ('Rome',      '18')") or die(mysql_error() );
mysql_query("INSERT INTO my_table (city, temp) VALUES ('Edinburgh', '12')") or die(mysql_error() );



// MAKING A SELECT QUERY AND TESTING THE RESULTS
$sql = "SELECT city, temp FROM my_table ORDER BY temp ASC";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
} // IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE SELECTED
$old_temp = -1000.0;
while ($row = mysql_fetch_assoc($res))
{
    if ($old_temp != $row["temp"])
    {
        // CREATE NEW GROUP
        echo "<br/>{$row["temp"]}";
        echo PHP_EOL;
        $old_temp = $row["temp"];
    }

    echo "<br/>&nbsp;{$row["city"]}" . PHP_EOL;
}

Open in new window

0
 
maeltarCommented:
Ray, if you noticed, the distinct is on the temps NOT the cities....
0
 
maeltarCommented:
The purpose is that we only need to know the unique temps..  the use the results to get the cities that are those temps...

0
 
Ray PaseurCommented:
@maeltar: Ha! I just noticed that. (D'Oh, slaps forehead).

Still, I think I would go with one query and get all the data at once; that's just the way I think about problems like this one.
0
 
allanch08Author Commented:
Thanks maeltar, I started trying Rays method when I received your suggestion. I will try yours I've finished this. thanks
0
 
allanch08Author Commented:
Thanks for the help!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now