Replace special characters

[prova17]

Good evening!

 

I'm new in the forum and programming php!

I'm trying to create an application with jqmobile.

 

From the following I obtain results from db mysql but I obtain value "null" when there are special characters like ò, à, è, ù ...

 

Is it possible to replace the special characters?

 

THANKS!!!

<?php
include 'config.php';

$sql = "SELECT * FROM table_books WHERE info5 = 'thriller' ORDER BY id DESC";

try {
	$dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);	
	$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$stmt = $dbh->query($sql);  
	$employees = $stmt->fetchAll(PDO::FETCH_OBJ);
	$dbh = null;
	echo '{"items":'. json_encode($employees) .'}'; 
} catch(PDOException $e) {
	echo '{"error":{"text":'. $e->getMessage() .'}}'; 
}

?>

[sKunKbad]

You could do a preg replace for chars using the following regex:

 

$regex = '/[^\x20-\x7E\s]/';

 

You should also do something client-side so that the chars cannot be typed or pasted into the input fields.

[alpine]

In cases where the database charset is different from utf8 you should always encode the text before sending it through ajax, this is also the case with JQM as its no way to override utf8 charset unless you edit the JQM source

 

Replacing special chars is probably not your best option

// you have some control in this example, no special characters as we can avoid them
$js_return['status'] = 'saved';

// this however might contain special characters depending of db charset etc, convert it to utf8 before sending it
$js_return['text'] = utf8_encode($db_data); 

echo json_encode($js_return);

[prova17]

Thanks for help!

 

I tryed to insert the utf8_encode but I obtain the result "Undefined".

try {
	$dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);	
	$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$stmt = $dbh->query($sql);  
	$employees = $stmt->fetchAll(PDO::FETCH_OBJ);
	$dbh = null;
	echo '{"items":'.  json_encode(utf8_encode($employees)) .'}'; 
} catch(PDOException $e) {
	echo '{"error":{"text":'. $e->getMessage() .'}}'; 
}

If you need there is the .js file.

    var serviceURL = "/services/";

    var employees;

    $(document).ready (function() {
    getEmployeeList();
    });

    $(document).delegate('#employeeListPage','pageshow', function(event) {
         getEmployeeList();
    });


    $('#employeeListPage').bind('pageinit', function(event) {
       getEmployeeList();
    });

    function getEmployeeList() {
       $.getJSON(serviceURL + 'getemployees.php', function(data) {
          $('#employeeList li').remove();
          employees = data.items;
          $.each(employees, function(index, employee) {
             $('#employeeList').append('<li><a href="employeedetails.html?id=' + employee.id + '">' +
                   '<img src="img/' + employee.img + '" width="80px" height="80px" />' +
                   '<h4>' + employee.title + '</h4>' +
                   '<p>' + employee.info1 + '</p>' +
                   '</a></li>');
          });
          $('#employeeList').listview('refresh');
       });
    }

Somebody could help me?

Thanks!

[alpine]

You cannot utf8_encode an array, apply utf8_encode to the values (string)

[prova17]

Thanks!

 

I'm sorry I'm not an expert but my string should be this $employees = $stmt->fetchAll(PDO::FETCH_OBJ); ?

[alpine]

Something like this

function utf8_apply($val){
  return utf8_encode($val);
}

$employees = array_map("utf8_apply", $employees);

[prova17]

No way!

 

Do you have another exaple to insert in my code?
 

Thanks!!!

 

P.S.: I'm not an expert and maybe I'm doing something wrong, if is possible insert examples in my code

Really Thanks

[prova17]

I found a solution but I don't if it is the best.

I replace the special characters by SELECT REPLACE directly on the query.

 

$sql = "SELECT REPLACE(title, 'ò', '&ograve') as title, id, description, img, info1 FROM table_books WHERE info5 = 'thriller' ORDER BY id DESC";

 

Is it a good solution or not?

 

thank you!

[prova17]

I found a solution

 

I added --> array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'") and now it works

$dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));   

Now I have only a problem...When I see the description of the review book I see all the HTML tags (<p style="text-align: justify;">Trovarsi a leggere un libro noir scritto....) How can I avoid it?

 

Really thanks for all the people helped me!!

[jazzman1]

Use character setting instead a set names sql command:

$dbh = new PDO("mysql:host=$dbhost;dbname=$dbname;charset=utf8", $dbuser, $dbpass);

 

.When I see the description of the review book I see all the HTML tags (<p style="text-align: justify;">Trovarsi a leggere un libro noir scritto....) How can I avoid it?

 

Why do you want to store html markup inside DB and latter on you want to avoid of dispalying them? 

[alpine]

You shouldnt use json to transport html formatted content as it will most lightly break (as it does right now in your example - its json that breaks it), and if you are sending content to JQM you should send pure data and format it in JQM for best result. JQM can be a real pain to work with even if you try to do it according to standars, especially when moving outside utf8 (as some of us have to).

 

When working with mobile content you also want to send as little amount of data as possible between server and mobile unit for best possible performance and smallest data size.

 

This alltogether would save you from the trouble you are experiencing at this stage.

 

So if this is a new db/content setup you should reconcider storing pure data in db, if not you probably need to clean away any html before sending it through json_encode.

 

Your alternative is to drop json and send it as html through ajax.

 

Your best option is probably to run the query with default encoding and adapt the data with php before sending it.

Edited November 3, 2013 by alpine

[prova17]

Thanks for the advice but I tryed as you said and it doesn't work.

 

I have a question, is it incorrect use this code?

$dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));

About the HTML markup in the DB don't ask me why but I'm getting data from a CMS and save the HTML markup in the description.

I solved the problem in the js file using "html" insted "text".

function displayEmployee(data) {
	var employee = data.item;
	console.log(employee);
	$('#employeePic').attr('src', 'img/' + employee.img);
	$('#employeeTitle').text(employee.title);
	$('#description').html(employee.description);

Thanks!

[prova17]

Thanks alpine for the advice, unfortunately I'm not really an expert and I'm trying to learn as I can by forum and internet.

Hope what I did isn't included in "best practise" for an application using JQM and phonegap.

I installed in a mobile device and it work perfectly, I'm just scared it will be slow in the future when there will be more data in the db, unfortunately I can't change the db settings.

 

Thank you!

[jazzman1]

 

I have a question, is it incorrect use this code?

 

No, it's just fine