[SOLVED] Driving me insane.

[waynew]

I recently had to make a few edits to a custom content management system for clothing items. Each clothing item now has a number of sizes. I've done this by adding a separate table for item sizes, moving away from the delimiter approach that the system had been using for sizes.

 

You can see the basic schema of the two tables below:

 

Item table

mysql_query("
        CREATE TABLE IF NOT EXISTS item(
            item_id INT(11) NOT NULL AUTO_INCREMENT,
            item_name TEXT NOT NULL,
            item_description TEXT NOT NULL,
            item_size VARCHAR(200) DEFAULT NULL,

 

Sizes table

mysql_query("
        CREATE TABLE IF NOT EXISTS sizes(
            size_id INT(11) NOT NULL AUTO_INCREMENT,
            item_id INT(11) NOT NULL,
            INDEX item_id_index(item_id),
            item_size INT(11) NOT NULL,
            INDEX size_index(item_size),
            stock_num INT(11) DEFAULT 0,

 

The size is linked to the clothing item it belongs to via the item_id column.

 

Problem:

I have a class function that selects clothing items. By default, it will just select every clothing item. However, it can also handle certain search by name, category etc. I want to have it so that a user can also search by size.

 

The function is below. Any help would be greatly appreciated. And yes, I know that it's not good practise to use variables such as $_GET and $_POST inside a class function. 

 

function get_items(){
    
            $conditions = ""; //blank by default
            
            //providing category search
            if(isset($_GET['category_id'])){
                $category = (int) $_GET['category_id'];
                if($category != 0){
                    $conditions = " AND item.category_id = '$category' ";
                }
            }
            
            //providing size search
            if(isset($_GET['size'])){
                $size = (int) $this->db->clean($_GET['size']);
                if($size != 0)
                    $conditions = $conditions." "; //Problem is here
            }
            
            //search for new arrivals specifically
            if(isset($_GET['new_arrivals']) && $_GET['new_arrivals'] == "Y"){
                $conditions = $conditions." AND item.is_new_arrival = 1 ";
            }
            
            //search for special offers specifically
            if(isset($_GET['special_offers']) && $_GET['special_offers'] == "Y"){
                $conditions = $conditions." AND item.is_special = 1 ";
            }
            
            //search by price
            if(isset($_GET['price'])){
                $price = (double) $_GET['price'];
                $conditions = $conditions." AND (item.original_price = '$price' OR (item.special_price = '$price' AND item.is_special = 1)) ";
            }
            
            //this query is for pagination purposes
            $count = "
                SELECT 
                    COUNT(*) AS num
                FROM
                    item, image
                WHERE 
                    item.item_id = image.item_id AND image.is_main = 1 ".$conditions."
                ORDER BY item.item_name";
            
            //this is the actual select query                
            $query = "
                SELECT 
                    item.*, image.image_id, image.thumb_image 
                FROM
                    item, image
                WHERE 
                    item.item_id = image.item_id AND image.is_main = 1 ".$conditions."
                ORDER BY item.item_name";
                        
            $results = $this->pagination->get_results($count,$query);
            return $results;
    
    }

[mrMarcus]

so, this is your question/issue:

 

I want to have it so that a user can also search by size.

 

man, next time just come out and say it.  also, what is the problem?  where are you in the code?  what errors are you receiving (if any)?  etc., etc.

[waynew]

so, this is your question/issue:

 

I want to have it so that a user can also search by size.

 

man, next time just come out and say it.  also, what is the problem?  where are you in the code?  what errors are you receiving (if any)?  etc., etc.

 

I wanted to provide as much helpful info about my problem as possible. The problem is that I'm not sure how to form the query if somebody is searching by size. I know that the second IF condition and that last two queries will need to be changed.

[kickstart]

Hi

 

Crude way of doing it would be to add this:-

 

  
            //search by size
            if(isset($_GET['size'])){
                $size = $_GET['size'];
                $conditions = $conditions." AND (item.item_id IN (SELECT item_id FROM sizes WHERE item_size = $size)) ";
            }

 

Better would be to use a JOIN to bring in the size into the whole piece of SQL when required, but this would be messier.

 

<?php
function get_items(){
    
            $conditions = ""; //blank by default
            
            //providing category search
            if(isset($_GET['category_id'])){
                $category = (int) $_GET['category_id'];
                if($category != 0){
                    $conditions = " AND item.category_id = '$category' ";
                }
            }
            
            //providing size search
            if(isset($_GET['size'])){
                $size = (int) $this->db->clean($_GET['size']);
                if($size != 0)
                    $conditions = $conditions." "; //Problem is here
            }
            
            //search for new arrivals specifically
            if(isset($_GET['new_arrivals']) && $_GET['new_arrivals'] == "Y"){
                $conditions = $conditions." AND item.is_new_arrival = 1 ";
            }
            
            //search for special offers specifically
            if(isset($_GET['special_offers']) && $_GET['special_offers'] == "Y"){
                $conditions = $conditions." AND item.is_special = 1 ";
            }
            
            //search by price
            if(isset($_GET['price'])){
                $price = (double) $_GET['price'];
                $conditions = $conditions." AND (item.original_price = '$price' OR (item.special_price = '$price' AND item.is_special = 1)) ";
            }
            
            //search by price
            if(isset($_GET['size'])){
                $size = $_GET['size'];
			$ExtraJoin = " INNER JOIN sizes ON item.item_id = sizes.item_id ";
                $conditions = $conditions." AND (sizes.item_size = '$size' ) ";
            }
            
            //this query is for pagination purposes
            $count = "
                SELECT 
                    COUNT(*) AS num
                FROM item
			INNER JOIN image
			ON item.item_id = image.item_id 
			".$ExtraJoin."
                WHERE image.is_main = 1 ".$conditions."
                ORDER BY item.item_name";
            
            //this is the actual select query                
            $query = "
                SELECT 
                    item.*, image.image_id, image.thumb_image 
                FROM item
			INNER JOIN image
			ON item.item_id = image.item_id 
			".$ExtraJoin."
                WHERE image.is_main = 1 ".$conditions."
                ORDER BY item.item_name";
                        
            $results = $this->pagination->get_results($count,$query);
            return $results;
    
    }
?>

 

All the best

 

Keith

[waynew]

Perfect! Thanks a million.