trying to extract rows, create separate xml file for each

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

trying to extract rows, create separate xml file for each

Post by Sinemacula »

I'm trying get data from two tables of a database, and have an xml file created for each record.

I have tried putting something together based on a couple of different sources, but I can't get it to work - and my php and mysql skills aren't up to the task of figuing it out (I've been trying - but don't seem to be getting anywhere).

So, here's what I've got (it's probably a mess on several levels by now):

Code: Select all

<?php
$db_name = "xxxxxx";
$db_host = "xxxxxx";
$db_user = "xxxxxxx";
$db_pass = "xxxxxX";
for ($user_id = 1;
$db = mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name,$db);
$result = mysql_query("SELECT u.email AS `email`, c.firstname AS `firstname`, c.lastname AS `lastname` FROM mos_comprofiler AS c JOIN mos_users AS u USING(u.id = c.user_id)",$db);

while ($row = mysql_fetch_row($result))
{
$XMLpath = './';
$email = $row['email'];
$firstname = $row['firstname'];
$lastname = $row['lastname'];

function WriteToXML($email,$firstname,$lastname,$XMLpath)
    {    
    $filename = time();
    $fp = fopen("$XMLpath$filename", "a");
    $XML = '<?xml version="1.0" encoding="UTF-8"?>
    < !DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
    <plist version="1.0">
    <dict>';
    $XML .= "
            <key>defaultElectronicAddress.url</key>
            <string>$email</string>
            <key>firstname</key>
            <string>$firstname</string>
            <key>lastname</key>
            <string>$lastname</string>
            </dict>
    </plist>";
    $write = fputs($fp, $XML);
    fclose($fp);
    }
// end write to xml file

}

$user_id++);
?>
Any help at all sorting this out would be greatly appreciated.

Thanks!

Scott
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

the function isn't called, but it is redefined with each iteration of the loop. You need to pull it out of the loop and call it. Your for loop is malformed too.
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Post by Sinemacula »

Thanks for your reply, feyd... I'm afraid my php skills aren't good enough for me to figure out what to do based on your comment, though.

One thing I am wondering -- if I pull the function out of the loop, will it still create a separate xml file for each record?

Thanks,
Scott
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

If you call it, yes.
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Post by Sinemacula »

Okay, thank you. I think I can probably figure that part out...

Can you give me any hints about the malformation of the loop? I don't mind having to figure out the details, but I'm not quite sure what to look for...

Thanks,
Scott
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Your for loop has a large condition block, which although technically is allowed, will create a parse error due to how you built the logic. Here's the basic way you should build it:

for( setup expression; break expression; iteration expression)
{

iteration code
}


However, you have other problems in your code. Particularly, you don't appear to even use the for loop outside of creating an infinite loop out of it (probably on accident.) Also, I believe your SQL query has syntax errors regarding your usage of USING.
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Post by Sinemacula »

Thanks for pointing me in the right direction! I'll see what I can do from here.

As for the query, you're right -- I forgot I had changed that to ON to get it to work.

Thanks!
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Post by Sinemacula »

feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Okay, I think I'm getting closer...   

...but it's still not working. However, there aren't any errors showing up in the error log any more.

Can you give me a hint as to where I've messed it on this version?

Code: Select all

<?php
$db_name = "xxxxxxx";
$db_host = "xxxxxxx";
$db_user = "xxxxxxx";
$db_pass = "xxxxxxx";
function WriteToXML($email,$firstname,$lastname,$XMLpath)
{ 
$filename = time();
    $fp = fopen("$XMLpath$filename", "a");
    $XML = '<?xml version="1.0" encoding="UTF-8"?>
    < !DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
    <plist version="1.0">
    <dict>';
    $XML .= "
            <key>defaultElectronicAddress.url</key>
            <string>$email</string>
            <key>firstname</key>
            <string>$firstname</string>
            <key>lastname</key>
            <string>$lastname</string>
            </dict>
    </plist>";
    $write = fputs($fp, $XML);
    fclose($fp);
}
for ($i = 0; $i>1000; $i++)
{
$user_id = $i;
$db = mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name,$db);
$result = mysql_query("SELECT u.email, c.firstname, c.lastname AS Link FROM mos_comprofiler AS c, mos_users AS u WHERE c.user_id = u.id AND u.id = $user_id)",$db);

$row = mysql_fetch_row($result);
$XMLpath = '/Library/WebServer/xmlwebleads/';
$email = $row['u.email'];
$firstname = $row['c.firstname'];
$lastname = $row['c.lastname'];

WriteToXML();

}

$user_id = $i++;

?>
Thanks,
Scott


feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

  1. SQL has a syntax error.
  2. Your call to WriteToXML doesn't pass any variables, a warning or error should fire for that.
    functionName(arg1, arg2, ..., argn);
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Post by Sinemacula »

Thanks for all your help... I've got it working, just not quite exactly the way I want...

Instead of creating a separate xml file for each record, it's creating a single xml file, with all the records.

I assume that this means I've got something in the wrong order (I added some echo statements so I could verify the sql query while working on it - and also made some changes to the actual xml file, as the output turned out to be different than I needed):

Code: Select all

<?php
$db_name = "xxxxx";
$db_host = "xxxxxx";
$db_user = "xxxxxx";
$db_pass = "xxxxxx";
function WriteToXML($email, $firstname, $lastname, $XMLpath)
{ 
$filename = time();
    $fp = fopen("$XMLpath$filename.xml", "a");
    $XML = "{
   \"firstname\" = \"".$firstname."\";
   \"lastname\" = \"".$lastname."\";
   \"defaultElectronicAddress.url\" = \"".$email."\";
}";
    $write = fputs($fp, $XML);
    fclose($fp);
}

for ($i=1; $i<1000; $i++)
{
$user_id = $i;
$db = mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name,$db);
$result = mysql_query("SELECT t2.email AS `email`, t1.firstname AS `firstname`, t1.lastname AS `lastname` FROM mos_comprofiler AS t1 JOIN mos_users AS t2 USING(id) WHERE t1.user_id = $user_id",$db);
while ($myrow = mysql_fetch_array($result))
if (!$result) {
   echo 'Could not run query: ' . mysql_error();
   exit;
}
else {

echo "This is the email: " .$myrow["email"], ", is it not?<br />";
echo "This is the first name: " .$myrow["firstname"], ", is it not?<br />";
echo "This is the last name: " .$myrow["lastname"], ", is it not?<br /><br />";

$email = $myrow["email"];
$firstname = $myrow["firstname"];
$lastname = $myrow["lastname"];
$XMLpath = "/Library/WebServer/xmlwebleads/";

WriteToXML($email, $firstname, $lastname, $XMLpath);
}
}
?>
It looks to me like the WriteToXML file should be called between each record as it is, but since the output is all ending up in a single file, I'm wondering if I've called the function in the wrong place?

--edit -- addition --

In addition to getting it to create separate xml files, I'm going to need to figure out how to "mark" records as already processed. I'm thinking that I could add a new field to the mos_comprofiler table, and then update each record with a "1" in that field... then add to the SELECT query

Code: Select all

WHERE t1.processed != 1
But just as with the WriteToXML function, I'm not sure where to put the UPDATE query to make sure it happens properly.
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Post by Sinemacula »

A little trial and error, and I solved my own problem in terms of "marking" the records as processed.

So, now all I need is a bit of help figuring out how to get the WriteToXML function to create separate files for each record.

Here's the current, otherwise working script:

Code: Select all

<?php
$db_name = "xxxxxxx";
$db_host = "xxxxxxx";
$db_user = "xxxxxx";
$db_pass = "xxxxxx";
function WriteToXML($email, $firstname, $lastname, $XMLpath)
{ 
$filename = time();
    $fp = fopen("$XMLpath$filename.xml", "a");
    $XML = "{
   \"firstname\" = \"".$firstname."\";
   \"lastname\" = \"".$lastname."\";
   \"defaultElectronicAddress.url\" = \"".$email."\";
}";
    $write = fputs($fp, $XML);
    fclose($fp);
}

for ($i=1; $i<1000; $i++)
{
$user_id = $i;
$db = mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name,$db);
$result = mysql_query("SELECT t2.email AS `email`, t1.firstname AS `firstname`, t1.lastname AS `lastname` FROM mos_comprofiler AS t1 JOIN mos_users AS t2 USING(id) WHERE t1.user_id = $user_id AND t1.processed = 0",$db);
$processed = mysql_query("UPDATE mos_comprofiler SET processed=1 WHERE id = $user_id",$db);
while ($myrow = mysql_fetch_array($result))
if (!$result) {
   echo 'Could not run query: ' . mysql_error();
   exit;
}
else {
$email = $myrow["email"];
$firstname = $myrow["firstname"];
$lastname = $myrow["lastname"];
$XMLpath = "/Library/WebServer/xmlwebleads/";

WriteToXML($email, $firstname, $lastname, $XMLpath); 

echo "This is the email: " .$myrow["email"], ", is it not?<br />";
echo "This is the first name: " .$myrow["firstname"], ", is it not?<br />";
echo "This is the last name: " .$myrow["lastname"], ", is it not?<br /><br />";
$myrowproc = mysql_fetch_array($processed);
}
}
?>
I have tried moving the call to the function around, but I haven't had any success getting it to create separate files per record.
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Post by Sinemacula »

I discovered that once in a while when I ran the script, it would produce two files, usually with a few records in one, and most in the other... and it occured to me that the script was executing within a single value for "time()" so I added a "sleep(1);" statement and now it works fine.

Here's what I ended up with - if there are changes I could make to make it better, please feel free to let me know -- but at least it's producing the output I want! :D

Code: Select all

<?php
$db_name = "xxxxxx";
$db_host = "xxxxxx";
$db_user = "xxxxxx";
$db_pass = "xxxxxx";
function WriteToXML($email, $firstname, $lastname, $XMLpath)
{ 
$filename = time();
    $fp = fopen("$XMLpath$filename.xml", "a");
    $XML = "{
   \"firstname\" = \"".$firstname."\";
   \"lastname\" = \"".$lastname."\";
   \"defaultElectronicAddress.url\" = \"".$email."\";
}";
    $write = fputs($fp, $XML);
    fclose($fp);
}
for ($i=1; $i<200; $i++)
{
$user_id = $i;
$db = mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name,$db);
$result = mysql_query("SELECT t2.email AS `email`, t1.firstname AS `firstname`, t1.lastname AS `lastname` FROM mos_comprofiler AS t1 JOIN mos_users AS t2 USING(id) WHERE t1.user_id = $user_id AND t1.processed = 0",$db);
$processed = mysql_query("UPDATE mos_comprofiler SET processed=1 WHERE id = $user_id",$db);
while ($myrow = mysql_fetch_array($result))
{
$email = $myrow["email"];
$firstname = $myrow["firstname"];
$lastname = $myrow["lastname"];
$XMLpath = "/Library/WebServer/xmlwebleads/";

WriteToXML($email, $firstname, $lastname, $XMLpath); 
sleep(1);
$myrowproc = mysql_fetch_array($processed);
}

}

echo "Process complete.";
?>
Thanks for your help, feyd - your feedback did point me in the right direction!

Scott
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Post by Sinemacula »

Now that I've got the xml files all being created, and put on the server, is there an easy way to automatically download them all and then delete them from the server?

I thought perhaps I should zip the directory first... I found this code for a function, and I've added it to my script:

Code: Select all

function compress($srcName, $dstName)
{
  $fp = fopen($srcName, "r");
  $data = fread ($fp, filesize($srcName));
  fclose($fp);

  $zp = gzopen($dstName, "w9");
  gzwrite($zp, $data);
  gzclose($zp);
}

$date = date('Y-m-d');
// Compress a file
compress("/Library/WebServer/xmlwebleads/", "/Library/WebServer/xmldownload/xmlwebleads$date.gz");
Unfortunately, it seems it doesn't compress the whole xmlwebleads directory, which is what I would need. Is there a way to tweak this to do the whole directory? Or do I need to be looking for something else?

Then, of course, there's the downloading or emailing of the compressed file... but I'll settle for getting the compressed file working first.

Thanks,
Scott
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

have a look at the Zip class in Code Snippets.
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Post by Sinemacula »

Excellent... thank you - I got the directory zipped using the Zip class. :D

Now for my next steps...

1. I'm trying to figure out how to integrate the immediate download that's in zipcreate.ex2.php into what I've used from zipcreate.ex3.php to zip the directory... I can tell it's the "header" statements, but I'm not sure which variables to put in there.

2. I've also got to empty the source directory after it's been zipped.

I'll keep looking and doing trial and error - but as always, pointers are appreciated! :wink:

Thanks,
Scott
Post Reply