What are best practices for updating PS tables in phpmyadmin

[Whispar1]

Sorry, I do not know sql that well but I want to update some database tables in phpmyadmin using a csv file derived from an excel spreadsheet (mainly ps_stock_available AND ps_product_attribute) for inventory updates. What is the best way to accomplish this?

[Bill Dalton]

I hope you get lots of good answers.

 

But I think Microsoft Access is the best for updating data from a local source. Keep in mind I'm talking about updating data, not adding new records. When you add new records lots of related tables need to be updated as well. So you really need to know exactly what you are doing to add records.

 

That said, updating data, for examples inventory levels, you can use Microsoft Access to link to your excel spread sheet, (or any ODBC data), and link to your web site MySQL database. Update query's in MS Access are created visually, no coding required.

 

The advantage is that you do not need to upload any file to your web server.

It is very easy to setup.

 

Please see,

http://dev.mysql.com/doc/connector-odbc/en/connector-odbc-examples-tools-with-access-linked-tables.html

[Whispar1]

Thanks Bill. Firing up Access as we speak. I am using Office2010 and am having a hard time figuring out how to connect it to the server for my site Will keep plugging away.

[Bill Dalton]

Control Panel\System and Security\Administrative Tools\ODBC Data Sources (64-bit) or (32-bit)

Depends on which ODBC driver you have installed. If you haven't yet,
http://dev.mysql.com/downloads/connector/odbc/

 

Create a User account or a System account. I use "User" because no other machine can access it. If you have more than one user that will use the connection create a system account.

 

Data Source Name: (Can be anything, call it what you will)
Discription: (whatever)
Choose TCP/IP - your domain name
USER:
Database Name:
Password:

 

You also need to allow access to your MySQL database. I belive you mentioned that you used Shop Manager, so you should already have your permissions set. If the above dosen't work, then we may need to provide public access to your MySQL database.

[Bill Dalton]

For example here is the documentation for Store Manger Direct Connection to MySQL Server
http://store-manager-for-prestashop-documentation.emagicone.com/quick-start-guide

 

If you have that in place you are all set to go.

 

If not you may find this helpful,
http://www.youtube.com/watch?v=xXOq9U9fzOo

 

It is pretty much the same in PHPMYADMIN.
 

[Whispar1]

Yeah - having issues connecting. I added by IP through mysql remote and access is still denied. Host looked it over and said permissions are okay on their end so I am not sure what is going on... Should I look at setting up a mysql bridge?? this is squirly for me (pun intended)

Edited May 21, 2014 by Whispar1 (see edit history)

[Bill Dalton]

If permissions are setup ... and your host is sure that port 3306 is enable for their firewall for public access it should work.

 

Do you use Store Manger with a direct connection?

[Whispar1]

If permissions are setup ... and your host is sure that port 3306 is enable for their firewall for public access it should work.

 

Do you use Store Manger with a direct connection?

Well, they say it is enabled - not sure how to test it......

As far as SM, I was months ago and I do not have it on my machine any more. For some reason I think it was a bridge but honesty don't remember.

[Whispar1]

See anything off here? I have 3 drivers (sql and unicode) I can use if the ANSI is an issue

[Bill Dalton]

If you have windows telnet installed, (and who doesn't  ), just type in the run command,

telnet server_host 3306

If telnet hangs or the connection is refused, the port is blocked, if you get a connection and some garbage characters, the port is open.

[Bill Dalton]

Ok, that looks good, that is the response you should receive if proper privileges are not granted to the user. So you just need to give proper access.

 

I would say your local computer is set, you just need to give correct access to the user at your host. Your port access appears to be all setup.

Edited May 21, 2014 by Bill Dalton (see edit history)

[Bill Dalton]

Do you have SSH access?

[Whispar1]

Nope - port blocked with telnet... stay tuned

 

Not sure about SSH

Edited May 21, 2014 by Whispar1 (see edit history)

[Whispar1]

Okay Im in.... now what

[Bill Dalton]

 Now? It be careful what you wish for time.

[Whispar1]

Yeah no kidding! I assume I should be able to at least see my database files from within Access? I won't be linking anything until I am sure I won't blow anything up.

[Bill Dalton]

Open Access
Create New Blank Data Base

In your menu tab choose External Data.

Here you can see the choice for ODBC and Excel

 

You will use both of these to connect your data. Notice that on the left is for connecting to data, and on the right is for exporting data.

First connect to you local excel spread sheet, and just link to it don't import it. That way you can just replace the file with the same name going forward rather than again importing.

[Bill Dalton]

Do the same thing with ODBC, choose to link. A box will popup asking for file access, choose "Machine Access" and then choose the ODBC User you created above.

[Whispar1]

Do the same thing with ODBC, choose to link. A box will popup asking for file access, choose "Machine Access" and then choose the ODBC User you created above.

 

Good Lord!.... another error

[Bill Dalton]

Are you using a 64 bit Access application and a 32 bit ODBC driver?

 

Added: Or the other way around ...

Edited May 21, 2014 by Bill Dalton (see edit history)

[Whispar1]

Yup - 32Bit Access and 64 bit driver..... Downloaded correct version now all is well

[Dh42]

Sorry to jump in, but one thing I would suggest is to be careful. What I do when having to work directly in a database is to export the tables I am working in as a sql file, then as a sql.gz file. Then I export the whole database as a sql.gz file. I know it sounds like a lot, but I have had corrupted files in the past. It so I am careful. 

 

If I need to work with the data itself, I usually do those exports, then export it in a csv format and use open office to manipulate it, but usually I will just do it on the server using sql unless I have to merge other data. 

[Whispar1]

Yeah I appreciate the heads up. Will be careful for sure. And no, it's not a lot, especially when you have clients like you do - I would think they would appreciate the extra measure you take to make sure nothing blows up - especially on a live shop 

 

This all stems from the inventory update thread you commented on yesterday. With many of the tables, I like working with them directly through phpmyadmin. It's a lot quicker for some things than going through every item one by one in the BO.  For now, the only files I need to update are ps_stock_available but the only way I can update that is if I somehow tie in ps_product_attribute because it's the only table I have found that has both the reference AND product id.  The attached image is a little messy but gives an idea on what I am thinking... the blue arrows are the two columns that I need to update regularly. Make sense? 

[Bill Dalton]

Dh42 give great advice.

 

I personally have never had a problem using a database manager. After all, you are connecting to the MySQL database with the same method used by Prestashop Store Manger. Truly the most danger is not from data corruption, it is from the power you now have to manipulate data.

 

Making sure you have a daily backup is truly important. Also it wouldn't hurt to setup a test database to "learn" Microsoft Access. After you are sure your query's work as expected you can just copy and paste your query's from one database to another.

True be told, once you begin to make use of Microsoft Access, you just keep finding more and more uses.

[Whispar1]

As always, thanks for the help Bill... gonna sit down with a rye and coke and figure out how best to accomplish this... Canadian Mist of course

[Bill Dalton]

If you are not familiar with Microsoft Access Michael, you will now want to create a select query and add the PS tables you need. In your case, with what you want to do, add ps_product_attribute and ps_stock-available.

 

MS Access has a query Wizard that is good at some things such as making Cross Tab Query's, but for most query's you will make you should just do the following.
 

Choose Create

Choose Query Design

Next, drag and drop ps_product_attribute and ps_stock-available into your query designer.

At this point all that is needed to is create a join between the two tables on a field that both tables have in common. To create a join just click on a field in the left table and drag it to the matching field on the right hand table.
 

Drag and drop some of the fields down to the Field Grid.
 

Run the select query to make sure the result shows the data you want combined. Once you have the right combo of fields to your liking save the query. Best practices is to save a query with qry prefix. For example qryPSinventoryAMT. Call it whatever you like, but the name tells you it is a query, (useful when you use it in code) and gives you an idea of what it does. Believe me, you will forget in time.

 

Then do the same for your excel spread sheet. The reason you use a query for your excel spread sheet even though it is only one table is that allows you to add only the fields that are required and also to apply filters so that you can work with just subsets of the data.
 

After you have these two query's made, create a third query and add both of the new query's and again join on a common fields, that may be Reference number or UPC as the case may be.

That should produce a view of your data that contains both of your data sources and you will be ready to change the select query to an update query.
 

Edited May 21, 2014 by Bill Dalton (see edit history)

[Whispar1]

For some reason, I can not get a vlookup function to return a value other than NA. I am trying to link the reference number in my spreadsheet to the id_product_attribute from ps_product_attribute (downloaded as a csv and imported into a spreadhseet)..... I use vlookups all the time flawlessly - I have no idea why this is happening.... Does VB have a similar type function where I can link these items or is this a losing battle?

[Bill Dalton]

To create a join in MS, you drag the tables into the query designer, and than drag the field in table "A" you wish to join to the field in Table "B" that matches. I really can't explain it better than I already have in post #26.

[Whispar1]

Sorry Bill - just voicing a little frustration...Your instructions are quite clear - I just fixed the excel issue so am on my way... just have some bugs to work out. Now back to our regularly scheduled program.

[Bill Dalton]

Here is a video,