Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySQL IF clause

Posted on 2013-01-02
10
Medium Priority
?
266 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

721 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