3 Replies - 1499 Views - Last Post: 17 April 2013 - 10:33 PM

#1 eyesackery  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 14-April 13

Nooby SQL command issue

Posted 14 April 2013 - 12:17 AM

Hey guys have been literally pulling my hair out over a problem I have been having with this piece of code. Basically I have two tables, one called items, and one called categories.

I want to be able to search the items table according to the user search query, however I also want to limit those search results to the category selected, (if the user selected one).

I am very new to mySQL but trying so hard to get the hang of it! I think in this case I just lack the knowledge on how to tackle this situation.

This is what I have tried, it works, but it's ugly as hell. Also I'm sure it would be cleaner/more efficient to implement some sort of unique key between the items table and the category table to eliminate the use of the $category variable within php.

Any help would be greatly appreciated! :)

    function get_items($str, $category) {
        $connection = mysql_open();

        $query ="SELECT title, description, vendor, category_id, starting_price FROM ea_items ";

        if ($category == 0) {
            if ($str != '') {
                $query.="WHERE title like '%$str%' or description like '%$str%' or vendor like '%$str%' ";
            } 
        } else {
            if ($str != '') {
                $query.="WHERE title like '%$str%' or description like '%$str%' or vendor like '%$str%' ";
                $query.="AND category_id = $category";
            } else {
                $query.="WHERE category_id = $category";
            }
        }



Is This A Good Question/Topic? 0
  • +

Replies To: Nooby SQL command issue

#2 ChrisGulddahl  Icon User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 18
  • Joined: 17-April 13

Re: Nooby SQL command issue

Posted 17 April 2013 - 03:14 PM

Performance wise your code is all good. Some of the query is repeated, which I'm guessing is what you want to avoid.
For a query like this I would recommend a solution as the one below (I haven't tested it, but you should get the idea).
<?php
function get_items($str, $category) {
    $connection = mysql_open();

    $query ="SELECT title, description, vendor, category_id, starting_price FROM ea_items ";
	
	// Save all where_conditions in this array
	$where_conditions = array();
	if($category != 0){
		$where_conditions[] = "category_id = $category";
	}
	if(empty($str)){
		$where_conditions[] = "title LIKE '%$str%' or description LIKE '%$str%' or vendor LIKE '%$str%' ";
	}
	// Add a new if statement here if you need more som day
	
	// Merge all where_conditions together with ANDs
	if(size($where_conditions) > 0){
		$query .= "WHERE ".implode(" AND ", $where_conditions);
	}
	//(...)
}
?>


The implode function is really where it all comes together.
  • If there are multiple conditions it will put them together with " AND " in between.
  • If there is one condition it will return only that.
  • If there are no conditions it will return an empty string


The code above is not really more efficient than your own. However it is easier to understand and easier to expand with another condition later on.
I'm not sure how you would eliminate the $category variable, but you could consider adding a MySQL index over the category column in your ea_items table. It could potentially speed up queries for items in specific categories if you plan to get a lot of items in that table.

Other things to consider:
- Protect against MySQL-injection attacks by using mysqli_real_escape_string()
- Or even better, use prepared statements from "MySQL Improved" functions which will also prevent MySQL-injections (with this solution the code above will probably have to be changed quite a bit though.)

Let me know if you have further questions
Was This Post Helpful? 1
  • +
  • -

#3 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: Nooby SQL command issue

Posted 17 April 2013 - 07:39 PM

Another thing you might want to consider is the order of operations for boolean. AND precedes OR, so your query string might be producing an unexpected result. If you use the code given by ChrisGulddahl above, I would suggest changing line 13 to this:
$where_conditions[] = "(title LIKE '%$str%' or description LIKE '%$str%' or vendor LIKE '%$str%') ";


Was This Post Helpful? 0
  • +
  • -

#4 ChrisGulddahl  Icon User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 18
  • Joined: 17-April 13

Re: Nooby SQL command issue

Posted 17 April 2013 - 10:33 PM

View Poste_i_pi, on 17 April 2013 - 07:39 PM, said:

Another thing you might want to consider is the order of operations for boolean. AND precedes OR, so your query string might be producing an unexpected result. If you use the code given by ChrisGulddahl above, I would suggest changing line 13 to this:
$where_conditions[] = "(title LIKE '%$str%' or description LIKE '%$str%' or vendor LIKE '%$str%') ";


Yes, that's clearly a good idea.

Alternatively this can be done when imploding the conditions like so
$query .= "WHERE (".implode(") AND (", $where_conditions).")";

(although this code may be less intuitive)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1