php/mysql - while loop ..pass result in array?

Question

my title is confusing..sorry about that..anyway..kindly look at my code:

<?php
include('../connectDB.php'); //connect to db

echo '{ ';
    echo '"success": 1, ';
        echo '"result": [ ';
            $result = mysql_query("SELECT * FROM roominventory");
            while($row = mysql_fetch_array($result)) { //start while
                $getId = $row['id']; //get value
                $getRoomId = $row['room'];

                echo '{ ';
                    $ar = $row['arrival']; //assign value to variable
                    $dep = $row['departure'];

                    $date = str_replace('-', '/', $ar); //format the date
                    $formatArr =  date('m/d/Y', strtotime($date));

                    $date2 = str_replace('-', '/', $dep); //format the date
                    $formatDep =  date('m/d/Y', strtotime($date2));

                    $mSt = strtotime($formatArr) * 1000; //convert to milliseconds
                    $mEd = strtotime($formatDep) * 1000;

                    echo '"id": ' . '"' . $getId. '"' . ', ';

                    $resulta = mysql_query("SELECT * FROM rooms_amenities WHERE id='$getRoomId'");
                    while($rowa = mysql_fetch_array($resulta)) {
                        $getName = $rowa['name'];
                    }

                    echo '"title": ' . '"' . $getName . '"' . ', ';
                    echo '"url": ' . '"' . 'http://example.com' . '"' . ', ';
                    echo '"class": ' . '"' . 'event-warning' . '"' . ', ';
                    echo '"start": ' . '"' . $mSt . '"' . ', '; //echo the converted date
                    echo '"end": ' . '"' . $mEd . '" ';
                echo '} ';
                echo ', '; //comma (should only in between entries and not in the very end of the very last entry)
            } //end while
    echo '] ';
echo '}';
?>

now this is the result of the file:

{ "success": 1, "result": [ { "id": "254", "title": "Standard Room 202", "url": "exampledotcom", "class": "event-warning", "start": "1470693600000", "end": "1471384800000" } ] }

no problem about that. now, problem is that when there's more than 1 row in the table in my db the result becomes like this:

{ "success": 1, "result": [ { "id": "255", "title": "Standard Room 201", "url": "exampledotcom", "class": "event-warning", "start": "1471903200000", "end": "1472076000000" }, { "id": "256", "title": "Standard Room 202", "url": "exampledotcom", "class": "event-warning", "start": "1471903200000", "end": "1472076000000" }, ] }

notive the "comma" at the last entry "1472076000000" }, ] }

what my desired/expected result should be like this:

{ "success": 1, "result": [ { "id": "255", "title": "Standard Room 201", "url": "exampledotcom", "class": "event-warning", "start": "1471903200000", "end": "1472076000000" }, { "id": "256", "title": "Standard Room 202", "url": "exampledotcom", "class": "event-warning", "start": "1471903200000", "end": "1472076000000" } ] }

notice the "comma" after the first entry and between the second entry { "id: "...}, { "id: "...} and no comma after the very last entry.

i tried to echo the comma outside/end of while-loop. but in the result, the comma is only at the very last entry, no in-between

if last row is reached then there should be no comma at the very last entry. i don't know how can i make the desired result.

is there any other approach/way to this? like using array/json_encode? ..but i don't know how to do it

json_encode() is your friend, you should never be building JSON strings like this — Borjante
– Borjante, Commented Aug 9, 2016 at 6:59
to correct your code, you should put echo ', '; before the first "echo '{ ';" in the while, in an if staement if(isset($ar) { echo ', '; } — M. I.
– M. I., Commented Aug 9, 2016 at 7:06
im still learning using mysqli. sorry. i'm so primitive. i'll change it later when i have learned it. thank you for suggestion :) — silver01
– silver01, Commented Aug 11, 2016 at 6:40

Dhaval Purohit · Accepted Answer · 2016-08-09 07:05:50Z

You can either use the json_encode for this to get the proper json output or you can get rid of this "," (comma) by using this technique.

//first use an variable $cnt=0;
//then check into the while loop
//whether the $cnt==0 then not to put the , before the making of an entry 
//for example,
<?php
include('../connectDB.php'); //connect to db
$cnt=0;

echo '{ ';
echo '"success": 1, ';
    echo '"result": [ ';
        $result = mysql_query("SELECT * FROM roominventory");
        while($row = mysql_fetch_array($result)) { //start while
            $getId = $row['id']; //get value
            $getRoomId = $row['room'];
            if($cnt==0)
            {
                 echo '{ ';
            }
            else
            {
                 echo ',{';
            }
                $ar = $row['arrival']; //assign value to variable
                $dep = $row['departure'];

                $date = str_replace('-', '/', $ar); //format the date
                $formatArr =  date('m/d/Y', strtotime($date));

                $date2 = str_replace('-', '/', $dep); //format the date
                $formatDep =  date('m/d/Y', strtotime($date2));

                $mSt = strtotime($formatArr) * 1000; //convert to milliseconds
                $mEd = strtotime($formatDep) * 1000;

                echo '"id": ' . '"' . $getId. '"' . ', ';

                $resulta = mysql_query("SELECT * FROM rooms_amenities WHERE id='$getRoomId'");
                while($rowa = mysql_fetch_array($resulta)) {
                    $getName = $rowa['name'];
                }

                echo '"title": ' . '"' . $getName . '"' . ', ';
                echo '"url": ' . '"' . 'http://example.com' . '"' . ', ';
                echo '"class": ' . '"' . 'event-warning' . '"' . ', ';
                echo '"start": ' . '"' . $mSt . '"' . ', '; //echo the converted date
                echo '"end": ' . '"' . $mEd . '" ';
            echo '} ';
            //echo ', '; //comma (should only in between entries and not in the very end of the very last entry)
           $cnt=1;
        } //end while
echo '] ';
echo '}';

this one works perfectly! thank you. really appreciate this :)

Pruthviraj Chudasama · Accepted Answer · 2016-08-09 07:27:31Z

0

you can get that output using json_encode easily. try this code its should work fine for you.

<?php
include('../connectDB.php'); //connect to db

$result = mysql_query("SELECT * FROM roominventory");
while($row = mysql_fetch_array($result)) 
{ //start while
    $getId = $row['id']; //get value
    $getRoomId = $row['room'];

    $ar = $row['arrival']; //assign value to variable
    $dep = $row['departure'];

    $date = str_replace('-', '/', $ar); //format the date
    $formatArr =  date('m/d/Y', strtotime($date));

    $date2 = str_replace('-', '/', $dep); //format the date
    $formatDep =  date('m/d/Y', strtotime($date2));

    $mSt = strtotime($formatArr) * 1000; //convert to milliseconds
    $mEd = strtotime($formatDep) * 1000;

    $resulta = mysql_query("SELECT * FROM rooms_amenities WHERE id='$getRoomId'");

    while($rowa = mysql_fetch_array($resulta))
    {
        $getName = $rowa['name'];
    }

    $new_list= array('id'=>$getId,'title'=>$getName,'url'=>'http://example.com','class'=>'event-warning','start'=>$mSt,'end'=>$mEd);
    $response = array("success"=>1,"result"=>$new_list);    
}
echo json_encode($response);
?>

you can get output which you shown in your questions..thanks

answered Aug 9, 2016 at 7:27

Pruthviraj Chudasama

4462 silver badges15 bronze badges

1 Comment

silver01 Over a year ago

it works, but it remove/gets rid of the white spaces which i needed too. thankyou for this :)

Naveed Ramzan · Accepted Answer · 2016-08-09 07:38:37Z

0

$result = mysql_query("SELECT * FROM roominventory");
$jsonData = '';
while($row = mysql_fetch_array($result)) { //start while
    $jsonData .= json_encode($row);
}
echo $jsonData;

Try this one.

answered Aug 9, 2016 at 7:38

Naveed Ramzan

3,5893 gold badges27 silver badges33 bronze badges

Collectives™ on Stack Overflow

php/mysql - while loop ..pass result in array?

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related