[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Need assistance with join statement PHP/PDO

Posted on 2016-09-22
7
Medium Priority
?
89 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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!

 

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

Industry Leaders: 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!

Question has a verified solution.

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

In this article, we’ll look at how to deploy ProxySQL.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

656 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