Formatting JSON Object with PHP from SQL data

Question

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

?>

Why i'am getting 2 entries for each key in user key in JSON object ?
Why my annonces array is empty ? Is there any problem when i try to get the is of the user for running my second SQL request ?

MrCode · Accepted Answer · 2014-05-22 12:09:45Z

1

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

answered May 22, 2014 at 12:09

MrCode

64.6k10 gold badges95 silver badges114 bronze badges

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

Comments

kskaradzinski · Accepted Answer · 2014-05-22 12:07:53Z

0

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

answered May 22, 2014 at 12:07

kskaradzinski

5,10410 gold badges52 silver badges71 bronze badges

Collectives™ on Stack Overflow

Formatting JSON Object with PHP from SQL data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related