modding up a 2.3.4BS Gold

[greasemonkey]

@@BrockleyJohn Hey, John, I`m currently working on;

  $listing_sql = "select " . $select_column_list . " p.products_id, 
    SUBSTRING_INDEX(pd.products_description, ' ', 20) as products_description, 
	p.manufacturers_id, 
	p.products_price, 
	p.products_tax_class_id, 
	IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, 
	IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . 
	TABLE_PRODUCTS_DESCRIPTION . " pd, " . 
	TABLE_PRODUCTS . " p left join " . 
	TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . 
	TABLE_SPECIALS . " s on p.products_id = s.products_id 
	left join (select status, specials_new_products_price from specials where customers_group_id = '" .$customer_group_id. "') s on s.products_id = p.products_id
	where p.products_status = '1' 
	and p.products_id = pd.products_id 
	and pd.language_id = '" . (int)$languages_id . "'
	";

Is this what you intended? I'm getting this error

 

1052 - Column 'status' in field list is ambiguous

select p.products_image, pd.products_name, p.products_id, SUBSTRING_INDEX(pd.products_description, ' ', 20) as products_description, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join specials s on p.products_id = s.products_id left join (select status, specials_new_products_price from specials where customers_group_id = '0') s on s.products_id = p.products_id where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' order by p.products_id DESC limit 0, 24

 

I believe that is pointing to; select status, specials_new_products_price

 

Saying it's in multiple tables... Is this correct? But I can't fix it by adding the alias s.status, or p.

[BrockleyJohn]

@@greasemonkey You need to take the specials table out of the main select so it's only in the subselect:

  $listing_sql = "select " . $select_column_list . " p.products_id, 
    SUBSTRING_INDEX(pd.products_description, ' ', 20) as products_description, 
	p.manufacturers_id, 
	p.products_price, 
	p.products_tax_class_id, 
	IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, 
	IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . 
	TABLE_PRODUCTS_DESCRIPTION . " pd, " . 
	TABLE_PRODUCTS . " p left join " . 
	TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id 
	left join (select status, specials_new_products_price from specials where customers_group_id = '" .$customer_group_id. "') s on s.products_id = p.products_id
	where p.products_status = '1' 
	and p.products_id = pd.products_id 
	and pd.language_id = '" . (int)$languages_id . "'
	";

It was right about its being ambiguous - it was there twice!

[greasemonkey]

@@BrockleyJohn thanks so much again John. That complains like

 

1054 - Unknown column 's.products_id' in 'on clause'

select count(p.products_id) as total from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join (select status from specials where customers_group_id = '0') s on s.products_id = p.products_id where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1'

[greasemonkey]

Ta da....@BrockleyJohn  I think I've fixed this in a sensible way... using the TABLE_SPECIALS_RETAIL_PRICES table.

  $listing_sql = "select " . $select_column_list . " p.products_id, 
    SUBSTRING_INDEX(pd.products_description, ' ', 20) as products_description, 
	p.manufacturers_id, 
	p.products_price, 
	p.products_tax_class_id, 
	IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, 
	IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . 
	TABLE_PRODUCTS_DESCRIPTION . " pd, " . 
	TABLE_PRODUCTS . " p left join " . 
	TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . 
	TABLE_SPECIALS_RETAIL_PRICES . " s on p.products_id = s.products_id 
	where p.products_status = '1' 
	and p.products_id = pd.products_id 
	and pd.language_id = '" . (int)$languages_id . "'
	";

Not sure exactly why this works yet.. And needs more testing...

[BrockleyJohn]

That's just because in the data you're testing you don't have multiple entries in the specials retail prices table for a product. I don't know if that will hold true for all data. Add products_id to the subselect and it will clear the error you were getting

(select status, specials_new_products_price, products_id from specials where customers_group_id = '" .$customer_group_id. "')

Sorry I don't have a suitable database to test the sql on

[BrockleyJohn]

@@greasemonkey, I'm sorry, there's a much better solution than the first one I gave you... for a simple filter on the join on specials you just need to add it into the join not the where:

  $listing_sql = "select " . $select_column_list . " p.products_id, 
    SUBSTRING_INDEX(pd.products_description, ' ', 20) as products_description, 
	p.manufacturers_id, 
	p.products_price, 
	p.products_tax_class_id, 
	IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, 
	IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . 
	TABLE_PRODUCTS_DESCRIPTION . " pd, " . 
	TABLE_PRODUCTS . " p left join " . 
	TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . 
	TABLE_SPECIALS . " s on p.products_id = s.products_id 
	and s.customers_group_id = '" .$customer_group_id. "'
	where p.products_status = '1' 
	and p.products_id = pd.products_id 
	and pd.language_id = '" . (int)$languages_id . "'
";

no worries on the performance like this

[greasemonkey]

Thanks so much John... I'll have a look at this in a couple days - super busy weekend (ya me).

[greasemonkey]

Hey All, working on intergrating HMTL emails from @Gergely  using his git fork here https://github.com/tgely/osCommerce-234-bootstrap/commits/cleaned_email_templates

 

I have pulled out all the new files and will start updating the core as a test this weekend.

[greasemonkey]

I've successfully integrated html emails. At the same time I've completely reworked create_account.php based on a couple thoughts (screen cap attached);

 

1) I never have like or used onepage checkout - I have used a the idea of shorter checkout HERE and modified for BS...

 

2) I've been a great blog post HERE  and have integrated some of these ideas (I've always re-titled created account to "new customers") by renaming to "about you", Slimming in down by removing all the <h2> and <lable> tags and also made into 2 columns (1 for "new" and one for "returning" by removing contentText and using <div class="col-md-6"> inside contentContainer.

 

Now I going a step further and trying to integrate the thoughts from @@burt found HERE to remove the password field completely. HOWEVER, I'm running into issues passing the $password variable to the new email templates added with html emails. The password is being set in the DB and the text is passing to the email... But no $password.

 

Here is an example from /includes/modules/pages/templates/email_create_account_html.php

                          <tr><td><?php echo EMAIL_WARNING; ?></td></tr>
                          <tr><td><?php echo sprintf(EMAIL_PASSWORD, $password); ?></td></tr>

Or in the text template

  echo strip_tags(EMAIL_WELCOME . EMAIL_TEXT . EMAIL_CONTACT . EMAIL_WARNING);
  echo sprintf(EMAIL_PASSWORD, $password);

[greasemonkey]

 

HOWEVER, I'm running into issues passing the $password variable to the new email templates added with html emails. The password is being set in the DB and the text is passing to the email... But no $password.

Nevermind... lol.... Figured it out by adding $password to globals in /includes/modules/pages/tp_email_create_account.php

[Dan Cole]

@@greasemonkey

 

 

 

2) I've been a great blog post HERE  and have integrated some of these ideas (I've always re-titled created account to "new customers") by renaming to "about you", Slimming in down by removing all the <h2> and <lable> tags and also made into 2 columns (1 for "new" and one for "returning" by removing contentText and using <div class="col-md-6"> inside contentContainer.

 

Excellent reference/blog post Scott.  Well worth reading

 

Dan

[burt]

@@greasemonkey - label tags - you should consider adding those back in.

 

Have a read of these two, both worthwhile;

 

http://www.nngroup.com/articles/form-design-placeholders/

http://www.pardot.com/faqs/forms/placeholders-and-labels/

[greasemonkey]

Hmmmmm... Food for thought @@burt.

[burt]

In our R-234 we do this:

 

   <div class="form-group has-feedback">
      <label for="inputFirstName" class="control-label col-sm-3"><?php echo ENTRY_FIRST_NAME; ?></label>
      <div class="col-sm-9">
        <?php
        echo tep_draw_input_field('firstname', NULL, 'required aria-required="true" id="inputFirstName" placeholder="' . ENTRY_FIRST_NAME . '"');
        echo FORM_REQUIRED_INPUT;
        if (tep_not_null(ENTRY_FIRST_NAME_TEXT)) echo '<span class="help-block">' . ENTRY_FIRST_NAME_TEXT . '</span>';
        ?>
      </div>
    </div>

In 2.4, this has been changed to:

 

    <div class="form-group has-feedback">
      <label for="inputFirstName" class="control-label col-sm-3"><?php echo ENTRY_FIRST_NAME; ?></label>
      <div class="col-sm-9">
        <?php echo tep_draw_input_field('firstname', NULL, 'minlength="' . ENTRY_FIRST_NAME_MIN_LENGTH . '"  required aria-required="true" id="inputFirstName" placeholder="' . ENTRY_FIRST_NAME_TEXT . '"'); ?>
        <?php echo FORM_REQUIRED_INPUT; ?>
      </div>
    </div>

We have moved the "helper text" from underneath each input box to the placeholder. This subtle change has allowed shopowners more flexibility in what they do with their forms...

 

The "label" not only shows the title of each box, it also allows to click on it, to start typing in the input => http://www.template.me.uk/234bs3/create_account.phpand click on the textual lable "First Name", the input box for the name should select itself and you should be able to start typinh without actually clicking into the input (hope that makes sense)..

[greasemonkey]

That seems like a better use of what I (incorrectly) thought was redundant. Thanks for that @@burt

[greasemonkey]

here is a screen cap of the re-reworked create account adding the <label> back in.... but keeping it within the 2 <div class="col-md-6">

[greasemonkey]

Other work from this weekend included reworking contact_us to add hours, and address in a responsive table within english/contact_us.php.

define('TEXT_HOURS', '
<table  class="table contactus table-responsive table-bordered table-condensed table-striped table-wrapping">
        <td border="0" width="50%" valign="top" cellspacing="0" cellpadding="2">
		<b>Retail Store:</b><br/>
		My Store<br/>
		My address<br/>
		City, Province<br/>
		Postal Code<br/>
		Toll Free: 555-555-5555<br/>
		Local: 555-555-5555<br/>
		Fax: 555-555-5555<br/>
		</td>
             <td border="0" width="50%"  valign="top" cellspacing="0" cellpadding="2">
		<b>Hours of Operation:</b><br/>
		Holidays: CLOSED<br/>
		Mondays, Tuesdays, Wednesdays & Fridays: 10am-6pm<br/>
		Thursdays: 10am-7:30pm<br/>
		Saturdays: 10am-4pm<br/>
		Sundays: 12pm-4pm<br/>
	     </td>
	   </table>');

And then in contact_us.php

<div class="row">
  <?php echo TEXT_HOURS; ?>
</div>

Also added the google map contact us content module from 28 days of coding.

 

I'm starting to look at the details now.... getting closer.

I hope to start digging into the DB this week.

 

Also have a lagging worry over the move - in particular redirecting from my current "mobile" directory. I need to be sure there is a 301 set up. @raiwa do you have any thoughts here... Have you tested anything?

[spiritalan]

Hey Scott and Burt,

 

I downloaded and installed OSC 2.3.4BS Edge last week. I'm taking the plunge.

 

I just found a closing ?> missing in catalog/admin/includes/languages/english.php file.

 

I'm modding in SPPC, this is how I found the missing ?>.

 

Alan

[spiritalan]

@@spiritalan , it should supposed to be missing.

 

pure php filesis allowed to dismiss the closing tag.

 

if have html/php mixture files, you must use the closing tag.

 

Thanks, I'm not a coder, just a cut and paster. It's something good to know. I have always seen these OSC PHP files with a closing tag. My next question, would it adversely affect it to add it in there?

[BrockleyJohn]

Thanks, I'm not a coder, just a cut and paster. It's something good to know. I have always seen these OSC PHP files with a closing tag. My next question, would it adversely affect it to add it in there?

 

As long as it's the very last thing in the file, with no spaces or invisible characters afterwards, no. But if there's anything following, you'll likely get a 'headers already sent by...' error, so it's better  to leave it out in an only php file.

 

Rule of thumb - if it ain't broke, don't fix it

[spiritalan]

Thanks John, I will leave it out.

Alan

[woodies]

mucho work today... Installed Canada Post, 

 

Hey Scott - which Canada Post module did you install?

 

I just installed canada.post.rest.rate.service.oscommerce.V2.3_1 by Sean Zhuang.

 

There are two problems - I get an error message in the shopping cart: "Shipping is currently not available for the selected shipping address. Please select or create a new shipping address to use with your purchase."

 

Additionally, any checked options for Disallowed Shipping Options don't "stick" after editing and saving the module's options.

 

Any thoughts as to what is causing either or both problems?

 

Glad to see you've been having successes! 

[greasemonkey]

Hi All, sorry its been so long since I updated this thread.

 

I wanted to thank everyone who's read it... And although I haven't been posting daily there has been work in the background.

 

I go live tomorrow!

 

I have a few issues to button up (one with html emails and another with wishlist) but no game changers...

 

I've practiced by export and import .... and double checked all urls... so wish me luck!

[ArtcoInc]

@@greasemonkey

 

Luck!

 

Malcolm

[BrockleyJohn]

Never mind the luck, Scott, I wish you lots of responsive sales!