Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

How do I fix this SELECT LEFT JOIN - PHP PDO

How do I fix this LEFT JOIN?   I am getting all of the values from the primary table, but nothing from the joined table.  I have tried several different ways...  What am I doing incorrect?  
 (The reason for the DISTINCT is because I have several of each ERPartNumber with different individual Compatible Part Numbers - CompAray lists all of the compatible part numbers)

Columns of the populated second table are:
 Category       Brand       Model       CompatiblePartNumber       ERPartNumber     CompArray        ModelArray


$query3 = $conn->query('SELECT DISTINCT ERPartNumber, ProductName, CategoryCode, Description, Qty, Active, Weight, SuggestedPrice, TierVPrice, CatalogPrice, ImageURL, Manufacturer, UPC, ETA FROM `ErepLaptopBattery` LEFT JOIN `ErepCrossReference1` USING (ERPartNumber)');

    while ($row3 = $query3->fetch(PDO::FETCH_ASSOC))
    {	
echo $row3[CompArray];	//NOTHING
	
$statement = $conn->prepare('INSERT INTO ErepProducts (ERPartNumber, ProductName, CategoryCode, CompatiblePartNumber, Description, Qty, Active, Weight, SuggestedPrice, TierVPrice, CatalogPrice, ImageURL, Manufacturer, Category, UPC, ETA, Brand, Model, CompArray, ModelArray) VALUES(:ERPartNumber, :ProductName, :CategoryCode, :CompatiblePartNumber, :Description, :Qty, :Active, :Weight, :SuggestedPrice, :TierVPrice, :CatalogPrice, :ImageURL, :Manufacturer, :Category, :UPC, :ETA, :Brand, :Model, :CompArray, :ModelArray)');

		$params = array(':ERPartNumber'=>isset($row3[ERPartNumber])?$row3[ERPartNumber]:"",
						':ProductName'=>isset($row3[ProductName])?$row3[ProductName]:"",
						':CategoryCode'=>isset($row3[CategoryCode])?$row3[CategoryCode]:"",
						':CompatiblePartNumber'=>isset($row3["CompatiblePartNumber"])?$row3["CompatiblePartNumber"]:"",
						':Description'=>isset($row3[Description])?$row3[Description]:"",
						':Qty'=>isset($row3[Qty])?$row3[Qty]:"",
						':Active'=>isset($row3[Active])?$row3[Active]:"",
						':Weight'=>isset($row3[Weight])?$row3[Weight]:"",
						':SuggestedPrice'=>isset($row3[SuggestedPrice])?$row3[SuggestedPrice]:"",
						':TierVPrice'=>isset($row3[TierVPrice])?$row3[TierVPrice]:"",
						':CatalogPrice'=>isset($row3[CatalogPrice])?$row3[CatalogPrice]:"",
						':ImageURL'=>isset($row3[ImageURL])?$row3[ImageURL]:"",
						':Manufacturer'=>isset($row3[Manufacturer])?$row3[Manufacturer]:"",
						':Category'=>isset($row3["Category"])?$row3["Category"]:"",
						':UPC'=>isset($row3[UPC])?$row3[UPC]:"",
						':ETA'=>isset($row3[ETA])?$$row3[ETA]:"",
						':Brand'=>isset($row3[Brand])?$row3[Brand]:"",
						':Model'=>isset($row3[Model])?$row3[Model]:"",
						':CompArray'=>isset($row3[CompArray])?$row3[CompArray]:"",
						':ModelArray'=>isset($row3["ModelArray"])?$row3["ModelArray"]:"");
		$statement->execute($params); 
	
}

Open in new window

0
lawrence_dev
Asked:
lawrence_dev
  • 5
  • 4
1 Solution
 
Chris StanyonCommented:
Your query should look like this:

SELECT DISTINCT ERPartNumber, ProductName, CategoryCode, Description, Qty, Active, Weight, SuggestedPrice, TierVPrice, CatalogPrice, ImageURL, Manufacturer, UPC, ETA
FROM `ErepLaptopBattery`
LEFT JOIN `ErepCrossReference1`
ON  ErepLaptopBattery.ERPartNumber = ErepCrossReference1.ERPartNumber;

Open in new window


On another note - you only need to prepare your statement once, so do it BEFORE the while loop.
0
 
Chris StanyonCommented:
Looking at your code in closer detail, you're trying to insert some values into your new table that you never pull in from your SELECT query

   CompatiblePartNumber
   Brand
   Model
   CompArray
   ModelArray

Because they're never SELECTed, your $row3 will never contain them.
0
 
lawrence_devAuthor Commented:
Chris,
Thanks for your help!  

I am getting an 'ambiguous' error.  

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'ERPartNumber' in field list is ambiguous'


OK, so my code should look like this (Before the while loop)?
$conn->query('TRUNCATE TABLE `ErepProducts');	

$query3 = $conn->query('SELECT DISTINCT ERPartNumber, ProductName, CategoryCode, Description, Qty, Active, Weight, SuggestedPrice, TierVPrice, CatalogPrice, ImageURL, Manufacturer, UPC, ETA
FROM `ErepLaptopBattery`
LEFT JOIN `ErepCrossReference1`
ON  ErepLaptopBattery.ERPartNumber = ErepCrossReference1.ERPartNumber');

    
	
$statement = $conn->prepare('INSERT INTO ErepProducts (ERPartNumber, ProductName, CategoryCode, CompatiblePartNumber, Description, Qty, Active, Weight, SuggestedPrice, TierVPrice, CatalogPrice, ImageURL, Manufacturer, Category, UPC, ETA, Brand, Model, CompArray, ModelArray) VALUES(:ERPartNumber, :ProductName, :CategoryCode, :CompatiblePartNumber, :Description, :Qty, :Active, :Weight, :SuggestedPrice, :TierVPrice, :CatalogPrice, :ImageURL, :Manufacturer, :Category, :UPC, :ETA, :Brand, :Model, :CompArray, :ModelArray)');	
	
	
	while ($row3 = $query3->fetch(PDO::FETCH_ASSOC))
    {	
	
	

		$params = array(':ERPartNumber'=>isset($row3[ERPartNumber])?$row3[ERPartNumber]:"",
						':ProductName'=>isset($row3[ProductName])?$row3[ProductName]:"",
						':CategoryCode'=>isset($row3[CategoryCode])?$row3[CategoryCode]:"",
						':CompatiblePartNumber'=>isset($row3[CompatiblePartNumber])?$row3[CompatiblePartNumber]:"",
						':Description'=>isset($row3[Description])?$row3[Description]:"",
						':Qty'=>isset($row3[Qty])?$row3[Qty]:"",
						':Active'=>isset($row3[Active])?$row3[Active]:"",
						':Weight'=>isset($row3[Weight])?$row3[Weight]:"",
						':SuggestedPrice'=>isset($row3[SuggestedPrice])?$row3[SuggestedPrice]:"",
						':TierVPrice'=>isset($row3[TierVPrice])?$row3[TierVPrice]:"",
						':CatalogPrice'=>isset($row3[CatalogPrice])?$row3[CatalogPrice]:"",
						':ImageURL'=>isset($row3[ImageURL])?$row3[ImageURL]:"",
						':Manufacturer'=>isset($row3[Manufacturer])?$row3[Manufacturer]:"",
						':Category'=>isset($row3[Category])?$row3[Category]:"",
						':UPC'=>isset($row3[UPC])?$row3[UPC]:"",
						':ETA'=>isset($row3[ETA])?$$row3[ETA]:"",
						':Brand'=>isset($row3[Brand])?$row3[Brand]:"",
						':Model'=>isset($row3[Model])?$row3[Model]:"",
						':CompArray'=>isset($row3[CompArray])?$row3[CompArray]:"",
						':ModelArray'=>isset($row3[ModelArray])?$row3[ModelArray]:"");
		$statement->execute($params); 
	
}

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
lawrence_devAuthor Commented:
That is correct Chris, I am attempting to add all of the fields from the second table.
0
 
Chris StanyonCommented:
The error you're getting is because your SELECT is trying to include the ERPartNumber column, but that column exists in both the joined tables so it doesn't know which one to grab. You need to fully qualify it with the table name

$query3 = $conn->query('SELECT DISTINCT ErepLaptopBattery.ERPartNumber, ProductName, CategoryCode, Description, Qty, Active, Weight, SuggestedPrice, TierVPrice, CatalogPrice, ImageURL, Manufacturer, UPC, ETA
FROM `ErepLaptopBattery`
LEFT JOIN `ErepCrossReference1`
ON  ErepLaptopBattery.ERPartNumber = ErepCrossReference1.ERPartNumber');

Open in new window

0
 
Chris StanyonCommented:
When you're setting up the $params, you have code like this:

':CompatiblePartNumber'=>isset($row3["CompatiblePartNumber"])?$row3["CompatiblePartNumber"]:"",

But if you look at your SELECT query that you use for $row3, you are never selecting a column named CompatiblePartNumber, so it will never be set. It will always insert "" into your database. The same goes for the other columns I mentioned.
0
 
lawrence_devAuthor Commented:
Thanks Chris.  The script appears to be working, however, it has been running a long time.  I have indexed ERPartNumber on both tables to try and speed the script up.  Is there anything else you recommend to speed it up?  (They are both large tables)

Thanks again for your help!!
0
 
Chris StanyonCommented:
Seems a fairly straightforward query, so shouldn't take too long to run. Have you tried running it directly at your database, using something like phpmyadmin. Also, try running it without the DISTINCT line. If you've set up indexes on both your keys, then that should help.
0
 
lawrence_devAuthor Commented:
Thanks Chris!  Really appreciate your help!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now