asked on

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

ASKER CERTIFIED SOLUTION

Chris Stanyon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chris Stanyon

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.

lawrence_dev

ASKER

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

lawrence_dev

ASKER

That is correct Chris, I am attempting to add all of the fields from the second table.

Chris Stanyon

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

Chris Stanyon

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.

lawrence_dev

ASKER

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!!

Chris Stanyon

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.

lawrence_dev

ASKER

Thanks Chris! Really appreciate your help!