开发者

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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜