Problem with query to table with inner join

Question

Problem with query to table with inner join

Navigation

#1 by (1 votes)

0

I have a problem I hope someone can help me because I'm going crazy and I can not solve it.

I have the following code that I use to consult a table and display the results in a datatable:

$joinQuery = "FROM orders INNER JOIN clients on (client_id=order_cid) INNER JOIN users on user_id=order_responsible";

$extraCondition = "orders.order_type in ({$tramites}) or REPLACE(CONCAT(clients.client_name,clients.client_lastname), ' ', '') LIKE '%{$filter_fullname}%' or orders.order_deadline LIKE '%{$fecha}%' or orders.order_date_start LIKE '%{$fecha}%' or users.user_name LIKE '%{$valueRequestSearch}%'";

What I need is order for a new field "order_status".

What I tried was:

$joinQuery = "FROM orders INNER JOIN clients on (client_id=order_cid) INNER JOIN users on user_id=order_responsible";

$states = '1,2,3,4,5';
$extraCondition = "orders.order_state IN ({$states}) or orders.order_type in ({$tramites}) or REPLACE(CONCAT(clients.client_name,clients.client_lastname), ' ', '') LIKE '%{$filter_fullname}%' or orders.order_deadline LIKE '%{$fecha}%' or orders.order_date_start LIKE '%{$fecha}%' or users.user_name LIKE '%{$valueRequestSearch}%'";

The modified code does not throw an error but it does not list well in the datatable either. That is, I keep listing all the records and I just need you to list those with a status equal to 1,2,3,4,5.

I hope I explained correctly.

The complete code here: link

Thanks in advance.

mysql php datatable

asked by Cristiam 20.10.2017 в 23:57

source

1 answer

My footer leaves the body, and an article is not shown Get the name of a brand and show it in Wordpress

score 1 · Answer 1

The problem is that you use OR for a new condition and as the other conditions are fulfilled what is happening is that instead of restricting the query you are expanding it.

You are literally telling the handler all of the above plus those with order_state (1,2,3,4,5)

Use AND in the following way:

$joinQuery = "FROM orders INNER JOIN clients on (client_id=order_cid) INNER JOIN users on user_id=order_responsible";

$states = '1,2,3,4,5';
$extraCondition = "orders.order_state IN ({$states}) AND ( orders.order_type in ({$tramites}) or REPLACE(CONCAT(clients.client_name,clients.client_lastname), ' ', '') LIKE '%{$filter_fullname}%' or orders.order_deadline LIKE '%{$fecha}%' or orders.order_date_start LIKE '%{$fecha}%' or users.user_name LIKE '%{$valueRequestSearch}%')";

Greetings.