JSON encode mysql query

Question

I have a script that does a mysql query and if there are rows it encodes a message. But I want it to encode the message and the whole result of the query. This is the code I use:

<?php
include 'config.php';

// Connect to server and select databse.
mysql_connect("$dbhost", "$dbuser", "$dbpass")or die("cannot connect");
mysql_select_db("$dbname")or die("cannot select DB");


$id = @$_POST['id'];

// To protect MySQL injection (more detail about MySQL injection)
$id = stripslashes($id);
$id = mysql_real_escape_string($id);

$sql="Select * from table1 where ID='$id'";

$result=mysql_query($sql);

// Mysql_num_row is counting table row
$count=mysql_num_rows($result);
// If result matched $id and $mypassword, table row must be 1 row

if ($count == 1) {
     $message = array('status' => 'ok');
} 

header('Content-Type: application/json');
print '{"key":'. json_encode($message) .'}'; 
?>

So I want the JSON to look like:

{ "key": [ { "ID": "1", "NAME": "Test", "ADDRESS": "Test-street 123", "CONDITION": "false", "status": "ok" } ] }

So what should I add to $message to achieve this?

You do not do anything with the result except retrieving the number of selected rows. — Gumbo
– Gumbo, Commented Jun 15, 2012 at 6:38

Alexander · Accepted Answer · 2012-06-15 06:41:23Z

1

You are missing an associative array there.

$message = array(array(
             "ID"        => "1",
             "NAME"      => "Test",
             "ADDRESS"   => "Test-street 123",
             "CONDITION" => "false",
             "status"    => "ok"
           ));
echo json_encode($message);
// [{"ID":"1","NAME":"Test","ADDRESS":"Test-street 123","CONDITION":"false","status":"ok"}]

answered Jun 15, 2012 at 6:41

Alexander

23.5k11 gold badges65 silver badges74 bronze badges

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

2 Comments

user1423276 Over a year ago

I want the JSON to show the datafrom mysql query + status => "ok". If I do it like this doesn't it mean that every $message will look exactly the same?

Alexander Over a year ago

@user1423276, there's no database schema

Denys Séguret · Accepted Answer · 2012-06-15 06:45:27Z

0

You have to fetch the results of your query before you try encoding it in JSON. For example :

$result=mysql_query($sql);
$message->key = array();
while( $row = $db->sql_fetchrow($result) ) {
    $message->key[] = $row; // or a specific dispatching of values in keys
}

answered Jun 15, 2012 at 6:45

Denys Séguret

384k90 gold badges813 silver badges780 bronze badges

Comments

j0k · Accepted Answer · 2012-06-15 09:00:04Z

0

Fetch mysql result using mysql_fetch_assoc, somthing like this :-

if ($count == 1) {
    $message = mysql_fetch_assoc($result);
    $message['status'] = 'ok';
    $message = array($message);
    print '{"key":'. json_encode($message) .'}';
}

edited Jun 15, 2012 at 9:00

j0k

22.8k28 gold badges81 silver badges90 bronze badges

answered Jun 15, 2012 at 7:06

Tinku Rana

3603 silver badges10 bronze badges

Collectives™ on Stack Overflow

JSON encode mysql query

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related