Guest Posted March 4, 2007 Share Posted March 4, 2007 Modifications to 2.1a - much improved Shows all customers Save CSV does not show warning messages CSV file is properly named CSV file copes with commas in fields - Downloaded file has correct MIME type text/csv Removed two blank lines from start of CSV file Added header row to CSV file I have just installed All Customers Report 2.1a and have had to make some changes to get it up and running. In case these are useful to others I have detailed them here: Additionally I shall attempt to post my changes as a file on http://www.oscommerce.com/community/contributions,3045 1) I noticed that not all the customers were appearing in the report (only 6 out of 31). To solve this I changed the definition of $customers_query_raw in admin\all_customers.php (around line 117) to use a LEFT JOIN between TABLE_CUSTOMERS and TABLE_ZONES instead of a JOIN. Original: $customers_query_raw = "SELECT c.customers_id , c.customers_default_address_id, c.customers_email_address, c.customers_fax, c.customers_telephone, a.entry_company, a.address_book_id, a.customers_id, a.entry_firstname, a.entry_lastname, a.entry_street_address, a.entry_suburb, a.entry_city, a.entry_state, a.entry_postcode, a.entry_country_id, a.entry_zone_id, z.zone_code, co.countries_name FROM " . TABLE_CUSTOMERS . " c JOIN " . TABLE_ZONES . " z ON a.entry_zone_id = z.zone_id JOIN " . TABLE_COUNTRIES . " co ON a.entry_country_id = co.countries_id LEFT JOIN " . TABLE_ADDRESS_BOOK . " a ON c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id ORDER BY $db_orderby $sorted"; $customers_query = tep_db_query($customers_query_raw); Mine: $customers_query_raw = "SELECT c.customers_id , c.customers_default_address_id, c.customers_email_address, c.customers_fax, c.customers_telephone, a.entry_company, a.address_book_id, a.customers_id, a.entry_firstname, a.entry_lastname, a.entry_street_address, a.entry_suburb, a.entry_city, a.entry_state, a.entry_postcode, a.entry_country_id, a.entry_zone_id, z.zone_code, co.countries_name FROM " . TABLE_CUSTOMERS . " c LEFT JOIN " . TABLE_ZONES . " z ON a.entry_zone_id = z.zone_id JOIN " . TABLE_COUNTRIES . " co ON a.entry_country_id = co.countries_id LEFT JOIN " . TABLE_ADDRESS_BOOK . " a ON c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id ORDER BY $db_orderby $sorted"; $customers_query = tep_db_query($customers_query_raw); 2) The Save CSV button resulted in a page of warnings about header information not being able to be modified. I solved this using the information provided by cdickson (http://www.oscommerce.com/forums/index.php?s=&showtopic=142447&view=findpost&p=702159) 3) The CSV file downloaded did not have a file extension, it was simply named 'Customer_list' It was evident in the code within admin/all_customers.php that the filename was supposed to include the current date and time (around line 178) and was to end in .csv (around line 22). However the filename is terminated after 'Customer_list' because of the prence of at least one new line character introduced by the PHP code contained within the hidden input tag. This was solved by placing all the code to generate the filename on one line and moving the PHP comment: Original: <td class="smallText" colspan="4"><form action="<?php echo $_SERVER['PHP_SELF']; ?>" method=post><input type='hidden' name='csv' value='<?php echo $csv_accum; ?>'><input type='hidden' name='saveas' value='Customer_list <?php //suggested file name for csv, include year and month echo date("Y" . "-" . "m" . "-" . "d" . "-" . "Hi"); ?>'><input type="submit" value="<?php echo TEXT_BUTTON_REPORT_SAVE;?>"></form> </td> Mine: <td class="smallText" colspan="4"><form action="<?php echo $_SERVER['PHP_SELF']; ?>" method=post> <input type='hidden' name='csv' value='<?php echo $csv_accum; ?>'> <?php //suggested file name for csv, include year and month ?> <input type='hidden' name='saveas' value='Customer_List_<?php echo date("Y" . "_" . "m" . "_" . "d" . "_" . "Hi"); ?>'><input type="submit" value="<?php echo TEXT_BUTTON_REPORT_SAVE;?>"></form> </td> 4) The CSV file does not properly cope with commas within fields, it throws them away to prevent misalignment of fields, however this makes it impossible to read the customer names as it is unclear where the surname ends and the forname(s) start. The CSV standard states that any field containing a comma should have double quotes around it (and also that fields containing double quotes should be surroud by double quotes and the original double quote should be repeated!) [http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm]. Basically the way the CSV file is currently put together breaks the CSV standard in several ways. It would be too complicated to look for fields containg characters which mean they must be surrounded by quotes, thankfully the standard allows you to surround all fields by double quotes and those that don't need to be there are ignored. Therefore I modified the mirror_out function detailed in the install.txt which is pasted into /admin/includes/functions/general.php to surround all fields by double quotes, not remove commas from fields, and replace any double quotes within a field with two sets of double quotes. Original: // Produce CSV string for output function mirror_out ($field) { global $csv_accum; echo $field; $field = strip_tags($field); $field = ereg_replace (",","",$field); if ($csv_accum=='') $csv_accum=$field; else {if (strrpos($csv_accum,chr(10)) == (strlen($csv_accum)-1)) $csv_accum .= $field; else $csv_accum .= "," . $field; }; return; }; Mine: // Produce CSV string for output function mirror_out ($field) { global $csv_accum; echo $field; $field = strip_tags($field); $field = ereg_replace ("\"","\"\"",$field); if ($csv_accum=='') $csv_accum= "\"" . $field . "\""; else {if (strrpos($csv_accum,chr(10)) == (strlen($csv_accum)-1)) $csv_accum .= "\"" . $field . "\""; else $csv_accum .= "," . "\"" . $field . "\""; }; return; }; 5) Item 4 threw up another problem in that the CSV file produced contained escaped double quotes rather than just double quotes. This was solved by modifying admin/all_customers.php where the CSV file is provided to the browser for download (around line 34) to put the text through the stripslashes function: Original: echo $csv_string; Mine: echo stripslashes($csv_string); 6) I also noticed the MIME type of the downloaded file was given in admin/all_customers.php (around line 32) as Application/octet-stream which is incorrect. The correct MIME type must be a text type rather than an application type and the best MIME type I could find was text/csv [http://www.shaftek.org/publications/drafts/mime-csv/draft-shafranovich-mime-csv-00.html] (the other option given is text/comma-separated-values) Original: header("Content-Type: Application/octet-stream"); Mine: header("Content-Type: text/csv"); 7) The resulting CSV file was almost correct now, however it contained two blank lines at the top of the file which appear in OpenOffice.org Calc/Excel. These two lines were caused by the call in admin/all_customers.php to the require function (around line 15). To solve this I moved the statement 'require('includes/application_top.php');' to below the block of code producing the CSV file, it ended up after the lines else echo "CSV string empty"; exit; }; and before ?> (i.e. around line 39 in the original file). In doing so I moved the call to ob_start(); to be the first statement in the block relating to the CSV output, i.e. // entry for bouncing csv string back as file // Turn on output buffering ob_start(); 8) The CSV file did not contain a header row with the column names. To solve this i modified the tep_sort_order function detailed in the install.txt which is pasted into /admin/includes/functions/general.php to add the headings to the CSV data as each heading is displayed on screen. Original: // Sort Function function tep_sort_order ($orderby, $sorted, $title, $order_criteria){ if (!isset($orderby) or ($orderby == $order_criteria and $sorted == "ASC")) $to_sort = "DESC"; else $to_sort = "ASC"; $link = '<a href="' . tep_href_link(FILENAME_ALL_CUSTOMERS, 'orderby=' . $order_criteria .'&sorted='. $to_sort) . '" class="headerLink">' . $title . '</a>'; return $link; } Mine: // Sort Function function tep_sort_order ($orderby, $sorted, $title, $order_criteria){ global $csv_accum; if (!isset($orderby) or ($orderby == $order_criteria and $sorted == "ASC")) $to_sort = "DESC"; else $to_sort = "ASC"; $field = strip_tags($title); $field = ereg_replace ("\"","\"\"",$field); if ($csv_accum=='') $csv_accum= "\"" . $field . "\""; else {if (strrpos($csv_accum,chr(10)) == (strlen($csv_accum)-1)) $csv_accum .= "\"" . $field . "\""; else $csv_accum .= "," . "\"" . $field . "\""; }; $link = '<a href="' . tep_href_link(FILENAME_ALL_CUSTOMERS, 'orderby=' . $order_criteria .'&sorted='. $to_sort) . '" class="headerLink">' . $title . '</a>'; return $link; } Has the problem with apostraphes (') in fields been solved yet? e.g. a name "O'Neil". Presumably when making up the string to go in the hidden text field each field could be put throw addslahes (since now everything should work out as I call stripslahes when outputting the file itself). If I get time I shall try this out/test it. In conclusion All Customers is a great add on for OsCommerce, just version 2.1a contains a number of bugs which limit its usability. Quote Link to comment Share on other sites More sharing options...
azer Posted March 5, 2007 Share Posted March 5, 2007 Has the problem with apostraphes (') in fields been solved yet? e.g. a name "O'Neil". Presumably when making up the string to go in the hidden text field each field could be put throw addslahes (since now everything should work out as I call stripslahes when outputting the file itself). If I get time I shall try this out/test it. thanks for all those 2.1B update and clear explanations , we are improving this customer list : 2 problem : - i got apostrophes in adresse and name and it make the csv be broken and finish to the first apostrophe :-( - in the cvs , the field name are in english, can't we use the language define for the field so that they appears in the admin language? Quote MS2 Link to comment Share on other sites More sharing options...
Optimalkiller Posted March 5, 2007 Share Posted March 5, 2007 Hi all I'm getting this error message when running all_customers.php can any kind soul help me out. 1054 - Unknown column 'a.entry_zone_id' in 'on clause' Thanks Optimal Quote Link to comment Share on other sites More sharing options...
Optimalkiller Posted March 11, 2007 Share Posted March 11, 2007 (edited) Problem now resolved. just do the following. This was fixed by member name:- jocg $customers_query_raw = "SELECT c.customers_id , c.customers_default_address_id, c.customers_email_address, c.customers_fax, c.customers_telephone, a.entry_company, a.address_book_id, a.customers_id, a.entry_firstname, a.entry_lastname, a.entry_street_address, a.entry_suburb, a.entry_city, a.entry_state, a.entry_postcode, a.entry_country_id, a.entry_zone_id, z.zone_code, co.countries_name FROM " . TABLE_CUSTOMERS . " c JOIN " . TABLE_ZONES . " z ON a.entry_zone_id = z.zone_id JOIN " . TABLE_COUNTRIES . " co ON a.entry_country_id = co.countries_id LEFT JOIN " . TABLE_ADDRESS_BOOK . " a ON c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id ORDER BY $db_orderby $sorted"; WITH: $customers_query_raw = "SELECT c.customers_id , c.customers_default_address_id, c.customers_email_address, c.customers_fax, c.customers_telephone, a.entry_company, a.address_book_id, a.customers_id, a.entry_firstname, a.entry_lastname, a.entry_street_address, a.entry_suburb, a.entry_city, a.entry_state, a.entry_postcode, a.entry_country_id, a.entry_zone_id, z.zone_code, co.countries_name FROM " . TABLE_CUSTOMERS . " c, ". TABLE_ZONES ." z, " . TABLE_ADDRESS_BOOK . " a, " . TABLE_COUNTRIES . " co WHERE a.entry_zone_id = z.zone_id AND a.entry_country_id = co.countries_id AND c.customers_id = a.customers_id AND c.customers_default_address_id = a.address_book_id ORDER BY $db_orderby $sorted"; Edited March 11, 2007 by Optimalkiller Quote Link to comment Share on other sites More sharing options...
Guest Posted March 11, 2007 Share Posted March 11, 2007 thanks for all those 2.1B update and clear explanations , we are improving this customer list : 2 problem : - i got apostrophes in adresse and name and it make the csv be broken and finish to the first apostrophe :-( - in the cvs , the field name are in english, can't we use the language define for the field so that they appears in the admin language? Thanks for the feedback on the apostrophes issue. I think I have this sorted now. In admin/all_customers.php you need to make the following changes: Line 32: echo stripslashes($csv_string); needs to be changed to echo html_entity_decode(stripslashes($csv_string), ENT_QUOTES); Line 179 <input type='hidden' name='csv' value='<?php echo $csv_accum; ?>'> needs to be changed to <input type='hidden' name='csv' value='<?php echo htmlentities($csv_accum, ENT_QUOTES); ?>'> The technical explanation behind this is that the apostrophe in the csv_accum variable was closing the value='' attribute in the above input tag. Therefore when the csv form field was submitted only the data up to the first apostrophe in the file was transmitted. To overcome this I have used the htmlentities function to convert apostrophes into their html code '. When the CSV is outputted I call html_entity_decode to reverse this and convert the ' codes back to apostrophes. (Actually htmlentities converts more than just the apostrophes but since html_entity_decode completely reverses the conversion this does not matter, we are only interested in getting the apostrophes sorted. As for the CSV headings always being in English: The headings written to the CSV are exactly the same as those displayed on screen in the report. It looks in the code as if the titles are hard coded. Perhaps the original devloper was hoping to make use of the language files but didn't get to implementing it in the report? //BOF HEADER ?> <tr class="dataTableHeadingRow"> <? /*<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_NUMBER; ?></td> */ ?> <td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, FULL_NAME, 'name');?></td> <td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, EMAIL, 'email');?></td> <td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, ADDRESS, 'address');?></td> <td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, CITY_NAME, 'city');?></td> <td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, STATE, 'state');?></td> <td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, POSTAL_CODE, 'pcode');?></td> <td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, CONTRY_NAME, 'country');?></td> <td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, TELEPHONE_NUMBER, 'telephone'); //EOF HEADER ?></td> </tr> Quote Link to comment Share on other sites More sharing options...
Guest Posted March 11, 2007 Share Posted March 11, 2007 thanks for all those 2.1B update and clear explanations , we are improving this customer list : 2 problem : - i got apostrophes in adresse and name and it make the csv be broken and finish to the first apostrophe :-( - in the cvs , the field name are in english, can't we use the language define for the field so that they appears in the admin language? Thanks for the feedback on the apostrophes issue. I think I have this sorted now. In admin/all_customers.php you need to make the following changes: Line 32: echo stripslashes($csv_string); needs to be changed to echo html_entity_decode(stripslashes($csv_string), ENT_QUOTES); Line 179 <input type='hidden' name='csv' value='<?php echo $csv_accum; ?>'> needs to be changed to <input type='hidden' name='csv' value='<?php echo htmlentities($csv_accum, ENT_QUOTES); ?>'> The technical explanation behind this is that the apostrophe in the csv_accum variable was closing the value='' attribute in the above input tag. Therefore when the csv form field was submitted only the data up to the first apostrophe in the file was transmitted. To overcome this I have used the htmlentities function to convert apostrophes into their html code '. When the CSV is outputted I call html_entity_decode to reverse this and convert the ' codes back to apostrophes. (Actually htmlentities converts more than just the apostrophes but since html_entity_decode completely reverses the conversion this does not matter, we are only interested in getting the apostrophes sorted. As for the CSV headings always being in English: The headings written to the CSV are exactly the same as those displayed on screen in the report. It looks in the code as if the titles are hard coded. Perhaps the original devloper was hoping to make use of the language files but didn't get to implementing it in the report? //BOF HEADER ?> <tr class="dataTableHeadingRow"> <? /*<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_NUMBER; ?></td> */ ?> <td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, FULL_NAME, 'name');?></td> <td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, EMAIL, 'email');?></td> <td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, ADDRESS, 'address');?></td> <td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, CITY_NAME, 'city');?></td> <td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, STATE, 'state');?></td> <td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, POSTAL_CODE, 'pcode');?></td> <td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, CONTRY_NAME, 'country');?></td> <td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, TELEPHONE_NUMBER, 'telephone'); //EOF HEADER ?></td> </tr> Quote Link to comment Share on other sites More sharing options...
Guest Posted March 11, 2007 Share Posted March 11, 2007 It looks in the code as if the titles are hard coded. Perhaps the original devloper was hoping to make use of the language files but didn't get to implementing it in the report? Actually looking more carefully at the source code the language file is used for the headings since the definition for ADDRESS is Street Address in the english language file and this is what gets displayed on screen and in the CSV. Perhaps the issue you are having is more to do with getting the correct lanague file to be used? Quote Link to comment Share on other sites More sharing options...
Guest Posted March 12, 2007 Share Posted March 12, 2007 New SQL Query For those of you that have had SQL errors appearing (in particular invalid column id/syntax error around the join statements) or have noticed that customers without zones are not displayed try this revised SQL query I have put together: In file admin/all_customers.php (around line 117) $customers_query_raw = "SELECT c.customers_id , c.customers_default_address_id, c.customers_email_address, c.customers_fax, c.customers_telephone, a.entry_company, a.address_book_id, a.customers_id, a.entry_firstname, a.entry_lastname, a.entry_street_address, a.entry_suburb, a.entry_city, a.entry_state, a.entry_postcode, a.entry_country_id, a.entry_zone_id, z.zone_code, co.countries_name FROM " . TABLE_CUSTOMERS . " c INNER JOIN " . TABLE_ADDRESS_BOOK . " a ON c.customers_default_address_id = a.address_book_id INNER JOIN " . TABLE_COUNTRIES . " co ON entry_country_id = co.countries_id LEFT OUTER JOIN ". TABLE_ZONES ." z ON a.entry_zone_id = z.zone_id ORDER BY $db_orderby $sorted"; The join syntax has been changed as follows: JOIN > INNER JOIN LEFT JOIN > LEFT OUTER JOIN The LEFT OUTER JOIN to the TABLE_ZONES table is necessary to include those customers who do not have a zone. Quote Link to comment Share on other sites More sharing options...
azer Posted March 12, 2007 Share Posted March 12, 2007 New SQL Query For those of you that have had SQL errors appearing (in particular invalid column id/syntax error around the join statements) or have noticed that customers without zones are not displayed try this revised SQL query I have put together: In file admin/all_customers.php (around line 117) $customers_query_raw = "SELECT c.customers_id , c.customers_default_address_id, c.customers_email_address, c.customers_fax, c.customers_telephone, a.entry_company, a.address_book_id, a.customers_id, a.entry_firstname, a.entry_lastname, a.entry_street_address, a.entry_suburb, a.entry_city, a.entry_state, a.entry_postcode, a.entry_country_id, a.entry_zone_id, z.zone_code, co.countries_name FROM " . TABLE_CUSTOMERS . " c INNER JOIN " . TABLE_ADDRESS_BOOK . " a ON c.customers_default_address_id = a.address_book_id INNER JOIN " . TABLE_COUNTRIES . " co ON entry_country_id = co.countries_id LEFT OUTER JOIN ". TABLE_ZONES ." z ON a.entry_zone_id = z.zone_id ORDER BY $db_orderby $sorted"; The join syntax has been changed as follows: JOIN > INNER JOIN LEFT JOIN > LEFT OUTER JOIN The LEFT OUTER JOIN to the TABLE_ZONES table is necessary to include those customers who do not have a zone. fmeetsoft, do you think we should make a new version wih your modification , is it an upgrade or only to do for shop owner who know they got customer without zone ? Quote MS2 Link to comment Share on other sites More sharing options...
Shiker Posted March 22, 2007 Share Posted March 22, 2007 I installed All Customers Report version 2.1c and modified the request $customers_query_raw = "SELECT c.customers_id ... as previously noticed here. I have the following error displayed : Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /icons/admin/includes/functions/database.php on line 107 Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /icons/admin/includes/functions/database.php on line 99 Any idea ? Thanks in advance, Jacques Quote Link to comment Share on other sites More sharing options...
Guest Posted April 30, 2007 Share Posted April 30, 2007 When changing to the newest CUSTOMER_EXPORT.PHP file I get the following: Warning: filesize() [function.filesize]: Stat failed for <table border=0 cellpadding=5 cellspacing=5 style='border: solid 1px #E0E0E0;' ><tr style='border: 0px;'><td colspan='13' align=left><strong>osCommerce</strong><br><strong>Customer List</stong><br>Jan 15 2007</td></tr><tr style='background:#c0c0c0;'><th>Firstname</th><th>Lastname</th><th>Company</th><th>Address Line 1</th><th>Address Line 2</th><th>City</th><th>Zip</th><th>County</th><th>Country</th><th>Phone</th><th>Email</th><th>Fax</th><th>Newsletter</th></tr> <tr nowrap bgcolor='#FFFFFF' style='border: solid 1px #E0E0E0;'><td>Test</td><td>User</td><td></td><td>123 Main Street</td><td></td><td>Anywhere</td><t in /home/root/public_html/catalog/admin/customer_export.php on line 106 Warning: Cannot modify header information - headers already sent by (output started at /home/root/public_html/catalog/admin/customer_export.php:106) in /home/root/public_html/catalog/admin/customer_export.php on line 109 Warning: Cannot modify header information - headers already sent by (output started at /home/root/public_html/catalog/admin/customer_export.php:106) in /home/root/public_html/catalog/admin/customer_export.php on line 110 Warning: Cannot modify header information - headers already sent by (output started at /home/root/public_html/catalog/admin/customer_export.php:106) in /home/root/public_html/catalog/admin/customer_export.php on line 111 Warning: Cannot modify header information - headers already sent by (output started at /home/root/public_html/catalog/admin/customer_export.php:106) in /home/root/public_html/catalog/admin/customer_export.php on line 112 Warning: Cannot modify header information - headers already sent by (output started at /home/root/public_html/catalog/admin/customer_export.php:106) in /home/root/public_html/catalog/admin/customer_export.php on line 113 Warning: Cannot modify header information - headers already sent by (output started at /home/root/public_html/catalog/admin/customer_export.php:106) in /home/root/public_html/catalog/admin/customer_export.php on line 116 Warning: Cannot modify header information - headers already sent by (output started at /home/root/public_html/catalog/admin/customer_export.php:106) in /home/root/public_html/catalog/admin/customer_export.php on line 120 Warning: Cannot modify header information - headers already sent by (output started at /home/root/public_html/catalog/admin/customer_export.php:106) in /home/root/public_html/catalog/admin/customer_export.php on line 121 Warning: Cannot modify header information - headers already sent by (output started at /home/root/public_html/catalog/admin/customer_export.php:106) in /home/root/public_html/catalog/admin/customer_export.php on line 122 osCommerce Customer List Jan 15 2007 Firstname Lastname Company Address Line 1 Address Line 2 City Zip County Country Phone Email Fax Newsletter Followed by the address list, but it is displayed on the screen and not exported. Anthony I am having the same issue with the customers_export.php file. Any ideas? Thanks! Have a great day! Heather Quote Link to comment Share on other sites More sharing options...
ericcsc Posted July 9, 2007 Share Posted July 9, 2007 Hi I'm not able to see data even I have few records in it. Can anyone advice. Thanks. As I couldn't do print screen, there is the text captured shown below. All Customers Click title to sort +/- Name Email Street Address City Province Postal Code Country Telephone No. Quote Link to comment Share on other sites More sharing options...
Sowdy Posted July 10, 2007 Share Posted July 10, 2007 I installed All Customers Report version 2.1c and modified the request $customers_query_raw = "SELECT c.customers_id ... as previously noticed here. I have the following error displayed : Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /icons/admin/includes/functions/database.php on line 107 Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /icons/admin/includes/functions/database.php on line 99 Any idea ? Thanks in advance, Jacques I have that problem too ... anyone have a fix for it??? Quote Link to comment Share on other sites More sharing options...
oringmonkey Posted October 20, 2007 Share Posted October 20, 2007 Oh boy...whole site messed up now. Having the same problems as everyone else. New SQL query isn't helping out either. Anyone figure out any fixes? Quote Link to comment Share on other sites More sharing options...
premiere Posted January 30, 2008 Share Posted January 30, 2008 4) The CSV file does not properly cope with commas within fields, it throws them away to prevent misalignment of fields, however this makes it impossible to read the customer names as it is unclear where the surname ends and the forname(s) start. The CSV standard states that any field containing a comma should have double quotes around it (and also that fields containing double quotes should be surroud by double quotes and the original double quote should be repeated!) [http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm]. Basically the way the CSV file is currently put together breaks the CSV standard in several ways. It would be too complicated to look for fields containg characters which mean they must be surrounded by quotes, thankfully the standard allows you to surround all fields by double quotes and those that don't need to be there are ignored. Therefore I modified the mirror_out function detailed in the install.txt which is pasted into /admin/includes/functions/general.php to surround all fields by double quotes, not remove commas from fields, and replace any double quotes within a field with two sets of double quotes.Original: // Produce CSV string for output function mirror_out ($field) { global $csv_accum; echo $field; $field = strip_tags($field); $field = ereg_replace (",","",$field); if ($csv_accum=='') $csv_accum=$field; else {if (strrpos($csv_accum,chr(10)) == (strlen($csv_accum)-1)) $csv_accum .= $field; else $csv_accum .= "," . $field; }; return; }; Mine: // Produce CSV string for output function mirror_out ($field) { global $csv_accum; echo $field; $field = strip_tags($field); $field = ereg_replace ("\"","\"\"",$field); if ($csv_accum=='') $csv_accum= "\"" . $field . "\""; else {if (strrpos($csv_accum,chr(10)) == (strlen($csv_accum)-1)) $csv_accum .= "\"" . $field . "\""; else $csv_accum .= "," . "\"" . $field . "\""; }; return; }; I keep on getting a timeout error because of this. Tried using the original and I still get a timeout error. Sometimes it will go through. I have over 25,000 customers!!! What can solve this? Maybe showing 100 customers per page and only the CSV file show the total list? I dont know. Does anyone have any idea how to solve this? This is the error code I get using the original code. I would like to use this new feature but with that one, not even half of my list shows since it takes to long to generate it. Fatal error: Maximum execution time of 30 seconds exceeded in /home/premiere/public_html/admin/includes/functions/general.php on line 1330 PLEASE HELP!! Quote Link to comment Share on other sites More sharing options...
Andybird Posted March 4, 2008 Share Posted March 4, 2008 Hi Guys, I have an issue with downloading the file with Customers Export 1.5 contrib. Everything works fine on FireFox, the save window opens OK when hitting Export button, but nothing happens when using IE7. I hit the Export button but no window opens. Here is the header info for downloading the file: header("Content-Type: application/force-download\n"); header("Content-disposition: attachment; filename=customers_export_" . date("Ymd") . ".txt"); header("Pragma: no-cache"); header("Expires: 0"); echo $contents; die(); Any ideas? thanks. Quote Link to comment Share on other sites More sharing options...
scroll Posted April 28, 2008 Share Posted April 28, 2008 I have that problem too ... anyone have a fix for it??? Me too... Is there any fix for this ?? regards, Quote Link to comment Share on other sites More sharing options...
scroll Posted April 28, 2008 Share Posted April 28, 2008 Me too...Is there any fix for this ?? regards, ok, put after the main query: $customers_query = tep_db_query($customers_query_raw); ...and delete de c.head from the main query. Quote Link to comment Share on other sites More sharing options...
dets03ab Posted September 16, 2008 Share Posted September 16, 2008 Guys i have tried to download and install this addon on my 2.2 osc shop but it still doesent work!! I get a syntax error in admin under customers. Can you please specify the step 3 of installation which is very cery unclear what code to put and where to put it?? I am about to rip out my hair due to step 3 i have tried so many things and it still make me syntax errors! Here is the code i use currently in the reports file!: <?php /* $Id: reports.php,v 1.5 2003/07/09 01:18:53 hpdl Exp $ osCommerce, Open Source E-Commerce Solutions http://www.oscommerce.com Copyright (c) 2002 osCommerce Released under the GNU General Public License */ ?> <!-- reports //--> <tr> <td> <?php $heading = array(); $contents = array(); $heading[] = array('text' => BOX_HEADING_REPORTS, 'link' => tep_href_link(FILENAME_STATS_PRODUCTS_VIEWED, 'selected_box=reports')); if ($selected_box == 'reports') { $contents[] = array('text' => '<a href="' . tep_href_link(FILENAME_STATS_PRODUCTS_VIEWED, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_PRODUCTS_VIEWED . '</a><br>' . '<a href="' . tep_href_link(FILENAME_STATS_PRODUCTS_PURCHASED, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_PRODUCTS_PURCHASED . '</a><br>' . '<a href="' . tep_href_link(FILENAME_STATS_CUSTOMERS, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_ORDERS_TOTAL . '</a>'); '<a href="' . tep_href_link(FILENAME_ALL_CUSTOMERS, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_ALL_CUSTOMERS . '</a><br>' . } $box = new box; echo $box->menuBox($heading, $contents); ?> </td> </tr> <!-- reports_eof //--> This is what step 3 says: STEP 3 (Add link in Admin) =========================== Edit /admin/includes/boxes/reports.php. It's a small file. Find the section with lines that look like the following one, and add the following line (this reads easier with word wrap off): '<a href="' . tep_href_link(FILENAME_ALL_CUSTOMERS, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_ALL_CUSTOMERS . '</a><br>' . You can add the lines in any order you want, but make sure to follow the correct syntax. The finished section could look like this: if ($selected_box == 'reports') { $contents[] = array('text' => '<a href="' . tep_href_link(FILENAME_STATS_PRODUCTS_VIEWED, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_PRODUCTS_VIEWED . '</a><br>' . '<a href="' . tep_href_link(FILENAME_STATS_PRODUCTS_PURCHASED, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_PRODUCTS_PURCHASED . '</a><br>' . '<a href="' . tep_href_link(FILENAME_ALL_CUSTOMERS, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_ALL_CUSTOMERS . '</a><br>' . '<a href="' . tep_href_link(FILENAME_STATS_CUSTOMERS, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_ORDERS_TOTAL . '</a>'); } What should I put and WHERE SHOULD I PUT IT??? Any feedback is very very mucg appreciated :) Quote Link to comment Share on other sites More sharing options...
dets03ab Posted September 16, 2008 Share Posted September 16, 2008 now i get this error inside the admin under all customers: 1054 - Unknown column 'a.entry_zone_id' in 'on clause' SELECT c.customers_id , c.customers_default_address_id, c.customers_email_address, c.customers_fax, c.customers_telephone, a.entry_company, a.address_book_id, a.customers_id, a.entry_firstname, a.entry_lastname, a.entry_street_address, a.entry_suburb, a.entry_city, a.entry_state, a.entry_postcode, a.entry_country_id, a.entry_zone_id, z.zone_code, co.countries_name FROM customers c LEFT JOIN zones z ON a.entry_zone_id = z.zone_id JOIN countries co ON a.entry_country_id = co.countries_id LEFT JOIN address_book a ON c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id ORDER BY a.entry_lastname What should i do here? As I udnerstand the all_customers file already has some of these as default so why it makes this error? Also the link look like this: BOX_REPORTS_ALL_CUSTOMERS what am i doing wrong guys? Quote Link to comment Share on other sites More sharing options...
dets03ab Posted September 16, 2008 Share Posted September 16, 2008 Ok now i see i reached the same problem as others in this thread i have the following error: Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /mnt/w0509/d36/s07/b02be82e/ MY CATALOG/catalog/admin/includes/functions/database.php on line 107 AND Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /mnt/w0509/d36/s07/b02be82e/www/MYCATALOG/catalog/admin/includes/functions/database.php on line 99 Did any find a solution to this???? I have tried the sql update also - didnt work! What is the solution to this? any know a contribution that can do the same job as this (have spent so many hours on this one now is enough!)??? Quote Link to comment Share on other sites More sharing options...
dets03ab Posted September 19, 2008 Share Posted September 19, 2008 bump Quote Link to comment Share on other sites More sharing options...
dets03ab Posted October 3, 2008 Share Posted October 3, 2008 any has a solution to this? Quote Link to comment Share on other sites More sharing options...
Guest Posted October 7, 2008 Share Posted October 7, 2008 ok, put after the main query:$customers_query = tep_db_query($customers_query_raw); ...and delete de c.head from the main query. Please be a bit more specific please - which file is this in? Remember not everyone looking for help has your intimate knowledge of what is where Quote Link to comment Share on other sites More sharing options...
AGO0575 Posted November 12, 2008 Share Posted November 12, 2008 Great contribution. I added the "COMPANY" name to the file and translated to portuguese language. Everything works fine. Thanks a lot. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.