Php mysql json массив

How to Insert Json Data in Mysql with PHP

This is example. you can modify the data as you required.

Check this code this might help you:

1 Read the JSON file in PHP:

PHP supports the function file_get_contents() which will read the entire file and returns it as a string.

//read the json file contents $jsondata = file_get_contents('yourjsonfilename.json'); 

2 Convert JSON String into PHP Array:

//convert json object to php associative array $data = json_decode($jsondata, true); 

The first parameter $jsondata contains the JSON file contents. The second parameter true will convert the string into PHP associative array.

3 Parse the PHP Array Values and store in Variables:

//get the details $id = $data['Id']; $name = $data['personal']['name']; $age = $data['personal']['age']; $streetaddress = $data['personal']['address']['streetaddress']; $city = $data['personal']['address']['city']; $state = $data['personal']['address']['state']; $postalcode = $data['personal']['address']['postalcode']; 

4 Insert JSON to MySQL Database with PHP Code Now we will insert the extracted JSON object values into the MySQL table using below query.

//insert into mysql table $sql = "INSERT INTO tbl_students(studentId, name, age, streetaddress, city, state, postalcode) VALUES('$id', '$name', '$age', '$streetaddress', '$city', '$state', '$postalcode')"; if(!mysqli_query($con, $sql))

Now we have successfully imported JSON data into MySQL database as below:

Another way is:

 >'; // Database connection $conn = new mysqli('localhost', 'username', 'password', 'databasename'); // Insert data Query $sql = "INSERT INTO student_table ( name, jsondata ) VALUES ('Harry', '$josndata')"; if ($conn->query($sql) === TRUE) < echo "Insert your JSON record successfully"; >?> 

Источник

How to insert json array into mysql database

Hi I’m trying to insert the json array into my MySQL database. I’m passing the data form my iphone there i have converted the data into json format and I’m passing the data to my server using the url its not inserting into my server. This is my json data.

 //database connection close mysql_close($con); //> ?> 

Please tell where I’m doing wrong in the above code basically I’m not a php developer I’m mobile application developer so I’m using the php as a server side scripting please tell me how to resolve this problem.

Your code looks good i.e. parsing JSON and insert query I suppose its related to connection and db select. Use mysql_error() after the query to see what happened.

6 Answers 6

 $json = file_get_contents('php://input'); $obj = json_decode($json,true); 

I think you are passing the wrong variable. You should pass $json in json_decode as shown above.

You are missing JSON source file. Create a JSON file then assign it to var data:

There is no such variable as $data . Try

Rest looks fine. If the error still persists, enable error_reporting .

good catch !! even I tried his code locally but I had it correct while checking it so missed that completely that he was using wrong variable name !!

Its simple you can insert json datatype as below. reference link: click here for more details

insert into sgv values('c-106', 'admin','owner',false,'[, ]',0,'pasds'); 

$string=mysql_real_escape_string($json); 

Can you explain your code further? What does it do, and what makes you think that it solves the problem?

header("Access-Control-Allow-Origin: http://localhost/rohit/"); header("Content-Type: application/json; charset=UTF-8"); header("Access-Control-Allow-Methods: POST"); header("Access-Control-Max-Age: 3600"); header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With"); //files needed to connect to database include_once 'config/database.php'; include_once 'objects/main.php'; //get Database connection $database = new Database(); $db = $database->getConnection(); //instantiate product object $product = new Product($db); //get posted data $data = json_decode(file_get_contents("php://input")); //set product property values $product->b_nm = $data->Business_Name; $product->b_pno = $data->Business_Phone; $product->b_ads = $data->Business_Address; $product->b_em = $data->Business_Email; $product->b_typ = $data->Business_Type; $product->b_ser = $data->Business_Service; $name_exists = $product->nameExists(); if($name_exists)< echo json_encode( array( "success"=>"0", "message" => "Duplicate Record Not Exists." ) ); > else< if($product->b_nm != null && $product->b_pno != null && $product->b_ads != null && $product->b_em != null && $product->b_typ != null && $product->b_ser != null)< if($product->create())< //set response code http_response_code(200); //display message: Record Inserted echo json_encode(array("success"=>"1","message"=>"Successfully Inserted")); > > else< //set response code http_response_code(400); //display message: unable to insert record echo json_encode(array("success"=>"0","message"=>"Blank Record Not Exists.")); > > 

I don’t think anybody can use your code without these additional files. And I haven’t asked you to provide more code, but to provide an explanation — that’s what enables other peope to learn from your answer

90%-100% of the code in this answer is irrelevant to the question. Anything that might be relevant appears to be buried in other files not shown here. Perhaps you didn’t really understand the main point of the question.

Источник

php how to store and read json data via mysql?

then, how to update data value? read out the data, then insert it with an json_encode and decode process, or only easy way update?

4 Answers 4

Technically, you are going the wrong way with that. MySQL is used to store each of your ID/VALUE seperately. For the sake of NOT changing your code we’ll look at your solution first but then i’ll explain the «better» way of doing it.

First, you need to make your JSON as a variable, not part of your SQL:

mysql_query(«INSERT INTO text (data) VALUES («.mysql_real_escape_string(array(json_encode(‘id’ => $uid, ‘value’ => ‘yes’))).»)»);

mysql_query(«INSERT INTO text (data) VALUES (json_encode(‘id’ => $uid, ‘value’ => yes))»);

This first part will allow you to at least instead the data correctly into mysql. I am ASSUMING your table has an ID and that you will be using it to update or delete

When you retrieve your data, you can json_decode the $row[‘data’] to get your data back from the row and work with it. To update it, just do:

mysql_query("UPDATE text SET data = "'.mysql_real_escape_string(json_encode($myJsonToBeData)).'" WHERE rowid = '.$myrowid) 

Now, for the RIGHT way to do this:

The right way to do this would be to have these fields into your table: ID, JSONID, JSONVALUE and use this SQL instead:

SELECT * FROM text WHERE INTO text VALUES(NULL, $jsonid, $jsonvalue) UPDATE text SET jsonid = $jsonid, jsondata = $jsondata 

This is pretty basic, but it will allow you to have any number of entries in your database that make it searchable, indexed, sortable, queryable, etc.

Источник

Pulling data from MySQL into json array

I’m trying to pull data from my database using json in php. I have a few elements I need to specific then to post them on a page. I want to «fetch» the data from mysql and return it to a json_encode. How can I do this using the SELECT method. Some had used PDO methods and other have used mysql_assoc, which confuses me. For instance, I have rows of: ‘id’ , ‘title’ , ‘start’, ‘backgroundColor’. etc. along with a default value for all of them. ($array[] = «someValue = default») I want it to export like so:

array( 'id' => 1, 'title' => "someTitle", 'start' => "2012-04-16", 'backgroundColor' => "blue", 'someValue' = > "default", . ), . )); 

4 Answers 4

If you wanted to do this with PDO then here is an example:

query($sql)->fetchAll(PDO::FETCH_ASSOC); //To output as-is json data result //header('Content-type: application/json'); //echo json_encode($result); //Or if you need to edit/manipulate the result before output $return = []; foreach ($result as $row) < $return[] = [ 'id' =>$row['id'], 'title' => $row['title'], 'start' => $row['start'].' '.$row['time'], 'backgroundColor' => $row['backgroundColor'] ]; > $dbh = null; header('Content-type: application/json'); echo json_encode($return); ?> 

If your going to access the output with jQuery ajax() or such the you need to specify header content type to match dataType.api.jquery.com/jQuery.ajax (DataType Section) also Its always good practice to set content type on anything other then text/html

Okay. and this method will easily fetch all the data. What if I wanted to added two datas to one with a space? «data» => $row[«start»].» «.$row[«time»],

I have update my answer. If you want to manipulate the data before you assign values into an array then you need to loop it, there are enough examples to give yo an idea of what todo now.

this actually makes is extremely easier to use with you writing out like this. All I need to do is set the variables for my password and username and add the remaining array fetches. But if I wanted to leave the items that are blank, not null. Could I specify, «FROM my_table where length(column) > 0» or how exactly would I word that?

Источник

Getting mysqli result as array for json_encode() in PHP

I finally convinced myself to switch from PHP mysql to mysqli after upgrading my old PHP version. However, I did not manage to implement the same approach as before: This is the old approach:

$sth = mysql_query("select * from . "); $rows = array(); while($r = mysqli_fetch_assoc($sth)) < $rows[] = $r; >print json_encode($rows); 

$prename = "Peter"; $rows = array(); $mysqli = new mysqli($server, $user, $pass, $dbase); if ($stmt = $mysqli->prepare("select lastname where prename = ? order by prename asc")) < /* bind parameters for markers */ $stmt ->bind_param("s", $prename); /* execute query */ $stmt -> execute(); /* bind result variables */ $stmt -> bind_result($lastname); /* fetch value */ $stmt->fetch(); echo $lastname; /* close statement */ $stmt -> close(); > /* close connection */ $mysqli -> close(); print json_encode($rows); 

How can I add the result of the query to the $rows[] array? The return value has to be a json string that will be parsed by my webapplication. I tried several solutions with $stmt -> fetch_array but none of them worked. Thank you very much for your help.

There must be a better solution. The example above is edited. Originally I have like 20 fields from the database that is returned. I want to be able to do something similar like I did with the normal mysql_query: $rows[] = $r;

6 Answers 6

You will find a solution to your question on the php.net mysqli bind_result page here:

check out the comment by nieprzeklinaj at gmail dot com

he/she provides a function fetch() that will work as a fetch all for prepared mysqli statements (returning the full result set in an array). It will work with a dynamic number of selected fields.

You can add the fetch() function to your php code (of course you may call it whatever you like).

Then to use it in the code you provided above you would do something like:

$prename = "Peter"; $rows = array(); $mysqli = new mysqli($server, $user, $pass, $dbase); if ($stmt = $mysqli->prepare("select lastname where prename = ? order by prename asc")) < /* bind parameters for markers */ $stmt ->bind_param("s", $prename); /* execute query */ $stmt -> execute(); /* call the fetch() function provided by (nieprzeklinaj at gmail dot com) */ $rows = fetch($stmt); > /* close connection */ $mysqli -> close(); print json_encode($rows); 

I created a test table named «comment» and gave it a «prename» field and some other random fields just for demonstration purposes:

store_result(); $variables = array(); $data = array(); $meta = $result->result_metadata(); while($field = $meta->fetch_field()) $variables[] = &$data[$field->name]; // pass by reference call_user_func_array(array($result, 'bind_result'), $variables); $i=0; while($result->fetch()) < $array[$i] = array(); foreach($data as $k=>$v) $array[$i][$k] = $v; $i++; // don't know why, but when I tried $array[] = $data, I got the same one result in all rows > > elseif($result instanceof mysqli_result) < while($row = $result->fetch_assoc()) $array[] = $row; > return $array; > $prename = "Peter"; $rows = array(); $server = 'localhost'; $user = 'user'; $pass = 'pass'; $dbase = 'mydatabase'; $mysqli = new mysqli($server, $user, $pass, $dbase); $prename = "Peter"; $rows = array(); if ($stmt = $mysqli->prepare("select * from comment where prename = ? order by prename asc")) < /* bind parameters for markers */ $stmt ->bind_param("s", $prename); /* execute query */ $stmt -> execute(); /* call the fetch() function provided by (nieprzeklinaj at gmail dot com) */ $rows = fetch($stmt); > else< //print error message echo $mysqli->error; > /* close connection */ $mysqli -> close(); print json_encode($rows); 

database table info (so you can check the output):

mysql> describe comment; +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | prename | varchar(100) | YES | | NULL | | | comment_id | int(11) | YES | | NULL | | | fullname | varchar(150) | YES | | NULL | | | email | varchar(150) | YES | | NULL | | +------------+--------------+------+-----+---------+-------+ mysql> select * from comment; +---------+------------+----------+------------+ | prename | comment_id | fullname | email | +---------+------------+----------+------------+ | Peter | 1 | Peter 1 | some email | | Peter | 2 | Peter 2 | some email | | Peter | 3 | Peter 3 | some email | +---------+------------+----------+------------+ 

Источник