Mysql Multiple Table query statement

Question

I have a table as follows

Ads Table
user_id ad_id   title             message   repost_days_id   repost_times_id
   1      1     Message Title 1   Message   1                1
   1      1     Message Title 2   Message   0                0

Repost Days Table:
repost_days_id   Value
1                5

Repost Times Table:
Repost_times_id   value
1                 10

Now I want to query the ads table to show ads with the ads title ads message repost days and repost times. For example it for ad 1 it will be

Message Title 1
Message Repost Days=10
Repost Times=5.

How do I write the sql statement for that? This is what I have done and is not displaying any result.

$q = "
select
    a.ad_id,
    a.title,
    a.message,
    rt.value,
    rd.value,
from
    ad_repost_times rt,
    ad_repost_days rd, 
    ads a 
where
    a.user_id='{$_SESSION['user_id']}' 
and rt.repost_times_id=a.repost_times_id 
and rd.repost_days_id=a.repost_days_id
";

You can find that in the Repost Days and Repost times table, there is no id value of 0. but in the ads table, both the repost_days_id and repost_times_id has been set to a default value of 0. Do you think this will affect the query. — stanley
– stanley, Commented May 23, 2011 at 13:52

dee-see · Accepted Answer · 2011-05-23 13:39:52Z

1

The query can't satisfy the a.user_id='{$_SESSION['user_id']}' condition because you have no user_id field in the ads table.

answered May 23, 2011 at 13:39

dee-see

24.1k6 gold badges63 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

stanley Over a year ago

I ommited the user_id field. It is there in the table

Ken Bloom · Accepted Answer · 2011-05-23 16:19:37Z

1

It appears that you have an extra comma at the end of the field list. This would cause a problem executing your query in the first place. Make sure you're checking for errors after you try to run the query that would alert you to the problem.
Your a.user_id='{$_SESSION['user_id']}' means there will be braces around your user id when you submit it in the SQL query. If $_SESSION['user_id'] holds the integer 1234, then your query will look like a.user_id='{1234}'. (Note that this "error" may not actually be the cause of your problem if you made the same error all over your code.)
The following isn't a direct cause of your problem, but it's important for security reasons, and it would have avoided the problem in #2: you need to remove the variable substitution a.user_id='{$_SESSION['user_id']}', and change it a parameter binding with a.user_id=? in order to avoid an SQL injection attack (and also coincidentally eliminate the typo causing issue #2).
To handle the default value of zero in the repost_days_id, repost_times_id you need to use a LEFT JOIN, like so:
```
$q = "
select
    a.ad_id,
    a.title,
    a.message,
    rt.value,
    rd.value
from
    ads a
    LEFT JOIN ad_repost_times rt ON rt.repost_times_id=a.repost_times_id
    LEFT JOIN ad_repost_days rd ON rd.repost_days_id=a.repost_days_id
where
    a.user_id='{$_SESSION['user_id']}' 
";
```
The results for rt.value and rd.value will be null for rows that have 0's for the id.

Note that I have reordered the tables (ads a needs to come first, otherwise you need to use RIGHT JOIN in certain places instead of LEFT JOIN), and that there should no longer be a comma between the table names. (LEFT JOIN replaces the comma).

edited May 23, 2011 at 16:19

answered May 23, 2011 at 13:55

Ken Bloom

59.1k14 gold badges114 silver badges171 bronze badges

3 Comments

stanley Over a year ago

Hello Ken, Thanks for the feedback. I have removed the extra comma on the last field query and it is showing out results now. However, It is not showing the results from the table where both the repost_days_id and repost_times_id has been set to a default value of 0. Since there is no 0 value in the respective repost days and repost times table. How do i work around this. I don't want to add 0 to those tables.

stanley Over a year ago

Thank you so much. Your explanations really helped me.How do i implement this "you need to remove the variable substitution a.user_id='{$_SESSION['user_id']}', and change it a parameter binding with a.user_id=?"

Ken Bloom Over a year ago

You have to use MySQLi, and then you use the mysqli_stmt_bind_param function.

Tudor Constantin · Accepted Answer · 2011-05-23 13:36:15Z

0

Is this query executed without any errors?

From your table structure it seems that Ads table does not have field user_id and you are querying on it

answered May 23, 2011 at 13:36

Tudor Constantin

26.9k7 gold badges54 silver badges73 bronze badges

Collectives™ on Stack Overflow

Mysql Multiple Table query statement

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related