• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 502
  • Last Modified:

Filtering mysql results by array

Hi:

I have a page where I have several checkboxes for the user to select keywords to filter on the next page a product database by this keywords. Checkboxes are:

<input name="keyword[]" type="checkbox" id="keyword" value="value1" />
<input name="keyword[]" type="checkbox" id="keyword" value="value2" />
<input name="keyword[]" type="checkbox" id="keyword" value="value3" />

This will be passed in an URL via GET to the next page like:

mysite.com/index.php?keyword[]=value1&keyword[]=value2&keyword[]=value3

Now on this page I want to get  the URL keyword array in PHP/Mysql.

My query right now looks like this:

mysql_select_db($database_inburn, $inburn);
$query_products = "SELECT * FROM products_keywords LEFT JOIN products ON products.id = products_keywords.id_keywords WHERE products.ProdName LIKE _______here array_______  AND products.active = 1";
$products = mysql_query($query_products, $inburn) or die(mysql_error());
$row_products = mysql_fetch_assoc($products);
$totalRows_products = mysql_num_rows($products);

Can somebody please let me know how best to filter the product database using the mysql query with the keywords submitted via the URL array?

Thank you
0
michael789
Asked:
michael789
  • 7
  • 5
5 Solutions
 
atannusCommented:
You question is a bit unclear...

Is your problem accessing the array within php?

Or is it building a proper query for utilizing the array information?

If time wasn´t an issue tonight, I´d answer both, but it is!

waiting...
0
 
michael789Author Commented:
it is the proper query. i think i also need to process the array in php before utilizing the array in the query, or not?
thanks a lot
0
 
Roger BaklundCommented:
Try this:
if(!isset($_POST['keyword']))
  $keyword_sql = '1=1'; # dummy condition when there are no keyword parameter
elseif(is_array($_POST['keyword'])) { # array keyword parameter
  $keyword_sql = array();
  foreach($_POST['keyword'] as $kw) {
    $kw = mysql_real_escape_string($kw);
    $keyword_sql[] = "products.ProdName LIKE '%$kw%'";
  }
  $keyword_sql = '('.implode(' or ',$keyword_sql).')';
} else { # single keyword parameter
  $kw = mysql_real_escape_string($_POST['keyword']);
  $keyword_sql = "products.ProdName LIKE '%$kw%'";
}
$query_products = "SELECT * FROM products_keywords LEFT JOIN products ON products.id = products_keywords.id_keywords WHERE $keyword_sql AND products.active = 1";

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
michael789Author Commented:
looks good. I change POST to GET however it only shows 1 result after running the query for a specific keyword?
0
 
Roger BaklundCommented:
>> it only shows 1 result

The code I provided does not show results, it only produces the SELECT statement.

Try running the produced SELECT directly in a mysql client (or phpmyadmin). Do you get multiple rows, while your php code only shows one row? If so, post the php code that is supposed to show the result.
0
 
michael789Author Commented:
i am sorry about that. I really appreciate your help. here is the page
0
 
michael789Author Commented:
0
 
Roger BaklundCommented:
Did you try to run the query directly in a mysql client?
0
 
michael789Author Commented:
ok..i changed the query and everything seems to work. Thank you for your help!
0
 
michael789Author Commented:
Hi:

I am not sure if you are still available however I ran into another issue with the query below. It actually inserts a blank/empty record into the products table each time I execute the code in my page. Any ideas why this could be? There is no insert record in this page?

$currentPage = $_SERVER["PHP_SELF"];
$maxRows_products = 12;
$pageNum_products = 0;
if (isset($_GET['pageNum_products'])) {
  $pageNum_products = $_GET['pageNum_products'];
}
$startRow_products = $pageNum_products * $maxRows_products;

if(!isset($_GET['tradeshow']))
  $keyword_sql = '1=1'; # dummy condition when there are no keyword parameter
elseif(is_array($_GET['tradeshow'])) { # array keyword parameter
  $keyword_sql = array();
  foreach($_GET['tradeshow'] as $kw) {
    $kw = mysql_real_escape_string($kw);
    $keyword_sql[] = "tradeshows.showname LIKE '%$kw%'";
  }
  $keyword_sql = '('.implode(' or ',$keyword_sql).')';
} else { # single keyword parameter
  $kw = mysql_real_escape_string($_GET['keyword']);
  $keyword_sql = "tradeshows.showname LIKE '%$kw%'";
}

mysql_select_db($database_inburn, $inburn);
$query_products = "SELECT * FROM products_trade
LEFT JOIN products ON products.id = products_trade.ID_product_trade
LEFT JOIN tradeshows ON tradeshows.id_show = products_trade.tradeshowID
WHERE $keyword_sql
GROUP BY products_trade.ID_product_trade";
$products = mysql_query($query_products, $inburn) or die(mysql_error());
$row_products = mysql_fetch_assoc($products);
$totalRows_products = mysql_num_rows($products);

Do you want me to open a new question for that?
0
 
Roger BaklundCommented:
The only way to insert a record is with a INSERT, a REPLACE or a LOAD DATA sql statement. This code only contains a SELECT statement, so there is no way this code can insert a row into the table. The problem must be somewhere else.
0
 
michael789Author Commented:
i know...that's why it is so weird...thank you for your response. Here the entire page. I don't see anything else that could trigger the insert!
index-results-tradeshow.txt
0
 
Roger BaklundCommented:
I don't see anything in that script explaining the new record. There are some included files, you should check those:

Connections/inburn.php
includes/mxi/MXI.php
_drawratingmusic.php
includes/tng/tNG.inc.php
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now