Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

How Did You Hear About Us - Support


Recommended Posts

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

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

  • Replies 318
  • Created
  • Last Reply

Top Posters In This Topic

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

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

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?

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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 by nrlatsha

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

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!

"Resistence is Futile"

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)

"Resistence is Futile"

Link to comment
Share on other sites

  • 2 weeks later...

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 by p51mustang
Link to comment
Share on other sites

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? ;)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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....

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 ============================

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...