Ajax json php database

Ajax / JSON / PHP database data

I’m more of a designer but I need to grab some data from a database. I would be able to get what I need without problem using PHP but I’m creating a mobile app in Phonegap so can’t use anything other than HTML, CSS or JS. I’ve read that this can be done in a PHP file on the server, encoded to JSON and then grabbed in the HTML with Ajax. Can anyone show me how do I do this, as simply as possible please?

Do you have anything so far? Do you know what your database schema is? Do you know how to connect to your database? Do you even know what type of database you have (MySQL, SQLServer, Postgres, etc)?\

«Anything other than HTML, CSS and JS» — basically that’s the same that you have available in any web browser. PHP is server side technology. Internet is full of examples how to make AJAX call. Just make sure do you have jQuery available or not and search for jQuery example or pure JS one.

@thatidiotguy Hey. It’s MySQL. I don’t really have anything yet as I’ve just been trying out examples.

2 Answers 2

Javascript file

$.ajax(< type: "GET", dataType: "JSON", url: "php/phpDb.model.php", data: < sataVariable: "here is some data send with GET method" >, success: function(data) < console.log(data); >, error: function(data) < console.log(data); >>); 

php file (phpDb.model.php)

// Connect to database $con = mysqli_connect("host", "userName", "password", "database") or die('< "error" : true, "errorMessage" : "Cannot connect to database '.mysqli_error($con).'">'); // Query $sqlCalling = "SELECT * FROM table WHERE "; $result = mysqli_query($con,$sqlCalling); $rowCalling = mysqli_fetch_array($resultCalling); mysqli_close($con); //getting column_1 and column_2 $column_1 = $rowCalling['column_1']; $column_2 = $rowCalling['column_2']; // Return a JSON parseable array return array( "column_1" => $column_1 , "column_2" => $column_2 , ); 

This solution is very generic, you will most likely just replace the name of the tables and the name of column with what they actually are in your database.

Источник

How to pass mysql result as jSON via ajax

And in your javascript part, you don’t have to do anything to get back your data, it is stored in data var from success function. You can just display it and do whatever you want on your webpage with it

header('Content-Type: application/json'); $row2 = array(); $result = array(); $statement = $pdo->prepare("SELECT * FROM posts WHERE subid IN (:key2) AND Poscode=:postcode2"); $statement->execute(array(':key2' => $key2,':postcode2'=>$postcode)); // $row = $statement->fetchAll(PDO::FETCH_ASSOC); while( $row = $statement->fetch()) < echo $row['Name'];//How to show this in the html page? echo $row['PostUUID'];//How to show this in the html page? $row2[]=$row; >if(!empty($row2))< $result['type'] = "success"; $result['data'] = $row2; >else < $result['type'] = "error"; $result['data'] = "No result found"; >echo json_encode($row2); 

$("form").on("submit",function() < var data = < "action": "test" >; data = $(this).serialize() + "&" + $.param(data); $.ajax(< type: "POST", dataType: "json", url: "ajax2.php", //Relative or absolute path to response.php file data: data, success: function(data) < console.log(data); if(data.type == "success")< for(var i=0;i" +db_data.Name ); console.log("name -- >" +db_data.PostUUID); > > if(data.type == "error") < alert(data.data); >> >); return false; >); 

Источник

How to Get JSON Data from PHP Script using jQuery Ajax

The jQuery ajax is very useful when you want to post or get data from PHP script without page refresh. Generally, you return the string to Ajax call for updating a part of the web page. But sometimes requires getting the object or array data from PHP file for showing values in the different area. Using $.ajax() method in jQuery you can get JSON data from a file and set in the HTML element.

In this tutorial, we will show you how to process ajax request using jQuery and call a PHP script that returns JSON data. The PHP script will fetch data from the MySQL database and returns JSON data to Ajax. The returned data is parsed using JavaScript and set values to the specific elements.

jQuery AJAX Call to PHP Script with JSON Return

For your better understanding, we will fetch user details from the database based on the user ID via Ajax call using jQuery, PHP, and MySQL.

Database Table Creation

The users table holds the basic information of the users. Our example script will retrieve user details from this demo (users) table.

CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `phone` varchar(15) COLLATE utf8_unicode_ci NOT NULL, `created` datetime NOT NULL, `modified` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

HTML Code

Initially, an input box is displayed to provide the user ID whose details you want to retrieve. The “Get Details” button initiates an Ajax request and the respective user details are displayed under the input box.

input type="text" id="user_id" /> input type="button" id="getUser" value="Get Details"/> div class="user-content" style="display: none;"> h4>User Detailsh4> p>Name: span id="userName">span>p> p>Email: span id="userEmail">span>p> p>Phone: span id="userPhone">span>p> p>Register Date: span id="userCreated">span>p> div>

JavaScript Code (jQuery & AJAX)

$.ajax() method perform an Ajax request and post the user ID to a PHP file to get the user details from the database. If the request succeeds the data returned from the server as the specified format in the dataType parameter. In our example script, JSON is specified in dataType , the data will be returned as JSON format. The parsed JSON data sets to the respective element content.

script> $(document).ready(function()< $('#getUser').on('click',function()< var user_id = $('#user_id').val(); $.ajax(< type:'POST', url:'getData.php', dataType: "json", data:, success:function(data)< if(data.status == 'ok')< $('#userName').text(data.result.name); $('#userEmail').text(data.result.email); $('#userPhone').text(data.result.phone); $('#userCreated').text(data.result.created); $('.user-content').slideDown(); >else< $('.user-content').slideUp(); alert("User not found. "); > > >); >); >); script>

PHP Script (getData.php)

This PHP script is called by the Ajax request. Based on the user ID the details are fetched from the database using PHP and MySQL. The retrieved data returns as JSON format to the Ajax.

if(!empty($_POST['user_id'])) $data = array(); 
 
//database details 
$dbHost = 'localhost'; 
$dbUsername = 'root'; 
$dbPassword = '*****'; 
$dbName = 'codexworld'; 
 
//create connection and select DB 
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); 
if($db->connect_error) die("Unable to connect database: " . $db->connect_error); 
> 
 
//get user data from the database 
$query = $db->query("SELECT * FROM users WHERE style="color: #007700">$_POST['user_id']>"); 
 
if($query->num_rows > 0) $userData = $query->fetch_assoc(); 
$data['status'] = 'ok'; 
$data['result'] = $userData; 
>else $data['status'] = 'err'; 
$data['result'] = ''; 
> 
 
//returns data as JSON format 
echo json_encode($data);
>
?>

Are you want to get implementation help, or modify or enhance the functionality of this script? Click Here to Submit Service Request

If you have any questions about this script, submit it to our QA community — Ask Question

Источник

How To Return JSON Response in PHP & MySQL using Ajax and jQuery

In this tutorial, I will share with you how to return JSON response in PHP & MySQL using Ajax & jQuery this is useful to display multiple types of data from server response and process it to our client-side using ajax and jquery. Don’t worry this method is easy we are going to use an array from the server and encode it with JSON format.

Basic Example

Let’s do the basics first we will create a new file called basic.php as you can see below code I created an associative array with first_name & last_name name keys. Then I use json_encode to encode the array into JSON format.

Then this is the result when executing it in the browser.

AJAX Integration

For ajax integration we need to create a simple button in our index.html then a simple ajax function to communicate the above code basic.php.

Let’s code our button with HTML.

Then next is our javascript ajax code. We will create our function named basic() inside scripts.js. Here is the code below:

Then if we will click the button «Basic JSON Response» the result will be like this on the console.

Next, we will parse the response JSON so that we can easily call it as JSON. See the code below.

As you can see we add a new line of code «response = JSON.parse(response);». Kindly see the console result after this code.

As you can see from the result above it is clickable now because we already parse it as JSON from a string.

Display by JSON Property

Now we will display the JSON by property like if you want to display the first_name value only. Here is the code update below:

As you can see below we add a new line of code «console.log(«First Name:» + response.first_name);» now in this code, we only access the first_name value.

Display JSON Property in HTML

Now we will display the specific property on your web page. We will update our HTML code below.

Then also our javascript code inside scripts.js. See below code updates.

As you can see above I add new line of code «$(«#json_first_name»).html(«First Name: » + response.first_name);». I call the ID selector «#json_first_name» and use html() function to display the first_name value from your server.

Now since you have the basic knowledge already of how to Response the JSON we will implement the dynamic data using PHP & MySQL with Ajax.

Display Dynamic Data Using Response JSON with PHP & MySQL

I will share with you the easy method to display the dynamic data from your server-side. In this example, we will display the employee in Edit Employee Modal using ajax and jquery.

Here is the HTML code inside index.html

Our HTML code for modal inside index.html

  
 
 
  
Edit Employee
 × 
  
 
  
Email 
First Name 
Last Name 
Address 
 Update Close  
 
 
 

Then our PHP code inside get.php as you can see below that I encode the $row variable array from the result query.

query($sql); // Fetch Associative array $row = $results->fetch_assoc(); // Free result set $results->free_result(); // Close the connection after using it $db->close(); // Encode array into json format echo json_encode($row); ?>

Then now we will add our javascript code get() function inside scripts.js.

function get() < $(document).delegate("[data-target='#edit-employee-modal']", "click", function() < var employeeId = $(this).attr('data-id'); // Ajax config $.ajax(< type: "GET", //we are using GET method to get data from server side url: 'get.php', // get the route value data: , //set data beforeSend: function () , success: function (response) >); >); >

Loop the JSON Response

Since you already know how to display your JSON response to your web page. Now I will give you an example of how to loop the JSON. Below are the screenshot example on listing the employees via ajax with JSON response.

Before we dive in I will show you first a basic code on how to do it. As you can see in the code below I use «$.each(response, function(key,value)

Kindly check the console result below of the above code.

So that’s the basic way on how to loop the JSON. So we will apply it in the advance by displaying the list of employees using the bootstrap list group as you can see the result below:

Now let do the code. First set up our HTML code inside index.html.

Then next our PHP Code inside all.php.

query($sql); // Fetch Associative array $row = $results->fetch_all(MYSQLI_ASSOC); // Free result set $results->free_result(); // Close the connection after using it $db->close(); // Encode array into json format echo json_encode($row); ?>

Then now let’s code the javascript for looping the employee’s result via ajax. You will find this code inside scripts.js with all() functions.

function all() < // Ajax config $.ajax(< type: "GET", //we are using GET method to get all record from the server url: 'all.php', // get the route value success: function (response) '; // Loop the parsed JSON $.each(response, function(key,value) < // Our employee list template html += ''; html += "
" + value.first_name +' '+ value.last_name + " (" + value.email + ")" + "
"; html += "
" + value.address + "
"; html += "Edit"; html += "Delete"; html += ''; >); html += '