need help with looping query

[davehaz]

Howdy,

I am trying to create a Mysql query out of an array. Here is my code.

$lookup = mens jackets;
$lookup = explode(' ',$lookup);
$count=count($lookup);

for($i=1; $count >= $i; $i++){
$query =("select * from stuff WHERE description LIKE ('$lookup[0]')
$query .= AND ('$lookup[$i]')
$query .= ");}


here are my results I am getting;

select * from stuff WHERE description LIKE ('mens') select * from stuff WHERE description LIKE ('mens') .= AND ('jackets') .= .= AND ('') select * from stuff WHERE description LIKE ('mens') .= AND ('jackets') .= .=


it made more sense to me to have the for loop after the initial query, but when I do I get this.

select * from stuff WHERE description LIKE ('mens') for(=1; 2 >= ; ++){ .= AND ('') .=


anybody got any idea on how to do what i want to do

[MillerTime]

if what i am thinking you want is right..this should help

<?
$lookup = "mens jackets";
$lookup = explode(' ',$lookup);
$count=count($lookup);

$query = "select * from stuff WHERE description LIKE ('$lookup[0]')";
for($i=1; $count >= $i; $i++){
$query .= "AND ('$lookup[$i]')";
}
$result = mysql_query("$query");
?>

that should work if i am correct but just so you know, i think you have an error in your query

[davehaz]

that did help a lot, but you are correct my query is not pulling anything, what do you see?

thanks.

[missing-score]

if what i am thinking you want is right..this should help

<?
$lookup = "mens jackets";
$lookup = explode(' ',$lookup);
$count=count($lookup);

$query = "select * from stuff WHERE description LIKE ('$lookup[0]')";
for($i=1; $count >= $i; $i++){
$query .= "AND ('$lookup[$i]')";
}
$result = mysql_query("$query");
?>

that should work if i am correct but just so you know, i think you have an error in your query


That wont work.. this looks like a search, so try something like this:


$lookup = "mens jackets";
$lookup = explode(' ',$lookup);
$count=count($lookup);

$query = "SELECT * FROM stuff";
if( $count > 0 ){
$query .= "WHERE ";
}
for( $i = 0; $i<$count; $i++ ){
$query .= " description LIKE '%{$lookup[$i]}%' AND ";
}

$query = substr( $query, 0, (strlen($query)-5));

$result = mysql_query( $query );


You need to use the % (wildcard) to match words within the description, otherwise nothing will be matched. Also, for putting array variables inside a string you need to use {} around the variable, as shown above with : {$lookup[$i]}

[davehaz]

Miller Time, you almost had it, thanks.


$query .= " description LIKE '%{$lookup[$i]}%' AND ";


needed to be

$query .= " description LIKE( '%$lookup[$i]%') AND ";


not to be picky, but it started kicking out results after I changed that.

thank you both very much.

[missing-score]

I dont think thats right...

Firstly, I have never used brackets for LIKE, but i dont know if it matters either way.

if you use an array value in paranthesis like that ( "$value[$i]" ) or whatever, it wont show a variable. This may have tricked you into falsley believing that your script is working..

Instead of running the query, print it out using echo and compare the 2. If you need more help, post back here.

[davehaz]

almost there, not sure if this problem is for here or the mysql forum,
however, when I do a search on say mens jackets, it pulls up everything with mens in, such as womens as well. I thought maybe taking the %s out would help but, I get no results then. any ideas?

thanks.

[missing-score]

ok, if you add a space in, eg:


description LIKE '% {$lookup[$i]} %'

[davehaz]

this is what I get when I do it without the curly brackets

SELECT * FROM stuff WHERE description LIKE ('%mens%') AND description LIKE ('%jackets%')
8 Items found that matched your search.


I have only ever had to use curly brackets in a query when I used a global variable like

$_SESSION['var'] or a $_POST[lookup].

But I am very new at this. and don't get to spend near enough time with it.

[missing-score]

Hmm thats odd, Ive always needed to use curly brackets..

Anyway, how is your script working now?

[davehaz]

almost there, not sure if this problem is for here or the mysql forum,
however, when I do a search on say mens jackets, it pulls up everything with mens in, such as womens as well. I thought maybe taking the %s out would help but, I get no results then. any ideas?

thanks.

[davehaz]

the spaces did it. it is humming along now. thanks for all the help.