Getting info from 2 tables

Question

Having trouble with some select statements.

I have 2 tables. sms_log and sms_messages.

sms_log :

id
message_id
user_id

sms_messages:

message_id
admin_id
message
date_sent
date_delivered

I want to get the sms_message data where sms_log.user_id = $id

How do i do it, im a bit stuck. Also, how will i access it through php, exactly the same as a standard query, or with the table name in front of the cell :

    `
echo $myrow['sms_messages.message'];

OR

echo $myrow['message'];

thankyou !

Answer 1

First, you will need to perform an inner join on the SMS_MESSAGe and SMS_LOG tables on messageId. This will look something like this:

SELECT m.Message 
FROM sms_Messages me
INNER JOIN sms_Log l ON l.Message_Id = m.Message_Id
Where l.user_Id = $id;

At this point, in your myrow variable, you'd access the message like:

echo $myrow['message'];

Answer 2

select m.message as message
, m.date_sent as date_sent
FROM sms_messages as m
LEFT JOIN sms_log as l
ON m.message_id = l.message_id
WHERE l.user_id = $id

You'd still be able to access the array as you'd expect:

echo $arr['message'] ' sent on ' $arr['date_sent'] ;

Answer 3

Despite Stephen's brave attempt, it is impossible to determine from the information you provided how to join the two tables. The fact that there are columns in each table called message_id implies that it might be a foreign key in one and not the other.

Are there any primary keys declared for the tables? any unique indexes?

Is this your own data design or someone elses? What are the data types for the columns? What DBMS are you using? Are any of the columns populated from a sequence generator or mysql auto-increment?

What do these tables actually signify (OK SMS messages - I get that, but are they just incoming?

C.