A general single sql query.

Question

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" ) and $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

Answer 1

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.

Answer 2

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' )";

Answer 3

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' )";