Comments (5)
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();
from pdo_class_wrapper.
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
from pdo_class_wrapper.
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
from pdo_class_wrapper.
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?
from pdo_class_wrapper.
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!!
from pdo_class_wrapper.
Related Issues (17)
- wild cards? HOT 1
- Please make a PHP8 code review HOT 3
- [Feature request] Add time elapsed in showQuery
- [Question] Update incremental value HOT 3
- [Feature request] Add more description to debug problems
- [Bug] date format is wrong
- [Feature request] Add support for PDO::PARAM_LOB
- SQLSTATE[08004] [1040] Too many connections HOT 7
- Connect through unix_socket HOT 1
- PHP8 Compatibility
- Insert issue HOT 1
- Insert issue HOT 3
- how can you compare array against string function HOT 3
- Update query is not executing HOT 2
- SQL IN operator HOT 1
- Do you have the latest version for PHP7 ? HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from pdo_class_wrapper.