Solved

MySQL IF clause

Posted on 2013-01-02
10
263 Views
Last Modified: 2013-01-28
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!
0
Comment
Question by:benwiggy
  • 5
  • 5
10 Comments
 
LVL 12

Expert Comment

by:adrian_brooks
ID: 38738668
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.
0
 
LVL 6

Author Comment

by:benwiggy
ID: 38738684
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
0
 
LVL 12

Expert Comment

by:adrian_brooks
ID: 38738786
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)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Author Comment

by:benwiggy
ID: 38738797
Hi, no, queued_time is when it is added to the queue, i.e. "now"
0
 
LVL 6

Author Comment

by:benwiggy
ID: 38738798
And many thanks
0
 
LVL 12

Expert Comment

by:adrian_brooks
ID: 38738807
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?
0
 
LVL 6

Author Comment

by:benwiggy
ID: 38738811
Yes, that's right
0
 
LVL 12

Assisted Solution

by:adrian_brooks
adrian_brooks earned 500 total points
ID: 38738822
Okay then...try running this either at a linux command line or in your browser.
I wrote it so that it will emulate retrieval of two records from the fly_flights table and then operates against the data. I believe this is what you are after.

<?php
  $data[0] = new stdClass();
  $data[0]->id                = 1;
  $data[0]->user_id           = 101;
  $data[0]->airport_departing = 'LAX';
  $data[0]->airport_arriving  = 'ABQ';
  $data[0]->date_departure    = '2013-01-21';
  $data[0]->days_before       = 2;
  $data[0]->days_after        = 1;
  $data[0]->economy           = TRUE;
  $data[0]->business          = FALSE;
  $data[0]->first             = FALSE;
  $data[0]->deleted           = FALSE;

  $data[1]->id                = 2;
  $data[1]->user_id           = 111;
  $data[1]->airport_departing = 'CHI';
  $data[1]->airport_arriving  = 'DNV';
  $data[1]->date_departure    = '2013-01-28';
  $data[1]->days_before       = 3;
  $data[1]->days_after        = 2;
  $data[1]->economy           = TRUE;
  $data[1]->business          = FALSE;
  $data[1]->first             = FALSE;
  $data[1]->deleted           = FALSE;

  foreach($data as $row) {
    $flight_id   = $row->id;
    $days_before = 0 - $row->days_before;
    $days_after  = $row->days_after;
    $spread      = abs($days_before) + abs($days_after);
    $date_now    = date('Y-m-d H:i:s');

    if($spread > 0) {
      print "We have a spread of $spread days\n";
      for($d = $days_before; $d < $days_after; $d++) {
        $sql = "INSERT INTO fly_queue_rewards (`flight_id`, `date_offset`, `queued_time`) VALUES ($flight_id, $d, '$date_now')";
        print "$sql\n";
      }
    }
  }
?>

Open in new window

0
 
LVL 6

Author Comment

by:benwiggy
ID: 38740696
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

0
 
LVL 12

Accepted Solution

by:
adrian_brooks earned 500 total points
ID: 38773023
Ben,

Your code is fine. The fact that you're creating all of your insert values in the for loop and then performing the query afterwards means that you will only be hitting the MySQL server with one instruction rather than a series of inserts.

This should serve your needs just fine without any efficiency related impacts.

~AB
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question