how to write a delete query

[narasimha-kvl]

Hi Admin

I have a delete query not able to execute using the class.
Here is my query, please let me know how to execute

DELETE FROM students WHERE studentID IN (1,2,3,4,5)

Regards
Narasimha

[noncent]

Hi Narasimha,

You can use below example to DELETE with IN.

/**
 *  run simple delete query with 'IN' operator
 *
 *  showQuery = display executed query
 *  affectedRows = get no of affected rows
 */

// here is id's which gonna delete
$arrayCustomerNumber = array(103, 112, 114, 119, 121);

// here is delete query with ? bind params
$deleteQuery = 'DELETE FROM `customers` WHERE customerNumber IN (?, ?, ?, ?, ?)';

// execute delete query and pass id's array
$exec = $db->pdoQuery($deleteQuery, $arrayCustomerNumber)->showQuery()->affectedRows();

// print affected rows by delete operation
$helper->PA($exec);

/**
 * Here is raw query which will be generate
 *
 * Executed Query -> delete from `customers` where customernumber in (103, 112, 114, 119, 121)
 *
 * No of affected rows : 5
 */

// here is your query

// execute delete query and pass id's array
$exec = $db->pdoQuery('DELETE FROM students WHERE studentID IN (?, ?, ?, ?, ?)', array(1, 2, 3, 4, 5))->showQuery()->affectedRows();

[narasimha-kvl]

Hi

Thank you for the quick reply. This is fine if there are fixed nnumber of id's, bu iif the id's vary from 1 to N, is there is any other way to execute

Thank you once again.

Regards
Narasimha

[noncent]

Hi Narasimha,

The pdoQuery accepts second param as array, if the array is associative then PDO Class Wrapper use namespace bindparam (:param, value) and if numeric then its work bindparam with '?' (1, value). So, you are free to use 'N' number of values within 2nd param array but ensure that you have to have same no of '?' too.

// array sample
$N_No_Of_Array_IDs = range(1,45);

// define vars
$idsCollection = $placeholder = array();

// parse each ids and create ids array and placeholder
foreach ($N_No_Of_Array_IDs as $key => $ids) {
    $idsCollection[] = $ids;
    $placeholder[] = '?';
}

$affected_rows = $db->pdoQuery("DELETE FROM students WHERE studentID IN (".implode(',',$placeholder).")", $idsCollection)->affectedRows();

-Best
Neeraj Singh

[futuretechmag]

I don't mean to re-open this, but since the question pertains to IN (WHERE) clause, and seeing the example in this is the same... is pdoQuery basically meant for running multi-row (custom/complex) queries? The "delete", "update" and "select" functions are meant for single WHERE clause queries?

[noncent]

Hi @futuretechmag,
The pdoQuery works only for single query at a time, its doesn't support multi query yet, but definitely it's a good idea and we will make this in future release. Well, delete, update and select has where clause functionality, you can find more example in doc file. Yes, you can use where as multi param like multiple where clause or multiple where with OR clause.

Example: Custom Where Clause with Select Method:

### You can set your own custom where clause

$whereConditions = array('lastname ='=>'bow', 'or jobtitle ='=> 'Sales Rep', 'and isactive ='=>1, 'and officecode ='=> 1 );
$data = $db->select('employees','',$whereConditions)->results();

Raw Query:
SELECT * FROM `employees` WHERE lastname = "bow" OR jobtitle = "sales rep" AND isactive = 1 AND officecode = 1 ;

OR

$whereConditions = array('lastname ='=>'bow', 'or jobtitle ='=> 'Sales Rep', 'and isactive ='=>1, 'and officecode ='=> 1 );
$data = $db->select('employees',array('employeenumber','lastname','jobtitle'),$whereConditions)->results();

Raw Query:
SELECT employeenumber, lastname, jobtitle FROM 'employees' WHERE lastname = "bow" OR jobtitle = "sales rep" AND isactive = 1 AND officecode = 1 ;

Hope, you got me :), Please feel free to ask questions, we would like to take care all of them.

Cheers!!