PHP : SQL request works fine but return text = null for some element

Question

My PHP script works fine, but for some element, i get null values whereas in the table the text is well here :

[
   {
      "etat":"online",
      "nb_visiteurs":"0",
      "id":"1",
      "date_debut":"19\/05\/2014",
      "prix":"40",
      "description":null,
      "id_author":"1",
      "titre":null
   },
   {
      "etat":"online",
      "nb_visiteurs":"0",
      "id":"2",
      "date_debut":"21\/05\/2014",
      "prix":"30",
      "description":null,
      "id_author":"1",
      "titre":null
   },
   {
      "etat":"offline",
      "nb_visiteurs":"0",
      "id":"3",
      "date_debut":"22\/05\/2014",
      "prix":"23",
      "description":"TOP NOIR STYLE ASIATIQUE EN BON ETAT T 3\r\n\r\nFAIRE PROPOSITION",
      "id_author":"1",
      "titre":"Top noir style asiatique en bon etat t3"
   },
   {
      "etat":"online",
      "nb_visiteurs":"0",
      "id":"4",
      "date_debut":"22\/05\/2014",
      "prix":"45",
      "description":null,
      "id_author":"1",
      "titre":"Lit+sommier+matelas+ table de chevet+commode"
   }
]

Here is the content of my annonces table :

enter image description here

And here is the structure of the table :

enter image description here

<?php
$PARAM_hote='aaaaaa'; 
$PARAM_port='3306';
$PARAM_nom_bd='bbbbbbbbb'; 
$PARAM_utilisateur='ccccccccc'; 
$PARAM_mot_passe='dddddddddd';
// Create connexion to BDD
$connexion = new PDO('mysql:host='.$PARAM_hote.';port='.$PARAM_port.';dbname='.$PARAM_nom_bd, $PARAM_utilisateur, $PARAM_mot_passe);

try {

    // Getting username / password
    $username = $_POST['username'];
    $password = $_POST['password'];

    // Prepare SQL request to check if the user is in BDD
    $result1=$connexion->prepare("select * from tbl_user where username = '".$username."' AND password = '".$password. "'");
    $result1->execute();

    // Getting all results in an array
    $arrAllUser = $result1->fetchAll(PDO::FETCH_ASSOC);

    // If the size of array is equal to 0, there is no user in BDD
    if (sizeof($arrAllUser) == 0) { 
        echo "fail";
    }
    // In this case, a user has been found, create a JSON object with the user information
    else {

        // Getting id of the user
        $id_user = $arrAllUser[0]['id'];

        // Prepare a second SQL request to get all annonces posted by the user
        $result2=$connexion->prepare(" select * from annonces where id_author = '".$id_user."' ");
        $result2->execute();

        // Getting all annonces posted by the user in an array
        $arrAllAnnonces = $result2->fetchAll(PDO::FETCH_ASSOC);

        // Set in key user the USER structure
        $array['user'] = $arrAllUser[0];
        // Set in key annonces all annonces from the user
        $array['annonces'] = $arrAllAnnonces;

        // JSON encore the array
        $json_result = json_encode($array);
        echo $json_result;
    }

} catch(Exception $e) {
    echo 'Erreur : '.$e->getMessage().'<br />';
    echo 'N° : '.$e->getCode();
}

?>

volkinc · Accepted Answer · 2014-05-22 18:29:01Z

2

It's happening because french letters, the function fails to parse them. try to use flag JSON_UNESCAPED_UNICODE

json_encode($a, JSON_HEX_TAG | JSON_HEX_APOS | JSON_HEX_QUOT | JSON_HEX_AMP | JSON_UNESCAPED_UNICODE)

you can play with the flags

try this

$output = array();
foreach ($array as $row)
{
    $output[]=array_map("utf8_encode", $row);
}
 $json_result = json_encode($output);
 echo $json_result;

edited May 22, 2014 at 18:29

answered May 22, 2014 at 15:42

volkinc

2,1381 gold badge16 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

wawanopoulos Over a year ago

Same result with this flag.. Have you got other ideas?

volkinc Over a year ago

just for test purpose change you strings with french to "some text in English" to check if the french letters are a reason, than will see

wawanopoulos Over a year ago

Yes if i replace the french text by english text, my script works fine.

wawanopoulos Over a year ago

It's better ! Now i got all text but in the text i get "\u00e9".

volkinc Over a year ago

fr.openclassrooms.com/forum/sujet/…

xyzdev · Accepted Answer · 2014-05-22 15:54:43Z

agree with volkinc answer,if you have a new php version you can use

json_unescaped_unicode instead of json_encode

and if your php version 5.3 and older unescaped wont work , just add this function at the bottom of the script and use it instead of json_encode

function my_json_encode($in) { 
  $_escape = function ($str) { 
    return addcslashes($str, "\v\t\n\r\f\"\\/"); 
  }; 
  $out = ""; 
  if (is_object($in)) { 
    $class_vars = get_object_vars(($in)); 
    $arr = array(); 
    foreach ($class_vars as $key => $val) { 
      $arr[$key] = "\"{$_escape($key)}\":\"{$val}\""; 
    } 
    $val = implode(',', $arr); 
    $out .= "{{$val}}"; 
  }elseif (is_array($in)) { 
    $obj = false; 
    $arr = array(); 
    foreach($in AS $key => $val) { 
      if(!is_numeric($key)) { 
        $obj = true; 
      } 
      $arr[$key] = my_json_encode($val); 
    } 
    if($obj) { 
      foreach($arr AS $key => $val) { 
        $arr[$key] = "\"{$_escape($key)}\":{$val}"; 
      } 
      $val = implode(',', $arr); 
      $out .= "{{$val}}"; 
    }else { 
      $val = implode(',', $arr); 
      $out .= "[{$val}]"; 
    } 
  }elseif (is_bool($in)) { 
    $out .= $in ? 'true' : 'false'; 
  }elseif (is_null($in)) { 
    $out .= 'null'; 
  }elseif (is_string($in)) { 
    $out .= "\"{$_escape($in)}\""; 
  }else { 
    $out .= $in; 
  } 
  return "{$out}"; 
}

and use my_json_encode instead of json_encode and dont forget this line :

header('content-type:text/html;charset=utf-8');

thanks to the person who made this function i forgot from where i got it.

Collectives™ on Stack Overflow

PHP : SQL request works fine but return text = null for some element

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related