how to select from two tables

[cssweb]

hello, how are you, i have an idea and want to apply it.

i have two tables the first one is called topics and the second is sections

these sections contains these columns

sections: section_name | sec_id
topics: topic_name | topic_id

and i want to select every section with their topics for instance

from section:news
topic1
topic2
topic3
topic4
topic5

from section:sport
topic1
topic2
topic3
topic4
topic5

of course sec_id and topic_id has the same id.i have tried to do it by left join but the section name is loopin with every topic i do not want by this way i want just the setion name once with their topics

this is exactly about what i want : http://www.elnashra.com/

[jkon]

Don’t topics have section id ?

[cssweb]

certainly sec_id and topic_id are the same id

[minitauros]

Well I don't think it is necessary to have sec_id and topic_id be the same ID. What you can do is:

Table 1 (sections):
id | section_name

Table 2 (topics):
id | section_id | topic_name

Then when a user opens for example the section with the ID 1, you could do the following:

SELECT topic_name FROM topics WHERE section_id = [the given section ID]

Is that about what you are looking for?

If you would want to select all sections with their topics at once, you could (for example) do this:

SELECT topics.topic_name,
    sections.section_name
FROM topics
JOIN sections ON topics.section_id = sections.section_id
ORDER BY sections.section_id DESC

[cssweb]

thanks minitauros but if you read my topic above i have methioned the sections is looping with each topic like this :

news
topic 1
news
topic 2
news
topic 3
news
topic 4

and i do not want by this way i want them like this :

news :
topic 1
topic 2
topic 3
topic 4

sport :
topic 1
topic 2
topic 3
topic 4

etc...
i am waiting a solution for this metter.pleas help

[minitauros]

Well since you must retrieve all sections and topics anyway, you could still use the query in my last example and then use PHP to sort the results. For example:

<?php
$q = 'SELECT topics.topic_name,
        sections.section_name
    FROM topics
    JOIN sections ON topics.section_id = sections.section_id
    ORDER BY sections.section_id DESC';

// Execute the query the way you want to execute it.

while(// Loop through the results in your own way.)
{
    // Get the current record's section name.
    $section_name = $results['section_name'];

    // If the current record's section name differs from the previous,
    // it means a new section has started.
    if($section_name != $old_section_name)
    {
        // A new section has started. Do anything you want to do if it has.
    }

    // Remember the last section name that we've used.
    $old_section_name = $results['section_name'];

    // Display info.
}

[diafol]

minituros' example is the way I'd do it. However, you could also use the GROUP_CONCAT method

SELECT s.section_name, GROUP_CONCAT(CONCAT(t.topic_id,'|',t.topic_name)) AS conc FROM sections AS s LEFT JOIN topics AS t ON s.section_id = t.topics_id GROUP BY s.section_id ORDER BY s.section_name

But the first method is cleaner and probably quicker.

[annaharris]

You have to apply a left join with the section table using the ORDER BY clause on Section id. This will give you the exact result that you need.

[cssweb]

minitauros thank you your code work's well,but what about if i want to limit some sections?

for example i want section:news limit 0,15 and sections sports limit 0,10

thank for all guys who try to help me

[minitauros]

Pfew I don't think you can do that in just one query. Not sure though, please correct me if I'm wrong. What I would do if you want to have a different limit for each section, is just execute multiple queries, one for each section, to exactly fit your needs.

[diafol]

For a single query for all those section limits, it can be done, but I think you'd need quite a few subqueries - which would be very slow.

I'd limit the number via the php loop. So using minitauros' sql, then modify the code in the loop with something like

$limits = array("sports"=>10, "news"=>15...);
$max = 20; //default max for sections not in $limits array

[minitauros]

Yes or that, of course :). I was thinking of that but I thought hmm yeah but what if you're retrieving a million rows per section, then sorting it in PHP might be a bit slower than executing a couple of queries with a limit ^^. So depends on your situation!

[diafol]

So depends on your situation!

agreed

[cssweb]

diafol how can use your code with this code :

for example : 


<?php
$q = 'SELECT topics.topic_name,
        sections.section_name
    FROM topics
    JOIN sections ON topics.section_id = sections.section_id
    ORDER BY sections.section_id DESC';
// Execute the query the way you want to execute it.

while(// Loop through the results in your own way.)
{
    // Get the current record's section name.
    $section_name = $results['section_name'];
    // If the current record's section name differs from the previous,
    // it means a new section has started.
    if($section_name != $old_section_name)
    {
        // A new section has started. Do anything you want to do if it has.
    }
    // Remember the last section name that we've used.
    $old_section_name = $results['section_name'];
    // Display info.
}

if i do a query for each section and select their topics manualy by assign the section id does it will cause hight load on my site or this method can consider wrong?

<div id='headblcok'>technology</div>
<?php 
$result=mysql_query("select `topic_title`,`id_topic`,`status`,`count` from `topics` where status='1'and topic_sec='21'order by id_topic desc limit 0,10");

        while($row = mysql_fetch_object($result))
        {


        echo "<div id='subhead'>

<a href='shownews.php?full=news&id_topic=$row->id_topic'>■ $row->topic_title</a></div>";

        }

?>

[diafol]

It depends on your numbers as mentioned by minitauros. If you have large numbers of topics, then individual queries or subqueries will be better than retreiving and looping over the entire resultset from the simple joined query. Even if you have small numbers now, you may envisage how your data will grow over the next few years. Plan ahead, so that you don't have to refactor your code.

Here's an idea:

Do a preliminary query to get totals of topics, so you can order them according to number of topics if you want.

Consider the following simplified tables:

FIELD         TYPE              COLLATION        NULL    KEY     DEFAULT  Extra          
------------  ----------------  ---------------  ------  ------  -------  -------------- 
section_id    INT(3) UNSIGNED   (NULL)           NO      PRI     (NULL)   AUTO_INCREMENT         
section_name  VARCHAR(20)       utf8_unicode_ci  YES             (NULL)
max_show      INT(3)            (NULL)           YES             20

FIELD        TYPE              COLLATION        NULL    KEY     DEFAULT  Extra
-----------  ----------------  ---------------  ------  ------  -------  --------------
topic_id     INT(10) UNSIGNED  (NULL)           NO      PRI     (NULL)   AUTO_INCREMENT
section_id   INT(3)            (NULL)           YES             (NULL)
topic_title  VARCHAR(20)       utf8_unicode_ci  YES             (NULL)

If you run this query:

SELECT s.section_id, s.section_name, s.max_show AS mx, COUNT(t.section_id) AS cnt FROM sections AS s LEFT JOIN topics AS t ON s.section_id = t.section_id GROUP BY t.section_id ORDER BY cnt DESC

You'll get something like this (well my data anyway - News has 43 topics and is limited to the first 10 news topics; Sports has 31 topics and is limited to display 15 of them...) :

section_id  section_name    mx  cnt
----------  ------------    --  ---
1           News            10  43
2           Sports          15  31
3           Entertainment   20  1

So armed with this, we can build a 'multi-union' query. Notice that the resultset is ordered via 'cnt', so the most popular section (or with most topics anyway) comes first. If you want a static order, you could add a 'ranked' field to the sections table:

FIELD         TYPE              COLLATION        NULL    KEY     DEFAULT  Extra          
------------  ----------------  ---------------  ------  ------  -------  -------------- 
section_id    INT(3) UNSIGNED   (NULL)           NO      PRI     (NULL)   AUTO_INCREMENT         
section_name  VARCHAR(20)       utf8_unicode_ci  YES             (NULL)
max_show      INT(3)            (NULL)           YES             20
ranked        INT(3)            (NULL)           YES             (NULL)

SELECT s.section_id, s.section_name, s.max_show AS mx, COUNT(t.section_id) AS cnt FROM sections AS s LEFT JOIN topics AS t ON s.section_id = t.section_id GROUP BY t.section_id ORDER BY s.ranked

Another addition to the initial query could be a LIMIT clause, where you limit the total number of sections that you want to display on the page. For example,

SELECT s.section_id, s.section_name, s.max_show AS mx, COUNT(t.section_id) AS cnt FROM sections AS s LEFT JOIN topics AS t ON s.section_id = t.section_id GROUP BY t.section_id ORDER BY cnt DESC LIMIT 5

Or

SELECT s.section_id, s.section_name, s.max_show AS mx, COUNT(t.section_id) AS cnt FROM sections AS s LEFT JOIN topics AS t ON s.section_id = t.section_id GROUP BY t.section_id ORDER BY s.ranked LIMIT 5

Okay, now we're set, let's build the UNION:

//using mysql here - it's deprecated soon, so need to change to mysqli or PDO
$union = array();
while($data = mysql_fetch_assoc($result){
    $union[] = "(SELECT s.section_name, t.topic_id, t.topic_title FROM sections AS s LEFT JOIN topics AS t ON s.section_id = t.section_id WHERE s.section_id = {$data['section_id']} LIMIT {$data['mx']})"; 
}

$sql = implode(" UNION ", $union);

You then build the sections panel as before (as minitauros suggests).
This may seem a bit over-complicated, but it may tidy up keeping hard-coded limits for certain sections.

Is there a better way? I'd be interested to know.

[minitauros]

Well that's one nice explanation :)! What I would do is something like this:

<?php
// Get all your sections.
$q = 'SELECT section_id,
        section_name,
        topics_limit
    FROM sections
    ORDER BY section_id DESC';
$rq = mysql_query($q);

while($fetch = mysql_fetch_assoc($rq))
{
    // Looping through sections.

    // Retrieve topics for this section.

    $q2 = 'SELECT topic_id
            topic_name
        FROM topics
        WHERE section_id = "' . $fetch['section_id'] . '"
        ORDER BY topic_id DESC
        LIMIT ' . $fetch['topics_limit'];
    $rq2 = mysql_query($q2);

    while($fetch2 = mysql_fetch_assoc($rq2))
    {
        // Looping through topics.

    }
}

[diafol]

That looks like it should work too. I was trying to reduce the number of query calls to the DB to a bare minimum (2 in total), although multiple UNIONS are reknowned for being sloooow.

How do you think your nested loops stack up against a union, speed-wise? Could write a test I suppose. Interesting...

[masterjiraya]

you have the DB Normalization of BCNF whereas the section ID consists of multiple topic ID's. In this manner of Normalization, you can use the combination of left join function with where clause and like function. that is so short and yet it is simple

[diafol]

you have the DB Normalization of BCNF whereas the section ID consists of multiple topic ID's. In this manner of Normalization, you can use the combination of left join function with where clause and like function. that is so short and yet it is simple

Would you like to expand on that?

[minitauros]

Yea that sounds interesting :o.

[cssweb]

thank you minitauros my problem has been solved using your last code and thank you diafol.