Inner join...

[arunpatal]

$result = mysql_query("SELECT s.sub_cat_id, s.category_id, s.sub_cat_name, t.id, t.topic_title
        FROM subcategory s INNER JOIN datatb t ON(s.sub_cat_id=t.sub_id)
        ORDER BY s.sub_cat_id $DESC_ASC, t.id $DESC_ASC LIMIT 5");

 

 

The number of limit should be from subcategory table but its geting limit of datatb..... ????????

[bashy]

LIMIT 5 will limit all the final results? It will only return as many rows as you want where s.sub_cat_id = t.sub_id...

[arunpatal]

lets say i hav 6 subcategory and each subcategory have 10 entrys from datatb table...

I want to limit subcategory to 5 but then problem is that its showing only one subcategory with 5 entrys.

 

I want to display 5 rows of subcategory

Edited November 27, 2013 by arunpatal

[dalecosp]

5 rows of each subcategory?

[jazzman1]

lets say i hav 6 subcategory and each subcategory have 10 entrys from datatb table...

I want to limit subcategory to 5 but then problem is that its showing only one subcategory with 5 entrys.

 

I want to display 5 rows of subcategory

 

Then, GROUP BY each subcategory entries which exist in datatb table, count only these which have for example minimum 10 records using a HAVING clause and finaly LIMIT them to 5. That's all. 

 

PS: My logic about this query here is something like that:

$result = mysql_query("
        SELECT s.sub_cat_id, s.category_id, s.sub_cat_name, t.id, t.topic_title
        FROM subcategory s INNER JOIN datatb t ON (s.sub_cat_id=t.sub_id)
        GROUP BY s.sub_cat_name
        HAVING(COUNT(*) >= 1)
        ORDER BY s.sub_cat_id $DESC_ASC, t.id $DESC_ASC LIMIT 5");

Edited November 28, 2013 by jazzman1

[arunpatal]

I need 5 rows of subcategroy....

 

The code above brings 5 row of subcategorys but it shows only 1 item in each subcategory..

 

Like this

 

 

Subcat1.

 itrem1

 

subcat2

 item2

 

subcat3

 item3.....   and so on.....

 

I want it to be like this

 

subcat 1

 item1, item2, item3

subcat 2

 item1, item2

subcat 3

 item1, item2, itme3, item4

subcat 4

 item1, item2

subcat 5

 item1, item2, itme3, item4, item5

Edited November 28, 2013 by arunpatal

[Barand]

Try something like this (untested)

SELECT s.sub_cat_name
, GROUP_CONCAT(t.topic_title ORDER BY t.id $DESC_ASC SEPARATOR ', ')
FROM subcategory s 
INNER JOIN datatb t 
    ON(s.sub_cat_id=t.sub_id)
GROUP BY s.sub_cat_id
ORDER BY s.sub_cat_id $DESC_ASC,  
LIMIT 5

[arunpatal]

 

Try something like this (untested)

SELECT s.sub_cat_name
, GROUP_CONCAT(t.topic_title ORDER BY t.id $DESC_ASC SEPARATOR ', ')
FROM subcategory s 
INNER JOIN datatb t 
    ON(s.sub_cat_id=t.sub_id)
GROUP BY s.sub_cat_id
ORDER BY s.sub_cat_id $DESC_ASC,  
LIMIT 5

 

 

It gives error like this

 

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\my_site\functions.php on line 28

[jazzman1]

Try to remove the comma before a LIMIT clause, not sure that this syntax is proper.

Edited November 28, 2013 by jazzman1

[arunpatal]

Try to remove the comma before a LIMIT clause, not sure that this syntax is proper.

 

Its working like

 

subcat 1

 item1

subcat 2

 item1

subcat 3

 item1

 

and so......

 

Subcategory have more item in it............... 

[jazzman1]

It should work I think. What happens if you run the sql code directly to the db server via phpmyadmin or mysql workbench

[Barand]

At last! I've being trying to connect to the forum for over 3 hours.

 

Yes, I missed the comma when I was rearranging your query. When I run with my test data I get this

mysql> SELECT s.sub_cat_name
    -> , GROUP_CONCAT(t.topic_title ORDER BY t.id DESC SEPARATOR ', ') as items
    -> FROM subcategory s
    -> INNER JOIN datatb t
    ->     ON(s.sub_cat_id=t.sub_id)
    -> GROUP BY s.sub_cat_id
    -> ORDER BY s.sub_cat_id DESC
    -> LIMIT 5;
+--------------+--------------------+
| sub_cat_name | items              |
+--------------+--------------------+
| subcat f     | mmm, kkk           |
| subcat e     | lll, jjj           |
| subcat d     | iii, hhh, ggg, fff |
| subcat c     | eee, ddd           |
| subcat b     | ccc                |
+--------------+--------------------+

test data:

 

 

 

mysql> select * from subcategory;
+------------+--------------+
| sub_cat_id | sub_cat_name |
+------------+--------------+
|          1 | subcat a     |
|          2 | subcat b     |
|          3 | subcat c     |
|          4 | subcat d     |
|          5 | subcat e     |
|          6 | subcat f     |
+------------+--------------+

mysql> select * from datatb;
+----+--------+-------------+
| id | sub_id | topic_title |
+----+--------+-------------+
|  1 |      1 | aaa         |
|  2 |      1 | bbb         |
|  3 |      2 | ccc         |
|  4 |      3 | ddd         |
|  5 |      3 | eee         |
|  6 |      4 | fff         |
|  7 |      4 | ggg         |
|  8 |      4 | hhh         |
|  9 |      4 | iii         |
| 10 |      5 | jjj         |
| 11 |      5 | lll         |
| 12 |      6 | kkk         |
| 13 |      6 | mmm         |
+----+--------+-------------+

 

 

Edited November 28, 2013 by Barand

[arunpatal]

It should work I think. What happens if you run the sql code directly to the db server via phpmyadmin or mysql workbench

Same result

[arunpatal]

I think it is because of loop.......

 

Check this code......

$categories = array();
    $result = mysql_query("SELECT *
    , GROUP_CONCAT(t.topic_title ORDER BY t.id DESC SEPARATOR ', ') as items
    FROM $subcategory s
    INNER JOIN $datatb t
    ON(s.sub_cat_id=t.sub_id)
    GROUP BY s.sub_cat_id
    ORDER BY s.sub_cat_id DESC
    LIMIT 5;");

    while($row = mysql_fetch_assoc($result)){
    $categories[$row['sub_cat_id']]['subcat'] = array('name' => $row['sub_cat_name']);
    $categories[$row['sub_cat_id']]['topics'][$row['id']] = array('name' => $row['topic_title'], 'id' => $row['id']);
    }

    foreach($categories as $sub_cat_id => $row):
    
    echo "<div id='subcat_list'>".strtoupper($row['subcat']['name'])."</div>";
        
        foreach($row['topics'] as $sub_id => $sub_row):
         
         echo "<div class='topic_list' id='topic_list$sub_row[id]'> <a href='review.php?topic_id=$sub_row[id]&datatb=$datatb'>";
        echo strtoupper($sub_row['name']);
        echo "</a></div>";
    
        endforeach;
        
    echo  "<br>";
    endforeach;
    }};

Edited November 28, 2013 by arunpatal

[jazzman1]

Same result

Then, you have to show us your database schema for those two tables and some sample of data. 

[Barand]

$sql = "SELECT s.sub_cat_name

    , GROUP_CONCAT(t.topic_title ORDER BY t.id $DESC_ASC SEPARATOR ', ') as items

    FROM subcategory s 

    INNER JOIN datatb t 

        ON(s.sub_cat_id=t.sub_id)

    GROUP BY s.sub_cat_id

    ORDER BY s.sub_cat_id $DESC_ASC  

    LIMIT 5";

$res = $db->query($sql);

while ($row = $res->fetch_assoc()) {

    echo "<strong>{$row['sub_cat_name']}</strong><br>{$row['items']}<br><br>";

}


/** results ****************

subcat a

aaa, bbb


subcat b

ccc


subcat c

ddd, eee


subcat d

fff, ggg, hhh, iii


subcat e

jjj, lll


****************************/

[arunpatal]

This is how my topic table look like......

Table name PHP

 

id int auto_increment primary key,
    topic_title mediumtext,
    topic_detail mediumtext,
    added_date date,
    sub_id int(11) This is sub_cat_id from subcategory table

 

 

And this is my subcategory table look like

 

sub_cat_id int auto_increment primary key,
sub_cat_name varchar(500),
category_id int(11)

[jazzman1]

Does the foreign key sub_id int(11) column in the topic table have a group of repeating values like the master's table? 

Edited November 28, 2013 by jazzman1

[arunpatal]

Does the foreign key sub_id int(11) column in the topic table has a group of repeating values like the master's table? 

No, and i have no idea how to do this.....?????????

[arunpatal]

Ok.........

Its working

"SELECT s.sub_cat_name, s.sub_cat_id, t.topic_title, t.id
    , GROUP_CONCAT(t.topic_title ORDER BY t.id DESC SEPARATOR ', ') as topic_title
    FROM $subcategory s
    INNER JOIN $datatb t
    ON(s.sub_cat_id=t.sub_id)
    GROUP BY s.sub_cat_id
    ORDER BY s.sub_cat_id DESC
    LIMIT 5;"

But new problem is that all topics are showing as one link........

 

Something like this  Item1,Item2,Item3

 

i am sure it have to do with loop....

 

This is the new code to echo ..

while($row = mysql_fetch_assoc($result)){
    $categories[$row['sub_cat_id']]['subcat'] = array('name' => $row['sub_cat_name']);
    $categories[$row['sub_cat_id']]['topics'][$row['id']] = array('name' => $row['topic_title'], 'id' => $row['id']);
    }

    foreach($categories as $sub_cat_id => $row):
    
    echo "<div id='subcat_list'>".strtoupper($row['subcat']['name'])."</div>";
        
        foreach($row['topics'] as $sub_id => $sub_row):
         
         echo "<div class='topic_list' id='topic_list$sub_row[id]'> <a href='review.php?topic_id=$sub_row[id]&datatb=$datatb'>";
        echo strtoupper($sub_row['name']);
        echo "</a></div>";
    
        endforeach;
        
    echo  "<br>";
    endforeach;
    }};

[jazzman1]

Can you show us the output of next foreach:

foreach($categories as $sub_cat_id => $row):
    
    echo "<div id='subcat_list'>".strtoupper($row['subcat']['name'])."</div>";
        
        foreach($row['topics'] as $sub_id => $sub_row):
         
        // here 
         echo "<pre>".print_r($sub_row,true)."</pre>"; 
    
        endforeach;
        
    echo  "<br>";
    endforeach;

[Barand]

I want it to be like this

 

subcat 1

 item1, item2, item3

subcat 2

 item1, item2

subcat 3

 item1, item2, itme3, item4

subcat 4

 item1, item2

subcat 5

 item1, item2, itme3, item4, item5

 

... which is exactly what the query and my code gives you. Now you introduce the need for links. When you get people's time for free, don't tell them you want one thing, then, when they have done it, tell them you wanted something different. It wastes their time. Good luck.

[arunpatal]

 

Can you show us the output of next foreach:

foreach($categories as $sub_cat_id => $row):
    
    echo "<div id='subcat_list'>".strtoupper($row['subcat']['name'])."</div>";
        
        foreach($row['topics'] as $sub_id => $sub_row):
         
        // here 
         echo "<pre>".print_r($sub_row,true)."</pre>"; 
    
        endforeach;
        
    echo  "<br>";
    endforeach;

 

 

This is the result

Subcategory 1
Array
(
    [name] => topic1, topic2, topic3, topic4
    [id] => 6
)

Subcategory 2
Array
(
    [name] => topic1, topic2, topic3, topic4
    [id] => 8
)

[arunpatal]

... which is exactly what the query and my code gives you. Now you introduce the need for links. When you get people's time for free, don't tell them you want one thing, then, when they have done it, tell them you wanted something different. It wastes their time. Good luck.

 

Sorry about that........  I could not explain nicely at the beginning

[jazzman1]

Then don't use CONCATE_GROUP().

Edited November 28, 2013 by jazzman1