You are here:  » Populate Magic Parser with Data from Database


Populate Magic Parser with Data from Database

Submitted by jp_solspot on Thu, 2010-02-25 05:12 in

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?

Submitted by support on Thu, 2010-02-25 10:24

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.

Submitted by jp_solspot on Thu, 2010-02-25 14:36

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/");
  }
?>

Submitted by support on Thu, 2010-02-25 14:41

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.

Submitted by jp_solspot on Thu, 2010-02-25 15:33

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.

Submitted by support on Thu, 2010-02-25 15:39

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.

Submitted by jp_solspot on Thu, 2010-02-25 18:07

David-

THanks again for the help but I am still having an issue. I get the following php error:

Notice: Undefined variable: id

JP

Submitted by support on Thu, 2010-02-25 18:18

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.

Submitted by jp_solspot on Thu, 2010-02-25 21:43

Thanks David!