0

I want to use multiple table to get recent post (Q) and user name (handle) but when I am trying to get with this code it is giving error

Table Name qa_posts and qa_users and need to connect userid from qa_posts to handle from qa_users

SELECT *
FROM qa_posts, qa_users
WHERE type="Q" AND qa_posts.userid = "qa_users.handle"
ORDER BY created DESC
LIMIT 5

Error message

SELECT *
FROM qa_posts, qa_users
WHERE qa_posts.userid = "qa_users.handle"
ORDER BY created DESC
LIMIT 5

Error 1052: Column 'created' in order clause is ambiguous

Actual Code

qa_db_connection();
$query_post = qa_db_query_raw('         
    SELECT *
    FROM qa_posts, qa_users
    WHERE type="Q" AND qa_posts.userid = "qa_users.handle"
    ORDER BY created DESC
    LIMIT 5

') or die(mysql_error());

while($row = mysql_fetch_array($query_post)) {
    echo $row['title'];
    echo '<span style="font-size:65%;font-style:italic">'.$row['created'].'</span>';
    echo '<br/>';
}

See this image for table structure Table Strucute

Code Lover
  • 8,099
  • 20
  • 84
  • 154

2 Answers2

1

The error is telling you have you have multiple columns with the name created so you need to specify which table the created field is coming from to perform the ORDER BY:

SELECT *
FROM qa_posts, qa_users
WHERE qa_posts.userid = "qa_users.handle"
ORDER BY qa_posts.created DESC
LIMIT 5

or:

SELECT *
FROM qa_posts, qa_users
WHERE qa_posts.userid = "qa_users.handle"
ORDER BY qa_users.created DESC
LIMIT 5

Edit #1, without seeing the full table structure it looks like you might want to do this you had the qa_users.handle in quotes, these should probably be removed:

SELECT *
FROM qa_posts
INNER JOIN qa_users
   ON qa_posts.userid = qa_users.handle
ORDER BY qa_posts.created DESC
LIMIT 5

Edit #2 you are joining on the wrong field:

SELECT *
FROM qa_posts
INNER JOIN qa_users
   ON qa_posts.userid = qa_users.userid
WHERE qa_posts.type="Q" 
ORDER BY qa_posts.created DESC
LIMIT 5
Taryn
  • 242,637
  • 56
  • 362
  • 405
  • Thanks a lot, error gone but it is not rendering any item. I am adding my full code with while loop I request you to please check for better understanding.. – Code Lover Sep 08 '12 at 18:02
  • 1
    @pixelngrain my suggestion is to try running the query separate first to see if you get results, then attempt to implement it in the rest of your code. – Taryn Sep 08 '12 at 18:05
  • I actually got post title and created date but now I want to display user name so need to connect both table from qa_posts userid with qa_users handle....Alright let me check the qa_users table output – Code Lover Sep 08 '12 at 18:06
  • @pixelngrain please see my edit, I do not know why you had the `qa_users.handle` in quotes. – Taryn Sep 08 '12 at 18:10
  • I have added table screenshot pls check it once – Code Lover Sep 08 '12 at 18:21
1

The given error cames out when you do have same name column in both table...So you have to specify column name along with table name like in following patter:

table_name.column_name

So, your query should be like this:

SELECT *
FROM qa_posts, qa_users
WHERE qa_posts.userid = "qa_users.handle"
ORDER BY qa_posts.created DESC (or put the ORDER BY qa_users.created DESC if you want to make order by `created` of `qa_users` )
LIMIT 5

If you really not desired to have userid of value "qa_users.handle", you should not quote them and should have query like this:

    SELECT *
    FROM qa_posts, qa_users
    WHERE qa_posts.userid = qa_users.userid AND qa_posts.type="Q" 
    ORDER BY qa_posts.created DESC 
    LIMIT 5

You would better to use JOIN in following way:

    SELECT *
    FROM qa_posts INNER JOIN qa_users ON qa_posts.userid = qa_users.userid 
    WHERE qa_posts.type="Q" 
    ORDER BY qa_posts.created DESC 
    LIMIT 5
Akash KC
  • 16,057
  • 6
  • 39
  • 59