Solved

MySQL IF clause

Posted on 2013-01-02
10
261 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

831 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