Using a WebAPI to load MySQL database

I've signed up with template monster's affiliate program. And instead of using all the scripts provided by them (where I just make requests from my site to their server, which is not easy for SEO and can create lag (they say this even themselves)) I am wanting to use their webAPI to create my own database from theirs.

The web API specification can be found here:

I've tried using the method from the link below:

Now I've created the database "tmtemplates" and I've imported the .sql file for the categories on my server. Where I am having trouble is on how to export the data from their server and import the data into my database. The steps from the method i'm following are below:

1) Import the static data (types, categories, etc.) using the file "ImportDirInfo.php" script. All data is included in the single XML file called.

2) Import the bigger XML file for all the other template data, use the script called "ImportXmlInfo.php" DON'T try to run this script via the browser

3) Transfer the screenshots from template monster to your server using the script "ImportScreenshots.php".
Use the script "TestMissingImages.php" once to check/copy missing screenshots.

4) Run the Template Update script "DailyTemplateUpdate.php" once to get the latest templates
Now template monster states: WebAPI scripts for getting information about templates should be invoked at least once per 24 hours. The best time to invoke them is about 8.00 AM EST. Currency rate script (currency.php) should be invoked once per hour.

How is the above done (running the DailyTemplateUpdate.php"? I would assume there is a way to have the server do this automatically. Would be a pain in the butt to have to do this everyday manually.

Any help would be appreciated.


Who is Participating?
Ray PaseurConnect With a Mentor Commented:
The only thing I can suggest after seeing this script is that you need to add data visualization and error reporting to the code.  Many of the called functions give return values to indicate success or failure, and the code does not test for these conditions - it just proceeds as if everything worked perfectly every time.  

Add these two lines at the top of the script:

ini_set('display_errors', TRUE);

Then go through the script and remove all the @ notation from any functions (@ suppresses error messages, and you do not want to suppress error messages -- at this point you want to get all error messages and correct the errors).  Add code that tests for success and creates error notifications when they are needed.  In particular, calls to external routines are important.  MySQL is not a black box - it can and will fail for reasons that are outside your control.  In the code snippet is an example of how to test for success after you call mysql_query().  You should set up similar tests and diagnostics for the other functions that call external servers or operating system functions; some of these are listed below.

Fortunately, all of the PHP functions are documented in the online PHP man pages, so you do not have to guess about how to test for success.  Example:

Other functions that you might want to look up and learn about:

Once you have finished that, learn about PHP Security.  Just make a Google search for that term and read the top ten pages that come up in the response.  The mantra is always, "Accept Only Known Good Values."  If, for example, you expect a positive integer, test for a positive integer and do it before you use an external value in a data base query.  The same goes for any other external data.  Think you have an email address?  Validate it with PHP filter_var().

If this seems like a lot of work, that is because it is a lot of work -- work that requires specialized skills and a deep background of knowledge in PHP, MySQL and IT security.  To put it in context, the University of Maryland now offers a full time four year Bachelor of Science degree in IT security.  That's why I think you might be better off with a professional developer.  You could read several books and try to learn these things on your own, but your chances of missing something important are fairly large.

Best of luck with it, ~Ray
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

if (!$res)
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";

Open in new window

Ray PaseurCommented:
Got a "neglected question alert" on this one.  I can see why -- it's not a question, it is a requirement for application development.  You might want to hire a professional developer to help you with this.  Elance and other free-lance web sites have developers who could probably help.
MorganAuthor Commented:

Thanks for the reply. I think I'm getting it so far. I figured out I needed to have a SSH client to work with to do what is needed. So I'm slowly figuring it out. I donloaded and SSH client, had the SSH ability enabled through my host provider, and Have learned someof the basic commands. I've done some of the steps above and so far so good. The only hang up I'm having presently is importing the t_info.xml file. I keep receiving "segmentation fault" error (in my SSH client) when I try to running the php script: ImportXmlInfo.php

If i try to open the t_info.xml file on my machine here at home, before uploading to the server, I receive the error "Failed to open document". So I think the file is corrupted..but not sure, it maybe do to its size perhaps? Its 207 MB. But I don't know...would not think that would be to big of an issue.

If you have any thoughts they would be greatly welcomed. If not I'll go ahead and accept your comment above.


Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

purplepomegraniteConnect With a Mentor Commented:
What server are your scripts running on?

The best way for the updates is to set up a cron job.  One to run once daily to update the database, and one to run hourly to update the currency.  These should be set up on the server rather than trying to initiate them using SSH.

An alternative would be to run the cron jobs as a php script, but this would rely on the visitors to your site kicking them off, and has the downside that the visitor that kicked off the hourly or daily script would be the one to experience the delay as the script runs.
MorganAuthor Commented:

Thanks for the reply.

The scripts are run on the server hosting the site (these scripts are separate from the site itself, they are only being used to create the database). I was running these scripts this way to to initialize the database for the first time. I was going to use CRON once all this was done to do the updates every 24hours. As for the currency - I'm not to worried about as this is handled more through template monsters payment gate that my customers have to go through when buying a template. All my templates are going to be displayed in USD. So currency is not an issue.

Looking up the segmentation fault issue it seems to be a memory one (when looking it up online), so is the size of the t_info.xml the problem you think? Though I cannot open the t_info.xml file on my machine here at home I don't know.


MorganAuthor Commented:
Alright - I've been able to access the t_info.xml file now, either through notepad or on my browser through the server. However when I try to access the ImportXmlInfo.php file through the browser I get the error:
Chrome gives this:
 [an error occurred while processing this directive]

Explorer gives this:
HTTP 500 Internal Server Error

With these errors in mind I am now leaning towards the ImportXmlInfo.php script.

I've added the associated scripts below. If you all could take a gander that would be great.


**** ImportXmlInfo.php *****

ini_set('memory_limit', '1024M');


mysql_query("TRUNCATE screenshots_list");
mysql_query("TRUNCATE pages");
mysql_query("TRUNCATE page_screenshots");

$xmlstr = file_get_contents('t_info.xml');
$xml = new SimpleXMLElement($xmlstr);
foreach ($xml->template as $item) {
	$sql = sprintf("REPLACE INTO templates SET id = %d, state = %d, price = %d, exc_price = %d, inserted_date = '%s', update_date = '%s', downloads = %d, type_id = %d, type_name = '%s', is_flash = %d, is_adult = %d, width = '%s', author_id = %d, author_nick = '%s', package_id = %d, is_full_site = %d, is_real_size = %d, keywords = '%s', sources = '%s', description = '%s', software_required = '%s'", $item->id, $item->state, $item->price, $item->exc_price, $item->inserted_date, $item->update_date, $item->downloads, $item->template_type->type_id, $item->template_type->type_name, $item->is_flash, $item->is_adult, $item->width, $item->author->author_id, $item->author->author_nick, $item->package->package_id, $item->is_full_site, $item->is_real_size, $item->keywords, $item->sources, $item->description, $item->software_required);
	//echo '<br>'.$sql;
	foreach ($item->screenshots_list->screenshot as $scr) {
		$main = (!empty($scr->main_preview)) ? 1 : 0;
		$small = (!empty($scr->small_preview)) ? 1 : 0;
		insert_data($item->id, 'screenshots_list', 0, $scr->uri, $scr->filemtime, $main, $small);		
	foreach ($item->styles->style as $st) {
		insert_data($item->id, 'styles', $st->style_id, $st->style_name);		
	foreach ($item->categories->category as $cat) {
		insert_data($item->id, 'categories', $cat->category_id, $cat->category_name);		
	foreach ($item->sources_available_list->source as $so) {
		insert_data($item->id, 'sources_available_list', $so->source_id, '');		
	foreach ($item->software_required_list->software as $soft) {
		insert_data($item->id, 'software_required_list', $soft->software_id, '');		
	if (!empty($item->pages->page)) {
		foreach ($item->pages->page as $p) {
			mysql_query(sprintf("REPLACE INTO pages SET tpl_id = %d, name = '%s', id = NULL ", $item->id, $p->name));
			$page_id = mysql_insert_id();
			if (!empty($p->screenshots->scr)) {
				foreach ($p->screenshots->scr as $psc) {
					$href = (!empty($psc->href)) ? (string)$psc->href : '';
					mysql_query(sprintf("REPLACE INTO page_screenshots SET page_id = %d, description = '%s', uri = '%s', scr_type_id = %d, width = %d, height = %d, href = '%s'", $page_id, $psc->description, $psc->uri, $psc->scr_type_id, $psc->width, $psc->height, $href));

**** db.php *****

	define("DB_NAME", "---");
	define("DB_USER", "----");
	define("DB_PASSWORD", "----");

define("DB_SERVER", "localhost"); // most of the time it's localhost

define("AFF_ID", '-----');
define("AFF_PW", '-----');
define("PR_CODE", '-----');

define("DOC_ROOT", dirname(__FILE__));
define("THUMB_FOLDER", '/img/small/');
define("MAIN_FOLDER", '/img/main/');
define("INSTALL_FOLDER", ''); // if you install the site above the root, for example "/foldername" or empty!

define("SEARCH_PAGE", INSTALL_FOLDER.'/Example.php');

define("ADMIN_MAIL", '');
define("HOMEPAGE", '');

$conn_str = mysql_connect(DB_SERVER, DB_USER, DB_PASSWORD);
mysql_select_db(DB_NAME, $conn_str);


***** func.php ******


function insert_data($tpl, $table, $id_val, $str_val, $data = '', $extra1 = 0, $extra2 = 0) {
	switch ($table) {
		case 'categories':
		$sql = sprintf("REPLACE INTO categories SET tpl_id = %d, category_id = %d, category_name = '%s'", $tpl, $id_val, $str_val);
		case 'screenshots_list':
		$sql = sprintf("INSERT INTO screenshots_list SET tpl_id = %d, uri = '%s', filemtime = '%s', main_preview = %d, small_preview = %d", $tpl, $str_val, $data, $extra1, $extra2);
		case 'software_required_list':
		$sql = sprintf("REPLACE INTO software_required_list SET tpl_id = %d, software_id = %d", $tpl, $id_val);
		case 'sources_available_list':
		$sql = sprintf("REPLACE INTO sources_available_list SET tpl_id = %d, source_id = %d", $tpl, $id_val);
		case 'styles':
		$sql = sprintf("REPLACE INTO styles SET tpl_id = %d, style_id = %d, style_name = '%s'", $tpl, $id_val, $str_val);
	//echo $sql;

function test_image($path) {
	@$info_img =getimagesize($path);
	if ($info_img['mime'] == 'image/jpeg') {
		return true;
	} else {
		return false;

function create_Local_ImgPath($url) {
	$url_parts = parse_url($url);
	$file_name = basename($url_parts['path']);
	$haystack = $file_name;
	$needle = '-';
	$id = substr($haystack, 0, strpos($haystack, $needle));
	$in_folder = ($id >= 100) ? floor($id/100)."00/" : "0/";
	return array('file'=>$file_name, 'dir'=>$in_folder, 'full'=>$in_folder.$file_name);

function copy_thumbs($url, $folder) {
	$tPath_array = create_Local_ImgPath($url);
	$dest_folder = $folder.$tPath_array['dir'];
	$dest = $dest_folder.$tPath_array['file'];
	if (!test_image($dest)) {
		if (!is_dir($dest_folder)) {
			mkdir($dest_folder, 0755);
		$ch = curl_init($url);
		curl_setopt($ch, CURLOPT_TIMEOUT, 10);
		curl_setopt($ch, CURLOPT_USERAGENT, "Mozilla/5.0 (Windows; U; Windows NT 5.1; rv:1.7.3) Gecko/20041001 Firefox/0.10.1");
		curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
		curl_setopt($ch, CURLOPT_FOLLOWLOCATION, false);
		$data = curl_exec($ch);
		$info = curl_getinfo($ch);
		if ($info['content_type'] == 'image/jpeg') {
			$handle = fopen($dest, 'w');
			fwrite($handle, $data);
			chmod($dest, 0644);
			if (test_image($dest)) {
				return true;
			} else {
				return false;
		} else {
			return false;
	} else {
		return true;


Open in new window

MorganAuthor Commented:
Thanks ray :)

It was helpful. I would like to find a professional but I lack the funds. I generally only mess around with front end designs with little server scripting (generally only for online forms). I found some additionally info regarding my needs and going through it at present. I would agree about the error reporting and handling. The code provided was code I found online from the source stated in my original post - it does leave a lot to be desired. I am in the works right now in re-writing the code based on the new information found.

I understand it is a lot of work, but its not all that bad for me as it maybe for others - I do have a 4 year degree in Software Engineering, so the learning curve wont be to bad.

To all who posted I appreciate the feedback. I've assigned points to each according to the info provided.

Thanks and take care!

MorganAuthor Commented:
Ray PaseurCommented:
Thanks for the points.  With your background in SE, you will be able to make good use of these two books to get yourself up to speed very rapidly in PHP and MySQL.

All the best, ~Ray
MorganAuthor Commented:
Thanks for the book references. I actually went down and bought the book "PHP and MySQL Web Development" yesterday. I'll take a gander at the other two as well. The more the merrier!


Ray PaseurCommented:
Once you get a grip on the language syntax, you'll probably find Eli White's book to be a great asset.  I have read it cover to cover and often use it for a reference, as well.  Really well done!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.