0

Hi to all just wanna ask how to achieve something like on show all data if a certain field on the table is equal to 2. basically what im trying to make is a search form by the way the searching part is working it just that its showing all the data what i really need to achieve is show the data if tablefiled==2

THE Table structure is

Id    Name        Sdate         Active_view

1     Person 1    2013-04-1          2
2     Person 2    2013-04-5          1
3     Person 3    2013-04-25         1
4     Person 4    2013-04-1          2
5     Person 1    2013-04-1          1

The process that it undergo is
$searchterm="2013-04-1"

output is

Name          Serving Date  

Person 1      2013-04-1
Person 4      2013-04-1      

and my query is

select * from advance where CONCAT_WS(' ',SDate,'/',Time) like '{$searchterm}%'  ORDER BY Id DESC

i tried also something like this but it now showing anything..

select * from advance where Active_view==2 AND CONCAT_WS(' ',SDate,'/',Time) like '{$searchterm}%'  ORDER BY Id DESC

Edited by ehpratah

2
Contributors
5
Replies
35
Views
4 Years
Discussion Span
Last Post by ehpratah
0

what is the purpose of CONCAT_WS(' ',SDate,'/',Time) in the query?

this will get all rows with active_view = 2 and SDate = '2013-04-1'
select * from advance where Active_view=2 AND SDate = '2013-04-1'

0

the CONCAT_WS() will just concatenate string with separator for me it is the " / " it is use to search date and time in the query

i tried it and its now working fine but when i add another search term its showing again all the data on my table

here's my whole query

select * from advance WHERE Icm_active=2 AND RA like '{$term}%' OR CONCAT_WS(' ',SDate,'/',Time) like '{$term}%'  OR Remarks like '{$term}%'  ORDER BY Id DESC

what i observed is if i only put one search term on the query its working fine but when i add another searchterm it will ony read the first search term before the AND statement and the other will not really be invoke but will dispaly all the data even the one that Active_view=1...seems that it is ignoring this part WHRE Icm_active=2 of the query

Edited by ehpratah

0

Hi i figured a way already by altering the query from this

    select * from advance WHERE Icm_active=2 AND RA like '{$term}%' OR CONCAT_WS(' ',SDate,'/',Time) like '{$term}%' OR Remarks like '{$term}%' ORDER BY Id DESC

to this

select * from advance where Icm_active=2 AND RA like '{$term}%' OR Icm_active=2 AND CONCAT_WS(' ',SDate,'/',Time) like '{$term}%' OR Icm_active=2 AND Remarks like '{$term}%'  ORDER BY Id DESC

literally the problem is solved but when you look into the query it has a repeated process my question is how to simplified the query?

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.