Page 1 of 2
trying to extract rows, create separate xml file for each
Posted: Fri Mar 31, 2006 3:55 am
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
Posted: Fri Mar 31, 2006 9:34 am
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.
Posted: Fri Mar 31, 2006 11:09 am
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
Posted: Fri Mar 31, 2006 11:37 am
by feyd
If you call it, yes.
Posted: Fri Mar 31, 2006 12:23 pm
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
Posted: Fri Mar 31, 2006 12:48 pm
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.
Posted: Fri Mar 31, 2006 12:56 pm
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!
Posted: Fri Mar 31, 2006 3:56 pm
by Sinemacula
feyd | Please use 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
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]
Posted: Fri Mar 31, 2006 4:23 pm
by feyd
- SQL has a syntax error.
- Your call to WriteToXML doesn't pass any variables, a warning or error should fire for that.
functionName(arg1, arg2, ..., argn);
Posted: Fri Mar 31, 2006 8:54 pm
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
But just as with the WriteToXML function, I'm not sure where to put the UPDATE query to make sure it happens properly.
Posted: Fri Mar 31, 2006 9:34 pm
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.
Posted: Sat Apr 01, 2006 12:37 am
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!
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
Posted: Sat Apr 01, 2006 3:00 pm
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
Posted: Sat Apr 01, 2006 6:11 pm
by feyd
have a look at the Zip class in Code Snippets.
Posted: Sat Apr 01, 2006 7:57 pm
by Sinemacula
Excellent... thank you - I got the directory zipped using the Zip class.
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!
Thanks,
Scott