CODEWITHSUNDEEP
phpmysqljson
Tuffy G
Tuffy G

Reputation: 1521

mysql results from json

i have a mysql database from which i require an rss feed of its content. i require the equivalent of this in json:

mysql_select_db("mydb", $con);
$result = mysql_query("select date, title, description, url from blah where type = 'OFFERS' order by ref desc");

echo '<?xml version="1.0"?>
<rss version="2.0">
<channel>

<title></title>
<description></description>
<link></link>';

while($row = mysql_fetch_array($result))
  {
  echo "
  <item>
  <title>" . $row['title'] . "</title>
  <description>" . $row['description'] . "</description>
  <link>" . $row['url'] . "</link>
  <image>" . $row['date'] . "</image>
  </item>";
  }

so far the closest thing i could get to work is this:

<?php

$host="localhost"; 
$username="username"; 
$password="password"; 
$db_name="mydb"; 

$con=mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");
$sql = "select date, title, description, url from blah where type = 'OFFERS' order by ref desc"; 
$result = mysql_query($sql);
$json = array();
if(mysql_num_rows($result)){
while($row=mysql_fetch_row($result)){
$json['title'][]=$row;
}
}

mysql_close($db_name);

echo json_encode($json); 

?>

any help would be appreciated

Upvotes: 0

Views: 399

Answers (2)

Shiplu Mokaddim
Shiplu Mokaddim

Reputation: 57640

As mysql_fetch_row returns both array keys and indexes its better to use mysql_fetch_assoc. This will only return the keys.

Also you are fetching items, not titles. So use $json['items'] instead of $json['title'].

Beside this, there are other information in the rss too. you can add them in $json as well.

The resultant code is, 

$json=array();
$json['title'] = 'My JS/RSS';
$json['link']  = "http://". $_SERVER['HTTP_HOST']. $_SERVER["REQUEST_URI"];
$json['description'] = "";
$json['itmes'] = array();
while($row=mysql_fetch_assoc($result)){
    $json['items'][]=$row;
}

Upvotes: 1

blaff
blaff

Reputation: 304

while ($row = mysql_fetch_assoc($result)) {
    $json[] = $row;
}

echo json_encode($json);

Upvotes: 1

Related Questions