I'm trying to figure out how to build my script and need some help.
I'm using PHP 5 and MySQL 5.1.67
My goal is to be able to extract certain fields from the DB, reformat them in PHP and display them as a list. The reformatting will include HTML sequences. My goal is to have an automated CRON script from this which will automatically update the webpages.
I have very little experience with these languages. So any help would be good.
My logic is that I should load everything into one (mysql) table. My XML uses latin1 character set. Category, id, title, and description are each columns in the table.
Here is the mysql structure:
category mysql uses VARCHAR Latin1
site just used to group below
id mysql uses VARCHAR Latin1
title mysql uses VARCHAR Latin1
description mysql uses VARCHAR Latin1
The XML structure is as follows:
// XML Structure
// 23 categories to loop through
// hunderds of sites to loop through per category
//
<catalog>
<category>
<name>Category_Name</name>
<site>
<id>UR545665U</id>
<pagerank>1</pagerank>
<title>Title_Name</title>
<description>Description_of_the_site</description>
</site>
</category>
</catalog>
//
//
I've gotten to the point of loading my XML and DB using the below code.
//mysql connection
$con2 = mysql_connect("www.hosting.com","db_username","db_password");
if (!$con2) {
die('Could not connect: ' . mysql_error());
}
$dbcon1 = mysql_select_db("database_name", $con2);
if (!$dbcon1) {
die ('Can\'t use database_name : ' . mysql_error());
}
//simplexml load xml file with simplexml
$library= simplexml_load_file('feed.xml');
if ($xml === false) {
echo "Failed loading XML\n";
foreach(libxml_get_errors() as $error) {
echo "\t", $error->message;
}
}
Then here is where I'm having trouble... I need to loop through categories and within them loop through sites. The trouble I'm having is that I can't use the getName() function since <category>
and <site>
are not named witin the xml. So I'm using <name>
to identify the category since it is unique, and <id>
to identify the site and also unique.
So my logic here is to have a foreach() function within itself. Looping through categories and looping through each site within its category.
//begin loop each category and each site
foreach($library->xpath('/Catalog/Category/Name') as $category) {
foreach($library->xpath('/Catalog/Category/Name/Site/Id') as $id) {
$site = $library->xpath('//Site');
$title = $site->Title;
$description = $site->Description;
From this point, is the proper formatting of the sql process with mysql 5 proper escape sequences to avoid hacking.
The way I'm thinking of doing this is as follows:
// Format Query String into a variable
// Note: VALUES are in "" because they may contain strings
// sprintf() will run on each loop to format the new <site> string
$mynewquery = sprintf('REPLACE INTO Table_Name (id, title, description, category) VALUES (\"%4$s\",\"%6$s\",\"%7$s\",\"%3$s\")');
if ($mynewquery === false) {
echo "Failed formatting query string\n";
foreach(libxml_get_errors() as $error) {
echo "\t", $error->message;
}
}
//Run Query String to load data into DB
mysql_query($mynewquery);
if (!$mynewquery) {
die ('Error running Query: ' . mysql_error());
}
//
// close the loops and database connection after this.
I've used echo statements (not shown) to get feedback on the process. It goes all the way through loading the XML without error. My guess is that I have a syntax problem in the looping process. So I have a few questions:
- Is my logic correct?
- if the XML has a DTD referenced within it, is there any special coding I need to place in my script?
- Am I using proper variables and functions to accomplish my intent?
Any suggestions on how to make this work? I've tried it, but DB doesn't load the data.