-2

hey i am a beginner in PHP and MYSQL, i am stuck with a basic problem. How can i make my query from URL parameters

MY URL EXAMPLES:

www.abs.com/index.php?cat=shoes

www.abs.com/index.php?cat=shoes&subcat=sports

www.abs.com/index.php?cat=shoes&subcat=sports&color=blue

How can i handle this kind of URL's in my query, that is just my example query, i have a large set of fields for both of my tables. I need help with the WHERE part of query, main problem is i am passing (cat) in my URL where it is (b.category) in my database.

MY QUERY:

select a.p_id, a.p_name, a.p_prize, b.p_id b.color, b.category, b.subcategory
FROM products a INNER JOIN details b ON a.p_id=b.p_id
Jason C
  • 38,729
  • 14
  • 126
  • 182
Junaid Rehman
  • 113
  • 1
  • 9

2 Answers2

2

This is easy enough: 

$cat = $_GET['cat'];
$query = "SELECT a.p_id, a.p_name, a.p_prize, b.p_id b.color, b.category, b.subcategory
    FROM products a 
    INNER JOIN details b ON a.p_id=b.p_id
    WHERE b.category = '" . mysql_real_escape_string(trim($cat)) . "'";

Please note a couple of things, firstly the use of mysql_real_escape_string to help protect your database. It helps protect against SQL injections, although isn't entirely foolproof.

Also note that MySQL_* is depreciated. You shouldn't be writing queries in it any more. You should look into MySQLi or PDO.

Styphon
  • 10,304
  • 9
  • 52
  • 86
  • :) how can i handle when user selects color, category or brand at the same time, real question was my names are not the same as of my db fields – Junaid Rehman Feb 27 '14 at 16:54
  • That would depend on where colour, category and brand are in your database. If they are in details then just add more clauses onto the end of the query using `AND`. – Styphon Feb 27 '14 at 16:55
  • my database fields in query are i.inventory_color, p.product_category where values in my URL are cat and color – Junaid Rehman Feb 27 '14 at 16:57
  • Can you update your original question? You need to include more detail so add your full table structure. – Styphon Feb 27 '14 at 16:59
  • [detailed Question](http://stackoverflow.com/questions/22049860/using-url-parameters-in-mysql-search?noredirect=1#comment33476843_22049860) – Junaid Rehman Feb 27 '14 at 17:10
  • @JunaidRehman Generally, it is better to update an existing question when adding more detail instead of creating a new question, as valid and useful answers are already posted and we don't want to lose them. Failing that, you'd want to link to your new question in a comment on your post, not on an answer; that way everybody can easily see it. As it stands, since you've already done it, I've marked this question as a duplicate of your new one. – Jason C Feb 27 '14 at 19:40
  • i am new to stack over flow, instead of showing me the right way its not good you give me negative points – Junaid Rehman Mar 01 '14 at 10:36
1

First you need to process the URL using PHP by assigning the URL parameters to PHP variables:

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

Then you can use these variables to create a MySQL query string:

$queryString = "SELECT a.p_id, a.p_name, a.p_prize, b.p_id b.color, b.category, b.subcategory
FROM products a INNER JOIN details b ON a.p_id=b.p_id WHERE b.category = '" . mysql_real_escape_string( $cat ) . "' AND b.subcategory = '" . mysql_real_escape_string( $subcat ) . "' AND b.color = '" . mysql_real_escape_string( $color ) . "' ";

You can then use this query sting to query the database.

egd
  • 162
  • 2
  • 9
  • Don't put unsanitised user input directly into the database. – Styphon Feb 27 '14 at 16:57
  • I would agree in the case of inserting in to a database, though the query string above simple selects from a database and as such should be safe. Though best practice would be to use _mysql_real_escape_string_. Answer edited to show best practice. – egd Feb 27 '14 at 17:01
  • Go for mysqli or pdo :-) – Olavxxx Feb 27 '14 at 17:28
  • @egd if you believe that you need to read up more on [SQL injections](http://stackoverflow.com/questions/5759603/mysql-injection-use-select-query-to-update-delete). Especially as people are moving over to MySQLi which supports multiple queries. – Styphon Feb 27 '14 at 21:17