A general single sql query
I have a table like this:
id | roll_no | name
---------------------
1 | 111 | Naveed
2 | 222 | Adil
3 | 333 | Ali
If I have data like this:
$fields = array( "id" , "roll_no" )
a开发者_JS百科nd $values = array( "1,111", "2,222" );
It means I have to write a sql query to get records from table where (id != 1 and roll_no != 111) and (id != 2 and roll_no != 222). It means 3rd record will be fetched.
If I have data like this:
$fields = array( "id" )
and $values = array( "2", "3" );
It means I have to write a sql query to get records from table where (id != 2) and (id != 3). It means 1st record will be fetched.
Q: How to write a general single query using php to get data from table using above two data arrays.
Thanks
select * from dummy where concat_ws (',', id, roll_no) not in ('1,111', '2,222')
Complete solution:
$tableName = "test";
$fields = array( "id" , "roll_no" );
$values = array( "1,111", "2,222" );
$fieldsStr = implode(',', $fields);
$valuesStr = implode("','", $values);
$sql = "SELECT *
FROM $tableName
WHERE concat_ws(',', $fieldsStr ) NOT IN ( '$valuesStr' )";
You will probably always have to explode the Array
in PHP and pass the values as a string into the query (sprintf
) so you probably can, and should, do all in PHP.
One thing that catches my eye is that you are always using ID's. Are the ID's a unique or primary field? If so just forget about the roll_no as your query will be faster using just ID's.
Complete solution with the help of accepted answer.
$tableName = "test";
$fields = array( "id" , "roll_no" );
$values = array( "1,111", "2,222" );
$fieldsStr = implode(',', $fields);
$valuesStr = implode("','", $values);
// Get all records from remote table
$sql = "SELECT * FROM $tableName WHERE concat_ws(',', $fieldsStr ) NOT IN ( '$valuesStr' )";
精彩评论