Onlinesales Help

User Search Data

2007-04-20T00:19:00.000+01:00

We've rolled out some new functionality to the internal search system within the onlinesales database.

A new table has been created that will store various aspects of the search, including the search, the date & time, and the amount of results returned for products, galleries and brands.



DROP TABLE IF EXISTS searches_tbl;
CREATE TABLE searches_tbl (
id INT unsigned NOT NULL auto_increment,
search char(255) default NULL,
search_date DATETIME default NULL,
search_ip char(30) default NULL,
product_results INT unsigned default 0,
gallery_results INT unsigned default 0,
brand_results INT unsigned default 0,
PRIMARY KEY(id),
UNIQUE KEY id (id)
);

You'll shortly begin to see a series of new report functions within the admin section. These will report on various aspects including:

Frequent searches (you can use that data to perhaps improve the navigation to get people to popular items)
Searches with no results - See for market opportunity and to improve existing text and descriptions within your site

Changing a Gallery Image

2007-04-04T13:55:00.000+01:00

By default the gallery will display the latest image to be added to the gallery. You can change it easily via the gallery page in the admin section.

The galleries are listed alphabetically, simply find the gallery you need, select edit, then enter in the name of the file you want to use for the gallery image, which should be uploaded in your site's media folder.

Please see the screencast:
Changing Gallery Images.

Associating Products with Brands

2005-06-27T13:15:00.000+01:00

To aide associating a product and a brand (or if you prefer consider the brand as a supplier or manufacturer) you can now edit a product and select the appropriate brand from a drop down menu from the standard "product search" page.

As the product to brand is a one-to-many relationship (a product could be associated with several different brands) we have the following rules for editing here:
1. If no association exists we add the new one..
2. If more than 1 association already exists we just add the new one too.
3. If only one association exists we replace the old one.

I've found that the brands page is a great entry page as it contains many keywords for your site. I strongly recommend using this new facility.

Voucher & Discounts.

2005-06-08T13:30:00.000+01:00

v_code	is the code you pass on to your customers.
v_rate	Is a percentage discount from the total price (excluding P&P) (Or use v_amount)
v_amount	a fixed amount discount. (Or use v_rate)
v_usage	1 = individual; 2 = multiple. Indiviual means it can only be used once, such as a one of gift certifacte, 2 means it can be used many times.
country_id	If you want to limit the discount to a particilar country.
postage_zone	" " " postage zone
valid_from	Date in format YYYY-MM-DD
expiry_date	Date in format YYYY-MM-DD
minimum_order	If customer has to sepnd a certain amount (before p&p) to quality enter amount here.
valid	1 is valid, 0 turns the voucher off (in case of fraud)
v_method	1 - Discount. 2 - No discount: perhaps a free gift or to charity.
Comments


database.vouchers_tbl
+---------------+-----------------------+------+-----+---------+----------------+
| Field         | Type                  | Null | Key | Default | Extra          |
+---------------+-----------------------+------+-----+---------+----------------+
| id            | int(10) unsigned      |      | PRI | NULL    | auto_increment |
| v_code        | char(32)              |      |     |         |                |
| v_rate        | decimal(8,2) unsigned | YES  |     | 0.00    |                |
| v_amount      | decimal(8,2) unsigned | YES  |     | 0.00    |                |
| v_usage       | tinyint(3) unsigned   | YES  |     | 0       |                |
| country_id    | int(10) unsigned      | YES  |     | 0       |                |
| postage_zone  | tinyint(3) unsigned   | YES  |     | 0       |                |
| valid_from    | datetime              | YES  |     | NULL    |                |
| expiry_date   | datetime              | YES  |     | NULL    |                |
| minimum_order | decimal(8,2) unsigned | YES  |     | 0.00    |                |
| valid         | tinyint(3) unsigned   | YES  |     | 1       |                |
| v_method      | tinyint(3) unsigned   | YES  |     | 1       |                |
| comments      | char(255)             | YES  |     | NULL    |                |
+---------------+-----------------------+------+-----+---------+----------------+

UPDATE - Product Rules p_description_rule

2005-05-25T11:06:00.000+01:00

p_description_rule = Product Description Rule. Enter a value (1-255) to display that many characters of the products description in a gallery view. (0 displays the whole entry.)

Upload New Products - CSV - database table design

2005-05-16T13:00:00.000+01:00

The key tables for the adding a new product are given below.

In your CSV file you can add most fields (with the exception of IDs which are created automatically) and the admin_insert_csv_file.php page will take care of it.

A minimum amount of data would be:
product_code, product_name, price, gallery_id

If the "gallery_id" field is a numeric the system will add the product added to the gallery, if it is text it will look up the gallery_id, or if required, add a new gallery (always double check the text).

A more complex csv would be:
product_code, product_name, description, item_product_code, price, stock, size_id, colour_id, gallery_id, brand_id, image_use, filename, alt_text

As a shortcut for adding mroe than one image (a thumbnail and a display image) when adding a new product, you may use column headings of tb_filename and filename for each image on one line, thus:
product_code, product_name, description, item_product_code, price, stock, size_id, colour_id, gallery_id, brand_id, tb_filename, filename


product_tbl
+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
| id             | int(10) unsigned    |      | PRI | NULL    | auto_increment |
| product_code   | varchar(50)         | YES  | MUL | NULL    |                |
| product_name   | varchar(255)        | YES  |     | NULL    |                |
| description    | text                | YES  |     | NULL    |                |
| date_added     | datetime            | YES  |     | NULL    |                |
| product_status | tinyint(3) unsigned | YES  |     | 1       |                |
+----------------+---------------------+------+-----+---------+----------------+

product_details_tbl;
+--------------------+---------------------+------+-----+---------+----------------+
| Field              | Type                | Null | Key | Default | Extra          |
+--------------------+---------------------+------+-----+---------+----------------+
| id                 | int(10) unsigned    |      | PRI | NULL    | auto_increment |
| product_id         | int(10) unsigned    | YES  | MUL | NULL    |                |
| item_product_code  | char(50)            | YES  |     | NULL    |                |
| price              | decimal(8,2)        | YES  |     | NULL    |                |
| stock              | int(11)             | YES  |     | NULL    |                |
| size_id            | int(10) unsigned    | YES  |     | NULL    |                |
| colour_id          | int(10) unsigned    | YES  |     | NULL    |                |
| weight             | int(10) unsigned    | YES  |     | NULL    |                |
| height             | int(10) unsigned    | YES  |     | NULL    |                |
| width              | int(10) unsigned    | YES  |     | NULL    |                |
| depth              | int(10) unsigned    | YES  |     | NULL    |                |
| postage_local      | decimal(8,2)        | YES  |     | NULL    |                |
| postage_europe     | decimal(8,2)        | YES  |     | NULL    |                |
| postage_zone1      | decimal(8,2)        | YES  |     | NULL    |                |
| postage_zone2      | decimal(8,2)        | YES  |     | NULL    |                |
| next_delivery_date | datetime            | YES  |     | NULL    |                |
| unit_price         | decimal(8,2)        | YES  |     | NULL    |                |
| sold_by            | int(10) unsigned    | YES  |     | NULL    |                |
| barcode            | int(10) unsigned    | YES  |     | NULL    |                |
| details_status     | tinyint(3) unsigned | YES  |     | 1       |                |
+--------------------+---------------------+------+-----+---------+----------------+

image_tbl;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | int(10) unsigned    |      | PRI | NULL    | auto_increment |
| filename   | char(255)           | YES  |     | NULL    |                |
| product_id | int(10) unsigned    | YES  |     | NULL    |                |
| image_use  | tinyint(3) unsigned | YES  |     | NULL    |                |
| alt_text   | char(255)           | YES  |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+

gallery_tbl;
+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
| id             | int(10) unsigned    |      | PRI | NULL    | auto_increment |
| gallery        | varchar(255)        | YES  | MUL | NULL    |                |
| gallery_text   | text                | YES  |     | NULL    |                |
| gallery_image  | varchar(255)        | YES  |     | NULL    |                |
| gallery_rows   | tinyint(3) unsigned | YES  |     | 0       |                |
| gallery_cols   | tinyint(3) unsigned | YES  |     | 1       |                |
| default_text   | tinyint(3) unsigned | YES  |     | 1       |                |
| gallery_status | tinyint(3) unsigned | YES  |     | 1       |                |
+----------------+---------------------+------+-----+---------+----------------+

gallery_product_tbl;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned |      | PRI | NULL    | auto_increment |
| gallery_id | int(10) unsigned | YES  | MUL | NULL    |                |
| product_id | int(10) unsigned | YES  | MUL | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

brands_tbl;
+-------------------+---------------------+------+-----+---------+----------------+
| Field             | Type                | Null | Key | Default | Extra          |
+-------------------+---------------------+------+-----+---------+----------------+
| id                | int(10) unsigned    |      | PRI | NULL    | auto_increment |
| brand             | varchar(255)        | YES  | MUL | NULL    |                |
| brand_description | text                | YES  |     | NULL    |                |
| brand_image       | varchar(255)        | YES  |     | NULL    |                |
| brand_rows        | tinyint(3) unsigned | YES  |     | 0       |                |
| brand_cols        | tinyint(3) unsigned | YES  |     | 1       |                |
| brand_status      | tinyint(3) unsigned | YES  |     | 1       |                |
+-------------------+---------------------+------+-----+---------+----------------+

UPDATE - Product Page Image Pop-Ups

2005-05-13T11:45:00.000+01:00

The product page (product.php) has a new method of presenting pop-up images. We now call a new pape (popup.php) which will be sized to be a little larger than the largest image in the series and link to all the images associated with the product that aren't classified as thumbnails.

This means that if an image has a pop-up image, the new page will list first the pop-ups, then the inline, and finally the display image.

Each image will also have it's "alt text" listed underneath.

UPDATE - New customer search

2005-05-11T13:29:00.000+01:00

A new search has been added to the home page of the admin section.

Enter the customers name and the system will look up matches based on both the first_name and last_name fields. This is an inclusive search so will give as many matches as possible.

On the results page click "Orders" to display their adderss details and any ordes by the customer, click on their ID to edit.

Brands

2005-05-04T11:31:00.000+01:00

All products can be associated with a brand.

the easiest way is to initially set up the various brands, and then visit the "orphans" page and simply select the correct brand for each product.

Once the products have been associated your customers can view a range of products via the /onlinesales/brands.php page.

This defaults to being sorted alphabetically, but there is an option to also sort by date added with the newest first.

To link to the an individual brands and sort by date use the following link code, where the brand_id is the brand you want to display.


/onlinesales/brands.php?brand_id=5&od=1

TIPS: Product Search

2005-04-01T11:00:00.000+01:00

Admin >
Product/Category/Gallery >
Product Search

If using the product_code the system will actually search for both the main product code (product_tbl.product_code) and also the the individual items real product code (product_details_tbl.item_product_code). This allows the system to find individual items and display their parent product.

How-to: Change Prices, stock levels, sell by quantities etc (product details)

2005-04-01T10:46:00.000+01:00

Admin >
Product/Category/Gallery >
Product Search

Search for the product you want to update. See the "TIPS: Product Search" entry.

Once you have located the product click on the "Price" entry. This takes you to the "product details".

To recap, in the onlinesales system a single product can have several items within itself. So a product of "I Love NY T-Shirt" could have several items detailed within it for different colours & sizes. All of which will have their own prices, stock levels, sell by, item product codes (actual product code of that individual item) etc.

To edit an entry find the item you wish to change at the bottom of the page, enter the appropriate changes and click the edit button for that item. Each item needs to be updated separately.

UPDATE - New "Brands" reports.

2005-04-01T00:29:00.000+01:00

Two new reports. One will list all sales for each product in a brand, the other calculates the spend/sales on each brand.
Admin >
Report/Charts >
+ Product Chart by Brands
+ Chart: Brands

How-to Adding Products to a Brand

2005-03-30T16:10:00.000+01:00

Admin > Brands > Brands Products Orphans Form

This lists by default all the products that are currently 'orphans' (not associated with a brand, and have a product_status of 1 (Set to display).

Just select a "brand" and work your way though the product list.

Price routines

2005-03-29T13:27:00.000+01:00

Updated the price routines so that they can be called from pages outside the onlinesales directory. This allows branded pages to call the prices too and have the links to /onlinesales/view_cart.php work.

manufacturers.php

2005-03-29T12:17:00.000+01:00

The page manufacturers.php now has a 301 (redirect) sent to force the page to become brands.php. It also sends the 'manufacturers_id'. This will help search engines find the new page and also means we don't have to recreate all the links to the new brands page.

To Do List

2005-03-29T00:20:00.000+01:00

Please add your comments and I'll incorporate them into my list - or explain why not!

Gallery alignment options - allow tables/div to be created to verticaly align horizontal layout of galleries
Add hide to image status (so one can upload several images and then just turn some on and off at a whim to freshen the page)
Add comments to products

id
ip_address
user_id
comment
photo_id (seperate table)
status (on, off, pending)
date_posted
ordering (so editor can move posts up and down the order)
product_id
- Option - have to be registered to post (new flag in contact_tbl?)
- Option to registered gets posted, non registered require approval
- Option to approve all new messages
- Approve by click link in email (Don't have to log in)
- Upload images
  - Limit Image Size
  - Option to delay post until approval

UPDATE - Price routines

2005-03-24T15:12:00.000Z

I've updated the price routines, seperating them a little more from the presentation markup.

The first place you can see this will be on the brand page (see NPW Brands).

This price routine will also be used on the 'bespoke' best sellers pages soon too.

DEVELOPERS:
after getting the product_id call


include($_SERVER[DOCUMENT_ROOT]."/onlinesales/main_price_routine.php");

UPDATE - Manufacturers becoming Brands

2005-03-24T14:59:00.000Z

http://www.onlinesales.co.uk/onlinesales/manufacturers.php will become http://www.onlinesales.co.uk/onlinesales/brands.php

This was essentially to make it easier for me as I was getting sick of mistyping "manufacturers" in the database!

However, I've also taken the oppurtunity to add rows, columns, status (turn off a brand listing) and images.

Currently you can't chose the image for the brand, it will just use the thumbnail latest product added (that has an image). Naturally, the edit facility will follow. It will also (eventually) be possible to make it look like a normal shop gallery with rows and columns rather than just a list.

Long term I intend to have a separate brands_products page where one can look at the details for each product (like the current product page) but where the previous/next products are from the same brand rather than the gallery system we currently have.

This is all part of a long term goal where it will be possible for a shop to either display items in genre (like a normal retail shop) and/or in the brands (which is more appropriate to wholesale and certain ranges). There are other difficulties, for instance, with a large range we will need a way to subdivide it, but we can cross that bridge when we come to it.

How-to: Image Association

2005-03-24T14:27:00.000Z

Files are normally stored in the media directory of your website. We can associate images with products in two main ways.

Use an online form to upload a single file to the correct directory for you for each product.
FTP your image files to a target directory and then associate the images to products.

The online form method is useful for a small number of products, but when you are updating a large number the associative method is better and allows one to have great control of the target directories.

One has to first process the images and upload them to the media directory (or a sub folder from media) using a ftp program.

Associate Image
The process is fairly simple, first find your products; the most obvious method is via the main product list (product search) in the Product/Category/Gallery area.

1. Find your product
For recently added items you might like to search for the following:
Search: id (product id)
Operator != (NOT EQUAL)
Value leave the value box blank
Sort By date_added (Date the product was added)
Order By DESC (Descending – in this case newest first)
Limit 20 This would limit the restult to just 20 products,
With no value you will get ALL you products.

In the results click on the “Images” link.

You could also find products that are missing an image in the “Image Management System” area. Click on “Products Without Thumbnails” or “Products w/o Display Image”. This can be an intensive search so may take a few seconds to display the results. If you use this method I would recommend keeping the results in one window (or tab) and opening the ‘Product Images’ page by right clicking on the link and opening in a new window (or tab) so you don’t need to perform the data search again.

Product Images
This page (product_images.php) allows one to directly enter a filename (and path) for a product.

Some product details are listed such as product code, name and description to confirm that you are looking at the correct product and to help cut and paste for the alternate text that should be included with each image.

The first box is the image use:
· Thumbnail – the smaller image used in the gallery view.
· Display – The full sized image used on the product page
· Pop-up – alternative image, which will open in a new window if licked upon.
· Inline – Displayed on product page. Actual display is dependent on your own design and CSS file. Generally will appear under the main Display image and will align next to one another. So could be used to have one main Display image and two smaller thumbnails underneath.

The follows the filename:
The image file should always be in the media folder or a subfolder of media. You may use subfolders by including the path, so if one had an image called myimage.jpg in the mybrand folder of the media directory, www.onlinesales.co.uk/media/mybrand/ you would need to enter /mybrand/myimage.jpg in the filename box. Do not enter the full URL of the file, just the path beyond the media folder.

(It is recommended for simplicity to just upload files to the main media directory.)

Alt Text:
In this area enter some descriptive text of the image, this is useful for both search engines, browsers that have turned off images, and for use as links with the pop-up images.

Enter your data and hit submit.

If everything is okay, you should see the image listed in the details below the form, and that the Width and Height has values in it. If the Width and Height are blank then the filename/path are wrong and need to be verified. If that is the case click Edit and correct the error.

Add as many images as you want for the product, though obviously one thumbnail will only be displayed, and more than one display image will cause layout problems.

Notice the area just above the input form. This contains links to the previous and next products based on the product id.
<< previous product | next product >>

If you are updating several new products at once this can save you searching for each product individually.

How-to: Add A New Product - CSV method

2005-03-24T14:26:00.000Z

CSV Method

You can edit the file in most spreadsheet applications such as Microsoft Excel.

As this is a "Comma Seperated Values", please do not include commas in your file. You may edit the specific entries after if you wish to change the Product Name or Description.

The file contains the following headings:
product_code
product_name
description
price
stock
size_id
colour_id
weight
height
width
depth
postage_local *
postage_europe *
postage_zone1 *
postage_zone2 *
next_delivery_date
tb_filename
filename
gallery_id

* fields not yet used by system.

The fields should be fairly self-explanatory.

The price is input in decimal. 2.34 Do not include a currency symbol.

The stock level if given must be numeric. It can also be negative (out of stock and on back order) or zero. Depending on options in the administration sections your system may not display the data to customers, but you may find it useful.

The size_id and colour_id must be the id numbers as reported on their respective pages.

The weight field is in grammes, and may be used for postage calculations.

The height, width and depth are shown on some installations if the data is given.

The administration section optionally allows the next_delivery_date to be displayed on out of stock items.

tb_file_name is the filename of the thumbnail image used in the gallery view for a product, such as cat_a123_tb.gif

filename is the name of the display image as used by the product page.

Both images are uploaded the 'media' folder as specified in your administration section.

Please see the image naming conventions section for further advice.

The gallery_id is either a numeric id number or the name of a gallery. If a name is given, it will attempt to match it and return the existing id, otherwise a new gallery will be created. Matches have to be exact with case and punctuation.

To add new products from a csv file visit you administration section:
1. Product/Category/Gallery
2. Choose "Add New Products from CSV file"
3. Brose to find the updated csv form on your computer
4. Hit "upload"

The system will then attempt to add all the new products, and will add the relevant entires to the product-gallery relationship table, specific products details (such as pricing and sizes) and will add the image names if supplied.

The images themselves will now have to be uploaded separately, either via FTP or using the "Image Management System" section.

Image Naming Conventions

2005-03-24T14:25:00.000Z

For easy management name you images based upon their product name or code and use. On large installtions it can also be useful to have the product category.

I add a "_tb" to the end of the name part (before the file extension) for thumbnail images. The display images are left as is.

E.g, product_code_tb.gif, product_code.jpg

category_product_name_tb.gif

frying_pan_tb.gif
or
kitchen_frying_pan_tb.gif

As you may have several products with similar names, you may prefer to use the product code.
kitchen_12343_tb.gif

Note that all the image names are in lowercase. There are no spaces or non alpha numeric characters other than the underline.

Image Management System

2005-03-24T14:22:00.000Z

You can upload new files via the online form if you do not have easy access to an ftp programme.

Find the product in the product search page then click 'Upload' to store the new image.

Browse for the image on you hard drive, select the type (thumbnail, display, etc), add the 'alt_text' description.

TIP - If you've had included the image data in a new products CSV then there is no need to select the product or image use.

How-to: Add a New Product – Manual Method

2005-03-24T14:20:00.000Z

In Administation section visit the Product/Category/Gallery section.
Visit the "Product" page.
Add a product_code, name and description.

Click on the highlighted link to "Add / Edit details"

Enter the price, and any other details you require, such as colour and size. Please note, that in our system we give you the opportunity to price & give stock levels etc for each size/colour of a specific product separately. Click "Add New Detail For Product"

The item details for the product are listed at the bottom of the page, which you can edit or delete.

Each product needs to be in a gallery. From the left hand navigational menu select " Gallery Product Orphans" which will list the products not in a gallery

Just select the product you want, then the required "Gallery" and hit enter.
Now, in the Image Management Section, you can visit the " Products Without Thumbnails", and " Products w/o Display Image" pages to quickly upload a new image or associate an existing image.

The association section is that an image already exists on the server, it lists all products on one side and all images on the other of a form, and you can select the image type and associate an existing image with another product.