You are here:  » feed setup & checking feed value against mysql column


feed setup & checking feed value against mysql column

Submitted by keyesque2 on Mon, 2011-04-25 23:44 in

Background: Need to manually review a feed befor inserting values one by one in a table. Each is being hand-picked since almost all are irrelevant.

The insert is setup and working. However, been unable to set up feed for PRINT. This part is needed to manually view values prior to the individual inserts.

In addition to the feed/PRINT problem, I need to check the feed against those already table inserted - this, to give expirations by omission on the feed side.

The collection is soap, with source view: but the top of the page states: "This XML file does not appear to have any style information associated with it. The document tree is shown below."

Questions:

a) Must the collection be sent as RSS specifically to do feeds? Can it be done as soap?

c) Is there anything glaring wrong with the feed below? Can't get it to go and it doesn't appear to be an encoding issue (no spaces or special characters in $url) Am using lower case in another feed for the field names so I'm thinking this is okay.

b) In what form should the check be for mysql table against the feed?

WHERE JPosts2.JRef = JReference // to be compared with another JPosts2.JRef query

The insert presently works:

<?php
  require("../xmlParser/MagicParserForceUTF-8.php");
  $filename = "{link saved}";
  function myRecordHandler($record)
  {
$jref = ( $record["JREFERENCE"] );
    $jrefchoose = '45244921';
    if ($jref == $jrefchoose)
    {
  $sql = " INSERT INTO table SET
JTitle = '".mysql_real_escape_string( $record["TITLE"] )."',
JNumber = '".mysql_real_escape_string( $record["JREFERENCE"] )."'
";
  mysql_query($sql) or die(mysql_error());
  echo "Data Inserted";
    }
  }
  MagicParser_parse($filename,"myRecordHandler","xml|soap:Envelope/soap:Body/PostJob/Jobs/PostJob/");
?>

Though the feed is unsuccessful at:
 require("../xmlParser/MagicParserForceUTF-8.php");
  $url = "{link saved}";
  function myRecordHandler($record)
  {
    print "<br><a href='".$record["JReference"]."'>".$record["Title"]."</a>";
  }
  print "<strong>Jobs</strong><br>";
echo "<font size='2'>$url</font><br>";
   MagicParser_parse($filename,"myRecordHandler","xml|soap:Envelope/soap:Body/PostJob/Jobs/PostJob/");

Submitted by support on Tue, 2011-04-26 07:42

Hello keyesque,

For XML feeds, the field names are always upper case, so this line:

    print "<br><a href='".$record["JReference"]."'>".$record["Title"]."</a>";

...should actually be:

    print "<br><a href='".$record["JREFERENCE"]."'>".$record["TITLE"]."</a>";

So that should explain why nothing is displayed by the second script. Regarding altering you as to whether a record already exists, the following should work, based on the SQL from your INSERT script:

    print "<br><a href='".$record["JREFERENCE"]."'>".$record["TITLE"]."</a>";
    $sql = "SELECT JNumber FROM table WHERE JNumber =
              '".mysql_escape_string($record["JREFERENCE"])."'";
    $result = mysql_query($sql);
    if (mysql_num_rows($result))
    {
      print "<br />Exists!";
    }

Note that I added mysql_escape_string around the feed variables being used to construct the SQL - this is good practice with any externally sourced variable to ensure that its value does not break the SQL!

Hope this helps!
Cheers,
David.

Submitted by keyesque2 on Wed, 2011-04-27 01:10

David,

Don't know what I was thinking on the lower case field names...guess that's what happens when you don't verify other prior test feeds. Sorry.

About the second part -- works when the order is switched but I think you implied that with your answer (below, omitting the href stuff as well)

Thanks Again,

    $sql = "SELECT JNumber FROM table WHERE JNumber =
              '".mysql_escape_string( $record["JREFERENCE"])."'";
    $result = mysql_query($sql);
    if (mysql_num_rows($result))
    {
      print "<br>".$record["JREFERENCE"] ." ". $record["TITLE"]."<br>";
    }

Submitted by support on Wed, 2011-04-27 07:16

Hi,

No problem - if you wanted to reverse the sense and only see output for records that do not exist then you can use ! in front of mysql_num_rows() as follows:

if (!mysql_num_rows($result))
{
  print "<br>".$record["JREFERENCE"] ." ". $record["TITLE"]."<br>";
}

Cheers,
David.

Submitted by keyesque2 on Thu, 2011-04-28 02:13

David,

Is there a way, not to 'reverse the sense' but reverse the desplayed order of the fields in the latter example(s)?.

That would be from the page bottom to the top, DESC, instead of ASC

Submitted by support on Thu, 2011-04-28 08:04

Hi,

Sure - but it would involve loading the records you want to display into a global array (instead of displaying them) within myRecordHandler, and then after the parse reverse the array and display as before.

First, at the very top of your script:

$records = array();

Then, in place of the last snippet:

global $records;
if (mysql_num_rows($result))
{
  $records[] = $record;
}

And then at the bottom of your script (e.g. after the call to MagicParser_parse) then you can use this:

$records = array_reverse($records);
foreach($records as $record)
{
  print "<br>".$record["JREFERENCE"] ." ". $record["TITLE"]."<br>";
}

Hope this helps!
Cheers,
David.

Submitted by keyesque2 on Thu, 2011-04-28 14:33

To set this not equal to the table loaded fields, and that will return all-feed-results excluding this, while reversing the order -- this is maybe hanging from syntax and maybe not, around the array_reverse and can't discover why. Maybe I've jumbled this or misinterpreted it. Applying the curly brackets further makes me think there is something else going on:

$records = array();
$sql = "SELECT JNumber FROM table WHERE JNumber =
              '".mysql_escape_string( $record["JREFERENCE"])."'";
   $result = mysql_query($sql);
 global $records;
if (!mysql_num_rows($result))
{
  $records[] = $record;
}
function myRecordHandler($record)
$records = array_reverse($records);
foreach($records as $record)
    {
      print "<br>".$record["JREFERENCE"] ." ". $record["TITLE"]."<br>";
    }
 MagicParser_parse($filename,"myRecordHandler","xml|soap:Envelope/soap:Body/PostJob/Jobs/PostJob/");

Submitted by support on Thu, 2011-04-28 14:49

Hi keyesque,

Could you email me your full script and I'll arrange the sections as required...

Cheers,
David.

Submitted by keyesque2 on Thu, 2011-04-28 17:06

Sure we need this? There is other non-related script on the page, the actual url contains a username and password and I was trying to keep this simplified.

Submitted by support on Thu, 2011-04-28 17:12

Hi keyesque,

What you are wanting to do is quite straight forward, but requires changes in a few places, it was quite difficult to explain all that without seeing all the code.

But I only need to see the relevant parts - if you could post the complete relevant code (with any usernames or passwords hidden), but primarily the myRecordHandler functions and call to MagicParser_parse (or email if you prefer), I'll modify it as required as per the above for you...

Cheers,
David.

Submitted by keyesque2 on Fri, 2011-04-29 01:06

David,

Thank you, works nice!