Asked  7 Months ago    Answers:  5   Viewed   85 times

I'm trying to parse an XML file using PHP, but I get an error message:

parser error : Char 0x0 out of allowed range in

I think it's because of the content of the XML, I think there is a speical symbol "?", any ideas what I can do to fix it?

I also get:

parser error : Premature end of data in tag item line

What might be causing that error?

I'm using simplexml_load_file.

Update:

I try to find the error line and paste its content as single xml file and it can work!! so I still cannot figure out what makes xml file parse fails. PS it's a huge xml file over 100M, will it makes parse error?

 Answers

86

Do you have control over the XML? If so, ensure the data is enclosed in <![CDATA[ .. ]]> blocks.

And you also need to clear the invalid characters:

/**
 * Removes invalid XML
 *
 * @access public
 * @param string $value
 * @return string
 */
function stripInvalidXml($value)
{
    $ret = "";
    $current;
    if (empty($value)) 
    {
        return $ret;
    }

    $length = strlen($value);
    for ($i=0; $i < $length; $i++)
    {
        $current = ord($value{$i});
        if (($current == 0x9) ||
            ($current == 0xA) ||
            ($current == 0xD) ||
            (($current >= 0x20) && ($current <= 0xD7FF)) ||
            (($current >= 0xE000) && ($current <= 0xFFFD)) ||
            (($current >= 0x10000) && ($current <= 0x10FFFF)))
        {
            $ret .= chr($current);
        }
        else
        {
            $ret .= " ";
        }
    }
    return $ret;
}
Wednesday, March 31, 2021
 
JohnnyW
answered 7 Months ago
90
header('Content-type: text/html; charset=UTF-8') ;

/**
 * Encodes HTML safely for UTF-8. Use instead of htmlentities. 
 *
 * @param string $var 
 * @return string 
 */
function html_encode($var)
{
    return htmlentities($var, ENT_QUOTES, 'UTF-8');
}

Those two rescued me and I think it is now working. I'll come back if I continue to encounter problems. Should I store it in the DB, eg as "&" or as "&"?

Wednesday, March 31, 2021
 
ManojGeek
answered 7 Months ago
48

Answering my own question.

It seems it was not a phpmyadmin problem but a general encoding problem.

The problem was I was not setting my client connection charset.

here's what I got if I displayed the variables from a php file:

{"Variable_name":"character_set_client","Value":"latin1"}
{"Variable_name":"character_set_connection","Value":"latin1"}
{"Variable_name":"character_set_database","Value":"utf8"}
{"Variable_name":"character_set_filesystem","Value":"binary"}
{"Variable_name":"character_set_results","Value":"latin1"}
{"Variable_name":"character_set_server","Value":"utf8"}
{"Variable_name":"character_set_system","Value":"utf8"}
{"Variable_name":"character_sets_dir","Value":"/usr/share/mysql/charsets/"}



{"Variable_name":"collation_connection","Value":"latin1_swedish_ci"}
{"Variable_name":"collation_database","Value":"utf8_general_ci"}
{"Variable_name":"collation_server","Value":"utf8_general_ci"}

Initially i tried adding in my.cnf

[client]
default-character-set=utf8

IT DOES NOT WORK.

so the solution was to add:

charset=UTF8

in the PDO initialization

so after that I got the proper variable values:

{"Variable_name":"character_set_client","Value":"utf8"}
{"Variable_name":"character_set_connection","Value":"utf8"}
{"Variable_name":"character_set_database","Value":"utf8"}
{"Variable_name":"character_set_filesystem","Value":"binary"}
{"Variable_name":"character_set_results","Value":"utf8"}
{"Variable_name":"character_set_server","Value":"utf8"}
{"Variable_name":"character_set_system","Value":"utf8"}
{"Variable_name":"character_sets_dir","Value":"/usr/share/mysql/charsets/"}



{"Variable_name":"collation_connection","Value":"utf8_general_ci"}
{"Variable_name":"collation_database","Value":"utf8_general_ci"}
{"Variable_name":"collation_server","Value":"utf8_general_ci"}
Friday, May 28, 2021
 
MDDY
answered 5 Months ago
42

If you've copied this directly, then:

->setCellValue('B2', Ackermann') 

should be

->setCellValue('B2', 'Ackermann') 

In answer to your question:

Get the data that you want from limesurvey, and use setCellValue() to store those data values in the cells where you want to store it.

The Quadratic.php example file in /Tests might help as a starting point: it takes data from an input form and sets it to cells in an Excel workbook.

EDIT

An extremely simplistic example:

// Create your database query
$query = "SELECT * FROM myDataTable";  

// Execute the database query
$result = mysql_query($query) or die(mysql_error());

// Instantiate a new PHPExcel object
$objPHPExcel = new PHPExcel(); 
// Set the active Excel worksheet to sheet 0
$objPHPExcel->setActiveSheetIndex(0); 
// Initialise the Excel row number
$rowCount = 1; 
// Iterate through each result from the SQL query in turn
// We fetch each database result row into $row in turn
while($row = mysql_fetch_array($result)){ 
    // Set cell An to the "name" column from the database (assuming you have a column called name)
    //    where n is the Excel row number (ie cell A1 in the first row)
    $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $row['name']); 
    // Set cell Bn to the "age" column from the database (assuming you have a column called age)
    //    where n is the Excel row number (ie cell A1 in the first row)
    $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $row['age']); 
    // Increment the Excel row counter
    $rowCount++; 
} 

// Instantiate a Writer to create an OfficeOpenXML Excel .xlsx file
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); 
// Write the Excel file to filename some_excel_file.xlsx in the current directory
$objWriter->save('some_excel_file.xlsx'); 

EDIT #2

Using your existing code as the basis

// Instantiate a new PHPExcel object 
$objPHPExcel = new PHPExcel();  
// Set the active Excel worksheet to sheet 0 
$objPHPExcel->setActiveSheetIndex(0);  
// Initialise the Excel row number 
$rowCount = 1;  

//start of printing column names as names of MySQL fields  
$column = 'A';
for ($i = 1; $i < mysql_num_fields($result); $i++)  
{
    $objPHPExcel->getActiveSheet()->setCellValue($column.$rowCount, mysql_field_name($result,$i));
    $column++;
}
//end of adding column names  

//start while loop to get data  
$rowCount = 2;  
while($row = mysql_fetch_row($result))  
{  
    $column = 'A';
    for($j=1; $j<mysql_num_fields($result);$j++)  
    {  
        if(!isset($row[$j]))  
            $value = NULL;  
        elseif ($row[$j] != "")  
            $value = strip_tags($row[$j]);  
        else  
            $value = "";  

        $objPHPExcel->getActiveSheet()->setCellValue($column.$rowCount, $value);
        $column++;
    }  
    $rowCount++;
} 


// Redirect output to a client’s web browser (Excel5) 
header('Content-Type: application/vnd.ms-excel'); 
header('Content-Disposition: attachment;filename="Limesurvey_Results.xls"'); 
header('Cache-Control: max-age=0'); 
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 
$objWriter->save('php://output');
Wednesday, June 30, 2021
 
Jauco
answered 4 Months ago
55
$objPHPExcel->getActiveSheet()->setCellValue('H5', "HellonWorld");
$objPHPExcel->getActiveSheet()->getStyle('H5')->getAlignment()->setWrapText(true);

Works for me...

You should always use double quotes when you add escape sequences in a PHP string.

Wednesday, July 28, 2021
 
Jesse
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :