Getting data from mysql and then converting it to json with php

[borden0108]

Hi,

 

 

I have a problem with some code not working.

 

I need it to get the data from a mysql database and then export it as a json array in the format of id , title, author, date, imageUrl, text. (Please not that these are all variables )

 

PHP CODE:

<?php
$link = mysql_connect('localhost', 'root', '');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';

$arr = array();
$rs = ("SELECT `id`, `title`, `author`, `date`, `imageUrl`, `text` FROM `items`");

while($obj = mysql_query($rs)) {
$arr[0] =  $obj->id;
$arr[1] =  $obj->title;
$arr[2] =  $obj->author;
$arr[3] =  $obj->date;
$arr[4] =  $obj->imageUrl;
$arr[5] =  $obj->text;
}

echo '{"items":'.json_encode($arr).'}';
?>

 

Thanks For Your Help

borden0108

[Muddy_Funster]

"I have a problem with some code not working." - isn't remotely helpfull. 

 

Could you elaborate on what the problem is, what, if any, errors are shown and what exactly is/isn't happening versus what you expect the code to produce.

[borden0108]

Right now the php is producing this

 

 

Connected successfully{"items":[]}

 

 

and there is no json.

 

please i just need some help on this

[Muddy_Funster]

what do you get when you replace your echo with

 print_r($arr);

[borden0108]

i got Connected successfullyArray ( )

[Muddy_Funster]

So the way you are trying to populate the values into the array isn't working.  Think about alternatives to using ->

[borden0108]

when i use:

while($obj = $rs) {

$arr[0] =  $rs->id;

$arr[1] =  $rs->title;

$arr[2] =  $re->author;

$arr[3] =  $obj->date;

$arr[4] =  $obj->imageUrl;

$arr[5] =  $obj->text;

}

 

i get no data in the array

 

but when i use

 

$arr[0] =  $rs->id;

$arr[1] =  $rs->title;

$arr[2] =  $rs->author;

$arr[3] =  $rs->date;

$arr[4] =  $rs->imageUrl;

$arr[5] =  $rs->text;

 

i get this error on all of those lines

 

Trying to get property of non-object in C:\wamp\www\blackrain\Resources\json-gen.php on line <insert line number here>

 

but i get data in the json array

 

{"items":[null,null,null,null,null,null]}

 

i am stuck bettween a rock and a hard place.

 

[Muddy_Funster]

while($obj = mysql_query($rs))

 

This doesn't produce the result set data, you need to do a mysql_fetch_assoc() or mysql_fetch_array() to get back what you are looking for:

$rs_return = mysql_query($rs);
while($obj = mysql_fetch_array($rs_return)) {
$arr[0] =  $obj['id'];
$arr[1] =  $obj['title'];
$arr[2] =  $obj['author'];
$arr[3] =  $obj['date'];
$arr[4] =  $obj['imageUrl'];
$arr[5] =  $obj['text'];
}

 

You could use an array_push if you preffer.

[borden0108]

Sorry but that did not work here is the error produced.

 

 

( ! ) Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\blackrain\Resources\json-gen.php on line 12

 

 

I am wondering what the resolution to this problem is?

 

 

Matt

 

 

 

 

 

 

 

[jcbones]

Your query is failing, this should tell you why.

<?php
error_reporting(-1);
ini_set('display_errors',1);

$link = mysql_connect('localhost', 'root', '');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully<br />';

$arr = array();
$rs = "SELECT `id`, `title`, `author`, `date`, `imageUrl`, `text` FROM `items`";
$rs_return = mysql_query($rs) or trigger_error($rs . ' has encountered an error: <br />' . mysql_error());
while($obj = mysql_fetch_array($rs_return)) {
$arr[0] =  $obj['id'];
$arr[1] =  $obj['title'];
$arr[2] =  $obj['author'];
$arr[3] =  $obj['date'];
$arr[4] =  $obj['imageUrl'];
$arr[5] =  $obj['text'];
}
//numbering the array indexes, will only return 5 array indexes EVER.  All indexes will be over written on each loop.
echo '{"items":'.json_encode($arr).'}';
?>