chrometweaks.org

I purchased a domain name through iPage will it show up in google searches?

Click Here To View All Answers...


Question I have... I purchased a domain name through iPage will it show up in google searches? Thanks for any answer. 2nd question I got is.. I'm trying to create a query that will select everything I need to know about my orders so I can create a CSV file and work with them. I'm running into a snag with the orders_total table. If an order has sales tax attached to it, I need to know how much the sales tax was.

So I have something like this:.

Select.

<long list of columns>.

From.

Orders as o,.

Orders_products as op,.

Orders_total as ot,.

Orders_total as ott,.

Orders_total as si,.

Orders_total as st.

WHERE.

O.orders_id=op.orders_id.

AND.

O.orders_id=ot.orders_id.

AND.

Ott.orders_id = ot.orders_id.

AND.

Si.orders_id=ott.orders_id.

AND.

Ot.class = 'ot_shipping'.

AND.

Ott.class = 'ot_total'.

AND.

Si.class='ot_insurance'.

AND.

St.class ='ot_tax'.

AND.

St.orders_id = ot.orders_id;.

This returns only 3 of my hundreds of orders - the ones that have sales tax. Removing the references to sales tax returns all orders, but without the sales tax information (obviously). A good friend of mine who does Oracle for a living suggested that I could use an "outer join," which is designated by a "(+)." Apparently in Oracle syntax, that means to make this particular value null and still return the row if it does not exist. I found this page on the mySQL site:.

Http://dev.mysql.com/doc/maxdb/en/8c/ccce2...261/content.htm.

That page backs up my friend's suggestion. So I tried this:.

St.class ='ot_tax'(+).

And it caused a syntax error. This:.

St.class ='ot_tax(+)'.

Eliminates the syntax error, but returns 0 rows..

Edit - looking at that makes it obvious - sql is looking for the class 'ot_tax(+)' which obviously does not exist. /edit.

I've also tried various ways of putting a (+) on this line:.

St.orders_id = ot.orders_id.

But I can't seem to put it anywhere without causing a syntax error..

Any SQL masters out there have a suggestion?.

This post has been edited by.

BlueNoteMKVI.

: 17 April 2004, 17:50..

Comments (108)

Good question... I dunno what is the right answer. I'll do some poking around and get back to you if I discover an answer. You should email the people at iPage as they probably know..

Comment #1

Hrm....that returns this error:.

I tried both *= and =* - no luck..

This post has been edited by.

BlueNoteMKVI.

: 17 April 2004, 19:24..

Comment #2

Hi Chris,.

I was mixing sybase with mysql etc.....

I had this snippet saved.

So you might want to try replacing.

O.orders_id=op.orders_id.

With.

LEFT JOIN o ON o.orders_id=op.orders_id.

Or.

LEFT JOIN orders ON o.orders_id=op.orders_id.

I'm not sure which one will work..

HTH.

Tom.

This post has been edited by.

OceanRanch.

: 17 April 2004, 20:09..

Comment #3

I've been playing with that and can't get it to work..

I understand the idea of a left join, I think - but can I specify two conditions for it? There should only be a match if the order_id is right and the ot_class is right - and I'm trying to pull three different classes here. Here's the full query:.

"SELECT o.orders_id as orders_id, o.date_purchased as date_purchased, o.customers_name as customers_name, o.customers_telephone as customers_telephone, o.customers_email_address as customers_email_address, o.billing_name as billing_name, o.billing_street_address as billing_street_address, o.billing_city as billing_city, o.billing_postcode as billing_postcode, o.billing_state as billing_state, o.billing_country as billing_country, o.delivery_name as delivery_name, o.delivery_street_address as delivery_street_address, o.delivery_city as delivery_city, o.delivery_postcode as delivery_postcode, o.delivery_state as delivery_state, o.delivery_country as delivery_country, op.products_model as items_ordered, op.products_quantity as quantity_ordered, ot.title as shipping_method, ot.value as shipping_cost, si.value as shipping_insurance, st.value as sales_tax, ott.value as order_total, o.cc_type as cc_type, o.cc_owner as cc_owner, o.cc_number as cc_number, o.cc_expires as cc_expires, o.cvvnumber as cc_cvv FROM ".TABLE_ORDERS." as o, ".TABLE_ORDERS_PRODUCTS." as op, ".TABLE_ORDERS_TOTAL." as ot, ".TABLE_ORDERS_TOTAL." as ott, ".TABLE_ORDERS_TOTAL." as si, ".TABLE_ORDERS_TOTAL." as st WHERE o.orders_id=op.orders_id AND o.orders_id=ot.orders_id AND ott.orders_id = ot.orders_id AND ot.class = 'ot_shipping' AND ott.class = 'ot_total' AND ot.orders_id = si.orders_id AND ot.orders_id=st.orders_id AND si.class='ot_shipping_insurance' AND st.class='ot_sales_tax';";.

Where does the join belong?..

Comment #4

Hi Chris,.

I checked the following query using LEFT JOINS on osc orders and orders_total.

Table and it returned ALL of the orders and NULLed out those did not have.

A matching ot_shipping class..

SELECT orders.orders_id AS orders_id,.

Orders.date_purchased AS date_purchased,.

Orders_total.value AS shipping_total.

From orders.

LEFT JOIN orders_total ON orders.orders_id = orders_total.orders_id and orders_total.class = 'ot_shipping'.

I haven't tried to do this against another instance of orders_total but it.

Should work. I'll be messing with this in my spare time (right!!) but for.

Now at least you see one of the ways the JOIN needs to be to run properly..

I follow up with you as we proceed, got to go for now....

HTH.

Tom..

Comment #5

I also need to pull information from orders_products in this query -.

Where orders.orders_id=orders_products.orders_id.

Should the "where" clause go before or after the "left join" clause, or does it matter?..

Comment #6

The "where" clause comes after the "table" clause (which can contain the JOIN clauses)..

However, if you are going to include product information in this query you'll end up with many rows of order/order_total information. You might want to consider separating the queries and get all the order/order_total fields in a single row then loop through that using the order_id to query the products and many associated products tables..

Keep it simple and work the query incrementally until you get it correct. If you try to do the entire report or whatever in one fell swoop, I have found more often than not it takes more time and debugging headaches..

HTH.

Tom..

Comment #7

You got your syntax all wrong for mysql outer joins..

Too much talk. Not enough sql-ing..

Trial and error is going to get you nowhere...

Comment #8

Thanks to all who helped on this project, especially project_aism who had the winning answer!.

I didn't want to just.

Select *.

Because there's some info in those tables that's not necessary. Here's the final query I came up with:.

$filelayout_sql = "SELECT.

 o.orders_id as orders_id,.

 os.orders_status_name as orders_status,.

 o.date_purchased as date_purchased,.

 o.customers_name as customers_name,.

 o.customers_telephone as customers_telephone,.

 o.customers_email_address as customers_email_address,.

 o.billing_name as billing_name,.

 o.billing_street_address as billing_street_address,.

 o.billing_city as billing_city,.

 o.billing_postcode as billing_postcode,.

 o.billing_state as billing_state,.

 o.billing_country as billing_country,.

 o.delivery_name as delivery_name,.

 o.delivery_street_address as delivery_street_address,.

 o.delivery_city as delivery_city,.

 o.delivery_postcode as delivery_postcode,.

 o.delivery_state as delivery_state,.

 o.delivery_country as delivery_country,.

 op.products_model as items_ordered,.

 op.products_quantity as quantity_ordered,.

 ot.title as shipping_method,.

 ot.value as shipping_cost,.

 si.value as shipping_insurance,.

 o.usps_track_num as usps_track_num,.

 o.ups_track_num as ups_track_num,.

 o.fedex_track_num as fedex_track_num,.

 st.value as sales_tax,.

 ott.value as order_total,.

 o.cc_type as cc_type,.

 o.cc_owner as cc_owner,.

 o.cc_number as cc_number,.

 o.cc_expires as cc_expires,.

 o.cvvnumber as cc_cvv.

 FROM.

 ".TABLE_ORDERS." as o,.

 ".TABLE_ORDERS_PRODUCTS." as op,.

 ".TABLE_ORDERS_TOTAL." as ot,.

 ".TABLE_ORDERS_TOTAL." as ott,.

 ".TABLE_ORDERS_TOTAL." as si,.

 ".TABLE_ORDERS_STATUS." as os.

 left outer join ".TABLE_ORDERS_TOTAL." st on st.class ='ot_tax' AND st.orders_id = ot.orders_id.

 WHERE.

 o.orders_status= "$orders_status".

 AND.

 o.orders_id=op.orders_id.

AND o.orders_id=ot.orders_id.

AND ott.orders_id = ot.orders_id.

AND si.orders_id=ott.orders_id.

AND ot.class = 'ot_shipping'.

AND ott.class = 'ot_total'.

AND si.class='ot_insurance'.

AND os.orders_status_id=o.orders_status.

AND os.language_id=1;";.

$orders_status is set earlier in the script as $HTTP_GET_VARS['orders_status']. To select all orders, I ran the same query without that particular line..

So...this pulls all of the address (customer's default, billing and shipping) information, all of the products ordered, shipping charges, shipping insurance charges, sales tax if it was charged, the name of the orders_status, all tracking numbers for the shipment...that's a whole lot of data. Next task is importing the edited data back into the database...whee!..

Comment #9

To project_asiam,.

From the Mysql manual "The {OJ ... LEFT OUTER JOIN ...} syntax shown in the preceding list exists only for compatibility with ODBC. ".

SELECT orders.orders_id AS orders_id,.

Orders.date_purchased AS date_purchased,.

Orders_total.value AS shipping_total.

From orders.

LEFT JOIN orders_total ON orders.orders_id = orders_total.orders_id and orders_total.class = 'ot_shipping'.

My query is identical to your's. No big deal but your comment about too much talk and not enough sqling is off base..

Glad to hear that you came in the thread with the winning entry. Not a great way for me to start off my week on helping folks knowing I'll be second guessed and critiqued..

Tom.

This post has been edited by.

OceanRanch.

: 19 April 2004, 16:44..

Comment #10

I'm trying to create a query that will select everything I need to know about my orders so I can create a CSV file and work with them. I'm running into a snag with the orders_total table. If an order has sales tax attached to it, I need to know how much the sales tax was. However, if an order did not have sales tax, I still need the rest of the information about the order. So I have something like this:.

Select.

<long list of columns>.

From.

Orders as o,.

Orders_products as op,.

Orders_total as ot,.

Orders_total as ott,.

Orders_total as si,.

Orders_total as st.

WHERE.

O.orders_id=op.orders_id.

AND.

O.orders_id=ot.orders_id.

AND.

Ott.orders_id = ot.orders_id.

AND.

Si.orders_id=ott.orders_id.

AND.

Ot.class = 'ot_shipping'.

AND.

Ott.class = 'ot_total'.

AND.

Si.class='ot_insurance'.

AND.

St.class ='ot_tax'.

AND.

St.orders_id = ot.orders_id;.

This returns only 3 of my hundreds of orders - the ones that have sales tax. Removing the references to sales tax returns all orders, but without the sales tax information (obviously). A good friend of mine who does Oracle for a living suggested that I could use an "outer join," which is designated by a "(+)." Apparently in Oracle syntax, that means to make this particular value null and still return the row if it does not exist. I found this page on the mySQL site:.

Http://dev.mysql.com/doc/maxdb/en/8c/ccce2...261/content.htm.

That page backs up my friend's suggestion. So I tried this:.

St.class ='ot_tax'(+).

And it caused a syntax error. This:.

St.class ='ot_tax(+)'.

Eliminates the syntax error, but returns 0 rows..

Edit - looking at that makes it obvious - sql is looking for the class 'ot_tax(+)' which obviously does not exist. /edit.

I've also tried various ways of putting a (+) on this line:.

St.orders_id = ot.orders_id.

But I can't seem to put it anywhere without causing a syntax error..

Any SQL masters out there have a suggestion?.

This post has been edited by.

BlueNoteMKVI.

: 17 April 2004, 17:50..

Comment #11

Try replacing.

O.orders_id=ot.orders_id.

With.

O.orders_id *= ot.orders_id.

Inner/outter joins are setup with the *= or =* contruct..

HTH.

Tom..

Comment #12


This question was taken from a support group/message board and re-posted here so others can learn from it.