I am trying to create a dynamic magic parser application which will grab some values from a mySql table and then move thru these values to populate the Magic Parser code. Example:
function myRecordHandler($record)
{
global $counter;
global $link;
// change curso to the name of your database table, and the fields as required
// and add any more fields as required
$sql = "INSERT INTO tbl SET
id =field from query,
field1 ='".mysql_escape_string($record["FIELD1"])."',
field2 ='".mysql_escape_string($record["FIELD2"])."', etc..
MagicParser_parse("rss location from query","myRecordHandler","xml|CURRENT_OBSERVATION/");
the code will then loop thru the remaining records pulled from the query
Is this possible?
David-
The structure that you provided works great except I need to select data from table1 and insert the data into table2 using the unique id that is pulled from table1. SOmething like the following:
<?php
$link = mysql_connect("username","password","localhost");
mysql_select_db("database",$link);
function myRecordHandler($record)
{
global $counter;
global $link;
$sql = "insert into tbl2 SET
field1 ='".mysql_escape_string($record["FIELD1"])."',
field2 ='".mysql_escape_string($record["FIELD2"])."'
id = id from tbl2
mysql_query($sql,$link);
}
$sql = "SELECT urlstring,id FROM tbl2";
$result = mysql_query($sql,$link);
while($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
$rss = "http://www.example.com/feed.asp?id=".$row["urlstring"];
MagicParser_parse($rss,"myRecordHandler","xml|CURRENT_OBSERVATION/");
}
?>
Hi JP,
Your code isn't far off - but do you perhaps need to empty (TRUNCATE) tbl2 before the INSERTs, otherwise there would most likely be duplicate key errors; leaving you with the same (old) data. For example...
<?php
$link = mysql_connect("username","password","localhost");
mysql_select_db("database",$link);
function myRecordHandler($record)
{
global $counter;
global $link;
$sql = "insert into tbl2 SET
field1 ='".mysql_escape_string($record["FIELD1"])."',
field2 ='".mysql_escape_string($record["FIELD2"])."'
WHERE id = '".mysql_escape_string($record["ID"])."'";
mysql_query($sql,$link);
}
// empty tbl2
$sql = "TRUNCATE tbl2";
mysql_query($sql,$link);
// get list of request URLs from tbl1
$sql = "SELECT urlstring,id FROM tbl1";
$result = mysql_query($sql,$link);
while($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
$rss = "http://www.example.com/feed.asp?id=".$row["urlstring"];
MagicParser_parse($rss,"myRecordHandler","xml|CURRENT_OBSERVATION/");
}
?>
Hope this helps,
Cheers,
David.
David-
Thanks again for the prompt response. I am truncating table2 prior to executing the code and I may just update the table like you had had in the previous code. My main problem is how to call the current id while inserting data into the table.
Example:
field2 ='".mysql_escape_string($record["FIELD2"])."'
WHERE id = '".mysql_escape_string($record["ID"])."'";---> I need this to be the id from the select statement from tbl1
// get list of request URLs from tbl1
$sql = "SELECT urlstring,id FROM tbl1";---> THis is the ID that I need to be in the insert statement
$result = mysql_query($sql,$link);
while($row = mysql_fetch_array($result,MYSQL_ASSOC))
Am I missing something here? This ID is not contained within the XML and is pulled from tbl1 query.
THanks again for a great product and all of your help.
Hi JP,
I understand - I had assumed originally that the same ID was available as part of the response XML; however it's easy to access the original ID simply via a global variable.
Based on the code I posted above, the approach would be as follows:
<?php
$link = mysql_connect("username","password","localhost");
mysql_select_db("database",$link);
function myRecordHandler($record)
{
global $counter;
global $link;
global $id;
$sql = "insert into tbl2 SET
field1 ='".mysql_escape_string($record["FIELD1"])."',
field2 ='".mysql_escape_string($record["FIELD2"])."'
WHERE id = '".$id."'";
mysql_query($sql,$link);
}
// empty tbl2
$sql = "TRUNCATE tbl2";
mysql_query($sql,$link);
// get list of request URLs from tbl1
$sql = "SELECT urlstring,id FROM tbl1";
$result = mysql_query($sql,$link);
while($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
// populate $id variable so that it can be accessed within myRecordHandler
$id = $row["id"];
$rss = "http://www.example.com/feed.asp?id=".$row["urlstring"];
MagicParser_parse($rss,"myRecordHandler","xml|CURRENT_OBSERVATION/");
}
?>
That's all you should need to do!
Cheers,
David.
David-
THanks again for the help but I am still having an issue. I get the following php error:
Notice: Undefined variable: id
JP
Hi JB,
PHP should be giving you a line number along with that warning (it's not actually an error)...
Check that you have the
global $id;
line within your myRecordHandler function...
Cheers!
David.
Hello JP,
I understand what you're looking to do, but the structure is not really the way to go about this.
Instead, your database code to SELECT the id's (which you use construct the RSS location) should be outside the myRecordHandler function; and then within a loop you would call MagicParser_parse(); and the myRecordHandler function then updates the database. For example, it would look something like this:
<?php
$link = mysql_connect("username","password","localhost");
mysql_select_db("database",$link);
function myRecordHandler($record)
{
global $counter;
global $link;
$sql = "UPDATE tbl SET
field1 ='".mysql_escape_string($record["FIELD1"])."',
field2 ='".mysql_escape_string($record["FIELD2"])."'
WHERE
id = '".mysql_escape_string($record["ID"])."'";
mysql_query($sql,$link);
}
$sql = "SELECT id FROM tbl";
$result = mysql_query($sql,$link);
while($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
$rss = "http://www.example.com/feed.asp?id=".$row["id"];
MagicParser_parse($rss,"myRecordHandler","xml|CURRENT_OBSERVATION/");
}
?>
Hope this helps!
Cheers,
David.