PHP/MySql simpler more efficient code

Question

I am currently using three different queries to output information from a db.. I've put a sample of what I'm doing below, I'm just curious if there is a more efficient simpler way of spitting out this same info.

I have a db table for events, that has a date field, and parent event field. I have to group the events by their date, spit out the events per each date, then spit out each sub-event underneath those events (by using the parent event field).

Below is a sample of how my code it layed out, I'm just not that good in mysql if there is an easier query etc.

Thanks

$result = mysql_query("SELECT * FROM events GROUP BY date");
while ($event_date = mysql_fetch_assoc($result)) {

    echo 'Date Header';

    $result2 = mysql_query("SELECT * FROM events WHERE date = '" . $event_date['date'] . "' && parent_id = ''");
    while ($event = mysql_fetch_assoc($result2)) {

        echo 'Event display code';

        $result3 = mysql_query("SELECT * FROM events WHERE date = '" . $event_date['date'] . "' && parent_id = '" . $event['id'] . "'");
        while ($event = mysql_fetch_assoc($result3)) {

            echo 'Sub Event code';

        }
    }
}

To achieve a markup like the below.

<h3>Date 1</h3>
<div class="top-level-event">
    Some code
</div>
    <div class="sub-level-event">
        some sub level code
    </div>
    <div class="sub-level-event">
        some sub level code
    </div>

<h3>Date 2</h3>
<div class="top-level-event">
    Some code
</div>
    <div class="sub-level-event">
        some sub level code
    </div>
    <div class="sub-level-event">
        some sub level code
    </div>

ahoo · Accepted Answer · 2013-04-28 21:02:58Z

1

You can use one query:

SELECT * FROM events e1
LEFT JOIN events e2 ON e1.id=e2.parent_id AND e1.date=e2.date
WHERE e1.parent_id=''
ORDER BY e1.date

answered Apr 28, 2013 at 21:02

ahoo

1,3812 gold badges17 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

skywalker14 Over a year ago

Could you please elaborate? All this returns is all the results that don't have a parent_id assigned (IE: top level events). I guess this question was more asking about the php as well as the query. I'm just trying to determine if what I'm doing is overkill or if it can be done in a simpler way to achieve the following type html markup per distinct date in the db. I update my code with the type of markup I'm trying to achieve.

Collectives™ on Stack Overflow

PHP/MySql simpler more efficient code

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related