Skip to main content

Run a query after order is confirmed..

More
14 years 11 months ago #26786 by spoOx
Run a query after order is confirmed.. was created by spoOx
Hi,

I was wondering if somebody has done the following. Having read some previous posts I know it's possible. Unfortunately, my coding is not great so if somebody has done this and could kindly share their code I would highly appreciate it:

I am looking to run a mysql query after an order has been confirmed (payment received from paypal) and it contains a certain item. I would like to take the customer's details and insert them in to a seperate database.

As mentioned, I have little coding experience but any samples/exmaples should help me to get started..

Thanks in advance :)

Please Log in or Create an account to join the conversation.

More
14 years 11 months ago #26805 by nicolas
Replied by nicolas on topic Re: Run a query after order is confirmed..
Hi,

You should look at the history plugin which is installed by default with HikaShop, in the file plugins/hikashop/history.php
It implements the onAfterOrderUpdate event to add history information to orders.
There is also a query run in the onAfterOrderDelete event so you can see how queries are run.

Please Log in or Create an account to join the conversation.

More
14 years 11 months ago #26899 by spoOx
Replied by spoOx on topic Re: Run a query after order is confirmed..
Hi Nicolas,

thanks for the pointer. With my limited knowledge, I have put together the below. Seems to be working but I can't seem to get the email address, it comes up blank. Any idea why? Also, I want to only run the query if the product id is 1, 5 or 7 how do I do this? any pointers? And finally, is this code efficient or is there a more efficient way of doing it? Many many thanks in advance.

Code:
<?php /** * add user details to sub db */ defined('_JEXEC') or die('Restricted access'); ?> <?php class plgHikashopAdd_Sub extends JPlugin { function onAfterOrderUpdate(&$order,&$send_email){ if(!empty($order->order_id)){ if($order->order_status == 'confirmed'){ if(empty($order->order_product_id)){ $orderClass = hikashop::get('class.order'); $dbOrder = $orderClass->get($order->order_id); $orderClass=$orderClass->loadFullOrder($order->order_id, false, false); $fname = $orderClass->shipping_address->address_firstname; $add = $orderClass->shipping_address->address_street; $pc = $orderClass->shipping_address->address_post_code; $prod = $orderClass->order_full_price; } if(empty($orderClass->customer)){ $userClass = hikashop_get('class.user'); $orderClass->customer = $userClass->get($orderClass->order_user_id); $email = $orderClass->user_email; } $con = mysql_connect("localhost","username","password"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("test", $con); mysql_query("INSERT INTO subs (name, address, postcode, prod_id) VALUES ('$fname', '$add', '$pc', '$email')"); mysql_close($con); } } return true; } }

Please Log in or Create an account to join the conversation.

More
14 years 11 months ago - 14 years 11 months ago #26900 by spoOx
Replied by spoOx on topic Re: Run a query after order is confirmed..
I don't know why code isn't showing above. I have attached the php file.

This attachment is hidden for guests.
Please log in or register to see it.

Last edit: 14 years 11 months ago by spoOx.

Please Log in or Create an account to join the conversation.

More
14 years 11 months ago #26904 by nicolas
Replied by nicolas on topic Re: Run a query after order is confirmed..
Your code seems ok.

You should activate the display of errors in your php.ini in order to see the error message instead of a blank page.

Please Log in or Create an account to join the conversation.

More
14 years 11 months ago #26905 by spoOx
Replied by spoOx on topic Re: Run a query after order is confirmed..
Hi Nicolas,

sorry for the misunderstanding. There's no blank page. The code runs fine and inserts all details in to DB except the email address. Maybe I am doing something wrong in retreiving the email address? It is not being retrieved?

Also, how can I make it so that the query only works if the order contains a product with the the product id 1, 5 or 7 ? Something like: if (order_item_id == 1 or 5 or 7) then ...


Many Thanks

Please Log in or Create an account to join the conversation.

More
14 years 11 months ago #26909 by nicolas
Replied by nicolas on topic Re: Run a query after order is confirmed..
You have the code:
$orderClass->customer = $userClass->get($orderClass->order_user_id);

$email = $orderClass->user_email;

where it should be:
$orderClass->customer = $userClass->get($orderClass->order_user_id);

$email = $orderClass->customer->user_email;

Please Log in or Create an account to join the conversation.

More
14 years 11 months ago #26910 by nicolas
Replied by nicolas on topic Re: Run a query after order is confirmed..
For the products, you should loop on the variable $orderClass->products and see which products are there or not...
$ok = true;
foreach($orderClass->products as $product){
if($product->prtoduct_id == 1) $ok = false;
}
if($ok){
//do something
}else{
//do something else
}

Please Log in or Create an account to join the conversation.

More
14 years 11 months ago #27088 by spoOx
Replied by spoOx on topic Re: Run a query after order is confirmed..
Hi Nicolas,

many thanks for your replies. they have been extremely useful and I am almost done. However, 1 last problem: If the product I select has quantity of say 2 or 3, I run a for loop to add the field twice or three times in the database, however, if there is another item in the order which I don't want to select but that has a quantity of say 5 then the order is inserted in to db 5 times. For example:

Product 1 - ID = 1 - Quantity = 2 - I want to insert something in to the database 2 times but in the same order I have:
Product 2 - ID = 2 - Quantity = 5 - This is not one of the IDs I want to insert in to DB so it should only insert the record twice in DB but it does it 5 times for some reason :(

Below is the selected code:

foreach($orderClass->products as $product){
if($product->product_id == 1 || $product->product_id == 18 || $product->product_id == 42 || $product->product_id == 46 || $product->product_id == 22 || $product->product_id == 23 || $product->product_id == 43 || $product->product_id == 47) $ok = true;
}
if($ok){

$qty = $product->order_product_quantity;
$notes .= " Quantity: ";
$notes .= "$qty";

for ($i=1; $i<=$qty; $i++)
{
Run SQL Query here
.....
}

Please Log in or Create an account to join the conversation.

More
14 years 11 months ago - 14 years 11 months ago #27090 by spoOx
Replied by spoOx on topic Re: Run a query after order is confirmed..
I guess it looks as if it goes through the whole order and picks out the last product in the order and gets its quantity.

Is it possible for it to loop through the order one product at a time and if the product_id matches one of the ID's then get that products quantity and run the query?
Last edit: 14 years 11 months ago by spoOx.

Please Log in or Create an account to join the conversation.

More
14 years 11 months ago #27092 by nicolas
Replied by nicolas on topic Re: Run a query after order is confirmed..
You should put your whole query code in the foreach then:



foreach($orderClass->products as $product){
$ok = false; //Product ID check
if($product->product_id == 1 || $product->product_id == 18 || $product->product_id == 42 || $product->product_id == 46 || $product->product_id == 22 || $product->product_id == 23 || $product->product_id == 43 || $product->product_id == 47) $ok = true;

if($ok){

$qty = $product->order_product_quantity;
$notes .= " Quantity: ";
$notes .= "$qty";

$PID = $product->product_id;
$notes .= " PID: ";
$notes .= "$PID";

for ($i=1; $i<=$qty; $i++)
{

$con = mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_set_charset('utf8',$con);

mysql_select_db("test", $con);

mysql_query("INSERT INTO contacts2 (`First Name`, `Last Name`, `E-mail Address`, `Home Phone`, Address, City, `State/Province`, `Post Code`, `Country/Region`, `Date Subscribed`, `Subscription End Date`, `Notes`)
VALUES ('$fname', '$lname', '$email', '$tel', '$add', '$city', '$state', '$pcode', '$country', NOW(), DATE_ADD(NOW(), INTERVAL 1 YEAR), '$notes')");

mysql_close($con);

} // end of for loop

} // end of if OK


}

Please Log in or Create an account to join the conversation.

More
14 years 11 months ago #27093 by spoOx
Replied by spoOx on topic Re: Run a query after order is confirmed..
Hi Nicolas,

Thanks for the prompt reply but now it runs 7 times.. so 2 times for Product ID 1 and then 5 times for Product ID 2. It should only run for Product ID 1 as Product ID 2 is not included in:

if($product->product_id == 1 || $product->product_id == 18 || $product->product_id == 42 || $product->product_id == 46 || $product->product_id == 22 || $product->product_id == 23 || $product->product_id == 43 || $product->product_id == 47)

Please Log in or Create an account to join the conversation.

More
14 years 11 months ago - 14 years 11 months ago #27094 by spoOx
Replied by spoOx on topic Re: Run a query after order is confirmed..
think i've fixed it by running another if statement below:

if($PID == 1 || $PID == 18 || $PID == 42 || $PID == 46 || $PID == 22 || $PID == 23 || $PID == 43 || $PID == 47 || $PID == 3)

{
run the i loop etc
}

but is this efficient? why would it still run for product ID's not specified in the first if ?
Last edit: 14 years 11 months ago by spoOx.

Please Log in or Create an account to join the conversation.

More
14 years 11 months ago #27095 by nicolas
Replied by nicolas on topic Re: Run a query after order is confirmed..
If you don't want it to run several times for the same product, then you should remove your for ($i=1; $i<=$qty; $i++) so that the query is only run once per product.

Please Log in or Create an account to join the conversation.

Time to create page: 0.274 seconds
Powered by Kunena Forum