CODEWITHSUNDEEP
phpmysqlsql
Howdy_McGee
Howdy_McGee

Reputation: 10643

MYSQL Joins - Where Unique ID lies in 1 table

I have 2 tables I want to connect in a strange / dangerous / non-dynamic way. I don't have control over these tables. I'm trying to pull the summary from one table that contains event id but not category id but I need to reference another table to make sure that said event is in said category. This table contains both event id and cat id. I'm trying to join them but I keep getting returned nothing.

I know this is dangerous, but I also have control over the categories so I know that my category ID's will not change unless I specify. Since it auto-increments - my categories will be 1, 2, 3.

The Gist of my Tables

*events_vevent*

 - ev_id 
 - catid

 ---

*events_vevdetail*

 - evdet_id
 - summary

My Code

$data = array();
for($i = 0; $i < 3; $i++){
    $summary = array();
    $query_summary = mysql_query("SELECT events_vevdetail.summary FROM 
                     events_vevdetail, events_vevent 
                     WHERE 'events_vevent.evdet_id = $i' LIMIT 5") 
    or die(mysql_error());

    while(($row = mysql_fetch_array($query_summary)))
        $summary[] = $row[0];

    switch($i){
        case 0:
            $data['cat1'] = $summary;
            break;
        case 1:
            $data['cat2'] = $summary;
            break;
        case 2:
            $data['cat3'] = $summary;
            break;
    }
}

echo json_encode($data);

Explanation

so what I'm trying to do is: Since I know category 1 will always have an ID of 0, I want to pull the most recent 5 posts, but only posts in category ID 0. Same for cateogry2 and 3. Right now I'm getting empty arrays. I feel like I need 2 mysql queries (one for each table) and then compare but I'm not 100% sure and would rather do this the right way than the long way.

tl;dr is my MYSQL right?

Upvotes: 0

Views: 153

Answers (1)

VibhaJ
VibhaJ

Reputation: 2256

This query will return top most 5 records from each category.

SELECT e1 . *
FROM events_vevent e1
LEFT OUTER JOIN events_vevent e2 ON 
( e1.catid = e2.catid AND e1.ev_id < e2.ev_id )
GROUP BY e1.ev_id
HAVING COUNT( * ) < 5
ORDER BY catid, ev_id DESC

Upvotes: 3

Related Questions