Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Need SQL Help


SJones

Recommended Posts

I added a field to the create account section called 'cost_center_number' it is located in the customers table of the database. It is functional in both the create account and update account sections. I would like to have the result of the 'cost_center_number' show up on the

orders.php

invoice.php

packingslip.php

in the admin section. I can't seem to get the code to echo the result of that field. Can someone help???

Link to comment
Share on other sites

Adding a new field always ends up in more work than you think.

For each new field added to a table you need:

> a form field for each of the input and edit forms

> modify the insert and update SQL statements

> make sure that the new field is included in the select statement when editing the record.

 

A step by step guide would take a while to type out. i can if you really want

I ain't got time to bleed

Link to comment
Share on other sites

Adding a new field always ends up in more work than you think.

For each new field added to a table you need:

> a form field for each of the input and edit forms

> modify the insert and update SQL statements

> make sure that the new field is included in the select statement when editing the record.

 

A step by step guide would take a while to type out. i can if you really want

 

 

It would be great if you could point me to the right direction for updating the SQL statements. I have added 2 new custom fields the create_account.php page. They have input fields and seemingly work ok but of course the database does not write these values.

 

I need to know if I have to edit the table in the database for these fields and where are the statements that I must modify.

 

Thanks so much!

 

Chris

Link to comment
Share on other sites

Hi Chris,

 

Right i have started with a fresh copy of checkout_success.php so hopefully the line numbers should match yours.

 

As an example I am going to add customers_source as a new field to the customer table that will allow the user to tell us where they have come from, ie google, magazine etc.

 

First i would create the field in the database table itself. Using the command line

ALTER TABLE `customers` ADD `customers_source` VARCHAR( 50 ) NOT NULL ;

or mySQLadmin and click on add new field. One very common error with these forms is to mismatch a variable with a db field, ie i might have a db field called customer_source and form field called customers_source. This minor typo can be a mare to find. To help with this I stick to a convention.

 

Then at around line 30 you should see a bunch of code that looks like this:

    $city = tep_db_prepare_input($HTTP_POST_VARS['city']);

 

This is taking the variables from the form and running them through a function to check that no nasty characters are entered into the db

so we need to add one for our new field:

 

    $customers_source = tep_db_prepare_input($_POST['customers_source']);
// note: $HTTP_POST_VARS has been replaced by $_POST. The old syntax still works but it is better practice to use the new convention.

 

Then at line 173 you should see an array like this:

 

$sql_data_array = array('customers_firstname' => $firstname,
                             'customers_lastname' => $lastname,
                             'customers_email_address' => $email_address,
                             'customers_telephone' => $telephone,
                             'customers_fax' => $fax,
                             'customers_newsletter' => $newsletter,
                             'customers_password' => tep_encrypt_password($password));

 

This array contains all the submitted form information that is passed to another function tep_db_perform(TABLE_CUSTOMERS, $sql_data_array) to be inserted into the db. So we need to add another element to the array:

 

$sql_data_array = array('customers_firstname' => $firstname,
                             'customers_lastname' => $lastname,
                             'customers_email_address' => $email_address,
                             'customers_telephone' => $telephone,
                             'customers_fax' => $fax,
                             'customers_newsletter' => $newsletter,
                             'customers_password' => tep_encrypt_password($password),
                             'customers_source' => $customers_source);

 

Around this point in the script you will see that there are a couple of these arrays. The information you add in this form is actually held in three different tables, customers, customers_info and address_book. You need to bear this in mind. I added a couple of fields to the address_book table and so needed to change the array that deals with the addresses table rather than the customers one. It should be pretty obvious which is which

 

You then need to add a new form element to take the values from the user. The form starts at line 279. You can use all the normal html form elements. The most flexible is the input box. An input box for our purposes would look like this:

 

              
<tr>
 <td class="main"><?php echo ENTRY_CUSTOMER_SOURCE; ?></td>
 <td class="main"><?php echo tep_draw_input_field('customers_source'); ?></td>
</tr>

 

You also need to put a definition into the correct language file, this would be /includes/languages/english/create_account.php

You would need to add

define('ENTRY_CUSTOMER_SOURCE', 'Where did you heard about us?');

 

If you run into any trouble try using print_r() to see whats going on in different arrays, most importantly $_POST and the relevent $sql_data_array

 

hope this helps

I ain't got time to bleed

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...