close ad
Install the LAtest Updates to Work with CC 2017 and CC 2018
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

Wish List Database

Thread began 4/25/2014 4:40 am by sabastian130416448 | Last modified 5/07/2014 12:16 pm by Jason Byrnes | 1271 views | 24 replies |

sabastian130416448

Wish List Database

Hi guys.

I am trying to create a Wish List for my website but I have run into a little wall.

I understand from reading some of the other posts that I need to create a new Database Table that will store the product info and user info instead of pulling the info from the Database as in the case with the Add To Cart button.

My problem comes in that I have more than one Table in my Database where some of the products contain the same Product Id. The difference comes in the Description of each product so they are unique in their own way. I thought about this and came to the conclusion that to store the info in the Wish List Table I will have to assign a different ID to the products.

Up to here I am still ok my PROBLEM is that say I have a Table Called ProductsFruit and another Table called ProductsVeg. Now in Table ProductsFruit the first item is Apples with ID1, selling at $1.00. In Table ProductsVeg the first item is Carrots with ID1 selling at $1.20.

The client then adds 1 unit of Apples and 2 Units of Carrots to the Wish List. In the WishList Table the Carrots and the Apples are assigned new ID's because there can't be two ID1's, the Clients ID is saved and the Product Details are stored. Now if say tomorrow the Apples become $1.10 or even sells out, how will the data in the Wish List be updated?

How do I set the Wish List when a customer logs in to get the correct info from the correct table to find out if the price is still the same or if the product has maybe been sold out in the mean time? I can see how this will work if you are using only one table but I am not sure how to do this with different tables especially if the Product ID has to change when inserting products into the Wish List. I have about 20 Tables in my Database as each Table represents a different Company we sell products from.

Thanks for any help and advice.

Sign in to reply to this post

Jason ByrnesWebAssist

  I have about 20 Tables in my Database as each Table represents a different Company we sell products from.  



this is the heart of the problem and not a good way to set up the database.

you should have only one items table in your database.

in the items table, you can create a company column to define the company the item belongs to, much like you would do with category column.

your wish list table should store minimal data, a primary key ID, the Item ID from the items table, and the Users ID who added the item.

all of the specifics of the item should be stored in the item table.

If you need help in setting this up, we can assist through a premiere support appointment:
http://www.webassist.com/premier_request.php

Sign in to reply to this post

sabastian130416448

Hi Jason.

The problem is each company has about 500 plus different items in the Tables. We have prices that change on a regular basis as we import from the UK and we have to do a manual stock check as we have a retail store that sells using the same stock. Thus it will not be possible to have only one Table as I will search for hours to be able to get the product that I am looking for.

Is there no way to have the Wish List check the original Item to see if the price has changed or if the product is still in stock? I mean a company like Amazon or Ebay cant work with just one table and all the data is in there. That will cause things to become very slow when working with a database.

Sign in to reply to this post

Jason ByrnesWebAssist

Sure large companies can work with one table.

using a company column and a where clause in your SQL you can return only the records that belong to certain company from the one table

you could create the one table, then a view for each company, the view would make it seam as if there where a separate table when in actual fact is only one.

multiple tables that all do the same job is not an efficient way of structuring your database.

if you must go that way, then the wishlist is going take considerable hand coding to accomplish.

you would need to store the itemID, userID and company in the wish list table, and create 20 recordsets to return the items for each company

Sign in to reply to this post

sabastian130416448

Thanx Jason.

I will look more into this but it does seem like I will have to do the new Table route. Will also just be a whole lot of work to redo all my Recordsets on all my current pages. I am using SQL Manager to do my Database with so I will need to figure this whole thing out a bit.

Will using only one table not make your site load longer because it has to now go through all the Items to find the correct products for the correct page instead of as we have it now where I just tell the page (Recordset) which table to look in and which ones not to?

How do I create a "view" in SQL Manager for MySQL if you know how to do it?

Sign in to reply to this post

Jason ByrnesWebAssist

use indexes on identifying columns like the company column or the category column, using indexes will help the load times.

Sign in to reply to this post

sabastian130416448

What do you mean "indexes"? Sorry but I am not sure what you mean. Indexes on the php page itself?

Do you mean the "where" clause when setting up a recordset?

Sign in to reply to this post

Jason ByrnesWebAssist

google search:
mysql indexes


first result:
https://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

second:
https://dev.mysql.com/doc/refman/5.5/en/optimization-indexes.html

third:
http://www.tutorialspoint.com/mysql/mysql-indexes.htm

Sign in to reply to this post

sabastian130416448

Thank you. Will have a look at it and then get back to you if I get stuck.

Thank you for the help so long.

Sign in to reply to this post

sabastian130416448

Hi Jason.

I have created a single table with all my products in "product_range" and a table called "wishlist" where the wishlist info is to be stored. I have created a wishlist page that saves my productID, UserID and quantity in the table "wishlist".

The page enters the data correctly and it seems to work very well.

My problem now is that if the Wishlist cart is empty, how do I get the cart to pull the info from the "wishlist" table and get the correct product information from the "product_range" table?

If I use a recordset I get this error (Parse error: syntax error, unexpected T_OBJECT_OPERATOR in C:\xampp\htdocs\henkorsite\wishlist.php on line 60) and the coding on line 60 says if (WishList->IsEmpty() && isset($totalRows_Recordset1) && $totalRows_Recordset1 > 0) {

Thanks Jason

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...