How i can compare the result in one query or two?

Question

I am trying to fetch last 10 topics from smf database but topics are in two separate tables.

Subject, Post Time and Message ID in smf_messages.
Topic ID and Topic First Message in smf_topics.

I wrote this code to fetch topics but i dont know how to compare these two results.

$result = mysql_query("select subject,id_msg,id_topic from smf_messages");
$result2= mysql_query("select id_first_msg from smf_topics");
if(mysql_num_rows($result)!=0)
{
    while($read = mysql_fetch_object($result))
    {
        if ($read->id_msg==$read->id_topic) {
            echo "<a href='../index.php?topic=" . $read->id_topic . "'>".$read->subject."</a><br>";
        }
    }

What do you wanna do exactly? Do you want to search the first message from all topics? — Fernando Zamperin, Jul 21 '16 at 20:39
I wanna compare id_first_msg from smf_topics with id_msg from smf_messages. — Sezer Toker, Jul 21 '16 at 20:42
It's easier to do a join in mysql query and then fetch just the results you want from both tables, yes if your database definition is correct then they are. @RafaelShkembi — Fernando Zamperin, Jul 21 '16 at 20:46
i will do the same thing but i want to be sure about the question that's why i asking ;) @FernandoZamperin — Rafael Shkembi, Jul 21 '16 at 20:49

score 0 · Accepted Answer · answered Jul 21 '16 at 20:56

0

You probably want the following query

select subject, id_msg, id_topic, id_first_msg
from smf_messages
left join smf_topics on smf_message.id_msg = smf_topics.id_first_msg

answered Jul 21 '16 at 20:56

Hogan

69,564
10
76
117

score 0 · Answer 2 · answered Jul 21 '16 at 20:56

0

bind the records by joining in sql:
select subject id_msg, id_topic, id_first_msg from smf_messages join smf_topics on smf_messages.id_msg = smf_topics.id_topic

answered Jul 21 '16 at 20:56

user6622043

101
1
7

score 0 · Answer 3 · answered Jul 21 '16 at 21:00

mysql have many many options, with a small google search you could found about left join. tuttorial and examples.

Fist thing you have to do is stop using mysql_* and start using mysqli_* or pdo learn about sql injections

Your query should look like this.

select smf_messages.subject,smf_messages.id_msg,smf_messages.id_topic from smf_messages left join smf_topics on smf_topics.id_first_msg=smf_messages.id_msg

score 0 · Answer 4 · answered Jul 21 '16 at 21:02

If you want to do that without using SQL, this is my solution it's not good but you can have an idea of how it works:

$messages = array();
$topics = array();

while($readmessage = mysql_fetch_object($resultmessage)) {
    array_push($messages, $readmessage);
}
while($readtopic = mysql_fetch_object($resulttopic)) {
    array_push($topics, $readtopic);
}

foreach ($topics as $topic) {
    $result = array_search($topic->id_first_message);
    if($result != true){
        //HERE $result IS THE INDEX OF FIRST MESSAGE IF IT IS IN THE MESSAGES ARRAY
        $messages[$result].id_msg //Access message attributes
    }
}

How i can compare the result in one query or two?

4 Answers4