Solved

Need assistance with join statement PHP/PDO

Posted on 2016-09-22
7
27 Views
Last Modified: 2016-10-01
How do I correctly structure this join statement?

$query1 = $conn->query("SELECT * FROM `Outdoor_Invoices` LEFT JOIN `oc_order` USING (order_id) WHERE PONo = 'order_id'");

//LEFT JOIN `oc_url_alias` USING (query) WHERE product_id');

    while ($row1 = $query1->fetch(PDO::FETCH_ASSOC))
    {
		
	$orderid = $row1['order_id'];
	$InvAmt = $row1['InvAmt'];
	$OrderTotal = $row1['total'];
	$Profit = ($total - $InvAmt);
	
$conn->query("UPDATE `Outdoor_Invoices` SET OrderTotal = $OrderTotal, Profit = $Profit WHERE PONo = '$orderid'");
						
}

Open in new window

0
Comment
Question by:lawrence_dev
  • 3
  • 3
7 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41811884
Which one?
0
 

Author Comment

by:lawrence_dev
ID: 41813255
Thanks Terry!  I tried a little variation and still cannot get it to work.


$query1 = $conn->query("SELECT * FROM `Outdoor_Invoices` LEFT JOIN `oc_order` USING (order_id) WHERE Outdoor_Invoices.PONo = oc_order.'order_id'");
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41813367
I wonder if it's because order_id is in single quotes rather than backticks? You don't need them anyway for the given table and column names.

Try this:
$query1 = $conn->query("
SELECT * FROM Outdoor_Invoices
LEFT JOIN oc_order USING (order_id) 
WHERE Outdoor_Invoices.PONo = oc_order.order_id
");

Open in new window

0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:lawrence_dev
ID: 41813728
Terry,  the columns and table names are correct, however, I am getting the following error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'order_id' in 'from clause'' in OutdoorProfit.php:125 Stack trace: #0 OutdoorProfit.php(125): PDO->query('SELECT * FROM O...') #1 {main} thrown in OutdoorProfit.php on line 125

Column  /   Table

PONo   /  Outdoor_Invoices

order_id  /  oc_order
0
 
LVL 35

Accepted Solution

by:
Terry Woods earned 500 total points (awarded by participants)
ID: 41814123
Ok, it's reasonably clear you want to join the two tables using Outdoor_Invoices.PONo = oc_order.order_id

So I think perhaps this is what you want:
$query1 = $conn->query("
SELECT * FROM Outdoor_Invoices
LEFT JOIN oc_order ON oc_order.order_id = Outdoor_Invoices.PONo 
");

Open in new window

0
 

Author Comment

by:lawrence_dev
ID: 41816972
Sorry Terry for the delay!!  That worked great!!  THANKS!!!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
This article discusses how to create an extensible mechanism for linked drop downs.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now