PHP >> Mysql querying database with string array values

[djinnov8]

Hey there...

 

I'm so stuck on this problem I was hoping someone could help...

 

I need to query a MySQL Database using array values...

 

Here is my array ... $query_keywords = Array ( [7] => business [10] => home [11] => depo )

 

Here is my php ...$query = "SELECT * FROM product WHERE product_name, product_comment IN ($query_keywords)";

$result=mysql_query($query);

 

if(mysql_num_rows($result) > 0)

{

echo "results";

}

else

{

echo "no results";

}

 

 

Unfortunately, I get this ...

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in blah\blah\results.php on line 99

 

Please help me :'( :'(

All comments greatly appreciated...

 

[rwwd]

Your not specifying an index for the array, so if you were to echo the sql to screen the word array would be in the mix - so use a for each to iterate through the array, then you can generate the rest from that...

 

Cheers,

Rw

[djinnov8]

This forum is the best thing since sliced bread...

Thanks RWWD...Yes, I see what you mean so I'll give it a try...

 

I'm a newbie with coding and PHP and have been toying with the foreach loop but on top of the query I need is another problem...

 

I'll probably need to build the foreach loop to query the table for different combinations of the array values...

 

Looking at my array and my table - each column could contain one of my values e.g. 'business' OR...

each column could contain two of my values in a different order e.g. "business, depot"...OR "depot, business"

...and so on and so forth depending upon the amount of values I am looking for...

 

I have an idea your foreach loop will do that but my inexperience with coding is complicating matters...

 

If anyone has ideas for the syntax, please let me know...thanks in advance

[wildteen88]

so use a for each to iterate through the array, then you can generate the rest from that...

Or use implode

$query = "SELECT * FROM product WHERE product_name, product_comment IN ('" . implode("','", $query_keywords) . "')";

[djinnov8]

so use a for each to iterate through the array, then you can generate the rest from that...

Or use implode

$query = "SELECT * FROM product WHERE product_name, product_comment IN ('" . implode("','", $query_keywords) . "')";

 

Thanks for your input Wildteen...

 

I tried your query like this but still ended up with the error...

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in blah\blah\results.php on line 99

no results

 

$query = "SELECT * FROM product WHERE product_name, product_comment IN ('" . implode("' ,'", $query_keywords) . "')";

 

echo "<br />" . $query;

 

$result=mysql_query($query);

 

if(mysql_num_rows($result) > 0)

{

echo "results";

}

else

{

echo "no results";

}

 

Here is an echo of my select statement...

SELECT * FROM product WHERE product_name, product_comment IN ('business','home','depo')

 

 

[wildteen88]

Your query is has invalid syntax. The issue is highlighted in red

SELECT * FROM product WHERE product_name, product_comment IN ('business','home','depo')[/quoute]

What are you trying to do with that query?

[djinnov8]

Your query is has invalid syntax. The issue is highlighted in red

SELECT * FROM product WHERE product_name, product_comment IN ('business','home','depo')[/quoute]

What are you trying to do with that query?

 

Hi WT,

 

Trying to pick the out any data in the product_name and product_comment tables that contain any combination of the values in my array...

Looking at my example, my query should ask

"Does the product_name and product_comment tables have any rows with the word business OR home OR depo...

 

Example 1. So if one of the rows in the product_name table contains a product called like " The business accounting package", then it should pick up that row.

 

Example 2. If one of the rows in the product_name table contains nothing but the product_comment table contains a comment like "I run a business at home"...then my query should pick it up...

 

Example 3. If one of the rows in the product_comment table contains the phrase "I run a business at home called the depo company"...then it should pick this one out as well...

 

Hopefully, I can then use the results and maybe using PHP, choose which query picked out the most words/values from my array...and sort them accordingly.

 

So looking at my examples...

Example 3 had three hits from my array

Example 2 had two

Example 1 had one hit...

This means, I will show Example 3 query results at the top because it had the most matches from my array, then query results from Example 2, then Example 1...

 

Hopefully my explanation makes things a little clearer...

[djinnov8]

Oh WT...You spotted my first problem...query is referencing multiple columns...should be...

SELECT * FROM recommended_product WHERE product_comment LIKE '%business%' OR product_biz LIKE '%business%';

[wildteen88]

If you are searching the database then yes you'll need to use the LIKE clause for each word you're searching for. IN() will search for exact values.

[djinnov8]

Thanks Bro - Looks like RWWD was right - use a foreach loop to generate multiple queries going through each of the values in my array and possible combinations of those values...

Sheez - that looks like a huge headache for my server...

[DavidAM]

OR use a loop to build the WHERE clause for a single query:

 

$query_keywords = Array ( 7 => 'business', 10 => 'home', 11 => 'depo' );
$where = '';
foreach ($query_keywords as $keyword) {
  if (empty($where)) $where = 'WHERE ';
  else $where .= ' OR ';

  $where .= "product_name LIKE '%$keyword%' ";
  $where .= "OR product_comment LIKE '%$keyword%'";
}
$query = "SELECT * FROM product $where";

$result=mysql_query($query);