?
Solved

MySQL IF clause

Posted on 2013-01-02
10
Medium Priority
?
270 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

585 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