select data from mysql using json

Question

I m developping a mobile application with Ionic. when I try to select data from mysql, it works in my browser but when I use an external server it doesn't work (on my phone )!!! can someone help me :

ExtractData.php :

<?php

header("Access-Control-Allow-Origin: *");

$request=json_decode(file_get_contents("php://input"));
$con=mysql_connect("","","") ;//or die(mysql_error());
mysql_select_db("myDbB",$con);

$qry_em_1 = "SELECT id,matricule,idTransporteur FROM camion";
$qry_em_2 = "SELECT id,matricule FROM bateau";
$qry_em_3 = "SELECT id,codeTransporteur FROM transporteur";
$qry_em_4 = "SELECT id,designation FROM trajet";

$qry_em_5 = "SELECT id,codeFr FROM fournisseur";
$qry_em_6 = "SELECT codeArticle,description FROM article";
$qry_em_7 = "SELECT id,description FROM moyenTransport";

$result_1 = mysql_query($qry_em_1) or die('Could not query');
$result_2 = mysql_query($qry_em_2) or die('Could not query');
$result_3 = mysql_query($qry_em_3) or die('Could not query');
$result_4 = mysql_query($qry_em_4) or die('Could not query');
$result_5 = mysql_query($qry_em_5) or die('Could not query');
$result_6 = mysql_query($qry_em_6) or die('Could not query');
$result_7 = mysql_query($qry_em_7) or die('Could not query');

  //encode camion data
if(mysql_num_rows($result_1)){
    echo '{"camion":[';

    $first = true;
    while($row=mysql_fetch_row($result_1)){

        if($first) {
            $first = false;
        } else {
            echo ',';
        }
        echo json_encode($row);
    }
    echo '],';
} else {
    echo '[]';
}

//encode bateau data
if(mysql_num_rows($result_2)){
    echo '"bateau":[';

    $first = true;
    while($row=mysql_fetch_row($result_2)){

        if($first) {
            $first = false;
        } else {
            echo ',';
        }
        echo json_encode($row);
    }
    echo '],';
} else {
    echo '[]';
}

//encode transporteur data
if(mysql_num_rows($result_3)){
    echo '"transporteur":[';

    $first = true;
    while($row=mysql_fetch_row($result_3)){

        if($first) {
            $first = false;
        } else {
            echo ',';
        }
        echo json_encode($row);
    }
echo '],';
} else {
    echo '[]';
}

//encode trajet data

if(mysql_num_rows($result_4)){
    echo '"trajet":[';

    $first = true;
    while($row=mysql_fetch_row($result_4)){

        if($first) {
            $first = false;
        } else {
            echo ',';
        }
        echo json_encode($row);
    }
    echo '],';
} else {
    echo '[]';
}

//encode fournisseur data
if(mysql_num_rows($result_5)){
    echo '"fournisseur":[';

    $first = true;
    while($row=mysql_fetch_row($result_5)){

        if($first) {
            $first = false;
        } else {
            echo ',';
        }
        echo json_encode($row);
    }
echo '],';
} else {
    echo '[]';
}

//encode article data
if(mysql_num_rows($result_6)){
    echo '"article":[';

    $first = true;
    while($row=mysql_fetch_row($result_6)){

        if($first) {
            $first = false;
        } else {
            echo ',';
        }
        echo json_encode($row);
    }
echo '],';
} else {
    echo '[]';
}

//encode transport data

if(mysql_num_rows($result_7)){
    echo '"moyenTransport":[';

    $first = true;
    while($row=mysql_fetch_row($result_7)){

        if($first) {
            $first = false;
        } else {
            echo ',';
        }
        echo json_encode($row);
    }
    echo ']}';
} else {
    echo '[]';
}

mysql_close($con);

?>

I think that the problem in Json format because when I seperate the objects in different file.php it works , but I want to use one php file

If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Aug 14 '15 at 12:21
okii I will use it .. thank u for your advice .. do you think that is the problem ?? .. @JayBlanchard — beginner, Aug 14 '15 at 12:29
No, we cannot tell what the problem is because there is no error checking. Have you looked at your server's error logs? — Jay Blanchard, Aug 14 '15 at 12:31
there is no error !! as I said before : it works on my browser while using localhost but when I put my file on a server it doesn't work anymore !! I m sure that the problem is in json because when I modify the file and let it select just one object (camion for example ) it works .. @JayBlanchard — beginner, Aug 14 '15 at 12:40
If you moved from localhost to another server and it stopped working there *is* an error somewhere. My bet would be that the version of PHP on the server does not support the `mysql_` API and it is throwing deprecated notices left and right. — Jay Blanchard, Aug 14 '15 at 12:46

Rafael Beckel · Accepted Answer · 2015-08-14T13:41:03.833

0

Like @jay-blanchard said, you should use a library like PDO to run your queries, or a full-fledged ORM like Doctrine.

In order to show your data as Json, you could use json_encode to do that for you, instead of trying to render your Json manually.

So, assuming you are using Doctrine, you could do something like that:

$camion       = $entityManager->createQuery($qry_em_1)->getArrayResult();
$bateau       = $entityManager->createQuery($qry_em_2)->getArrayResult();
$transporteur = $entityManager->createQuery($qry_em_3)->getArrayResult();
$trajet       = $entityManager->createQuery($qry_em_4)->getArrayResult();
$fournisseur  = $entityManager->createQuery($qry_em_5)->getArrayResult();
$article      = $entityManager->createQuery($qry_em_6)->getArrayResult();
$mTransport   = $entityManager->createQuery($qry_em_7)->getArrayResult();

return json_encode([
    'camion'         => $camion,
    'bateau'         => $bateau,
    'transporteur'   => $transporteur,
    'trajet'         => $trajet,
    'fournisseur'    => $fournisseur,
    'article'        => $article,
    'moyenTransport' => $mTransport   
]);

edited Aug 14 '15 at 13:41

answered Aug 14 '15 at 13:04

Rafael Beckel

2,199
5
25
36

This is more of a comment than an answer. – Jay Blanchard Aug 14 '15 at 13:10
@JayBlanchard You're right, I'll edit to add an example. – Rafael Beckel Aug 14 '15 at 13:19
thank u so much , it works now :) .. @RafaelBeckel – beginner Aug 15 '15 at 19:44
@beginner Glad to help, and welcome to StackOverflow! Please close this question by marking any answer as accepted. Cheers – Rafael Beckel Aug 16 '15 at 12:57
@RafaelBeckel where should I find this ?? – beginner Aug 16 '15 at 13:29
@beginner Take a look at this post: http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work#answer-5235 – Rafael Beckel Aug 16 '15 at 18:18

score 0 · Answer 2 · answered Aug 15 '15 at 19:51

problem solved :

I replace mysql_* to PDO :

<?php
require_once("Connexion.php");
header("Access-Control-Allow-Origin: *");

$request=json_decode(file_get_contents("php://input"));

$qry_em_1 = "SELECT id,matricule,idTransporteur FROM camion";
$qry_em_2 = "SELECT id,matricule FROM bateau";
$qry_em_3 = "SELECT id,codeTransporteur FROM transporteur";
$qry_em_4 = "SELECT id,designation FROM trajet";

$qry_em_5 = "SELECT id,codeFr FROM fournisseur";
$qry_em_6 = "SELECT codeArticle,description FROM article";
$qry_em_7 = "SELECT id,description FROM moyenTransport";

$result_1 = $db->prepare($qry_em_1) or die('Could not query');
$result_2 = $db->prepare($qry_em_2) or die('Could not query');
$result_3 = $db->prepare($qry_em_3) or die('Could not query');
$result_4 = $db->prepare($qry_em_4) or die('Could not query');
$result_5 = $db->prepare($qry_em_5) or die('Could not query');
$result_6 = $db->prepare($qry_em_6) or die('Could not query');
$result_7 = $db->prepare($qry_em_7) or die('Could not query');

$result_1->execute();
$result_2->execute();
$result_3->execute();
$result_4->execute();
$result_5->execute();
$result_6->execute();
$result_7->execute();

$camion=$result_1->fetchAll(PDO::FETCH_NUM);
$bateau=$result_2->fetchAll(PDO::FETCH_NUM);
$transporteur=$result_3->fetchAll(PDO::FETCH_NUM);
$trajet=$result_4->fetchAll(PDO::FETCH_NUM);
$fournisseur=$result_5->fetchAll(PDO::FETCH_NUM);
$article=$result_6->fetchAll(PDO::FETCH_NUM);
$mTransport=$result_7->fetchAll(PDO::FETCH_NUM);

echo json_encode([
    'camion'         => $camion,
    'bateau'         => $bateau,
    'transporteur'   => $transporteur,
    'trajet'         => $trajet,
    'fournisseur'    => $fournisseur,
    'article'        => $article,
    'moyenTransport' => $mTransport   
]);

$db = null;

?>

connection.php

<?php
$hostname = "";
$username = "";
$database="";
$password = "";

try {
    $db = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

}
catch(PDOException $e) {
    echo $e->getMessage();
}
?>

thank you guys for your advices

select data from mysql using json

2 Answers2