0

My case is this: I have three tables: user, search_queries, and comments.

I want to display variable that I am getting from these three queries.

I have used INNER JOIN for both user and search_queries. This works very fine.

But how do I get the variables from the third table?

Below is my code so far.

<?php
$sql24 = "SELECT * FROM comments WHERE search_id=$search_id";
$result24=mysql_query($sql24);

$sql = "SELECT * FROM user INNER JOIN search_queries ON user.id = search_queries.id 
             ORDER BY search_id DESC";
$result=mysql_query($sql);

//-create  while loop and loop through result set
while($row=mysql_fetch_array($result))
{
    $fname =$row['fname'];
    $sname=$row['sname'];
    $id=$row['id'];
    $email=$row['email'];
    $profile_pic=$row['profile_pic'];
    $city=$row['city'];
    $country=$row['country'];
    $search_date=$row['search_date'];
    $QUERY=$row['QUERY'];
    $search_id=$row['QUERY'];

    //-display the result of the array
    echo "the results from 2nd query "; 
    echo "the results from the 3rd query "; 
}
?>

Fields in the underlying tables:

  • user: id, email, fname, sname, pass, city, profile_pic, etc.
  • comments: com_id, id, comment, date, etc.
  • search_queries: search_id, id, QUERY, date etc.

Instead, the first select query doesnt seem to work.

Please help.

Shadow
  • 33,525
  • 10
  • 51
  • 64
GuruCoder
  • 41
  • 7
  • can you provide the table structure ..i think you can join all three tables instead of current approach.. – narasimharaosp Mar 01 '16 at 12:25
  • You execute the 1st sql query, but then you do not process the results. Also, your code assigns value to $search_id after the query is executed, moreover, there is no error handling in your code, so you cannot even see if and why a query fails. – Shadow Mar 01 '16 at 12:26
  • user (id, email, fname, sname, pass, city, profile_pic etc) ... comments (com_id, id, comment, date etc) ..... search_queries (search_id, id, QUERY, date etc). – GuruCoder Mar 01 '16 at 12:30
  • @narasimharaosp, I just provided. – GuruCoder Mar 01 '16 at 12:37
  • When I join the three of them with an inner join, now the ID of the user who commented is wrong... $sql = "SELECT * FROM user INNER JOIN search_queries ON user.id = search_queries.id INNER JOIN comments ON search_queries.search_id=comments.search_id "; and also I only get the posts that have the comments on. – GuruCoder Mar 01 '16 at 12:51
  • 1. Use `left join` instead of `inner join` if you want posts that do not have comments. 2 You need to join the users table multiple times if you want the user id of the commenters as well. – Shadow Mar 01 '16 at 12:57
  • It's still unclear to me on what fields you would need to join these tables. If the search_queries are the posts and comments are responses to that post, then these 2 tables should be linked. However, users table should be linked to both comments and search_requests. The comments table has com_id (I guess it is the PK), but has an id column which we do not know which table it relates to. An FK is missing from the comments table. – Shadow Mar 01 '16 at 13:02
  • Also, not even the purpose of the query is clear to me: do you want to list all comments and posts of a user, or do you want to list all posts with their comments and the corresponding user's data? – Shadow Mar 01 '16 at 13:05
  • id on comments is the user id on user table. To mean, the user who has commented on that post. – GuruCoder Mar 01 '16 at 13:18
  • This is my expected results: I simply want to list all the posts from search_queries. That I am able to do so well using an inner Join. But when it comes to displaying the comments on those posts, problem begins,.... in that, I am not able to display correct data from comments table which should be: fname, sname, comment, date, id (user id of the person who commented). – GuruCoder Mar 01 '16 at 13:21
  • How do you know which comment belongs to which post? – Shadow Mar 01 '16 at 13:30
  • com_id is the id of the comment and search_id is the id of the post where the comment was posted. – GuruCoder Mar 01 '16 at 13:34
  • Your previous comment on the table structures do not indicate that the comment table has a search_id field. Can you pls confirm that there is a search_id field in the comments table? – Shadow Mar 01 '16 at 13:48
  • com_id, id, search_id, comment, comment_date .. that is the whole structure of the coments table. – GuruCoder Mar 01 '16 at 14:08
  • 1. DON'T USE 'SELECT *' – Strawberry Mar 03 '16 at 08:30

2 Answers2

0

Assuming id in comments and search_queries tables as user id

    $query = mysql_query("select * 
                            from user a, comments b, search_queries c
                            where a.id = b.id and a.id=c.id 
                            and c.search_id = '$search_id'");
    echo '<pre>';
    while($row=mysql_fetch_array($query)){
         print_r($row);
    }
    exit;

And try to use mysqli or PDO as mysql is deprecated reason

Community
  • 1
  • 1
narasimharaosp
  • 533
  • 3
  • 12
  • Thanks for this. I added a ')' here $search_id'"); but it didnt work as well. No data was printed. – GuruCoder Mar 01 '16 at 12:58
  • Your answer is mere speculation, we do not know how these tables are connected or what the OP wants with the query. – Shadow Mar 01 '16 at 13:06
  • Let me try to clarify: I want to display all the posts/queries whether they have comment or not. – GuruCoder Mar 01 '16 at 13:06
  • This is not enough. The comments table has only 1 foreign key, a field named id, which we do not know which table it references (users or posts). The foreign key to the other table is still missing. – Shadow Mar 01 '16 at 13:18
  • It references user table (it is the id of the user who commented). – GuruCoder Mar 01 '16 at 13:24
0

Okay, now we have all the required information on the 3 tables. Just to sum up:

  • You would like to list all posts (search_queries table), with all comments - irrelevant if a post has comments or not. You would also like to show the user details for both the posts and the comments.
  • id field in all 3 tables identify the user
  • there is a search_id foreign key in comments table identifying which post the comment belongs to

The sql query looks like as follows:

select u1.id as poster_id, --user id for the post
       u1....,--whatever other field(s) you would like to include about the user, who created the post, alias each of them
       u2.id as commenter_id, --user id for the comment
       u2....,--whatever other field(s) you would like to include about the user, who created the comment, alias each of them
       s.search_id, -- id of the post
       s....,       --whatever other field(s) you would like to include about the post
       c.com_id, -- id of the comment
       c....,       --whatever other field(s) you would like to include about the comment
from search_queries s
left join comments c on s.search_id=c.search_id  --left join ensures that all posts are listed, not just the ones with comments
inner join user u1 on s.id=u1.id --join to user table to get user details for the poster
left join user u2 on c.id=u2.id --join to get the user details of the commenter, note that I used left join again

If you want to get the details of a single post only, then in a where criteria provide the search_id.

I will leave you to figure out the php code for yourself. However, pls do note that you should not use mysql_*() functions any longer, use mysqli or pdo instead.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • Thanks a lot. I have this finally: $query =mysql_query("select u1.id, u1.fname, u1.sname, u2.id, u2.fname, u2.sname, s.search_id, s.QUERY, s.search_date, c.com_id, c.comment, c.date from user search_queries s inner join user u1 on s.id=u1.id left join user u2 on c.id=u2.id"); while($row=mysql_fetch_array($query)){ But it returns nothing at all – GuruCoder Mar 01 '16 at 15:02
  • 1. **Always** use error handling when invoking an sql statement. You do not do that, so you do not know that you have made syntax errors. 2. Although I gave you a copy-paste structure for the sql query, you left parts out of it. Pls compare the solution I provided with your code. 3. First design and run the query in your favourite mysql management application to see what the query does. When you are satisfied, then incoroporate it into your code. – Shadow Mar 01 '16 at 15:44
  • This thing is getting more complicated. Even if I use error handling, nothing comes.. I took the sql query and pasted directly on PHPMYADMIN and same issue. No error is displayed and no results. Here is the code I posted there: select u1.id as poster_id, u1.fname, u2.id as commenter_id, u2.fname, s.search_id, s.QUERY, c.com_id, c.comment from user search_queries s left join comments c on s.search_id=c.search_id inner join user u1 on s.id=u1.id left join user u2 on c.id=u2.id ... here it states that #1064 - error. – GuruCoder Mar 01 '16 at 16:21
  • Well, this is a different query from what you have posted previously. If this one does not return data that means you have inconsistencies within your data or you incorrectly described the relationships between the tables. Change the only inner join to left and see if you get back any records. Wait a minute, what is that error 1064 you are getting? That's a syntax error. – Shadow Mar 01 '16 at 16:26
  • same error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's LEFT JOIN comments c on s.search_id=c.search_id LEFT JOIN user u1 on s.id=u1.i' at line 1 – GuruCoder Mar 01 '16 at 16:40
  • Remove the word user right after `from`. This is why you have to add error handling to your code. – Shadow Mar 01 '16 at 16:53
  • Thanks a lot. It worked in the phpmyadmn.... now, how do I display one of the results. Say the fname of the posted user and that of the comenter user? Thanks lots. – GuruCoder Mar 02 '16 at 06:04
  • As I wrote in the comments in the sql code above, alias all fields coming from the user tables, so that you know which fields belong to the poster, which to the commenter. – Shadow Mar 02 '16 at 06:23
  • Cool... Finally got the php code... only lost of how to display the text "There are no comments for now" when a post doesnt have any comment. I was able to echo all the needed data in a
    but issue is how to now work within a while loop to show no comment when no comment is present....
     – GuruCoder Mar 02 '16 at 08:23
  • I'm sorry, but this is a different question altogether. Having said that, an if condition within php can probably do the trick. – Shadow Mar 02 '16 at 09:33