Post by jimbo208 » Wed May 25, 2011 8:38 am

Hi All! I'm new here but have been reading various posts for a while -

I am setting up a store that uses a drop ship supplier, meaning I don't have control of my stock. This means I need to keep the products on my site at least relatively up to date with their stock levels.

Fortunately, they provide an xml feed that has all the products listed along with the stock levels. I originally went about writing a script that simply looped through the feed and then updated the stock in product table - you can see a bit of it below:

Code: Select all

	
	// get xml file contents
	$xml = simplexml_load_file($url);
	
	//update product db
	foreach ($xml->products->product as $product) {
				
	$query = "
	UPDATE product 
	SET `quantity` = '$product->stock'
	WHERE `product`.`sku` = '$product->code';
		";
	$result = mysql_unbuffered_query($query) or die ("Error in query: $query. ".mysql_error());
		}
I had intended to simply set up a cron job and run this once or twice a day, unfortunately the feed is too big it seems - at over 3000 products, and it times out. I even tried echoing out the mysql queries and running in phpmyadmin directly, I calculated it would take nearly two minutes. Since im just on a shared host for now this can't be changed (I don't think?!).

Does anyone with more experience than me (im a bit of a newbie to php!) have an idea of how I could go about updating my stock levels? I thought about doing it every time a product page is loaded, just for that product but not sure where to start really - I guess the xml file would need to be downloaded to speed up the user experience.

Any tips are much appreciated!

Adult Toy Depot


Newbie

Posts

Joined
Wed May 25, 2011 8:19 am


Post by kkeith » Thu May 26, 2011 5:26 am

Have you tried putting:

Code: Select all

set_time_limit(0);
at the top of the page?

Newbie

Posts

Joined
Thu May 26, 2011 4:49 am

Post by jimbo208 » Sat May 28, 2011 5:00 am

thanks kkeith, I ended up writing the XML feed directly to a temporary, empty table and then updating the product and product options tables from that. This way it takes about 12 secs to run the script updating all 3000+ products, i'm pretty happy with that.

Adult Toy Depot


Newbie

Posts

Joined
Wed May 25, 2011 8:19 am


Post by jimbo208 » Sat Sep 03, 2011 10:50 pm

Someone just asked me how I did this, so I thought I would post the code here should it help any fellow noobs. I'm sure its not the best way to do it but it's straightforward and it works. Suggestions welcomed, I think a lot of people are trying to do this sort of thing with dropshippers.

I just run this on a cron job a couple of times a day, takes a few seconds to update and it keeps my db in good sync with my suppliers.

The basic process is:

Open connection to db
Get the contents of the xml file
Dump the SKU and stock level into a temporary table
Update the product table (and in my case the options table) with the temp table data

Code: Select all

<?php
	//error reporting 
	error_reporting(E_ALL);
	  
//connect to db
	$host = 'localhost';
	$user = 'root';
	$pass = '';
	$db = 'opencarttest';
	
	$connection = mysql_connect ($host, $user, $pass) or die
	        ("Error - Unable to Connect");
	
	mysql_select_db($db) or die ("Error- Unable to find database");
	
					
	// specify url of xml file
	$url = "http://example.com/example.xml";
		
	// get xml file contents
	$xml = simplexml_load_file($url);
	
	//clean temp table
	$query = "TRUNCATE stocktake;";
	$result = mysql_query($query) or die ("Error in truncate query: $query. ".mysql_error());
	
	//update product db
	foreach ($xml->products->product as $product) {
	$query = "
	INSERT INTO stocktake (sku, quantity)
	VALUES ('$product->code', '$product->stock')
			";
	$result = mysql_unbuffered_query($query) or die ("Error in query: $query. ".mysql_error());
		}
	//update product table
	$query = "
	UPDATE stocktake st, product p
	SET p.quantity = st.quantity
	WHERE p.sku = st.sku " 
	;
	$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());
	
	//update options table
	$query = "
	UPDATE stocktake st, product_option_value p
	SET p.quantity = st.quantity
	WHERE p.sku = st.sku " 
	;
	$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

		//close connection
	mysql_close($connection);	
	
  ?>

Adult Toy Depot


Newbie

Posts

Joined
Wed May 25, 2011 8:19 am


Post by Tee15 » Sat Jun 13, 2015 4:00 am

Hello and thank you so much for sharing your info and script!

I am in the same situation as you were when you made this post. This is all new to me, I am no programer and I am just trying to figure it out as I go. I have my product uploaded and need to integrate the dropshippers live data feeds to find a way to update my inventory.

Can I please ask you how did you integrate the dropshippers data feed to your site? I was going to upload the live link to my file manager via the control panel was just unsure how to install to my site after the upload. Did you use/purchase an extension? Also, where did you implement the script that you created?

I'm not even sure if you are still connected to the forum.... Just wanted to give it a try.

Thanks for any help you can offer.

Newbie

Posts

Joined
Sat Jun 13, 2015 12:50 am
Who is online

Users browsing this forum: No registered users and 11 guests