asked on

MySQL IF clause

I have 2 tables:

=== fly_queue_rewards ===
| id | flight_id | date_offset | queued_time | high_priority | done | skipped |

=== fly_flights ===
| id | user_id | airport_departing | airport_arriving | date_departure | days_before | days_after | economy | business | first | deleted

and this statement:
INSERT INTO fly_queue_rewards (flight_id,queued_time) VALUES ((SELECT id FROM fly_flights WHERE deleted = '0'),'".date("Y-m-d H:i:s")."')

days_before and days_after are integers

The INSERT statement needs to be adjusted to INSERT the correct number of rows, with the correct values for date_offset, based on the data in fly_queue_rewards.

For example:

If days_before = 2 and days_after = 1, then the INSERT statement should create 4 rows, one with date_offset at -2, one with date_offset at -1, one with date_offset at 0 and one with date_offset at 0.

Many thanks for your help!

Richard Davis

I would offer you a coding option, but your original post is a bit confusing. Your insert statement is likely going to fail in & of itself because it uses a sub-query to get ids and if that sub-query returns anything more than 1 record, it will crash the statement.

Additionally, your question does not clearly identify much in the lines of what will be inserted per iteration.

Aside from all that, I would rite a PHP script that would create your day spread from the sum of days before and days after and then create a loop that performs inserts for each iteration of the loop until your spread has been exhausted. That's about the best I can offer you given what you provided.

Best of luck to you.

benwiggy

ASKER

Hi,

Thanks for the reply. The aim is to create a queue of all records in fly_flights that have not been 'deleted'.

The complexity is that if days_before or days_after != 0, then we need to create multiple rows.

It's possible the existing code is bad - I've only tested where one row matches so far...

Please let me know if I can clarify at all. The codebase is PHP, if that helps.

Best,
Ben

Richard Davis

Ben,

I'm going to try to make some attempt at translating your request. I will provide some code after this post based on your answer to this next question. We're you requiring that the fly_queue_rewards.queued_time value change based on the date_offset?

In otherwords, is the date_offset to be reflected in the date used in the queued_time value? (e.g. now() = 2013-01-24...queued_time + date_offset[0] would result in 2013-01-24 - 2 days or 2013-01-22)

benwiggy

ASKER

Hi, no, queued_time is when it is added to the queue, i.e. "now"

benwiggy

ASKER

And many thanks

Richard Davis

Ben,

So, the only thing that would change would be the date_offset then? queued_time would be 4 inserted values of the same date?

benwiggy

ASKER

Yes, that's right

SOLUTION

Richard Davis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

benwiggy

ASKER

Here is the code I ended up using - but it doesn't feel very efficient..

$sql = "SELECT * FROM fly_flights WHERE deleted = '0'";
$result = mysql_query($sql) or die(mysql_error());

$sql = "INSERT INTO fly_queue_rewards (flight_id, date_offset, queued_time) VALUES";

while ($row = mysql_fetch_array($result)) {

    $flight_id   = $row['id'];
    $days_before = 0 - $row['days_before'];
    $days_after  = $row['days_after'] + 1;
    $spread      = abs($days_before) + abs($days_after) + 1;
    $date_now    = date('Y-m-d H:i:s');

    for($d = $days_before; $d < $days_after; $d++) {
        $sql .= " (".$flight_id.", ".$d.", '$date_now'),";
    }
  }

$sql=substr($sql,0,-1);

$result = mysql_query($sql) or die(mysql_error());

Open in new window

ASKER CERTIFIED SOLUTION

Richard Davis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial