CODEWITHSUNDEEP
phpsqljsonpdo
wawanopoulos
wawanopoulos

Reputation: 9794

Formatting JSON Object with PHP from SQL data

I am getting this result from my PHP script :

{
   "user":{
      "id":"1",
      "0":"1",
      "username":"test1",
      "1":"test1",
      "password":"pwd1",
      "2":"pwd1",
      "nom":"LUC",
      "3":"LUC",
      "prenom":"FOI",
      "4":"FOI",
      "ville":"Paris",
      "5":"Paris",
      "avatar":"avatar1",
      "6":"avatar1"
   },
   "annonces":[

   ]
}

As you can see, there is 2 problem : all keys in user are both. And annonces array is empty.

Here is my PHP script :

<?php
$PARAM_hote='aaaa'; 
$PARAM_port='3306';
$PARAM_nom_bd='bbbbbbb'; 
$PARAM_utilisateur='ccccccccc'; 
$PARAM_mot_passe='dddddddd';
// 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();

    // 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];

        // 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();

        // 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();
}

?>

Upvotes: 1

Views: 81

Answers (2)

MrCode
MrCode

Reputation: 64526

First issue is fetchAll() defaults to PDO::FETCH_BOTH which gets both the associative and numerical result. Change the call to fetchAll(PDO::FETCH_ASSOC) which will return associative keys only.

Second problem is this:

$id_user = $arrAllUser[0];

Here, $id_user is an array. To get the user ID for use in the other query, you need to:

$id_user = $arrAllUser[0]['id'];

Upvotes: 1

kskaradzinski
kskaradzinski

Reputation: 5084

  1. You have to use $result1->fetchAll(); with param PDO::FETCH_COLUMN
  2. Do You get any results from second query ?

Upvotes: 0

Related Questions