Adding a column to one of the database tables

I'm needing to change the zen cart database slightly for stock choosing really. I need to be able to allocate a location reference to all the items in my stocklist. The way I want this to work is via the orders page in the admin panel. I want to add a text box next to each item in the order which will allow me to enter a location reference for that item. This ref no. will then allow my staff to find the items much easier in our warehouse.

Before I change anything I was looking for your input with this please. I was wanting to know whether it is as simple as adding another column to the orders table in the mysql database or if its more complicated and id I'd need to change more tables than just that one.

If anyone could give me some advice on this it would be much appreciated.

Thanks in advance.

follow this and you will have what you are looking for. it takes some time but is well worth it.

http://www.zen-cart.com/forum/showthread.php?t=105416&highlight=product+field+invoice

Thanks for the quick reply. I've had a look at the link you posted. Before I start this I just wanted to clarify a few things.

1. Which table would I be better adding the new column to, orders or orders_prducts?

2. If I add a new column to any of the tables will it cause problems with my shop running as this site is still live and I can't afford for it to screw sutff up on the front of the site or stop customers placing orders.

3. If I can get this to work with one extra column there's nothing really to stop my adding another few, right?

Thanks for the help

1. Which table would I be better adding the new column to, orders or orders_prducts?
   If you are not using the mdl# entry you could make this the location code
2. If I add a new column to any of the tables will it cause problems with my shop running as this site is still live and I can't afford for it to screw sutff up on the front of the site or stop customers placing orders.
   Never do development work on your live shop - - make a test shop with it's own DB for this and keep good notes/steps to repeat a successful alteration
3. If I can get this to work with one extra column there's nothing really to stop my adding another few, right?
   Adding "records" to a table is the easy part - - using the data and displaying the data becomes the code writing step(s)

sketchhgal:

Thanks for the quick reply. I've had a look at the link you posted. Before I start this I just wanted to clarify a few things.

1. Which table would I be better adding the new column to, orders or orders_prducts?

2. If I add a new column to any of the tables will it cause problems with my shop running as this site is still live and I can't afford for it to screw sutff up on the front of the site or stop customers placing orders.

3. If I can get this to work with one extra column there's nothing really to stop my adding another few, right?

Thanks for the help

1. You should read the link that I gave you and follow those instructions. You should be adding it to the zen_products table. Which is what I did.

2. Although its not recommended to do anything on live site, I was able to do this while my site was live and it did not have any problems affecting my site. Just be careful.

3. You can as much as you want or do whatever you like if you are good enough. Thats the beauty of zen and open source.

ebaobao:

1. You should read the link that I gave you and follow those instructions. You should be adding it to the zen_products table. Which is what I did.

2. Although its not recommended to do anything on live site, I was able to do this while my site was live and it did not have any problems affecting my site. Just be careful.

3. You can as much as you want or do whatever you like if you are good enough. Thats the beauty of zen and open source.

this link with crazy_chris info and my previous link will get you going.
https://www.zen-cart.com/forum/showthread.php?t=57924

The way I understood it, sketchhgal wanted to track extra information associated with each order, not each product, since the intent was to track warehouse locations. Also because an order can have many associated products. So wouldn't it make more sense to add the field to the orders table, not the products table? Or perhaps I misunderstood?

Apsona
http://apsona.com

As stated sketchhgal's aim is to display the new information with each order, but the data itself is associated with each product (or sku), so adding it to the products table would appear more logical than adding it to orders.

But the requirement does seem a little confused. The suggestion of a text box on the orders page for entering the information seems a little backwards, better to do a lookup on the product when it appears in an order.

Also it's not clear once the information has been entered or looked up what is to be done with it. Presumably it's intended to go on the packing slip, but if this is the intention, then it's the packing slip that needs to change and not the order page.

maybe sketchgal can be more clear with what she wants.

the way I have mines setup is like this.

1. able to enter location via admin > catalog > cats/products. the location field is like another field for the products attributes, etc.
2. then when order comes in and order is printed out via packing slip, the order is printed out with the location next to each product.

Ok, but if you put the field in the products table, what happens in the case where you have (say) five different orders pending for the same product? Each order would presumably need a different location for that product, but you can only have one location attached to the product.

Or maybe I completely misunderstood?

Apsona
http://apsona.com

Sorry if I haven't been clear with this. I really appreciate you all taking the time to look at this in the first place.

What I'm looking to do is to change the layout of the orders.php page in the admin section and also add a few new fields to it.

The new fields I need to add are as follows and they will show for each individual product on any order.

Item Location.
Comments
Stage 2
Shipped
In Stock

The following items will require to be text input boxes that will be filled in by an administrator and updated with a button somewhere on the page:

Item Location.
Comments
Stage 2
Shipped

The In Stock part I was looking to do a product lookup I guess to check and see whether the item is in stock or not. If its not instock I want it to show as a red dot.

I was also wanting to change the way an item is displayed on the orders.php page.

Instead of an item showing as follows:

**1 x 09-10 England home

• England Size: 38"
• Other Name & Number: John Martins 78**

I was wanting it to show like this:

09-10 England home (John Martins 78) Size:38

I have no idea what the code will be to achieve this because what I thought would work is doing nothing.

Here's what I was trying to use for that part:

if (($order->products[$j]['attributes']=='Other Name & Number')){
		  echo'(' . $order->products[$i]['attributes'][$j]['option'][$j]['value'] . ')';}

There are a few parts I'm struggling with.
I've got the orders page showing text input boxes for all the parts I need but I'm not sure how to link it in with the database so it doesn't wreck the rest of the site. I know I will have to create new columns in the database but I'm not sure where to put them and what files and scripts if any I will have to change so that it doesn't effect the rest of my sight in a negative way.

Can anyone point me in the right direction with any of what I'm trying to do please?

Any help as always much appreciated.

Thanks in advance

why do you want the location input fields to be in the orders.php? Why does a admin need to enter the item location when the order comes in? Why not already have the item location set for that item ahead of time? That way you don't have to keep entering in the location for every new order for the same item? Seems like unneccsary repeated work to me.

I have to agree that it is duplication for that part on that one I will look into getting that entered at another point in the admin process prior to any orders being made.

I still am unsure though as to how I would get that data stored in the database though? If I added a column to the product table would that work?

I've spoken to the owner of the site and unfortunately due to his lack of organisational skills I'm going to have to get the system to be able to take a location number at the order page in the admin panel. Is there anyone that can help me with the layout of the product on the order page please? It's cracking me up that I can't manage to get the size attaribute to display as I want it to!

as far as entering input fields and columns into the database, did you see crazy_chris posts on how to do that? His directions will give you information on how to add new columns into database. Since you want to update information associated with the orders, I think maybe you should look at how the update works with the comments field and status fields within the orders page when you click on update. That can give you an idea of how to do it. Since I didn't do any updates to orders page, I'm not sure how to do it.

I just don't understand why the person that creates the product in zen and enters its info cannot just enter the location info as well. its just a input box, type the number of location and click update and you have it set

Totally agree with you. The only reason I can find for his reluctance to do this is down to his poor levels of organisation. From what I can gather the same stock is not necessarily put in the one place each time a new supply comes in!

I'll have a go at the instructions from crazy_chris and see how I get on.

Thanks for the help I'll let you know how it goes.