XML parsing and MySQL insert

[Carol Michael]

Hi
I am encountering a bizzare problem with a php script that I use to parse XML.Wonder if somebody can help.
I use a form generating software to create the forms I give to users. Once the users have entered the data and clicked the submit button on the form, this php script of mine will be called which parses the user data available in a post variable($_POST['data']. After parsing, I get all the field-value pairs in an array which I later use to generate the insert query. Most of the time this script works and the data is inserted into the table. However, I sometimes lose the data, and I am not able to figure out why it happens occasionally.
Following is the small php script I use:

<?php
require '../../connection.php'; //including the file containing the database connection code

//starting session
session_start();
$organisation_name = $_SESSION['sess_name'];


//initialise variables
$formname="";
$values=array();
$fieldname="";
$xmldata="";

try
{
$xmldata= rawurldecode($_POST["data"]);
if(empty($xmldata)
throw new Exception($organisation_name."--An exceptional error occured.Dataset empty");
}
catch(Exception $e)
{
error_log($e->getMessage(),1,"[e-mail]");
}


function dumpRecord($record)
{
$fpointer=fopen("temp/failed_data.txt", 'a');//opened for writing
fwrite($fpointer, "$record");
fclose($fpointer);
}


// initialize parser
$xml_parser = xml_parser_create();

// turn off whitespace processing
xml_parser_set_option($xml_parser,XML_OPTION_SKIP_WHITE, TRUE);
// turn on case folding
xml_parser_set_option($xml_parser, XML_OPTION_CASE_FOLDING, FALSE);

// set callback functions
xml_set_element_handler($xml_parser, "startElementHandler", "endElementHandler");
xml_set_character_data_handler($xml_parser, "characterDataHandler");



// called when parser finds start tag
function startElementHandler($parser, $name, $attributes)
{
global $currentTag, $fieldname,$formname;
$currentTag = $name;
$fieldname="";
if (strtolower($currentTag) == "fields")
{
$formname = $attributes["title"];


}
if (strtolower($currentTag) == "field")
{
$fieldname = $attributes["tag"];

}

}

// called when parser finds end tag
function endElementHandler($parser, $name)
{

}

// called when parser finds cdata
function characterDataHandler($parser, $data)
{
global $values, $fieldname;

if (trim($data) != "" && $fieldname != "")
{
$values[$fieldname] .= $data;


}

}
// error handler
if (!xml_parse($xml_parser, $xmldata) )
{
$ec = xml_get_error_code($xml_parser);
die("XML parser error (error code " . $ec . "): " . xml_error_string($ec) . "<br>Error occurred at line " . xml_get_current_line_number($xml_parser));
}

// all done, clean up!
xml_parser_free($xml_parser);

try
{
if(!$values)
throw new Exception($organisation_name."--An exceptional error occured.Array empty");
}
catch(Exception $e)
{

error_log($e->getMessage(),1,"[e-mail]");
}
//assign table name based on the title retrieved from xml
switch($formname)
{

case "form1":
$tablename = "table1";
break;
case "form2":
$tablename = "table2";
break;
case "form3:
$tablename = "table3";
break;


//a new case should be added for a new form
}
try
{
if(!$tablename)
throw new Exception($orgname."--An exceptional error occured.Table undefined");
}
catch(Exception $e)
{

error_log($e->getMessage(),1,"[e-mail]");
}


//INSERT RECORDS-WRITING THE VALUES FROM $VALUES ARRAY INTO THE DATABASE TABLE
try
{

$first=true;
$query = "INSERT INTO $tablename";
$query.= "(";

foreach($values as $name=>$value)
{

if($first==false) $query .= ",";
$first=false;
$query.= $name;
}
$query.= ") VALUES(";
$first=true;
foreach($values as $name=>$value)
{
if($first==false) $query .= ",";
$first=false;
$query.= "'". mysql_escape_string($value) . "'";
}
$query.= ");";

//print $query; //for debugging only
if(mysql_query($query)==false)//this will place the insert query in failed_date.txt' file if it fails
{

dumpRecord("\n"."OrganisationName:".$organisation_name." "."Xmldata:". $_POST['data']. "---".$query."\n".mysql_error()."\n". "Insert query failed.\n");
throw new Exception($organisation_name."--Insert Query failed");
}
}//end of try block
catch(Exception $e)
{
error_log($e->getMessage(),1,"[e-mail]");
}
//set session variable
$_SESSION['receiptno'] = $values['receiptno'];

// Build absolute path and go to page
header("Location: http://".$_SERVER['HTTP_HOST'].dirname($_SERVER['PHP_SELF'])."/showreceipt.php");
exit;

?>

As you can see I am writing to a text file, whenever insert fails and occasionally in the text file I get the error like

Xmldata:---INSERT INTO () VALUES();
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '() VALUES()' at line 1
Insert query failed.

I can understand that the array I use to generate the insert query is empty. The only reason I can think of is that the post variable $_POST['data'] is empty. However I do not understand why it is working sometimes and not all the time.The $_POST['data'] is generated by the form that is created using a proprietary software.

When I did all the testing , I never encountered an error like this in which the insert query is empty. This happens when users are submitting data, that also occasionally.

Can anyone advise, please. Any help would be much appreciated.

CMike



Edited 1 time(s). Last edit at 03/26/2010 12:07PM by Carol Michael.