How would you rewrite this without a loop?

I am trying to rewrite this messy code, so that I only make one database query and eliminate the for loop. I am hoping the code will be faster with one query instead of two.

The 开发者_开发百科loop exists for one reason: The date attribute, which is either "Today" or "Tomorrow" followed by the formatted date.

The main point here is that I want to keep the data structure (dayReport) the same. I want to know what date the result set belongs to (either "Today" or "Tomorrow").

It seems silly to have a loop for that reason alone.

So here is the code. It's in PHP, but really this is a language agnostic question:

for ($a=1; $a<=2; $a++)
{
    $b = $a - 1;
    $result = mysql_query("SELECT
                            name,
                            time,
                            date_format(time,'%M %d %Y %h:%i %p') as ftime,
                            date_format(time,'%l:%i %p') as ttime,
                            fee
                            FROM `foo_bar`
                            WHERE `cityId` = $cityId
                            AND time_utc > utc_timestamp()
                            AND time >= DATE_ADD(curdate(),INTERVAL $b day)
                            AND time < DATE_ADD(curdate(),INTERVAL $a day)
                            ORDER BY time ASC
                            " ) or die(mysql_error());


  if ($result && mysql_num_rows($result) > 0)
  {
    $day = new Day();
    $day->date = $a == 1 ? 'Today' . date(' - l, F d') : 'Tomorrow' . date(' - l, F d',strtotime('+'.$b.' day'));
    $dayStuff = array();
    while ($row = mysql_fetch_object($result))
    {
        $dayStuff[] = $row;
    }
    $day->foo = $dayStuff;
    $dayReport[] = $day;
  }
}

You could rewrite your query in this way, plain SQL no php code

SELECT
    name, time,
    date_format(time,'%M %d %Y %h:%i %p') as ftime,
    date_format(time,'%l:%i %p') as ttime,
    fee,
    `time` = CURDATE() AS is_today -- Note this flag
    FROM `foo_bar`
    WHERE `cityId` = $cityId
    AND time_utc > utc_timestamp()
    AND time BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 1 day) -- rewritten clause for clarity
    ORDER BY time ASC

Then you could completely remove the outer php for statement and use the is_today flag to distinguish between today and tomorrow records.

For what regards the Day structure you can build two of them before the inner while and fill id according to the is_today flag, something like

while ($row = mysql_fetch_object($result))
{
    if ($row['is_today']) {
        $todayStuff[] = $row;   
    } else {
        $tomorrowStuff[] = $row;
    }
}

Use stored procedure if your goal is to lessen the exchange of data between your web application and your database.

You could combine the two queries into a UNION or expand the rank of the WHERE clause to cover both days. However, the while() loop will need to know how to process the rows differently without the flag variable. You could do that by getting the SQL to create a flag.