Fetching information from two tables in the same query

[gunnarflax]

I've done some research on JOINs and UNIONs but haven't achieved what I want to do since something I do must be terribly wrong. I have the following SQL-statement:

SELECT posts.*, channels.channel
FROM posts
INNER JOIN channels
ON posts.channel_id = channels.id
ORDER BY posts.created DESC

What I want to do is to get all the columns from the "posts" table and the "channel" column from the "channels" table where posts.channel_id is the same as channels.id. Am I approaching this wrong? Should I skip JOINs and try a more advanced SELECT statement? Help me out here will you? :)

[debasisdas]

What is the problem , i don't see anything wrong with the SQL.

[gunnarflax]

I guess that somethings wrong while performing the query since the result is nonexistent:

Fatal error: Call to a member function fetch_assoc() on a non-object in C:\xampp\htdocs\HTSIBWORT\gw_sql_lib.php on line 70

But I don't get what could go wrong since this is how I execute it:

//$this->con is the MySQLi-object and &unprp_stmt is the statement
$stmt = $this->con->real_escape_string($unprp_stmt);
$result = $this->con->query($stmt);

[gunnarflax]

Ok, I've solved it, the problem was in that the statement was divided into multiple lines so when I ran mysqli::real_escape_string() line breaks "\n" was added into the script. But I've never had any trouble running multi-lined statements before... strange...

[debasisdas]

yes , that is exactly how developers trouble shoot. :)

[gunnarflax]

Thanks! I'm an amateur but I try :)

[gunnarflax]

Now I have another problem... When I'm trying to prepare this statement it won't return a mysqli_stmt handle:

SELECT posts.*, channels.channel FROM posts WHERE id=? OR id=? INNER JOIN channels ON posts.channel_id = channels.id ORDER BY posts.published DESC

It works if I don't use the where clause but this time I need it. Do you use WHERE differently when working with JOINs?

[debasisdas]

try this

SELECT posts.*, channels.channel 
FROM posts 
INNER JOIN channels 
ON posts.channel_id = channels.id 
WHERE  (posts.id=? OR posts.id=? )   ----better to use IN clause here.
ORDER BY posts.published DESC

[gunnarflax]

YES! You are the ultimate SQL-Guru! Thank you!

[debasisdas]

This is what is called a two-way communication.

Happy to help you.