1

HiAll,

I want to execute below sql query using Drupal 7 PDO, But could not find a way to do,

SQL:

"UPDATE  `books`  AS a 
  JOIN  `books` b  ON a.pid = b.pid    
  SET  a. `status` = 'INACTIVE'   
  WHERE   b.`pdays` * (36400) + b.`ptime_from` > 6546"

Drupal 7:

 $query = db_update('books','pb');  
    $query->fields($update_values);   
    $query->join('books', 'pc', 'pc.pid = pb.pid');
    //$query->condition("pc.pdays" * (36400) + "pc.ptime_from",$today ,'>');
   /* $query->expression('pc.pdays', 'pdays * :daytime + pc.ptime_from', array(':daytime' => '36400'),'>')*/
    $isbn_reds = $query->execute(); 

How can we write above SQL in D7 PDO?

  • why do you want to use a join? – Mohammed Shameem Jan 25 '13 at 9:09
  • No issue no bound to join, – Vikrant Ramteke Jan 25 '13 at 11:15
  • concern is implementation of : "WHERE b.pdays * (36400) + b.ptime_from > 6546" in D7 PDO – Vikrant Ramteke Jan 25 '13 at 11:16
1

Better you break this query into two parts. The first part will fetch the value and the second will do the update.

Fetch value

$query = db_select('books', 'a');
$query->join('books', 'b', 'a.pid = b.pid');
$query->fields('b', array('pdays'));
$query->execute();
$pdays = $query->fetchAssoc();

Update

$sql = "
UPDATE books as a
  join books as b on a.pid = b.pid
  set a.status = :type1
  where " . $pdays['pdays'] . " + b.ptime_from > :type2";
$args = array(':type1' => 'INACTIVE', ':type2' => 6546);
db_query($sql, $args);

Also you cannot use join in where clause.

| improve this answer | |
1

To execute clauses like that you would use a where funtion. so your code would be something like

$query = db_update('books','pb');
$query->fields($update_values);
$query->where("b.pdays * (36400) + b.ptime_from > 6546");
$isbn_reds = $query->execute();
| improve this answer | |
  • Thanks, it's works. Also we need to change : $query = db_update('books','pb'); it should be $query = db_update('books'); Update dose not take second parameter as alias, It is use for another special purpose. – Vikrant Ramteke Jan 29 '13 at 11:55
  • good to know that it helped... :-) – Mohammed Shameem Jan 29 '13 at 12:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.