Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MySQL IF clause

Posted on 2013-01-02
10
Medium Priority
?
267 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:Richard Davis
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:Richard Davis
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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:Richard Davis
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:Richard Davis
Richard Davis earned 1500 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:
Richard Davis earned 1500 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

Technology Partners: 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!

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

886 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