Avec Posted January 1, 2008 Share Posted January 1, 2008 Can anyone help with the correct SQL statement to sort the report by date (most recent at top) of the customer list found on admin/customer.php? It should be an easy join between customers and customers_info, but I cannot get it to work. Link to comment Share on other sites More sharing options...
♥geoffreywalton Posted January 1, 2008 Share Posted January 1, 2008 Please post the sql you are trying to use Need help installing add ons/contributions, cleaning a hacked site or a bespoke development, check my profile Virus Threat Scanner My Contributions Basic install answers. Click here for Contributions / Add Ons. UK your site. Site Move. Basic design info. For links mentioned in old answers that are no longer here follow this link Useful Threads. If this post was useful, click the Like This button over there ======>>>>>. Link to comment Share on other sites More sharing options...
Avec Posted January 1, 2008 Author Share Posted January 1, 2008 I believe this is the select query for the customer list $customers_query_raw = "select c.customers_id, c.customers_lastname, c.customers_firstname, c.customers_email_address, a.entry_country_id, c.customers_default_address_id from " . TABLE_CUSTOMERS . " c left join " . TABLE_ADDRESS_BOOK . " a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id " . TABLES_CUSTOMERS . "LEFT JOIN" . TABLES_CUSTOMERS_INFO . "on customers.customers_id = customers_info.customers_info_id" . $search . " order by c.customers_lastname, c.customers_firstname"; Link to comment Share on other sites More sharing options...
♥geoffreywalton Posted January 1, 2008 Share Posted January 1, 2008 Without testing it there are few things that leap out add ci after the cust info table name, add the field you want to order in the field list and change the order by, change left join conditions. Please check ci.customers_info_id holds a customer no. Didn't understand why $search id is there, does it hold asc or desc? might need a space in ci.customers_info_id[/b]" $customers_query_raw = "select c.customers_id, c.customers_lastname, c.customers_firstname, c.customers_email_address, a.entry_country_id, c.customers_default_address_id, ci.datefield from " . TABLE_CUSTOMERS . " c left join " . TABLE_ADDRESS_BOOK . " a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id " . TABLES_CUSTOMERS . " ci LEFT JOIN" . TABLES_CUSTOMERS_INFO . "on c.customers_id = ci.customers_info_id " . $search . " order by ci.datefield "; Remove html bold tags in the above if necessary. hope that moves you on. Need help installing add ons/contributions, cleaning a hacked site or a bespoke development, check my profile Virus Threat Scanner My Contributions Basic install answers. Click here for Contributions / Add Ons. UK your site. Site Move. Basic design info. For links mentioned in old answers that are no longer here follow this link Useful Threads. If this post was useful, click the Like This button over there ======>>>>>. Link to comment Share on other sites More sharing options...
Avec Posted January 1, 2008 Author Share Posted January 1, 2008 This is the code as entered. I changed 'datefield' to the correct customers_info_date_account_created. $customers_query_raw = "select c.customers_id, c.customers_lastname,c.customers_firstname, c.customers_email_address, a.entry_country_id, c.customers_default_address_id, ci.customers_info_date_account_created from " . TABLE_CUSTOMERS . " c left join " . TABLE_ADDRESS_BOOK . " a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id " . TABLES_CUSTOMERS . " c LEFT JOIN" . TABLES_CUSTOMERS_INFO . "ci on c.customers_id = ci.customers_info_id " . $search . " order by ci.customers_info_date_account_created ";$customers_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $customers_query_raw, $customers_query_numrows); This is the error message I received. 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLES_CUSTOMERS c LEFT JOINTABLES_CUSTOMERS_INFOci on c.customers_id = ci.custo' at line 1 select count(*) as total from customers c left join address_book a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id TABLES_CUSTOMERS c LEFT JOINTABLES_CUSTOMERS_INFOci on c.customers_id = ci.customers_info_id Link to comment Share on other sites More sharing options...
edschaum Posted January 1, 2008 Share Posted January 1, 2008 This is the code as entered. I changed 'datefield' to the correct customers_info_date_account_created. $customers_query_raw = "select c.customers_id, c.customers_lastname,c.customers_firstname, c.customers_email_address, a.entry_country_id, c.customers_default_address_id, ci.customers_info_date_account_created from " . TABLE_CUSTOMERS . " c left join " . TABLE_ADDRESS_BOOK . " a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id " . TABLES_CUSTOMERS . " c LEFT JOIN" . TABLES_CUSTOMERS_INFO . "ci on c.customers_id = ci.customers_info_id " . $search . " order by ci.customers_info_date_account_created ";$customers_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $customers_query_raw, $customers_query_numrows); This is the error message I received. You might need to add a space in here: "ci on c.customers_id to make it: " ci on c.customers_id Not sure about the rest of it though. Ed Link to comment Share on other sites More sharing options...
Avec Posted January 1, 2008 Author Share Posted January 1, 2008 Same error after adding the space. I removed the 'order' statement and the error is still the same. Somehow the join is not correct. Also, customer_id and customer_info_id are the correct fields between these two tables. Link to comment Share on other sites More sharing options...
♥geoffreywalton Posted January 1, 2008 Share Posted January 1, 2008 like I said without testing it.... This works in phpmydmin sql window select c.customers_id, c.customers_lastname, c.customers_firstname, c.customers_email_address, a.entry_country_id, c.customers_default_address_id, customers_info_date_account_created from customers c left join address_book a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id LEFT JOIN customers_info ci on c.customers_id = ci.customers_info_id order by ci.customers_info_date_account_created Need help installing add ons/contributions, cleaning a hacked site or a bespoke development, check my profile Virus Threat Scanner My Contributions Basic install answers. Click here for Contributions / Add Ons. UK your site. Site Move. Basic design info. For links mentioned in old answers that are no longer here follow this link Useful Threads. If this post was useful, click the Like This button over there ======>>>>>. Link to comment Share on other sites More sharing options...
edschaum Posted January 1, 2008 Share Posted January 1, 2008 You might want to have a look at this contribution: http://addons.oscommerce.com/info/223 Ed Link to comment Share on other sites More sharing options...
Avec Posted January 1, 2008 Author Share Posted January 1, 2008 You might want to have a look at this contribution: Boom! that was it. I did a google search and did not find this. Thank you very much Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.