stuck in the search query in php mysql

Question

I am writing a search query for the items in my cart. The problem is I am not getting the expected result. Here is the code:

$here = explode(" ",$sarc);
$query = "SELECT * FROM 126link WHERE (";
foreach($here as $each){
    $i++;
    if($i==1)
        $query.="title LIKE '%$each%') AND loginid = '$myid' ";
    else
        $query.="OR title LIKE '%$each%') AND loginid = '$myid' ";
}
$ritw = mysql_query($query);
$sup = @mysql_num_rows($ritw);
$query.="ORDER BY idl LIMIT 40";
$query = mysql_query($query);
$numrows = @mysql_num_rows($query);

I want to add an AND condition in this query which will search if idcolumn(in table) = '$id' , but I don't know where to put it.

The code above displays results for all carts, I'm trying to limit it to one.

What are you doing here actually? Could you show us a good amount of code here? — Shahriar Kabir, Jan 24 '13 at 23:06

score 1 · Answer 1 · answered Jan 24 '13 at 23:27

You need to understand the order of operations better:

http://dev.mysql.com/doc/refman/5.5/en/operator-precedence.html

AND is evaluated before OR so without providing parenthesis around your entire set of OR conditions, you will end up evaluating only the last LIKE condition together with the id filter, and then get all teh other rows meeting your OR conditions.

Even without regard to operator precedence, it is a good practice to make logical groupings of your operations to where they make more sense to someone (including yourself) trying to read the code.

So you WHERE condition should look like this:

WHERE (title LIKE %value% OR title LIKE %other value% ...) AND loginid = ?

You don't want the `AND loginid` part within your loop. Just have it after the loop like you did before and just close the parenthesis there like `") AND loginid = '$myid'"` — Mike Brant, Jan 24 '13 at 23:57

score 0 · Answer 2 · answered Jan 24 '13 at 23:06

0

Add it at the end?

$id=$_SESSION['id'];
$query = "SELECT * FROM table WHERE ";
foreach($here as $each){
    $i++;
    if($i==1)
        $query.="title LIKE '%$each%' ";
    else
        $query.="OR title LIKE '%$each%' ;
}
$query .= " AND something IN ($id)";

You need to provide a table structure with sample data and a desired result if you want a better answer.

answered Jan 24 '13 at 23:06

Kermit

33,827
13
85
121

**"You need to provide a table structure with sample data and a desired result if you want a better answer."** – Kermit Jan 24 '13 at 23:09
there is a table which has the items id and in another column there is the user id... – Prashant Jan 24 '13 at 23:10
Sorry, I can't help you if you don't show any effort. Voting to close this question. – Kermit Jan 24 '13 at 23:10
actually what i m doing is just checking whether that item belongs to this user or not... – Prashant Jan 24 '13 at 23:11
@Prashant We can't help you without your table structure. Please add it to your question. – Tim Post Jan 24 '13 at 23:13
i did exactly the same what @njk told to do...but still it isn't working...is my code right... – Prashant Jan 24 '13 at 23:18
@Prashant: What part of **We need a table structure** dont you understand? – raidenace Jan 24 '13 at 23:21
the table has three columns...first is id , second is the item name and the last one is userid... – Prashant Jan 24 '13 at 23:22
@njk sorry i m new to php but can you explain more about your last query "AND something IN ($ID)" – Prashant Jan 24 '13 at 23:28

score 0 · Answer 3 · edited May 23 '17 at 12:27

0

Faster way of doing this:

$here = explode(" ",$sarc);
$id=$_SESSION['id'];
$query  = "SELECT * FROM table WHERE title REGEXP '" . implode('|',$here) . "'";
$query .= " AND something IN ($id)";

Have another question about this

Mysql Like multiple values

edited May 23 '17 at 12:27

Community

1
1

answered Jan 24 '13 at 23:23

Ragen Dazs

2,115
3
28
56

sorry i m new to php but can you explain more about your last query "AND something IN ($ID)" – Prashant Jan 24 '13 at 23:27
@Mike Brant have explained in ther answer `AND something` is your `field` and `IN()` it's used for multiple comparison for ie: `and id IN(1,5,7,10)` – Ragen Dazs Jan 24 '13 at 23:31
1

so will it go like...AND loginid IN ($id) – Prashant Jan 24 '13 at 23:35

score 0 · Answer 4 · answered Jan 24 '13 at 23:57

This code should work .

$here = explode(" ",$sarc);
$query = "SELECT * FROM 126link WHERE loginid = '$myid'  AND title '".implode('|',$here)."';" ;

$ritw = mysql_query($query);
$sup = @mysql_num_rows($ritw);
$query.="ORDER BY idl LIMIT 40";
$query = mysql_query($query);
$numrows = @mysql_num_rows($query);

To learn REGEXP visit http://www.go4expert.com/forums/showthread.php?t=2337

To learn reqular expressions visit http://www.regular-expressions.info/

stuck in the search query in php mysql

4 Answers4