0

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

Naveed
  • 41,517
  • 32
  • 98
  • 131

3 Answers3

3
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' )";
gianebao
  • 17,718
  • 3
  • 31
  • 40
  • Remove space between `concat_ws` and `(`. It is causing this error on server: `FUNCTION CONCAT_WS does not exist` – Naveed Sep 24 '10 at 12:48
0

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.

Frankie
  • 24,627
  • 10
  • 79
  • 121
  • yes you are right. Fields are primary keys. Above logic may be implemented for different tables with different combinations of primary keys therefore I gave two data examples. I know it will include a part of php code as well. – Naveed Aug 31 '10 at 10:14
0

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' )";
Naveed
  • 41,517
  • 32
  • 98
  • 131