Return Empty Row If It Does Not Exist In The Db

[yii_king (Micho23)]

I have a SQL query which retrieves one row per day for day1, day2, day3, day4, day5, day6 and day7. Day1 is always today, day2 tomorrow etc.

The query looks like this right now:

$sql = “SELECT * FROM tbl_books WHERE (date = '”.$days[“day1”]."’ OR date = ‘".$days[“day2”]."’ OR date = ‘".$days[“day3”]."’ OR date = ‘".$days[“day4”]."’ OR date = ‘".$days[“day5”]."’ OR date = ‘".$days[“day6”]."’ OR date = ‘".$days[“day7”]."’)";

$f = Books::model()->findAllBySql($sql);

If one of the 7 rows (days) is missing (does not exist in the db) I would still like to have the resultset return a row with empty values for that date. Any ideas if I can achieve that in the SQL query directly?

I’ve tried to compare the dates (today and 6 days forward) in order to add an empty row to the resultset afterwards in the php code also, but no success. Any ideas?

[mentel (Yii)]

It seems a bit wrong.

Couldn’t you just check the returned dates?

[yii_king (Micho23)]

I think I’m getting closer now. I’m checking the predefined dates against the returned dates.

The table that I’m printing out contains the columns (predefined dates)

2012-10-12, 2012-10-13, 2012-10-14, 2012-10-15, 2012-10-16, 2012-10-17, 2012-10-18

These columns should always be there no matter if the resultset is missing one of the dates. In reality, 2012-10-14 is missing from the resultset.

In the comparison below I am now getting out that $d3 is false and I therefore know that is the value missing. So I’m trying to insert an empty row but getting the error "Trying to get property of non-object ". This is indicating that I’m insert it in the wrong way into the resulset. Any ideas about this?

// The predefined dates

$days = array(

"day1" => $day1,

"day2" => $day2,

"day3" => $day3,

"day4" => $day4,

"day5" => $day5,

"day6" => $day6,

"day7" => $day7,

);

// The result set

$forecasts = $this->getForecasts($id, $days);

// Compare dates

$d1 = false;

$d2 = false;

$d3 = false;

$d4 = false;

$d5 = false;

$d6 = false;

$d7 = false;

foreach ($forecasts as $forecast) {

if ($days["day1"] === $forecast->date && !$d1) {

$d1 = true;

}

if ($days["day2"] === $forecast->date && !$d2) {

$d2 = true;

}

if ($days["day3"] === $forecast->date && !$d3) {

$d3 = true;

}

if ($days["day4"] === $forecast->date && !$d4) {

$d4 = true;

}

if ($days["day5"] === $forecast->date && !$d5) {

$d5 = true;

}

if ($days["day6"] === $forecast->date && !$d6) {

$d6 = true;

}

if ($days["day7"] === $forecast->date && !$d7) {

$d7 = true;

}

}

if (!$d1) {

$this->array_put_to_position($forecasts, $days[“day1”], 0, ‘date’);

}

if (!$d2) {

$this->array_put_to_position($forecasts, $days[“day2”], 1, ‘date’);

}

if (!$d3) {

$this->array_put_to_position($forecasts, $days[“day3”], 2, ‘date’); // This is where it goes wrong

}

if (!$d4) {

$this->array_put_to_position($forecasts, $days[“day4”], 3, ‘date’);

}

if (!$d5) {

$this->array_put_to_position($forecasts, $days[“day5”], 4, ‘date’);

}

if (!$d6) {

$this->array_put_to_position($forecasts, $days[“day6”], 5, ‘date’);

}

if (!$d7) {

$this->array_put_to_position($forecasts, $days[“day7”], 6, ‘date’);

}

public function array_put_to_position(&$array, $object, $position, $name = null)

{


        $count = 0;


        $return = array();


        foreach ($array as $k => $v)


        {  


                // insert new object


                if ($count == $position)


                {  


                        if (!$name) $name = $count;


                        $return[$name] = $object;


                        $inserted = true;


                }  


                // insert old object


                $return[$k] = $v;


                $count++;


        }  


        if (!$name) $name = $count;


        if (!$inserted) $return[$name];


        $array = $return;


        return $array;


}

[mentel (Yii)]

You’re trying to atribute to the database something of responsibility of your application logic.

Since you already know that the date doesn’t exist in the database, you can just print empty fields.

The error you’re getting is because you’re treating something that isn’t an object (it is null) as an object.

[yii_king (Micho23)]

You’re trying to atribute to the database something of responsibility of your application logic.

Since you already know that the date doesn’t exist in the database, you can just print empty fields.

The error you’re getting is because you’re treating something that isn’t an object (it is null) as an object.

I’ve tried everything in the application logic without getting it to work. Instead I’d like to INSERT INTO the db if the row does not exist.

IF NOT EXISTS (SELECT * FROM tbl_f WHERE date = ‘2012-10-14’)

THEN (INSERT INTO tbl_f (date) VALUES(‘2012-10-14’)

I keep getting SQL syntax error when trying this. Is it possible to solve it in the SQL statement?