Need help with efficient query

[unrelenting]

For example, If I have a database full of lunch menus for each day of the week like this:

 

monday,sandwiches

monday,chips

monday,milk

monday,apple

tuesday,pizza

tuesday,lemonade

tuesday,orange

 

This isn't what I need fixed but if I can see how this is done I can add it to mine.

 

I want to display them like this:

 

monday

sandwiches

chips

milk

apple

 

tuesday

pizza

lemonade

orange

 

I have it working with a while loop inside of a while loop. The first of which I use the DISTINCT in my query to only get on day for each time it is displayed and in the second while loop I just use a WHERE day = "' . $row['day'] . '"

 

I realize this is not a good way to do it so I am asking you that are smarter than I to show me how to do it correctly.

[xtopolis]

I don't know the sql off hand, but I think you'd use "GROUP BY" on the column holding the day values [monday,tues..]

I think.

[F1Fan]

Yes, the GROUP BY clause is the way to go. I don't know the structure of your table, so it is difficult to give you the exact query, but something like this:

 

SELECT DISTINCT(food), day FROM lunch_menu GROUP BY day ORDER BY day

[unrelenting]

Yes, the GROUP BY clause is the way to go. I don't know the structure of your table, so it is difficult to give you the exact query, but something like this:

 

SELECT DISTINCT(food), day FROM lunch_menu GROUP BY day ORDER BY day

 

If the table is like this:

 

Table name: lunch_menu

Column 1: day

Column 2: food

 

That looks like this:

 

monday,sandwiches

monday,chips

monday,milk

monday,apple

tuesday,pizza

tuesday,lemonade

tuesday,orange

 

Using the statement you suggested only returns one day but it also only returns one distinct food. I need it to return a disticnt date and all of the foods from that day even if some of them are duplicates.

[dropfaith]

http://www.tizag.com/mysqlTutorial/mysqlgroupby.php  the group by function tutorial should help you out

[unrelenting]

http://www.tizag.com/mysqlTutorial/mysqlgroupby.php   the group by function tutorial should help you out

 

I understand how it works but I need it to display all of the foods from each day and this select statement is only returning one day with one food item. That's the part that I don't understand.

[JasonLewis]

Are you looping through the data?

 

As in:

 

$query = mysql_query("blah blah");

while($r = mysql_fetch_array($query)){
//echo data here
}

[dropfaith]

post the exact code your using so someone can fix it or point out whats going on

[unrelenting]

Are you looping through the data?

 

As in:

 

$query = mysql_query("blah blah");

while($r = mysql_fetch_array($query)){
//echo data here
}

 

No, I just tested the sql query in a query window. How would I need to word the loop to get it to display the results as I had in my initial post.

[unrelenting]

post the exact code your using so someone can fix it or point out whats going on

 

That's the problem. I don't have the code for it written. As I stated in my initial post, I have it set up to run as a loop inside of a loop right now and it works fine like that but I realize that isn't the best way to do it. I was asking how someone would do this simple example and I will then be able to apply it to my script, which is a lot more complex than this.

 

Basically, I just need to see this example looped out and working for me.

[corbin]

Order by day would group all of your days together.

 

 

IE:

 

SELECT * FROM menus ORDER BY day;

 

Would give you the results in order of the day....  If the days were letters though, it wouldn't give them in week order.

[unrelenting]

Order by day would group all of your days together.

 

 

IE:

 

SELECT * FROM menus ORDER BY day;

 

Would give you the results in order of the day....  If the days were letters though, it wouldn't give them in week order.

 

I understand that but it only displays one row per day. I need it to be displayed like the example in my first post.

[F1Fan]

I know what your asking for, and I know it's possible. I have done it many times, and I'm trying really hard to remember what it was. What about this?

 

SELECT day, DISTINCT(food) FROM lunch_menu GROUP BY day ORDER BY day

[unrelenting]

I know what your asking for, and I know it's possible. I have done it many times, and I'm trying really hard to remember what it was. What about this?

 

SELECT day, DISTINCT(food) FROM lunch_menu GROUP BY day ORDER BY day

 

That throws an error and I can't figure out why.

[dropfaith]

what is the error?

[unrelenting]

what is the error?

 

Error

 

SQL query: Documentation

 

SELECT DAY , DISTINCT (

food

)

FROM lunch_menu

GROUP BY DAY ORDER BY DAY

LIMIT 0 , 30

 

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT(food) FROM lunch_menu GROUP BY day ORDER BY day

LIMIT 0, 30' at line 1

[F1Fan]

Limit 0 would limit the query to returning 0 rows. Try removing the LIMIT/OFFSET part.

[corbin]

Order by day would group all of your days together.

 

 

IE:

 

SELECT * FROM menus ORDER BY day;

 

Would give you the results in order of the day....  If the days were letters though, it wouldn't give them in week order.

 

I understand that but it only displays one row per day. I need it to be displayed like the example in my first post.

 

 

 

And you couldn't format it in PHP?

[unrelenting]

Limit 0 would limit the query to returning 0 rows. Try removing the LIMIT/OFFSET part.

 

It added that on it's own in the PHPMyAdmin query window. I tried it with limit 10 and it errors the same way.

 

Error

 

SQL query: Documentation

 

SELECT DAY, DISTINCT (

food

)

FROM lunch_menu

GROUP BY DAY

ORDER BY DAY

LIMIT 10

 

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT(food) FROM lunch_menu GROUP BY day ORDER BY day LIMIT 10' at line 1

 

 

 

Here's the table dump if you'd like to try it out....

 

CREATE TABLE IF NOT EXISTS `lunch_menu` (
 `day` varchar(20) NOT NULL,
 `food` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `lunch_menu`
--

INSERT INTO `lunch_menu` (`day`, `food`) VALUES
('monday', 'sandwiches'),
('monday', 'chips'),
('monday', 'milk'),
('monday', 'apple'),
('tuesday', 'pizza'),
('tuesday', 'lemonade'),
('tuesday', 'orange'),
('tuesday', 'pizza');

[unrelenting]

Order by day would group all of your days together.

 

 

IE:

 

SELECT * FROM menus ORDER BY day;

 

Would give you the results in order of the day....  If the days were letters though, it wouldn't give them in week order.

 

I understand that but it only displays one row per day. I need it to be displayed like the example in my first post.

 

 

 

And you couldn't format it in PHP?

 

No, I can't. That's why I am here soliciting advice.

[corbin]

It would be fairly simple:

 

$q = mysql_query("SELECT * FROM lunch_menu ORDER BY day;");

if(mysql_num_rows($q)) {

    $menu = array('monday' => array(), 'tuesday' => array(), 'wednesday' => array(), 'thursday' => array(), 'friday' => array(), 'saturday' => array(), 'sunday' => array());

    while($r = mysql_fetch_assoc($q)) {
        $menu[$r['day']][] = $r['food'];
    }

    foreach($menu as $day => $foods) {
        echo "<strong>{$day}</strong><br />\n";
        foreach($foods as $food) {
            echo "{$food}<br />\n";
        }
    }
    
}
else {
    //no menu data
}

 

 

Terribly inneficient, but not much you can do with data structured like this.

[unrelenting]

It would be fairly simple:

 

$q = mysql_query("SELECT * FROM lunch_menu ORDER BY day;");

if(mysql_num_rows($q)) {

    $menu = array('monday' => array(), 'tuesday' => array(), 'wednesday' => array(), 'thursday' => array(), 'friday' => array(), 'saturday' => array(), 'sunday' => array());

    while($r = mysql_fetch_assoc($q)) {
        $menu[$r['day']][] = $r['food'];
    }

    foreach($menu as $day => $foods) {
        echo "<strong>{$day}</strong><br />\n";
        foreach($foods as $food) {
            echo "{$food}<br />\n";
        }
    }
    
}
else {
    //no menu data
}

 

 

Terribly inneficient, but not much you can do with data structured like this.

 

Thanks you!

 

That works well but what if you have no idea how many different days are in the table? How would you stop it from adding the extra days that don't have any data associated with them?

[corbin]

    foreach($menu as $day => $foods) {
        if(count($foods) == 0) continue;
        echo "<strong>{$day}</strong><br />\n";
        foreach($foods as $food) {
            echo "{$food}<br />\n";
        }
    }

[unrelenting]

    foreach($menu as $day => $foods) {
        if(count($foods) == 0) continue;
        echo "<strong>{$day}</strong><br />\n";
        foreach($foods as $food) {
            echo "{$food}<br />\n";
        }
    }

 

Cool. You say that is not terribly efficient but is it more efficient than say this:

 

$query = 'SELECT DISTINCT day FROM lunch_menu';
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) 
{
echo '<b>' . $row['day'] . '</b><br />';

$query2 = 'SELECT * FROM lunch_menu WHERE day = "' . $row['day'] . '"';
$result2 = mysql_query($query2);
while ($row2 = mysql_fetch_array($result2)) 
{
	echo '' . $row2['food'] . '<br />';
}
}

[corbin]

I would have to run tests to be sure, but I'm going to make an educated guess and say that the second way would be slower.