get the data from database with ajax

Question

i want to get the data from database through ajax but its gives me only one record not other but i want all the records from database i have search too much to understand the problem but can't get that

that is database image

php code

 //-------------------------------------------------------------------------- 
  // Example php script for fetching data from mysql database
  //--------------------------------------------------------------------------
  $host = "localhost";
  $user = "root";
  $pass = "";

  $databaseName = "search";
  $tableName = "ajax01";

  //--------------------------------------------------------------------------
  // 1) Connect to mysql database
  //--------------------------------------------------------------------------
  include 'DB.php';
  $con = mysql_connect($host,$user,$pass);
  $dbs = mysql_select_db($databaseName, $con);

  //--------------------------------------------------------------------------
  // 2) Query database for data
  //--------------------------------------------------------------------------
  $result = mysql_query("SELECT * FROM $tableName");          //query
  $array = mysql_fetch_row($result);                          //fetch result    

  //--------------------------------------------------------------------------
  // 3) echo result as json 
 //--------------------------------------------------------------------------
  echo json_encode($array);

?>

html

  <!-------------------------------------------------------------------------
  1) Create some html content that can be accessed by jquery
 -------------------------------------------------------------------------->
  <h2></h2>


  <script id="source" language="javascript" type="text/javascript">

  $(function () 
  {
//-----------------------------------------------------------------------
// 2) Send a http request with AJAX http://api.jquery.com/jQuery.ajax/
//-----------------------------------------------------------------------
$.ajax({                                      
  url: 'api.php',                  //the script to call to get data          
  data: "",                        //you can insert url argumnets here to    pass to api.php
                                   //for example "id=5&parent=6"
  dataType: 'json',                //data format      
  success: function(data)          //on recieve of reply
  {
   // var id = data[0];              //get id
  //  var vname = data[1];           //get name
    //--------------------------------------------------------------------
    // 3) Update html content
    //--------------------------------------------------------------------
    $('#output').html("<b>id: </b>"+id+"<b> name: </b>"+name); //Set output     element html
    //recommend reading up on jquery selectors they are awesome 
    // http://api.jquery.com/category/selectors/
  } 
});
 }); 

  </script>
 </body>
</html>

**Warning**: You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). — Quentin, Oct 07 '15 at 10:40

score 3 · Answer 1 · answered Oct 07 '15 at 10:40

3

You only ever get one row.

$array = mysql_fetch_row($result);

You need to loop that line and keep going until you run out of rows (creating an array of row arrays as you go).

Then json_encode that final array.

answered Oct 07 '15 at 10:40

Quentin

914,110
126
1,211
1,335

tcak · Answer 2 · 2015-10-07T12:44:21.277

0

The mysql_fetch_row takes only one record from result of query. You need to store each row of query result in an array, then finally convert the array to its JSON representation.

$records = [];

while( $row = mysql_fetch_row( $result ) ){
    array_push( $records, $row );
}

echo json_encode( $records );

Substitute the mysql_fetch_row and json_encode lines of your code with this to achieve it.

edited Oct 07 '15 at 12:44

answered Oct 07 '15 at 10:43

tcak

2,142
1
16
23

i m still not getting result – Sheraz Oct 07 '15 at 11:00
1

Please consider editing your post to add more explanation about what your code does and why it will solve the problem. An answer that mostly just contains code (even if it's working) usually wont help the OP to understand their problem. – SuperBiasedMan Oct 07 '15 at 11:28

score 0 · Answer 3 · answered Oct 07 '15 at 11:01

THe best way to do this, use 3 different files;

php_page.php
script.js
php_handler.php

In the php_page.php you have have actually the HTML In the script.js you make the request with ajax In php_handler.php you give the response and there you can make the connection and take all the data from your database that you need!

php_page.php

<!doctype html>
<html>
    <head>
        <title></title>
        <script type="text/javascript" src="script.js"></script>
    </head>
    <body>
        <ul id="list">
            <!-- In here comes the data! -->
        </ul>

        <button id="button">Get Data</button>
    </body>
</html>

script.js

$(document).ready(function() {
    $("#list").on("click", function() {
        $.ajax({
            url: "php_handler.php"
        }).done(function(data){
            $(this).empty().append("<li>"+ data +"</li>");
        });
    });
});

php_handler.php

<?php
$con = mysqli_connect($host, $user, $pass, $db);

$query = mysqli_query($con, "SELECT [FIELD_NAME] FROM [TABLE_NAME]");

foreach($query as $data)
{
    echo '<li>' . $data . '</li>';
}
?>

get the data from database with ajax

3 Answers3