Solved

Using a WebAPI to load MySQL database

Posted on 2011-03-03
11
1,321 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Morgan
  • 6
  • 4
11 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 35036995
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.
0
 
LVL 1

Author Comment

by:Morgan
ID: 35038286
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
0
 
LVL 24

Assisted Solution

by:purplepomegranite
purplepomegranite earned 50 total points
ID: 35039488
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.
0
 
LVL 1

Author Comment

by:Morgan
ID: 35039673
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
0
 
LVL 1

Author Comment

by:Morgan
ID: 35040262
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

0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 108

Accepted Solution

by:
Ray Paseur earned 450 total points
ID: 35046330
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);
error_reporting(E_ALL);

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:
http://us3.php.net/manual/en/function.mysql-query.php

Other functions that you might want to look up and learn about:
mysql_real_escape_string()
mysql_connect()
mysql_select_db()
file_get_contents()
curl_exec()
fopen()
fwrite()
chmod()

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
// CREATING AND SENDING A SELECT QUERY AND TESTING THE RESULTS
// MAN PAGE:http://php.net/manual/en/function.mysql-query.php
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS

Open in new window

0
 
LVL 1

Author Comment

by:Morgan
ID: 35047381
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
0
 
LVL 1

Author Closing Comment

by:Morgan
ID: 35047408
Thanks.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 35048137
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
0
 
LVL 1

Author Comment

by:Morgan
ID: 35048474
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
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 35057550
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!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql select time convert to  time to   a   b mysql 4 23
XSLT Help 12 20
update field on focusout 15 22
php variable basic question 12 27
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now