CODEWITHSUNDEEP
phpmysqlarrays
Joshua Greenhough
Joshua Greenhough

Reputation: 81

PHP drop fields into array and update array results on another table not working?

Hi there :) probably my title will be a bit hard to understand, sorry i couldn't make it any more brief. Anyway, what I have done is I've created a script that will get column data from a table where the timestamp on the rows are more than 30 days old. Then, it will store these IDs on an array, and will then loop through the arrays on another table. Sadly, nothing happens and I don't seem to get an error when I run the script.

Here's what the script says:

<?php

// connects to the DB
include ('connectdb.php');

//query that gets the "trade_id" from the table with all the trades, where the "open_time" is older than 30 days, "close_price" is NULL.
$result = $db->query("SELECT trade_id FROM trades WHERE open_time < (NOW() - INTERVAL 30 DAYS) AND close_price IS NULL");
// create an array
$rows = array();
// loop through the table and drop the data into the array
while($row = $result->fetch(PDO::FETCH_ASSOC)){
    $rows[] = $row;    
}       

$count = count($rows);
$i = 0;
//loops through the table
while($i < $count){
//updates the table "orders" where the unique_ID matches the trade_ID, and will change the column "notified" to -1
    $update = $db->prepare("UPDATE orders SET notified=? WHERE unique_id=$rows[$i]");
    $update->execute(array(-1));
//updates the table "trades" where the trade_ID matches the trade_ID, and will change the column "close_price" to -1
    $update2 = $db->prepare("UPDATE trades SET close_price=? WHERE trade_id=$rows[$i]");
    $update2->execute(array(-1));
    $i++;
}   
// Closes connection
$db = null;

?>

I've left notes throughout the code, however I am convinced I may have done the time script in the $result that may be wrong. Again, I am unsure why it doesn't update the rows. Any help I do vastly appreciate :)

Upvotes: 0

Views: 79

Answers (2)

chris85
chris85

Reputation: 23880

Here's a rough answer of how I'd try it.

$result = $db->query("SELECT trade_id FROM trades WHERE open_time < (NOW() - INTERVAL 30 DAYS) AND close_price IS NULL");
// create an array
$rows = array();
// loop through the table and drop the data into the array
$rows = $result->fetchAll(PDO::FETCH_ASSOC)){
foreach($rows as $row){
//updates the table "orders" where the unique_ID matches the trade_ID, and will change the column "notified" to -1
    $update = $db->prepare("UPDATE orders SET notified=? WHERE unique_id= ?");
    $update->execute(array(-1, $row['trade_id']));
//updates the table "trades" where the trade_ID matches the trade_ID, and will change the column "close_price" to -1
    $update2 = $db->prepare("UPDATE trades SET close_price=? WHERE trade_id=?");
    $update2->execute(array(-1, $row['trade_id']));
}   
// Closes connection
$db = null;
  1. Use fetchAll so you get all the records at once, don't need that while and variable assignment.
  2. Use foreach rather than a while then you don't need to make your own counter.
  3. Parameterize the update queries; otherwise could be open to second order SQL injection..

Upvotes: 1

Marol
Marol

Reputation: 36

Try to change this: 

WHERE unique_id=$rows[$i]

to this:

WHERE unique_id={$rows[$i]}

Upvotes: 0

Related Questions