Using URL parameters in MYSQL search

Question

I am trying to built a search page for products in which i have two table products and inventory, i have my product details in product and inventory details like color,size,price etc in inventory. Please suggest a solution i am really stuck and i need a better solution for a powerful design. Below are all necessary details:

Product:

product_id
product_name
product_sku
product_desc
product_category

Inventory:

inventory_id
product_id
inventory_color
inventory_size

MY QUERY:

SELECT p.product_name, p.product_sku, p.product_desc, i.inventory_color, i.inventory_color
FROM products as p
INNER JOIN  invetory as i ON p.product_id = i.product_id

URL:

www.abc.com/index.php?cat=shoe&color=black

How can i get the cat and color from URL and use them in my query? Or am i doing the right way?

possible duplicate of [Make MYSQL query from URL GET parametrs](http://stackoverflow.com/questions/22074954/make-mysql-query-from-url-get-parametrs) — Rowland Shaw, Feb 27 '14 at 16:52

score 2 · Accepted Answer · answered Feb 26 '14 at 18:33

2

Yes, you are in the right path. Get the values from url using get or request

$category=mysql_real_escape_string($_GET['cat']);
$color=mysql_real_escape_string($_GET['color']);

and make your SQL as

SQL="SELECT p.product_name, p.product_sku, p.product_desc, i.inventory_color, i.inventory_size
FROM products as p
INNER JOIN  invetory as i ON p.product_id = i.product_id
WHERE p.product_category='".$category."' AND i.inventory_color='".$color."'";

answered Feb 26 '14 at 18:33

Akhil Sidharth

746
1
6
16

thanks a lot, but another thing how can i convert my spaces(%20) to - when displaying in URL and vice verse when fetching data from database – Junaid Rehman Feb 26 '14 at 18:40
i just noticed one thing :) My parameters are different from my db fields, e.g. for above particular scenario it will work, what if user only clicks cat how would i handle that in product category, i am trying to use the facet search, which has many parameters coming. – Junaid Rehman Feb 27 '14 at 15:35
Can you tell me what all parameters you want to search ? – Akhil Sidharth Feb 27 '14 at 18:17

score 1 · Answer 2 · answered Feb 26 '14 at 18:33

1

You can catch values from an url. you can use " $_GET['parameter'] "

$color = $_GET['color'];
$category = $_GET['cat'];

answered Feb 26 '14 at 18:33

Suraj Malinga

101
1
15

score 0 · Answer 3 · answered Feb 26 '14 at 18:22

0

You can get it with $_GET like:

$product_id = $_GET['product_id'];

answered Feb 26 '14 at 18:22

Hardy

5,590
2
18
27

score 0 · Answer 4 · answered Feb 26 '14 at 18:25

using PDO, something like -

$dbh = new PDO(...)
$sth = $dbh->prepare('SELECT p.product_name, p.product_sku, p.product_desc, i.inventory_color
                      FROM products as p
                      INNER JOIN  invetory as i ON p.product_id = i.product_id 
                      WHERE p.cat = ? AND i.inventory_color = ?');
$sth->execute(array($_GET['cat'], $_GET['color']));
$results = $sth->fetchAll();

from example at http://www.php.net/manual/en/pdo.prepare.php

Using URL parameters in MYSQL search

4 Answers4

Linked

Related