Member Avatar for 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? :)

  • 2 Contributors
  • 9 Replies
  • 129 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by debasisdas

Dani AI

Generated

As discovered, the root causes here were not JOINs themselves but two common pitfalls: treating an entire SQL statement as data and a misplaced WHERE clause. mysqli::real_escape_string is meant to escape user data values (it escapes newlines and control characters), not to sanitize a full query string — escaping the SQL can inject backslash sequences and break parsing. See the PHP manual on mysqli::real_escape_string for details: mysqli::real_escape_string.

Always check for query/prepare errors before calling fetch_assoc() — that failure means the query returned false. Inspect the connection or statement error properties to get the real cause (for example mysqli::$error or the statement error). PHP docs: mysqli::$error.

SQL clause order matters: FROM, JOIN ... ON, then WHERE, then GROUP/HAVING, then ORDER BY. Putting WHERE before the JOIN/ON will produce a syntax error; qualify ambiguous column names (for example posts.id) to avoid collisions. See MySQL SELECT syntax: SELECT syntax. For multiple IDs prefer an IN(...) construct rather than repeated ORs, and use prepared statements rather than manual escaping.

When preparing queries with a variable number of parameters (an IN list), build the appropriate number of placeholders and bind them dynamically. Also guard against duplicate column names when using table.* — alias columns you need (for example p.id AS post_id) so fetch_assoc() keys are predictable. If rows without matching channels must be returned, use LEFT JOIN instead of INNER JOIN.

Quick troubleshooting checklist:

  • If prepare() or query() returns false, log mysqli->errno / mysqli->error or stmt->errno / stmt->error.
  • Do not call fetch_assoc() until you have a valid result object.
  • Use prepared statements for data and only escape literal values when absolutely necessary.

(, )

Recommended Answers

All 9 Replies

Member Avatar for debasisdas

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

Member Avatar for 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);
Member Avatar for 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...

Member Avatar for debasisdas

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

Member Avatar for gunnarflax

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

Edited by gunnarflax because: n/a
Member Avatar for 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?

Member Avatar for 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
Member Avatar for gunnarflax

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

Member Avatar for debasisdas

This is what is called a two-way communication.

Happy to help you.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.