Transitioning to PDO/MySQL. Need help with SELECT statement with LEFT JOIN

I need assistance with a left join on a a SELECT statement.  I am not getting any errors, however, I am not getting any results either...



$conn = new PDO("mysql:host=$host;dbname=$db;charset=$charset", $user, $pass);

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Establish a MySQL connection
	
	$query2 = $conn->prepare('SELECT * FROM `product`
			LEFT JOIN `product_description`
			USING (product_id)
			LEFT JOIN url_alias
			USING (query)');
	
    while ($row2 = $query2->fetch(PDO::FETCH_ASSOC))
    {
		
	 echo $row2['product_id']."  ".$row2['description']."  ".$row2['query'];
	  
	}	

Open in new window

lawrence_devAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
GaryConnect With a Mentor Commented:
Everyone missed the pink elephant in the room

You are preparing the statement but never executing it

	$query2 = $conn->prepare('SELECT * FROM `product`...

Open in new window



Code fixed:
	$query2 = $conn->query('SELECT * FROM `product`
			LEFT JOIN `product_description`
			USING (product_id)
			LEFT JOIN url_alias
			USING (query)');

    while ($row2 = $query2->fetch(PDO::FETCH_ASSOC))
    {
		
	 echo $row2['product_id']."  ".$row2['description']."  ".$row2['query'];
	  
	}	

Open in new window


When doing a query where there is no external input there is no need to use prepare - just execute the query immediately
0
 
GaryCommented:
If you run the query direct in PHPMyAdmin or similar do you get any errors or results
Hard to debug without knowing the tables, but it looks OK.
0
 
SimonConnect With a Mentor Commented:
For multiple outer joins (e.g. LEFT JOIN) I'd use the slightly more verbose ON syntax to join the table rather than USING. It's not clear from your example which tables contain the key column 'query'.
0
 
Ray PaseurConnect With a Mentor Commented:
Without delving too deeply into it, I can tell you that any SELECT statement that works in the MySQL extension will work the same way in the MySQLi or PDO extensions.  The only difference will be in the way the variables are injected into the query string.  MySQL is going to be the same under the covers of any PHP extension.

You may want to use try / catch blocks so you can visualize any error messages.  These messages will be useful for your debugging.
0
 
lawrence_devAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.