Avatar of Morgan
Morgan
 asked on

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:

http://www.templatemonster.com/webapi/

I've tried using the method from the link below:
http://www.finalwebsites.com/scripts/import-templates.php

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.

Thanks,

neo
PHPXMLMySQL Server

Avatar of undefined
Last Comment
Ray Paseur

8/22/2022 - Mon
Ray Paseur

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.
Morgan

ASKER
Ray:

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.

Thanks,

neo
SOLUTION
purplepomegranite

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Morgan

ASKER
Purple:

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 either...so I don't know.

Thanks,

neo
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Morgan

ASKER
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.

Thanks,

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

<?php
set_time_limit(0);
ini_set('memory_limit', '1024M');

include_once('../db.php');
include_once(DOC_ROOT.'/include/func.php');

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) {
	//print_r($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;
	mysql_query($sql);
	//print_r($item->screenshots_list->screenshot);
	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, '');		
	}
	//print_r($item->pages->page);
	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 *****

<?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", 'mwbaggs@totaleeyou.com');
define("HOMEPAGE", 'http://www.totaleeyoustore.com/');


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


?>


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

<?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);
		break;
		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);
		break;
		case 'software_required_list':
		$sql = sprintf("REPLACE INTO software_required_list SET tpl_id = %d, software_id = %d", $tpl, $id_val);
		break;
		case 'sources_available_list':
		$sql = sprintf("REPLACE INTO sources_available_list SET tpl_id = %d, source_id = %d", $tpl, $id_val);
		break;
		case 'styles':
		$sql = sprintf("REPLACE INTO styles SET tpl_id = %d, style_id = %d, style_name = '%s'", $tpl, $id_val, $str_val);
		break;
	}
	//echo $sql;
	mysql_query($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)) {
			umask(0);
			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);
		curl_close($ch);
		if ($info['content_type'] == 'image/jpeg') {
			$handle = fopen($dest, 'w');
			fwrite($handle, $data);
			fclose($handle);
			chmod($dest, 0644);
			if (test_image($dest)) {
				return true;
			} else {
				return false;
			}
		} else {
			return false;
		}
	} else {
		return true;
	}
}

?>

Open in new window

ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Morgan

ASKER
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!

neo
Morgan

ASKER
Thanks.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Ray Paseur

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.
http://www.sitepoint.com/books/phpmysql4/
http://www.amazon.com/PHP-5-Practice-Elliott-White/dp/0672328887/ref=sr_1_3?ie=UTF8&s=books&qid=1299449144&sr=1-3

All the best, ~Ray
Morgan

ASKER
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!

thanks,

neo
Ray Paseur

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!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23