I have been lurking for a while and thought I would try and give something back to the community that has given me so much.
Due to my hosting company changing their setup, i am no longer able to establish a remote mysql connection to the opencart database for my store as they have de-activated it. This was presenting me with issues as I have always used my online stores for inventory & stock control.
Years ago I created some POS software for taking orders over the phone which i now populate by exporting the product & description tables from phpmyadmin. This ensures that all my products are in my software with the correct price etc. I do not add new products often so this is an acceptable compromise over the remote mysql connection that i had previously.
However, I sell up to 500 items a day over the phone and up to 1000 items on the various online stores that I run. So it is vital that the stock holding is correct and it was unrealistic to manually update my inventory after each phone call.
I did not want to move my webhost as I pay next to nothing and have too many websites / stores to migrate.
So After looking into my problem, I realised that a php script residing on the webserver can connect to the mysql databases using localhost.
I am well versed in Visual Basic but have never tried my hand at PHP before now.
I created the following script that can reduce my inventory after each sale by simply posting an http request directly to the script via a URL.
Step 1
Create a new folder called "update" on your webserver within the "public_html" directory
Step 2
Create a new file in the "update" folder and name it ".htaccess" insert the below code into it and save. This will stop someone from accidently stumbling upon this folder and viewing its contents
Code: Select all
Options -IndexesCreate a new file called "reduce_inventory.php" and insert the below code into it.
Code: Select all
<?php
// Get posted data into local variables
$username=$_GET['username'];
$password=$_GET['password'];
$database=$_GET['database'];
$id=$_GET['id'];
$qty=$_GET['qty'];
// Connect to DB
$location="localhost";
$conn = mysql_connect("$location","$username","$password");
if (!$conn) die ("Could not connect MySQL");
mysql_select_db($database,$conn) or die ("Could not open database");
// Select product table and reduce inventory quantity by posted quantity
$result = "UPDATE product SET
quantity= quantity - '".mysql_real_escape_string($_GET['qty'])."'
WHERE product_id = '".$id."'";
mysql_query($result) or die(mysql_error());
// echo out the query
if (mysql_affected_rows()==1){
echo "Success";
} else {
echo "Failure";
}
?>
Now you need to construct the url. If you followed the above instructions then then below should work for you:
The first part of the url should look like this
http://mywebsite.com/update/reduce_inventory.php
We then need to send the following variables:
N.B. This info can be found in your store config.php file
database - this needs to be the database name that the store uses mine is "store"
username - this needs to be a username that is assigned to the above database e.g "gaz"
password- this needs to be the password that is assigned to the above username e.g. "password"
id - this is the product id as seen in phpmyadmin that you wish to change e.g 200
qty - this is the quantity that you want to deduct from the current inventory level
Now all of these need to be joined together into a single url, after the initial url of the script you add a "?" symbol and each variable is dived by the "&" symbol. for example:
If you set it all up correctly and type your joined URL into your web browser, it should say "success". This means that it has reduced your stock level of the product you specified by the quantity that you specified.
You could save the database name in the php file but I use this script to update several different databases so it was better for me to define this in the url. The same can be said for the username and password but I prefer to send them with each url post. In my mind (although I'm normally wrong lol) this adds a little extra security as someone is not able to tamper with my inventory levels without this info.
Like I said, i use this in my POS software so I use Visual Basic to dynamically construct the url and send via an HTTP post. It then waits for the Success or Failure response and feeds back to my software accordingly. This means that it is done "behind the scenes" and does not open a web browser for each post.
I'm sure that for those of you that would find this useful, you have probably already done something similar but I thought it would be good for my first post if I shared a solution that was causing me alot of issues.
Cheers
Gaz
