I’ve been busy tonight working on ChaosCollectibles, trying to get all of the products added. Since the first thing I did was grab all of the images, I was done “scanning.” Next, I needed to add them all to the site.
Dave had spent about 5 hours in total adding around 100 cards. We still had 140 to go, and let’s face it…I’m lazy. Well, not lazy. I just prefer scripting and saving time where I can. It’s like the old saying around work: “Work Smart.”
Since I had a standard naming convention for the image files, I was able to use those as a basis for my import. My hosting provider give me SQL access through PHPMyAdmin, so I can run SQL scripts…I just needed some SQL queries and stuff to run.
Product information is stored in 3 tables in the database for ZenCart. These tables are “products”, “products_description”, and “product_to_categories.” Products has a TON of columns. The others have 5 and 2, respectively. Now, as I was saying, I had a standard image naming convention. That convention was CTDOP_cardname.jpg.
Based on that, I could do some image manipulation, and fill out some basic data I needed for the product table. The products has a column called products_id that is auto-incremented. This column is referenced by the other two tables. To fill out the products table with the card-specific information, I needed the following:
Product name, image location, date added, model name, and a few other fields that I could have made static, but chose to use variables for. Since I used that handy naming convention above, I was able to manipulate the names of every image file into something I could insert into the database to keep with the other conventions. For example, my model name convention is CTDOPcardname (no underscores). My image location was…the name of the file. That’s easy enough. Then I had to format some dates, and output my SQL statement. So, I’m tired of blabbing. Here it is.
Edit: OK, I was having issues with my code formatting. I really need a new, code-friendly template. I’m open to suggestions.
Here’s the script: Add-Product.ps1 - PowerShell script to generate ZenCart products
So, you’ll be left with a long list of SQL statements. But, we’ve still got the other two tables. Import that list anyway. Make a note of the FIRST products_id that gets imported, after the import completes.
The next step involves checking that table you just imported all of those products into. Since products_id auto-increments, you need to see where you started, and where you finished and make a note of those numbers.
I’m going to end this one right here and add a second post. This is getting too long, I think. Check here.
Post a comment