CodeIgniter Forums
[SOLVED] Funky DB Query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: [SOLVED] Funky DB Query (/showthread.php?tid=42517)

Pages: 1 2

[SOLVED] Funky DB Query - El Forum - 06-09-2011

[eluser]loganbest[/eluser]
I'm passing 4 values through the URL vars to a function, but it's only reading 1 of the 4 values when it fetches the info from the database. I'm fairly new to CI so maybe I"m just doing it completely wrong but here's the code.

Any help is greatly appreciated.
Code:
public function listSpecials($c, $l, $a, $s) {
        $cuisine = ($c != "All" && $c != null) ? intval($c) : false;
        $location = ($l != "All" && $l != null) ? intval($l) : false;
        $ambiance = ($a != "All" && $a != null) ? intval($a) : false;
        $viptype = ($s != "All" && $s != null) ? intval($s) : false;
        //get all records from users table
        //$this->db->select('rest_name, preferred, phone, address, city, state, zip, url, single_special, couple_special, group_special, girl_scout_special, image');
        if($cuisine || $location || $ambiance || $viptype) {
            $this->db->join('tag_restaurants_rel', 'tag_restaurants_rel.restaurant_id = restaurants.restaurant_id');
            $filterarray = array();
        
            if(is_numeric($cuisine) && $cuisine!=false) {
                $this->db->where( 'tag_restaurants_rel.tag_id', $cuisine );
            }
            
            if(is_numeric($location) && $location!=false) {
                $this->db->where( 'tag_restaurants_rel.tag_id', $location );
            }
            
            if(is_numeric($ambiance) && $ambiance!=false) {
                $this->db->where( 'tag_restaurants_rel.tag_id', $ambiance );
            }
        }
        
        $this->db->group_by("restaurants.restaurant_id");
        $query = $this->db->order_by("rest_name", "asc")->get( 'restaurants' );

        if( $query->num_rows() > 0 ) {
            return array('count'=>$query->num_rows(), 'data'=>$query->result(), 'vars'=>array($cuisine, $c, $location, $l, $ambiance, $a, $viptype, $s));
        } else {
            return array('count'=>$query->num_rows(), 'error'=>'error', 'vars'=>array($cuisine, $c, $location, $l, $ambiance, $a, $viptype, $s));
        }
    }

for better clarification:
I have 3 tables in my db: restaurants, tag_restaurants_rel, tags
tag_restaurants_rel in InnoDB with the restaurant_id and tag_id fields referring to the other two tables.

[SOLVED] Funky DB Query - El Forum - 06-09-2011

[eluser]vitoco[/eluser]
can you post the resulting query ??

Code:
echo $this->db->last_query();


[SOLVED] Funky DB Query - El Forum - 06-09-2011

[eluser]loganbest[/eluser]
Code:
SELECT *
FROM (`restaurants`)
JOIN `tag_restaurants_rel` ON `tag_restaurants_rel`.`restaurant_id` = `restaurants`.`restaurant_id`
WHERE `tag_restaurants_rel`.`tag_id` = 40
AND `tag_restaurants_rel`.`tag_id` = 19
GROUP BY `restaurants`.`restaurant_id`
ORDER BY `rest_name` asc


[SOLVED] Funky DB Query - El Forum - 06-09-2011

[eluser]vitoco[/eluser]
you're passing ( in the widest case ) 3 values to the same field (AND)
Code:
if(is_numeric($cuisine) && $cuisine!=false) {
                $this->db->where( 'tag_restaurants_rel.tag_id', $cuisine );
            }
            
            if(is_numeric($location) && $location!=false) {
                $this->db->where( 'tag_restaurants_rel.tag_id', $location );
            }
            
            if(is_numeric($ambiance) && $ambiance!=false) {
                $this->db->where( 'tag_restaurants_rel.tag_id', $ambiance );
            }

and i may be worng, but one field can answer to 2 values at the same time in a query, at least if they are related by "AND"

Code:
...
WHERE
    `tag_restaurants_rel`.`tag_id` = 40 AND
    `tag_restaurants_rel`.`tag_id` = 19
...

EDIT : i think you made a copy/paste to the conditions that add the "wheres", and didn't change the name of the tables or the fields to search


Saludos

[SOLVED] Funky DB Query - El Forum - 06-09-2011

[eluser]loganbest[/eluser]
Nope that is correct.

The tag_restaurants_rel table has a row for each specific tag_id related to that restaurant_id. So I need a query that gets the results for a restaurant that has BOTH 40 AND 19 as a tag_id.

[SOLVED] Funky DB Query - El Forum - 06-09-2011

[eluser]vitoco[/eluser]
so.....BOTH implies
Code:
field = value1 OR field = value2
or as an alternative
Code:
IN( value1 , value2 , ... , valueN )
but never "AND" , cause as i said, one field in a row, can't have 2 values

Saludos

[SOLVED] Funky DB Query - El Forum - 06-09-2011

[eluser]loganbest[/eluser]
correction. I'm not trying to get a single restaurant. I'm getting all restaurants that have both 40 AND 19 as a tag_id.

[SOLVED] Funky DB Query - El Forum - 06-09-2011

[eluser]loganbest[/eluser]
well it would be IN i guess because it would need both to work properly. I tried to group it all by restaurant_id because that's the common denominator.

I tried the $this->db->where_in() with no luck either

[SOLVED] Funky DB Query - El Forum - 06-09-2011

[eluser]vitoco[/eluser]
i'm not used to work with active records, so i write the query in plain sql :
Code:
SELECT
    * ,
    COUNT( tag_id ) AS num_tags ,
    GROUP_CONCAT( tag_id ) AS tags
FROM
    restaurants
INNER JOIN
    tag_restaurants_rel
ON
    tag_restaurants_rel.restaurant_id = restaurants.restaurant_id
WHERE
    tag_restaurants_rel.tag_id    = 40 OR
    tag_restaurants_rel.tag_id    = 19
GROUP BY
    restaurants.restaurant_id
HAVING
    num_tags = 2
ORDER BY
    rest_name asc

explaination : if you select rows with tag_id = 40 OR 19 , you can get at the most , 2 rows to each "restaurant_id", so if you group by "restaurant_id" and count the tag_id , using INNER JOIN , the "num_tags" field can have 2 values, 1 and 2 , and if you filter the resulting rows using ( with HAVING ) to the rows that have 2 tags ( 40 and 19 ) you get what you want

[SOLVED] Funky DB Query - El Forum - 06-09-2011

[eluser]loganbest[/eluser]
so since I'm ideally passing in maybe 3 or 4 values to test with I would just change num_tags = 3 or 4?