Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

You're looping through the xml elements, but not referring to the loop variables at all. You need something more like this:

foreach($library->Category as $category) {
    foreach($category->Site as $site) {
        $id = $site->Id;
        $title = $site->Title;
        $description = $site->Description;

        //- insert into db here
    }
}
share|improve this answer
 
Thanks. I'm still not getting any load into the DB. Perhaps there is something wrong with my code. I've verified my DB connection properties and table name and it is all good... so it is either the MYSQL statement is incorrectly formatted, or the PHP code suggested above isn't looping through correctly. Any suggestions? –  Tony Feb 17 '13 at 16:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.