♥mdtaylorlrim Posted January 16, 2010 Posted January 16, 2010 Ok, I have tried in vain to produce a .sql file from phpMyAdmin that will do what I need to do without a lot of editing, and have decided that the best way to accomplish what I need to do it with a php script. But, as a perl programmer I need a little help with the php commands and format. So, what I am doing is building a sql file that I can turn around and load into a MSSQL database. So I have a table in the mssql database ready to accept data, and the scripts to get it out merged into a form that is usable for what I need. Now I just need to get the data out of the osC mysql database. And, I need a script that simply outputs the sql to the screen where I will copy it an paste it into the mssql query window. If anyone can follow this, and correct this to work I would really appreciate it. This can run in the admin directory and will output to the screen. I will be able to add a link in the Tools box to call it. <?php call a db connect here output html header here //the sql query is: $sql = select products_id, products_quantity, products_price, vendors_products_id from products where vendors_products_id is not null then loop throught the results... foreach $sql { echo "UPDATE my_table_name SET 'products_id' = " . $products_id . ", 'products_quantity' = " . $products_quantity . ", 'products_price' = " . $products_price . ", 'vendors_products_id' = " . vendors_products_id . ";" <- need line feed I think } // then maybe out put a link or button back the the admin index. ?> I'm gonna be working on this tonight and any shove in the right direction would be appreciated. And if anyone knows for certain a way to do this easier please feel free to yell out. Thanks M Community Bootstrap Edition, Edge Avoid the most asked question. See How to Secure My Site and How do I...?
♥mdtaylorlrim Posted January 16, 2010 Author Posted January 16, 2010 this is what I have so far... Anyone care to convert the text link to a button? <?php /* $Id: create_sql.php 1739 2007-12-20 00:52:16Z hpdl $ osCommerce, Open Source E-Commerce Solutions http://www.oscommerce.com Copyright (c) 2003 osCommerce Released under the GNU General Public License */ require('includes/application_top.php'); ?> <!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN"> <html <?php echo HTML_PARAMS; ?>> <head> <meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>"> <title><?php echo TITLE; ?></title> <link rel="stylesheet" type="text/css" href="includes/stylesheet.css"> </head> <body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF" "> <!-- header //--> <?php require(DIR_WS_INCLUDES . 'header.php'); ?> <!-- header_eof //--> <?php $sql_query = tep_db_query("select products_id, products_quantity, products_price, vendors_products_id from products where vendors_products_id is not null"); while ($results = tep_db_fetch_array($sql_query)) { echo "UPDATE my_table_name SET products_id = " . $results['products_id'] . ", products_quantity = " . $results['products_quantity'] . ", products_price = " . $results['products_price'] . ", vendors_products_id = " . $results['vendors_products_id'] . " where vendors_products_id = " . $results['vendors_products_id'] . ";<BR>"; } echo "<BR><BR><a href=\"https://mydomain.com/catalog/admin\">Return</a>" ?> <BR><BR> <?php require(DIR_WS_INCLUDES . 'footer.php'); ?> Seems to work just fine. Community Bootstrap Edition, Edge Avoid the most asked question. See How to Secure My Site and How do I...?
ozEworks Posted January 16, 2010 Posted January 16, 2010 Why not explain what you are actually trying to do? Someone might have done it before.
♥mdtaylorlrim Posted January 16, 2010 Author Posted January 16, 2010 Why not explain what you are actually trying to do? Someone might have done it before. I thought I had. But in more detail, I need to take four columns of the remote osC database and merge them into a local MSSQL database. After that i run a couple of queries and use the information retrieved. It all works fine with only one manual step, copy and paste the result text on the screen into a mssql query browser window. All that I really have left is the button and a lot of clean up on the script. But, if anyone has a method to read the four columns on the remote osC database and update the local MSSQL database all in one operation I am all ears. And for anyone needing the same, the mssql database is provided by a wholesale supplier. I added a column to the osC database to store the vendors_product_id so there is a common value between the two. I read the vendors_product_id, price, and quantity from osC, and put it into a new table in the mssql database. I then have a query that prints out the product_id, vendors_quantity_on_hand minus the osC quantity on hand, vendors_price * markup minus osC price. This returns a table showing me if I have something available in the store that the supplier is out of, and if my prices are at or above the cost+markup. If supplier is out of stock on something or if they go up in price on something I will know immediately. Not a big deal when the products are low cost or you stock inventory, but when the item cost upwards of $600 - $3000 and you do not inventory anything, and a price increase by a supplier that goes unnoticed, it can really ruin your day. Community Bootstrap Edition, Edge Avoid the most asked question. See How to Secure My Site and How do I...?
ozEworks Posted January 16, 2010 Posted January 16, 2010 If this is all for stock control why are you not simply checking stock levels in real time from your MSSQL database when people are shopping?
♥mdtaylorlrim Posted January 16, 2010 Author Posted January 16, 2010 If this is all for stock control why are you not simply checking stock levels in real time from your MSSQL database when people are shopping? Well, I would if I could figure out exactly how to do that. The MSSQL tables are updated by a program provided by the supplier and on my local windows machine. The osC tables are on a remote linux server. Wanna point me in a direction? Community Bootstrap Edition, Edge Avoid the most asked question. See How to Secure My Site and How do I...?
New 
Recommended Posts
Archived
This topic is now archived and is closed to further replies.