PHP/SQL retrieve BOTH categories and latest topic

Question

I'm currently "practicing" making a forum. I've never done anything this big before and it's been going on for a while. Pretty much everything works, but I can't get it to select the proper latest topic from the categories on the index page.

Here is the SQL:

    $sql = '
    SELECT topic_id, topic_subject, topic_by FROM topics WHERE topic_cat IN (1,2,3) ORDER BY topic_cat DESC LIMIT 3
    UNION
    SELECT cat_id, cat_name, cat_description FROM categories WHERE cat_id IN (1,2,3)
    ';

I will also supply some pictures of the two tables from the database. Here is the categories table.

This is the topics table.

The basic idea here, as you may tell from the SQL, is that it selects the three first categories and then selects the latest topic from those three categories.

The code for placing the information retrieved by sql into a table containing everything.

    $result = mysqli_query($conn, $sql);

    if (!$result) {
        echo 'Could not display categories. Error: ' . mysqli_error($conn);
    } else {
        if (mysqli_num_rows($result) == 0) {
            echo 'No categories found in the database.';
        } else {
            echo '
                <table>
                <h3>Top 3 Categories</h3>
                <tr>
                    <th>Category</th>
                    <th>Latest Topic</th>
                </tr>
            ';

            while ($row = mysqli_fetch_assoc($result)) {
                echo '<tr>
                    <td class="leftpart">
                        <h3><a href="category.php?id=' . $row['cat_id'] . '">' . $row['cat_name'] . '</a></h3>' . $row['cat_description'] . '
                    </td>
                    <td class="rightpart">
                        <a href="topic.php?id=' . $row['topic_id'] . '">' . $row['topic_subject'] . '</a>
                    </td>
                </tr>
                ';
            }

            echo '</table>';
        }
    }

I'm not sure what else to add to this, so if there are any questions, please comment it and I can either answer it there or add additional information to the main post!

Maybe this question helps You: http://stackoverflow.com/questions/1442527/how-to-select-the-newest-four-items-per-category — Roman Hocke, May 09 '17 at 09:59

score 2 · Accepted Answer · answered May 09 '17 at 10:16

2

what you are actually looking for is JOIN which basically used to combine rows from two or more tables, based on a related column between them.

Here are the different types of the JOINs in SQL:

(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

this what you want :

SELECT
    topics.topic_id,
    topics.topic_subject,
    categories.cat_id,
    categories.cat_name,
    categories.cat_description 
FROM
    topics 
    JOIN
        categories 
        ON topics.topic_cat = categories.cat_id 
ORDER BY
    topics.topic_date DESC LIMIT 3;

The reason you are not getting proper results with union have a look here :

The UNION operator is used to combine the result-set of two or more SELECT statements.

Each SELECT statement within UNION must have the same number of columns
The columns must also have similar data types
The columns in each SELECT statement must also be in the same order

Your tables does not meet the above creatiria

answered May 09 '17 at 10:16

Masivuye Cokile

4,754
3
19
34

will it give three categories or three topics or total three rows ? – Ahmed Ginani May 09 '17 at 10:19
total of three rows remeber we have joined the tables into one now @AhmedGinani – Masivuye Cokile May 09 '17 at 10:22
Thank you for your detailed answer, this works, but if someone posts 2 topics in the same category it shows 2 of the same category, is there any way to get rid of this? If what's happening is unclear I can send a screenshot – Chaost May 09 '17 at 10:55
after SELECT add DISTINCT so that is becames `SELECT DISTINCT...` @Chaost – Masivuye Cokile May 09 '17 at 11:15
Did not work, the whole syntax is now `SELECT DISTINCT * FROM topics JOIN categories ON topics.topic_cat = categories.cat_id ORDER BY topics.topic_date DESC LIMIT 3` – Chaost May 09 '17 at 11:20
what does it show? you might need to also add another order by which will be the cat ID desc or topic id desc – Masivuye Cokile May 09 '17 at 11:24

Web Artisan · Answer 2 · 2017-05-09T10:07:31.793

0

Try this:

select * from topics inner join categories 
 on topics.topic_cat = categories.cat_id
order by topics.topic_date desc limit 3

Basically, a inner join will do this.You can select particular field instead of * if you want.

edited May 09 '17 at 10:07

answered May 09 '17 at 09:57

Web Artisan

1,870
3
23
33

Okay, should I keep the "Union select" below this? – Chaost May 09 '17 at 09:58
No. Just this query. – Web Artisan May 09 '17 at 09:58
May I know Why Negative? – Web Artisan May 09 '17 at 10:03
Okay, so I tried this and it somewhat works. First of all it displays every category, although I only want the first 3 as this is the "home" page(I can probably fix this myself). It also just makes more of the ones that have several topics in it. So basically it displays for honor twice with each topic in it – Chaost May 09 '17 at 10:05
@Chaost you need to add LIMIT 3 – Masivuye Cokile May 09 '17 at 10:06
@BikashPaul not my downvote but I believe u got the downvote because of `select *` – Masivuye Cokile May 09 '17 at 10:07
Yes. I know it may be the reason. I had left it to the questioner. and pointed out that also. – Web Artisan May 09 '17 at 10:10
Had some trouble with the server right after i uploaded the new file with the new code. It resolved itself quickly to my surprise :p It definitely worked! Thank you so much :) – Chaost May 09 '17 at 10:20

PHP/SQL retrieve BOTH categories and latest topic

2 Answers2