Solved

Need assistance with join statement PHP/PDO

Posted on 2016-09-22
7
33 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

947 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

21 Experts available now in Live!

Get 1:1 Help Now