UPDATE based on calculated field

[davidannis]

Earlier I posted about how to best deal with a calculated field in MySQL and was advised not to store the value in the database. So, I took my calculated value out of the tables. I can select based on the calculated value

SELECT ... right_count, wrong_count, right_count + wrong_count AS Total_Count, 
right_count / (right_count + wrong_count) AS Right_Percent
...

but now I want to UPDATE based on Right_Percent. The old code was:

UPDATE leitner_vcard_boxes 
SET box=box+1 last_reboxed_date='$today' where box<7 
AND last_reboxed_date<'$cutoff_date' and right_percent<='$score'

The only way I can think of updating is something like:

UPDATE leitner_vcard_boxes SET box=box+1 last_reboxed_date='$today' 
WHERE box<7 and last_reboxed_date<'$cutoff_date' 
AND Right_Percent IN (SELECT right_count, wrong_count, right_count / (right_count + wrong_count) AS Right_Percent 
FROM leitner_vcard_boxes WHERE Right_Percent,='$score');

Will using a subquery even work? Is there a better way?

 

To further complicate things, I loop through 5 score/cutoff date pairs (so that a lower score gets reboxed faster than a higher score). I imagine it would be more efficient to try to combine it all into a single query.

     $cutoff_scores=array(25=>7,50=>14,75=>28,90=>40);
    foreach ($cutoff_scores as $score => $rebox_interval){
        $today=date('Y-m-d');
        $interval=$rebox_interval.' days';
        $cutoff_date=date_sub($today, date_interval_create_from_date_string($interval));
        $query="UPDATE leitner_vcard_boxes SET box=box+1 last_reboxed_date='$today' where box<7 and last_reboxed_date<'$cutoff_date' and right_percent<='$score'";
        $result=  mysqli_query($link, $interval);
    }

[Psycho]

Hmm . . . that query is invalid. Are you modifying it before posting here?

 

EDIT: I am seeing several things to be addressed:

 

1. You are trying to execute the variable $interval as the Query! WTH?! Not even sure what $interval should be used for as it doesn't appear in the $query. So, why is the loop even needed?

 

2. There are two values being set but there is no comma between them (this makes the query invalid)

 

3. You are creating a date string in PHP to be used in the query. That's unnecessary. You can either just use CURDATE() in the query OR if you want that date always updated whenever the record is updated, change the field to a timestamp with the option to auto-update. Then you don't even need to add the date field to your query. It would just be updated to the current timestamp whenever there is a change.

 

4. Same goes for $cutoff_date. It can be set directly in the query

 

5. You are correct - you do not need to run multiple queries. But, because there is an obvious problem with how the "values" of the array are not used in the query, I can't provide a solution.

 

Edit #2: Scratch the comments regarding the problem with not knowing how you are wanting to use the value of the array in the query. I see it is used to calculate the cutoff date.

Edited July 9, 2014 by Psycho

[fastsol]

2 things I see.

You have a syntax error after the Right_percent cause you have a unwelcome , before the =

FROM leitner_vcard_boxes WHERE Right_Percent,='$score');

And in your foreach loop you are calling the query with the $interval rather than the $query, so that will surely give you a error.

$result= mysqli_query($link, $interval); // Should be $query not $interval

Edited July 9, 2014 by fastsol

[Psycho]

OK, I thought you might need a SWITCH statement in the query to handle all the updates in one query. But, after further review I see that the UPDATES are all the same. The differences are with the WHERE conditions for determining which records to update. That males it a little simpler since you just need to build a combined WHERE condition.

 

It looks complicated, but isn' that hard. You have to know what the final query should look like. Then create the code to dynamically create it.

//Define the cuttoff conditions for WHERE clause
$cutoff_scores = array(25=>7,50=>14,75=>28,90=>40);
$CUTOFFS = array();
foreach ($cutoff_scores as $score => $rebox_interval)
{
    $CUTOFFS[] = "(last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL {$rebox_interval} DAY)
                     AND (right_count / (right_count + wrong_count)) <= '$score')";
}
 
//Define the WHERE clause
$WHERE = "WHERE box < 7 AND (\n" . implode("\n OR ", $CUTOFFS) . "\n)";
$query = "UPDATE leitner_vcard_boxes
          SET box = (box + 1),
              last_reboxed_date = CURDATE()
          $WHERE";
$result=  mysqli_query($link, $query);

The resulting query would look like this

UPDATE leitner_vcard_boxes
SET box = (box + 1),
    last_reboxed_date = CURDATE()
 
WHERE box < 7
  AND (
          ( last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL 7 DAY)
            AND (right_count / (right_count + wrong_count)) <= '25')
       OR ( last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL 14 DAY)
            AND (right_count / (right_count + wrong_count)) <= '50')
       OR ( last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL 28 DAY)
            AND (right_count / (right_count + wrong_count)) <= '75')
       OR ( last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL 40 DAY)
            AND (right_count / (right_count + wrong_count)) <= '90')
      )

Also, you should see from the above the answer to your original question. Since you don't have a Right_Percent column in the database and calculate it dynamically (as you should) you just need to use that calculation in the WHERE condition as well. No need for a subquery.

Edited July 9, 2014 by Psycho

[Psycho]

OK, after a little research, I see you can use a CASE statement in the WHERE clause. I don't know if this is any more efficient or not. But, I think it makes the logic and the resulting query a little easier to understand

 

 

//Define the WHEN conditions for WHERE clause CASE statement
$cutoff_scores = array(25=>7,50=>14,75=>28,90=>40);
$WHEN = '';
foreach ($cutoff_scores as $score => $rebox_interval)
{
    $WHEN .= "WHEN last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL {$rebox_interval} DAY)
                   AND (right_count / (right_count + wrong_count)) <= '$score'
                   THEN 1\n";
}
 
//Create the query
$query = "UPDATE leitner_vcard_boxes
          SET box = (box + 1),
              last_reboxed_date = CURDATE()
          WHERE box < 7
            AND CASE
                {$WHEN}
                ELSE 0
                END CASE";
$result=  mysqli_query($link, $query);

 

Resulting Query

 

UPDATE leitner_vcard_boxes
SET box = (box + 1),
    last_reboxed_date = CURDATE()
WHERE box < 7
  AND CASE
          WHEN last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL 7 DAY)
               AND (right_count / (right_count + wrong_count)) <= '25'
               THEN 1
          WHEN last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL 14 DAY)
               AND (right_count / (right_count + wrong_count)) <= '50'
               THEN 1
          WHEN last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL 28 DAY)
               AND (right_count / (right_count + wrong_count)) <= '75'
               THEN 1
          WHEN last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL 40 DAY)
               AND (right_count / (right_count + wrong_count)) <= '90'
               THEN 1
          ELSE 0
      END CASE

[davidannis]

That is what I was hoping for, a single query that I can execute so I'm not looping through mysql queries but something is still wrong, when I execute the query you provided in PHPMyAdmin I get

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE' at line 19

If I echo $query from the php code and paste it in I get a similar error.

[davidannis]

I think that this works:

UPDATE leitner_vcard_boxes SET box = ( box +1 ) ,
last_reboxed_date = CURDATE( ) WHERE box <7 AND (
last_reboxed_date < DATE_SUB( CURDATE( ) , INTERVAL 7 DAY ) AND (
right_count / ( right_count + wrong_count )
) <= '25'
) OR (
last_reboxed_date < DATE_SUB( CURDATE( ) , INTERVAL 14 DAY ) AND (
right_count / ( right_count + wrong_count )
) <= '50'
) OR (
last_reboxed_date < DATE_SUB( CURDATE( ) , INTERVAL 28 DAY ) AND (
right_count / ( right_count + wrong_count )
) <= '75'
) OR (
last_reboxed_date < DATE_SUB( CURDATE( ) , INTERVAL 40 DAY ) AND (
right_count / ( right_count + wrong_count )
) <= '90'
)

and I should be able to assemble the query in PHP.

 

Edit: Looks like you already showed me that in post #4. I skipped straight to 5. Thanks for your patience and assistance.

Edited July 9, 2014 by davidannis

[Psycho]

That is what I was hoping for, a single query that I can execute so I'm not looping through mysql queries but something is still wrong, when I execute the query you provided in PHPMyAdmin I get

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE' at line 19

If I echo $query from the php code and paste it in I get a similar error.

 

Yeah, it's kind of hard for me to test a query without the requisite DB. I don't use CASE statements all that often and I goofed on the format - it should just be "END" not "END CASE". Although that's not what the manual says.

 

Also, I found it could be simplified a little:

 

//Define the WHEN conditions for WHERE clause CASE statement
$cutoff_scores = array(25=>7,50=>14,75=>28,90=>40);
$WHENs = '';
foreach ($cutoff_scores as $score => $rebox_interval)
{
    $WHENs .= "WHEN last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL {$rebox_interval} DAY)
                  THEN (right_count / (right_count + wrong_count)) <= '$score'1\n";
}
 
//Create the query
$query = "UPDATE leitner_vcard_boxes
          SET box = (box + 1),
              last_reboxed_date = CURDATE()
          WHERE box < 7
            AND CASE 
                    {$WHENs}
                    ELSE 0
                END";
$result=  mysqli_query($link, $query);

 

Resulting query

 

UPDATE leitner_vcard_boxes
          SET box = (box + 1),
              last_reboxed_date = CURDATE()
          WHERE box < 7
            AND CASE 
                    WHEN last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL 7 DAY)
                        THEN (right_count / (right_count + wrong_count)) <= '25'1
                    WHEN last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL 14 DAY)
                        THEN (right_count / (right_count + wrong_count)) <= '50'1
                    WHEN last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL 28 DAY)
                        THEN (right_count / (right_count + wrong_count)) <= '75'1
                    WHEN last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL 40 DAY)
                        THEN (right_count / (right_count + wrong_count)) <= '90'1
                    ELSE 0
                END CASE

 

If a WHEN condition is met it will return the result of the THEN condition.