Modify Resultset

If I search for all cars added between 2012-09-24 and 2012-09-26 in my database, and there are only cars added 2012-09-24 and 2012-09-26, that’s all which will be returned.

In cases when there are no cars added, I’d like to have the application add information. Since there are no cars added 2012-09-25, I’d like to modify the resultset to show data anyway.

Instead of this output result:

2012-09-24 - Audi A6

2012-09-26 - Audi A7

I want to show this:

2012-09-24 - Audi A6

2012-09-25 - No cars found this date

2012-09-26 - Audi A7

I’m trying to achieve it this way:

Dear Friend

I created a model Car.(id(INT),name(VARCHAR),date_added(DATE)).

In the controller I declared a static method to increment a date by oneday.

public static function addOneDay($date)

    {

	$dateObject=new DateTime($date);

	$dateObject->add(new DateInterval('P1D'));

	$date=$dateObject->format('Y-m-d');

	return $date;

    }

In view

//getting the first date.

sql="SELECT name , date_added FROM car ORDER BY date_added ASC";

$db=Yii::app()->db;

$command=$db->createCommand($sql);

$firstDate=$row['date_added'];


//getting the all data.

$cars=$command->queryAll();


//getting the last date.

$sql="SELECT name , date_added FROM car ORDER BY date_added DESC";

$db=Yii::app()->db;

$command=$db->createCommand($sql);

$row=$command->queryRow();

$lastDate=$row['date_added'];


//making the loop

for($i=$firstDate;$i<=$lastDate;$i=TestController::addOneDay($i))

{   $noCars=true;

	foreach($cars as $car)

	{

		

		if($i==$car['date_added'])

		{

                 	echo $car['date_added'].": ".$car['name']."</br>";

		        $noCars=false;

		}

	}


    if($noCars)

		echo $i.": "."No cars added"."</br>";

}

At the outset, it looked very simple.

But I could not acheive it in a simple way.

But may be this can be done easily at database level playing with some sql.

I may miss some simple things.

Regards.