nrlatsha Posted July 16, 2004 Share Posted July 16, 2004 Here's a simple hack to display the referral source with the customer's information. This will also make an entry into the database if anything is written into that box, maybe you want to add "Website" as a source, they can then put "someoffthewallwebsite.com" and you'll be ablt to see it. catalog/create_account.php: LOOK FOR: //rmh referral start tep_db_query("insert into " . TABLE_CUSTOMERS_INFO . " (customers_info_id, customers_info_number_of_logons, customers_info_date_account_created, customers_info_source_id) values ('" . (int)$customer_id . "', '0', now(), '". (int)$source . "')"); if ($source == '9999') { tep_db_query("insert into " . TABLE_SOURCES_OTHER . " (customers_id, sources_other_name) values ('" . (int)$customer_id . "', '". $source_other . "')"); } //rmh referral end REPLACE WITH: //rmh referral start tep_db_query("insert into " . TABLE_CUSTOMERS_INFO . " (customers_info_id, customers_info_number_of_logons, customers_info_date_account_created, customers_info_source_id) values ('" . (int)$customer_id . "', '0', now(), '". (int)$source . "')"); // if ($source == '9999') { if (isset($HTTP_POST_VARS['source_other'])) { tep_db_query("insert into " . TABLE_SOURCES_OTHER . " (customers_id, sources_other_name) values ('" . (int)$customer_id . "', '". $source_other . "')"); } //rmh referral end admin/customers.php: LOOK FOR: </table></td> </tr> <tr> <td><?php echo tep_draw_separator('pixel_trans.gif', '1', '10'); ?></td> </tr> <tr> <td align="right" class="main"><?php echo tep_image_submit('button_update.gif', IMAGE_UPDATE) . ' <a href="' . tep_href_link(FILENAME_CUSTOMERS, tep_get_all_get_params(array('action'))) .'">' . tep_image_button('button_cancel.gif', IMAGE_CANCEL) . '</a>'; ?></td> </tr></form> ABOVE IT ADD: <?php $info_query = tep_db_query("select customers_info_source_id from " . TABLE_CUSTOMERS_INFO . " where customers_info_id = '" . $customers['customers_id'] . "'"); $info = tep_db_fetch_array($info_query); if ($info['customers_info_source_id'] ==4) { $info_query = tep_db_query("select sources_other_name from " . TABLE_SOURCES_OTHER . " where customers_id = '" . $customers['customers_id'] . "'"); $info = tep_db_fetch_array($info_query); ?> <td class="main">Referred by:</td> <td class="main"><? if (tep_not_null($info['sources_other_name'])) { echo $info['sources_other_name']; } else { echo 'Not Given'; }?> </td> <? } ?> Of course this line: if ($info['customers_info_source_id'] ==4) { Refers to my Friend / CoWorker select box. And it will not taint your "other" category, it would just be some info the customer put down when signing up. I think thats it, I didn't test it on a new install and it may not be perfect, but it works... Steel - If I get bored, I'll see about adding your idea to my site. And if it gets added to my site, I'll post the info here... Noel Quote 9 times out of 10 its a PEBCAK Error (Problem exists between chair and keyboard) Replace that and you're fine... Link to comment Share on other sites More sharing options...
nrlatsha Posted July 16, 2004 Share Posted July 16, 2004 Steel - I got bored and didn't feel like working on stuff I should be doing. admin/customers.php: LOOK FOR: } else { echo ' <tr class="dataTableRow" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href=\'' . tep_href_link(FILENAME_CUSTOMERS, tep_get_all_get_params(array('cID')) . 'cID=' . $customers['customers_id']) . '\'">' . "\n"; } ADD BELOW: $info_query = tep_db_query("select customers_info_source_id from " . TABLE_CUSTOMERS_INFO . " where customers_info_id = '" . $customers['customers_id'] . "'"); $info1 = tep_db_fetch_array($info_query); if ($info1['customers_info_source_id'] == 0) { $entry_referral = 'XXXXX'; } elseif (($info1['customers_info_source_id'] == 1) || ($info1['customers_info_source_id'] == 9999)) { $info_query = tep_db_query("select sources_other_name from " . TABLE_SOURCES_OTHER . " where customers_id = '" . $customers['customers_id'] . "'"); $info1 = tep_db_fetch_array($info_query); if ($info1['sources_other_name'] != '') { $entry_referral = $info1['sources_other_name']; } else { $entry_referral = 'XXXXXXX'; } } else { $info_query = tep_db_query("select sources_name from " . TABLE_SOURCES . " where sources_id = '" . $info1['customers_source_id'] . "'"); $info1 = tep_db_fetch_array($info_query); $entry_referral = $info1['sources_name']; } Again, this line: (($info1['customers_info_source_id'] == 1) || ($info1['customers_info_source_id'] == 9999)) Can be changed to match your dbase's source id's. FIND: <td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_ACCOUNT_CREATED; ?></td> REPLACE WITH: <td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_ACCOUNT_CREATED; ?></td> <td class="dataTableHeadingContent" align="right"><?php echo 'Referred By'; ?></td> FIND: <td class="dataTableContent" align="right"><?php echo tep_date_short($info['date_account_created']); ?></td> REPLACE WITH: <td class="dataTableContent" align="right"><?php echo tep_date_short($info['date_account_created']); ?></td> <td class="dataTableContent" align="right"><?php echo $entry_referral; ?></td> That should be it... Noel Quote 9 times out of 10 its a PEBCAK Error (Problem exists between chair and keyboard) Replace that and you're fine... Link to comment Share on other sites More sharing options...
Guest Posted July 16, 2004 Share Posted July 16, 2004 i'm very excited to get this mod working, thanks for creating this, hobbzilla! it seems i'm the only one having these problems, so please excuse me if this is just due to some dumb thing i did... 1) in the admin section none of my links are correct anymore. the file names in the URL are replaced with the variables. for instance, clicking the Configuration link produces the URL: catalog/admin/FILENAME_CONFIGURATION?gID=1&selected_box=configuration 2) going to the referrals page (catalog/admin/referrals.php) gives me this error: 1146 - Table 'catalog.TABLE_SOURCES' doesn't exist select count(*) as total from TABLE_SOURCES [TEP STOP] but i verified that the table "sources" does exist. i was in phpMyAdmin and editing the referral sources manually since the links in the admin section aren't working. (or is the table supposed to be table_sources? my sql knowledge is very limited.) any ideas on either issue? Quote Link to comment Share on other sites More sharing options...
hobbzilla Posted July 16, 2004 Author Share Posted July 16, 2004 did something happen to your filenames.php & database_tables.php ??? Quote Link to comment Share on other sites More sharing options...
Guest Posted July 16, 2004 Share Posted July 16, 2004 thank you for pointing me in the right direction. i'm really not sure what the problem was, but it must have been some small typo. i just went into those files and tried to straighten up the code and when i uploaded the new version, that fixed it. great contribution, thanks again! Quote Link to comment Share on other sites More sharing options...
Steel Posted July 17, 2004 Share Posted July 17, 2004 nrlatsha That's exactly what I was trying to do. Thanks it works great! Now what I am trying to do is find a way to have the source be listed on all customers with out having to manually enter in the ID and desired definition for each one like this below: } elseif (($info1['customers_info_source_id'] == 1) || ($info1['customers_info_source_id'] == 9999)) { $info_query = tep_db_query("select sources_other_name from " . TABLE_SOURCES_OTHER . " where customers_id = '" . $customers['customers_id'] . "'"); $info1 = tep_db_fetch_array($info_query); if ($info1['sources_other_name'] != '') { $entry_referral = $info1['sources_other_name']; } else { $entry_referral = 'Google'; } } elseif (($info1['customers_info_source_id'] == 4) || ($info1['customers_info_source_id'] == 9999)) { $info_query = tep_db_query("select sources_other_name from " . TABLE_SOURCES_OTHER . " where customers_id = '" . $customers['customers_id'] . "'"); $info1 = tep_db_fetch_array($info_query); if ($info1['sources_other_name'] != '') { $entry_referral = $info1['sources_other_name']; } else { $entry_referral = 'MSN'; } } elseif (($info1['customers_info_source_id'] == 5) || ($info1['customers_info_source_id'] == 9999)) { $info_query = tep_db_query("select sources_other_name from " . TABLE_SOURCES_OTHER . " where customers_id = '" . $customers['customers_id'] . "'"); $info1 = tep_db_fetch_array($info_query); if ($info1['sources_other_name'] != '') { $entry_referral = $info1['sources_other_name']; } else { $entry_referral = 'Yahoo'; } Im trying to figure out a way to get something like the following to work (this does not actually work but its an example of what Im trying to do) : if ($info1['customers_info_source_id'] == 0) { $entry_referral = 'N/A'; } elseif (($info1['customers_info_source_id'] == 1-9998) || ($info1['customers_info_source_id'] == 9999)) { $info_query = tep_db_query("select sources_other_name from " . TABLE_SOURCES_OTHER . " where customers_id = '" . $customers['customers_id'] . "'"); $info1 = tep_db_fetch_array($info_query); if ($info1['sources_other_name'] != '') { $entry_referral = $info1['sources_other_name']; } else { $entry_referral = '$info1['sources_name']'; } } else { $info_query = tep_db_query("select sources_name from " . TABLE_SOURCES . " where sources_id = '" . $info1['customers_source_id'] . "'"); $info1 = tep_db_fetch_array($info_query); $entry_referral = $info1['sources_name']; } Im pretty good at HTML but am still very new to PHP and its definitely a learning experience :rolleyes: Thanks Again Steel Quote Link to comment Share on other sites More sharing options...
Steel Posted July 17, 2004 Share Posted July 17, 2004 nrlatsha Ive found a bug in the code ...the code changed in the create account page opens up the "other" box to allow anything to be put in it and does not limit it to just 9999 and 4. this woundnt be that big a deal except that if someone were to put anything in the box now, even if they select google, yahoo or whatever, the text they put in the "other" box is now what will show up on the customers screen instead of the selected source. EXAMPLE customer selects "GOOGLE" as the source but also types in "Hi how are you" Instead of 'GOOGLE" showing up on the customer page it will show as this: Last Name-----First Name------Account Created-------Referred By-------Action DOE--------------JOHN--------------02/02/2002----------Hi how are you I tried changing it to this..... //rmh referral start tep_db_query("insert into " . TABLE_CUSTOMERS_INFO . " (customers_info_id, customers_info_number_of_logons, customers_info_date_account_created, customers_info_source_id) values ('" . (int)$customer_id . "', '0', now(), '". (int)$source . "')"); if ($source == '9999') { tep_db_query("insert into " . TABLE_SOURCES_OTHER . " (customers_id, sources_other_name) values ('" . (int)$customer_id . "', '". $source_other . "')"); } if ($source == '4') { tep_db_query("insert into " . TABLE_SOURCES_OTHER . " (customers_id, sources_other_name) values ('" . (int)$customer_id . "', '". $source_other . "')"); } //rmh referral end to see if it would fix the problem, but by doing this no source will show up and it will read not given on the customer edit screen :blink: Quote Link to comment Share on other sites More sharing options...
nrlatsha Posted July 17, 2004 Share Posted July 17, 2004 (edited) Steel - I have to work this weekend, and 12hr shifts don't really give me any time to code, but I can take a look next week... Perhaps I can append it to something like Google: Hi, How are you.... So if they type something useful in there, its there... Just a thought, I'll have others I'm sure... Edited July 17, 2004 by nrlatsha Quote 9 times out of 10 its a PEBCAK Error (Problem exists between chair and keyboard) Replace that and you're fine... Link to comment Share on other sites More sharing options...
sc00zy Posted July 19, 2004 Share Posted July 19, 2004 Hi, I just installed this great contrib for other purposes it's made for. In my shop customers can donate their 20% discount to organisations like the local soccerclub and so on. This contrib comes very close to what i'm looking for ... but I like the referral to show on the order, e-mail, invoice, packaginslip, etc... is this hard to do? I really don't have a clue to do this.... Thanks in advance! Quote "Resistence is Futile" Link to comment Share on other sites More sharing options...
hobbzilla Posted July 19, 2004 Author Share Posted July 19, 2004 Are you needing a referral each time a customer places an order? This can be done. However, instead of updating the customers info table.. you would have to update the order table and instead of making changes to the create account code, you would have to change checkout (shipping/billing) pages along with the order class. Quote Link to comment Share on other sites More sharing options...
sc00zy Posted July 19, 2004 Share Posted July 19, 2004 Yes... I need an referral each time a customer places an order. Or maybe a edit function would be needfull so they can edit their choice of referral later on... The problem is that I'm not really a big star in coding B) Quote "Resistence is Futile" Link to comment Share on other sites More sharing options...
Guest Posted July 29, 2004 Share Posted July 29, 2004 How do you find out what your databases source id is, as mentioned in noels upgrade. Quote Link to comment Share on other sites More sharing options...
p51mustang Posted August 2, 2004 Share Posted August 2, 2004 (edited) Hi I've a brand new store with no real customers yet. I've installed the contribution (including the count fix and adding source to customer info). All seems fine from the Admin side, but then: The problem occurs when when a new customer creates a new account; the following error message is displayed after entering the customers details and pressing 'Continue': 1062 - Duplicate entry '4' for key 1 insert into customers_info (customers_info_id, customers_info_number_of_logons, customers_info_date_account_created) values ('4', '0', now()) [TEP STOP] I think entry'4' is the new customer number. Under Admin - Customers, the following error message is displayed: Customers? Search:? Last Name First Name Account Created Referred By 1146 - Table 'saairfor_osc1.TABLE_SOURCES_OTHER' doesn't exist select sources_other_name from TABLE_SOURCES_OTHER where customers_id = '3' [TEP STOP] I've uploaded the file 'update.sql' via phpMyAdmin and it created two new tables: sources and sources_other. Anyone know how to fix this? Thanks. Edited August 2, 2004 by p51mustang Quote Link to comment Share on other sites More sharing options...
hobbzilla Posted August 2, 2004 Author Share Posted August 2, 2004 1062 - Duplicate entry '4' for key 1 insert into customers_info (customers_info_id, customers_info_number_of_logons, customers_info_date_account_created) values ('4', '0', now()) [TEP STOP] I think entry'4' is the new customer number. Sounds like you deleted a customer but not through the admin. If you did this.. you must also delete the cooresponding customer_info entry. Under Admin - Customers, the following error message is displayed: Customers Search: Last Name First Name Account Created Referred By 1146 - Table 'saairfor_osc1.TABLE_SOURCES_OTHER' doesn't exist select sources_other_name from TABLE_SOURCES_OTHER where customers_id = '3' [TEP STOP] I've uploaded the file 'update.sql' via phpMyAdmin and it created two new tables: sources and sources_other. Update your database_tables.php lately? ;) Quote Link to comment Share on other sites More sharing options...
p51mustang Posted August 3, 2004 Share Posted August 3, 2004 Hi Sounds like you deleted a customer but not through the admin. If you did this.. you must also delete the cooresponding customer_info entry. Yes, I did delete a customer, but it was via Admin - Customers :huh: Update your database_tables.php lately? Ooops, left one line out! However, the problem still remains. All the test customers I entered are still in there somewhere, I just canot access them via Admin. How do I reset the database (via phpMyAdmin?) to delete the customers? Thanks. Quote Link to comment Share on other sites More sharing options...
p51mustang Posted August 3, 2004 Share Posted August 3, 2004 OK, I deleted all the mods for this contribution and restored my backup database, so everything now works as it was before installing this contribution. <_< This contribution is a great idea, but I think I will wait for a new, consolidated release (ie, with the count fix and adding source to customer info, plus any new enhancements) before trying to install again. B) I really like this mod :D Quote Link to comment Share on other sites More sharing options...
cyberphool Posted August 4, 2004 Share Posted August 4, 2004 Hi there, hope you're still reading this thread Ryan :) Could you let me know how to change these fields from mandatory to optional please? thanks, David Quote Link to comment Share on other sites More sharing options...
cyberphool Posted August 4, 2004 Share Posted August 4, 2004 I AM A DOLT :) I just read through your installation notes again - "Admin can also make this required or not (Admin->Configuration->Customer Details)" cheers for that, solved ;) Quote Link to comment Share on other sites More sharing options...
dasaro Posted August 7, 2004 Share Posted August 7, 2004 I, too, am thankful for effort put forth on this wonderful contribution. I'm fairly new to all of this stuff and am working through as best I can. I finished all of the admin side additions just fine. Works great!! I was sure that all of the catalog changes were flawless too, until I tried to create a fake account just to try it out. I get the following error on the next page: 1062 - Duplicate entry '76' for key 1 insert into customers_info (customers_info_id, customers_info_number_of_logons, customers_info_date_account_created, customers_info_source_id) values ('76', '0', now(), '4') [TEP STOP] I understand from the previous posts that this results from a duplicate entry. I have found the problem in create_account.php as follows: // Guest Account Start if (!$guest_account) { tep_db_query("insert into " . TABLE_CUSTOMERS_INFO . " (customers_info_id, customers_info_number_of_logons, customers_info_date_account_created) values ('" . tep_db_input($customer_id) . "', '0', now())"); } else { tep_db_query("insert into " . TABLE_CUSTOMERS_INFO . " (customers_info_id, customers_info_number_of_logons, customers_info_date_account_created) values ('" . tep_db_input($customer_id) . "', '-1', now())"); } // Guest Account End //Referral start =========================== tep_db_query("insert into " . TABLE_CUSTOMERS_INFO . " (customers_info_id, customers_info_number_of_logons, customers_info_date_account_created, customers_info_source_id) values ('" . (int)$customer_id . "', '0', now(), '". (int)$source . "')"); if ($source == '9999') { tep_db_query("insert into " . TABLE_SOURCES_OTHER . " (customers_id, sources_other_name) values ('" . (int)$customer_id . "', '". $source_other . "')"); } //Referral end ============================ It is painfully obvious that the two queries at the beginning of 'Guest Account' and 'Referral' are nearly identical and cannot peacefully co-exist. However, I am not sure at all on how to fix this. Will it have anything to do with the '0' ? This error prevents any new customers from seemingly creating a new account. Their information is successfully stored in the table and they may log into their new account afterwards, but this error message is terribly unaesthetic, at best. Any help would be more than greatly appreciated. The sooner the better!!! :) -David Quote Link to comment Share on other sites More sharing options...
PM5K Posted August 9, 2004 Share Posted August 9, 2004 When I browse "sources_other" and go to export I see the following error: Error SQL-query : SHOW TABLE STATUS LIKE 'sources_other' FROM 'sources_other' MySQL said: You have an error in your SQL syntax near 'FROM 'sources_other'' at line 1 Back Thanks for the assistance.... Quote Link to comment Share on other sites More sharing options...
hobbzilla Posted August 10, 2004 Author Share Posted August 10, 2004 How Did you Hear About Us v1.2 released! Get it here: http://www.oscommerce.com/community/contributions,2159 v1.2 - 08/10/04 Referral Report Bug fixes Display Referrer for customer Create Account SQL Bug fixes Quote Link to comment Share on other sites More sharing options...
hobbzilla Posted August 10, 2004 Author Share Posted August 10, 2004 When I browse "sources_other" and go to export I see the following error: Error SQL-query : SHOW TABLE STATUS LIKE 'sources_other' FROM 'sources_other' MySQL said: You have an error in your SQL syntax near 'FROM 'sources_other'' at line 1 Back Thanks for the assistance.... where is this exactly? Sounds like phpmyadmin ??? Shouldn't be an export from the oscommerce admin side of things. Quote Link to comment Share on other sites More sharing options...
hobbzilla Posted August 10, 2004 Author Share Posted August 10, 2004 It is painfully obvious that the two queries at the beginning of 'Guest Account' and 'Referral' are nearly identical and cannot peacefully co-exist. However, I am not sure at all on how to fix this. Will it have anything to do with the '0' ? This error prevents any new customers from seemingly creating a new account. Their information is successfully stored in the table and they may log into their new account afterwards, but this error message is terribly unaesthetic, at best. Any help would be more than greatly appreciated. The sooner the better!!! :) -David Simply remove the insert from my code and append the portion to Guest Account: // Guest Account Start if (!$guest_account) { tep_db_query("insert into " . TABLE_CUSTOMERS_INFO . " (customers_info_id, customers_info_number_of_logons, customers_info_date_account_created, customers_info_source_id) values ('" . tep_db_input($customer_id) . "', '0', now(), '". (int)$source . "')"); } else { tep_db_query("insert into " . TABLE_CUSTOMERS_INFO . " (customers_info_id, customers_info_number_of_logons, customers_info_date_account_created, customers_info_source_id) values ('" . tep_db_input($customer_id) . "', '-1', now(), '". (int)$source . "')"); } // Guest Account End //Referral start =========================== if ($source == '9999') { tep_db_query("insert into " . TABLE_SOURCES_OTHER . " (customers_id, sources_other_name) values ('" . (int)$customer_id . "', '". $source_other . "')"); } //Referral end ============================ Quote Link to comment Share on other sites More sharing options...
dasaro Posted August 11, 2004 Share Posted August 11, 2004 Well, zilla, it seems that did the trick. I had attempted something of that sort earlier, but obviously it wasn't the correct fix. I do appreciate it. I'm getting ready to download the latest version. Can't wait! Again, great job on this contrib. Quote Link to comment Share on other sites More sharing options...
dasaro Posted August 11, 2004 Share Posted August 11, 2004 I just thought of another great addition for this contrib. I'm going to try to work on account_edit.php such that customers who created an account before this referral contribution was added may be able to edit their account and choose the same options. Not sure right now what that will entail, but why not find out... 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.