Krisz1 Posted July 22, 2010 Posted July 22, 2010 Hello guys ! I'm running OsC 2.2MS2 store for years, with very good results. Now that I bought some bar code readers, I'd like to give my staff a tool for them to scan prices quickly. I've tought of a single php file that can query OsC MySQL db for item price and description, just having the product code (model). Having very little knowledge of sql structures, specifically how OsC handles it's db records, I ask for your kind help into creating this tool. Something like: Product code: (entry field). ---------------------------------------------------------- Qty Product Name Prod Price Link to picture ----------------------------------------------------------- x yyyy zzzzz Link a bbbb ccccc Link Thanks a lot!
MrPhil Posted July 23, 2010 Posted July 23, 2010 Could you tell us how you envision using these bar code scanners? I presume that you have physical inventory that you are scanning UPC (?) codes on. How do you plan to get this scanned UPC number out of the scanner to a PC and then to your server? Are you looking to display a price for an item (looking up data from the osC database, given some ID code)? Do you want a price from the database as soon as your employee scans the product? At the very least, you'll need tables or files to map scan codes (e.g., UPC numbers) to osC's product IDs or some kind of model number. I don't see how you are going to do this in "live" mode (instantly get the price as it's scanned), unless you create a remote database connection from your server database to a PC that interfaces to the scanners, that could match the UPC code to a product model or ID and from there query the price. I take it you're opening a bricks-and-mortar physical store and want to have the same prices as you do online? Frankly, you might find it easier to have your prices controlled on the local PC (that connects to your scanners) and use that to create a CSV file to periodically update your eStore prices via EasyPopulate, rather than trying to keep a live connection between your local PC and the server database. I'm trying to think of any other scenario where you would want to get the retail price of an item immediately -- even for inventory control and valuation (for taxes), you would first count up all the units and then multiply by the per-unit value (not necessarily the retail price). For telephone sales, you would just use a catalog (or even ring up the sales on osC as you make a sale over the phone, with a special "customer" account for each salesperson, or create an account for the customer on the spot, so you could accept payment).
Krisz1 Posted July 23, 2010 Author Posted July 23, 2010 Hi Mr. Phil, thanks for your reply, Here is what I'm trying to do: Each product in OsCommerce has it's own model/ProductID or sometimes called in code products_model . I will print out bar codes that contain the specific model of each product. Ej: A001. This is the products_model in OsC db and in the products. There is no specific mapping as the codes within the db are the same that I'm printing out. This .php will be hosted in the same site (hosting) as the OsCommerce instalation, thus, it can access the OsC database. I load a URL like www.myoscommerceshop.com/PriceQuery.php I scan the code [MODEL] Then the script does the following (I'm no expert on MySQL syntax but you can get the idea) select FROM OscDB where products_model = MODEL, then return product.name + product.price + product.image So I get pulled out from OsC DB the name, description and file image of the product. Of course I'm skipping all the login credentials sent to the DB. For each scanned code, create a new row with product data. QTY MODEL PRODUCT_NAME PRODUCT_DESC PRICE IMG 1 A001 name description 9.99 [img_link] 3 A002 name description 2.99 [img_link] total 4 12.98 If code is scanned twice, raise qty to 2... etc At the end, add up all totals and prices to get the estimated purchase value. Rather than inventory, this is useful to some employees that want to get a quick total of products, just like a customer of a supermarket wants to know how much he has to pay. Thanks for your input.
MrPhil Posted July 24, 2010 Posted July 24, 2010 Well, the SQL would be something like SELECT pd.name as name, p.products_price as price, p.products_image as image FROM products p LEFT JOIN products_description pd on (p.products_id = pd.products_id) WHERE p.products_model='$model_code'; Naturally you would have to first map the scan code to the model code, or something else that's in the database. You might have to experiment a bit, doing the SELECT in phpMyAdmin's SQL tab to get it right. You should get one record back $query = "SELECT ..."; $result = mysql_query($query); $row = mysql_fetch_array($result); $row['name'] will be the name, etc. You would probably have an array keyed on model number, and if you already have scanned this model, increment its count, and if not, add an entry to the array with quantity 1. Then when they're done scanning and want the list, you print out the contents of the array.
Krisz1 Posted August 11, 2010 Author Posted August 11, 2010 Well, the SQL would be something like SELECT pd.name as name, p.products_price as price, p.products_image as image FROM products p LEFT JOIN products_description pd on (p.products_id = pd.products_id) WHERE p.products_model='$model_code'; Naturally you would have to first map the scan code to the model code, or something else that's in the database. You might have to experiment a bit, doing the SELECT in phpMyAdmin's SQL tab to get it right. You should get one record back $query = "SELECT ..."; $result = mysql_query($query); $row = mysql_fetch_array($result); $row['name'] will be the name, etc. You would probably have an array keyed on model number, and if you already have scanned this model, increment its count, and if not, add an entry to the array with quantity 1. Then when they're done scanning and want the list, you print out the contents of the array. Thank you very much !
♥bruyndoncx Posted August 11, 2010 Posted August 11, 2010 interesting discussion, I was thinking of a barcode scanning application eg on iphone to scan a barcode and get the inventory count. We use the products_id number as the barcode. We print this number on the labels, so when the scanner has trouble reading the code, it is also fast to key in the number. Anyone else thinking along those lines too ? KEEP CALM AND CARRY ON I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support). So if you are still here ? What are you waiting for ?! Find the most frequent unique errors to fix: grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt
Recommended Posts
Archived
This topic is now archived and is closed to further replies.