dcox
Posts: 29
|
| Posted: 07/13/2007, 11:13 AM |
|
I have been stuck for three looong days trying email the results from the shopping cart (After Execute Insert). My lasted attempt is to call a page with the user's last order record and email from there. (Only way I could come up with).
I need help with the syntax in the MySql statement (entered via SQL in the Visual Query Builder). I need to display ONLY the last inserted order_id and it's matching order items. It is successully limiting the list to the current user but not their last order.
receipt.php...
"SELECT users.user_id, users.first_name, users.last_name, store_orders.order_id, store_orders.order_date, store_orders.total, store_order_items.quantity, store_order_items.price, store_order_items.order_item_id, store_products.product_name, store_statuses.status_name, store_products.price*quantity AS itemprice
FROM users INNER JOIN (store_products INNER JOIN ((store_statuses INNER JOIN store_orders ON store_statuses.status_id = store_orders.order_status_id) INNER JOIN store_order_items ON store_orders.order_id = store_order_items.order_id) ON store_products.product_id = store_order_items.product_id) ON users.user_id = store_orders.user_id
Where (users.user_id={CCGetSession})
ORDER By store_orders.order_id DESC, store_order_items.order_item_id"
Please help before I go bald pulling my hair out!!!
_________________
Toshiba Satellite X205 laptop, Intel(R) Core(TM)2 Duo CPU T7300 @ 2GHz, 4gb ram, Windows Vista Ultimate 32-bit, Dual 200gb 7200rpm hard drives (no raid), NVidia GeForce 8700M GT
|
 |
|
matheus
Posts: 386
|
| Posted: 07/13/2007, 12:23 PM |
|
maybe limit 1?
_________________
Matheus Trevizan
Dynamix Software Ltda.
Blumenau SC Brasil
www.dynamix.com.br |
|
|
|
DonB
|
| Posted: 07/13/2007, 3:00 PM |
|
You need to change the WHERE to:
WHERE store_orders.order_id = ( SELECT MAX(order_id) FROM store_orders WHERE
user_id ={CCGetSession) )
to select just the last order they entered. Using LIMIT 1 does work, BUT it
queries for ALL matching rows and merely returns a single one from the
result set. It can be quite inefficient if there are many rows.
--
DonB
http://www.gotodon.com/ccbth
"dcox" <dcox@forum.codecharge> wrote in message
news:54697c0b45a525@news.codecharge.com...
> I have been stuck for three looong days trying email the results from the
> shopping cart (After Execute Insert). My lasted attempt is to call a page
with
> the user's last order record and email from there. (Only way I could come
up
> with).
>
> I need help with the syntax in the MySql statement (entered via SQL in the
> Visual Query Builder). I need to display ONLY the last inserted order_id
and
> it's matching order items. It is successully limiting the list to the
current
> user but not their last order.
>
>
> receipt.php...
> "SELECT users.user_id, users.first_name, users.last_name,
> store_orders.order_id, store_orders.order_date, store_orders.total,
> store_order_items.quantity, store_order_items.price,
> store_order_items.order_item_id, store_products.product_name,
> store_statuses.status_name, store_products.price*quantity AS itemprice
> FROM users INNER JOIN (store_products INNER JOIN ((store_statuses INNER
JOIN
> store_orders ON store_statuses.status_id = store_orders.order_status_id)
INNER
> JOIN store_order_items ON store_orders.order_id =
store_order_items.order_id) ON
> store_products.product_id = store_order_items.product_id) ON users.user_id
=
> store_orders.user_id
> Where (users.user_id={CCGetSession})
> ORDER By store_orders.order_id DESC, store_order_items.order_item_id"
>
> Please help before I go bald pulling my hair out!!!
> _________________
> Thanks for your help!
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
drcox
Posts: 6
|
| Posted: 07/13/2007, 10:15 PM |
|
You Da Man, Don!!! 
It works great!!! For anyone else coming across this problem there was a tiny typo in Don's message. The correction is below...
WHERE store_orders.order_id = (SELECT MAX(order_id) FROM store_orders WHERE user_id ={CCGetSession})
THANK YOU, DON!!!!!!!
|
|
|
|
Gena
Posts: 591
|
| Posted: 07/14/2007, 4:38 AM |
|
Quote drcox:
You Da Man, Don!!!
It works great!!! For anyone else coming across this problem there was a tiny typo in Don's message. The correction is below...
WHERE store_orders.order_id = (SELECT MAX(order_id) FROM store_orders WHERE user_id ={CCGetSession})
THANK YOU, DON!!!!!!!
I don't think that it is good idea to use MAX(order_id) in multiuser environment - you can just get order_id from the OTHER user that was added it a few seconds after.
To get last inserted value I am using this code
$db = new clsDBxxxxDB();
$SQL = "INSERT INTO store_orders " .
[skip]
$db->query($SQL);
$SQL = "SELECT LAST_INSERT_ID() FROM store_orders";
$db->query($SQL);
if ($db->next_record()) {
$last_order_id = $db->f(0);
}
_________________
Gena |
|
|
|
datadoit.com
|
| Posted: 07/14/2007, 5:57 AM |
|
Look at getting the value for mysql_insert_id() in your
AfterExecuteInsert event, as long as the PK is single column, auto
incremented.
|
|
|
|
Damian Hupfeld
|
| Posted: 07/15/2007, 5:47 AM |
|
Gena,
No issue with this code because it is MAX(order_id).... where
user_id={CCGetSession} limiting it to results for that user only...
Your method also works.
Damian
"Gena" <Gena@forum.codecharge> wrote in message
news:54698b5aebca0b@news.codecharge.com...
> Quote drcox:
> You Da Man, Don!!!
>
> It works great!!! For anyone else coming across this problem there was a
> tiny
> typo in Don's message. The correction is below...
>
> WHERE store_orders.order_id = (SELECT MAX(order_id) FROM store_orders
> WHERE
> user_id ={CCGetSession})
>
>
> THANK YOU, DON!!!!!!!
>
>
> I don't think that it is good idea to use MAX(order_id) in multiuser
> environment - you can just get order_id from the OTHER user that was added
> it a
> few seconds after.
>
> To get last inserted value I am using this code
>
>
> $db = new clsDBxxxxDB();
> $SQL = "INSERT INTO store_orders " .
> [skip]
> $db->query($SQL);
>
> $SQL = "SELECT LAST_INSERT_ID() FROM store_orders";
> $db->query($SQL);
> if ($db->next_record()) {
> $last_order_id = $db->f(0);
> }
>
> _________________
> Gena
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
Wkempees
|
| Posted: 07/15/2007, 6:44 AM |
|
@D
you r still a mindreader
|
|
|
|
drcox
Posts: 6
|
| Posted: 07/15/2007, 12:38 PM |
|
Thanks to all who have helped thus far! I am almost there but need a little more help. Everything is going as planned except that I placed my mail statement in the before show event of the page. Now, everytime the page is refreshed the email is sent again. Is there an event to place it in which the mail will only be sent once BUT no action is required from the user (it will send automatically at some point after the page is called)?
Thanks!
|
|
|
|
Gena
Posts: 591
|
| Posted: 07/16/2007, 9:03 AM |
|
Quote Damian Hupfeld:
No issue with this code because it is MAX(order_id).... where
user_id={CCGetSession} limiting it to results for that user only...
you are right
_________________
Gena |
|
|
|
datadoit.com
|
| Posted: 07/16/2007, 10:46 AM |
|
How about putting the mail send function in a page's BeforeShow event,
but that page doesn't do or show anything else, then redirect to the
page you want to go to from there - could even be the page you came from.
I like to even send the user to a page that has some sort of graphic,
such as a sending mail progress bar or animation, then redirect to a
page that does the mail send magic, then when all done send back to the
original calling page or some other page. Makes it a little more
intuitive, plus makes managing the send mail function easier (can be
called from many different places using parameters, variables, table
data, etc.).
To manage whether or not a mail message has already been sent, and to
avoid duplicate sending, try setting a session variable such as
MailSent=Yes and check for that prior to sending the message (or
redirecting to the mail send page).
|
|
|
|
drcox
Posts: 6
|
| Posted: 07/16/2007, 6:28 PM |
|
I am having trouble displaying the query results in the email message. I am able to get the correct email address using DLookup but need the get the items from the query for the message. I have never done this before and need someone to look at the code below. Am I going about it all wrong or is it just a syntax error? Thanks for your help!... PHP 4or5? MySql 5
$query = sprintf("SELECT users.user_id, users.first_name AS firstname, users.last_name AS lastname, users.email AS email, store_orders.order_id, store_orders.order_date, store_orders.total, store_order_items.quantity, store_order_items.price, store_order_items.order_item_id, store_products.product_name, store_statuses.status_name, store_products.price*quantity AS itemprice "
."FROM users INNER JOIN (store_products INNER JOIN ((store_statuses INNER JOIN store_orders ON store_statuses.status_id = store_orders.order_status_id) INNER JOIN store_order_items ON store_orders.order_id = store_order_items.order_id) ON store_products.product_id = store_order_items.product_id) ON users.user_id = store_orders.user_id "
."WHERE store_orders.order_id = (SELECT MAX(order_id) FROM store_orders WHERE user_id ={CCGetSession}) "
."ORDER By store_orders.order_id DESC, store_order_items.order_item_id",
mysql_real_escape_string($email),
mysql_real_escape_string($firstname),
mysql_real_escape_string($lastname));
// Perform Query
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
$to = $row['email'];
$subject = "test";
$message = print_r($attr); //no error but blank message field
$from = "me@me.com";
$headers = "From: $from\nReply-To: $from\nContent-Type: text/html";
mail ($to, $subject, $message, $headers);
}
mysql_free_result($result);
-----------------------------------------
The code above produces the following errors:
mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource
mysql_free_result(): supplied argument is not a valid MySQL result resource
|
|
|
|
datadoit.com
|
| Posted: 07/17/2007, 4:40 PM |
|
Let CodeCharge do the dirty work! That's why we use it.
Build a grid or form on your page using the SQL you've already written
as the data source. Then, in either the grid's BeforeShowRow event or
in the form's BeforeShow event send your email message with the info.
Doing it this way will also allow you to display the results on the page
to make certain you're getting the data you expect.
As suggested earlier, when you're done, redirect to another page (or the
calling page).
|
|
|
|
|
 |
|
|
|
