How to return mysql data to webpage without refresh (ajax/jquery/php/mysql)

Question

I managed to get quite far with another question last night but then I read that mysql_ was deprecated I switched to mysqli. Anyway I have the following php within my search.php file -ve kept some comments in which worked under my last attempt.

// Get all records
while ( $row = $results->fetch_assoc())
{
  //$data[] = $row;
  echo ('COLUMN1:'.$row["COLUMN1"]);
  echo ('COLUMN2:'.$row["COLUMN2"]);
  echo ('COLUMN3:'.$row["COLUMN3"]);
  echo ('<br>');
}
$mysqli->close();
//echo json_encode( $data );

 ?>

And I want to display this data within a result div on my index.html page. I will eventually want to replace this data with a graph but one step at a time. I am struggling to understand how the data is passed between Ajax and php. This is my jquery/ajax:

$('#btn_search').click(function(){
        txt_search = $('#txt_search').val();
        $.ajax({                                      
          url: './php/search.php',                  //the script to call to get data          
          data: {search: txt_search},                        //you can insert url argumnets here to pass to api.php for example "id=5&parent=6"
          dataType: 'json',                //data format      
          success: function(rows)          //on recieve of reply
          {
           $('#result').append(rows); 
          } 
        }); return false;
    });

I want to be able to write any columns from the DB table into the div, displaying as many rows as possible. I was originally using the following which wasn't quite working (only displaying 2 rows..I can see why (poor understanding of arrays I think):

/*for (var row in rows)
{
    var COLUMN1 = rows[1];
    var COLUMN2 = rows[2];
    $('#result').append("<b>COLUMN1 </b>"+COLUMN1 e+"<b> COLUMN2: </b>"+COLUMN2).append("<hr />");
} */

EDIT: HTML

<div id="main">
  <form id="search_form" role="form" action="" method="post">
    <div class="form-group">
  <input type="text" class="form-control" name="txt_search" id="txt_search" placeholder="Enter name here" autocomplete="off" >
  <p></p>
  <button type="submit" id="btn_search" class="btn btn-default">Retrieve </button>
</div>
  </form>
  <div class="result" id="result">this accessed by jquery and will be replaced</div>
  /div>

Anyway I have a div called result which I would like to populate with a table of results just now. Can anyone answer and help me get a better understanding (I am finding a problem with this type of thing is there are hundreds of examples online, no explanation of why they doing what they are doing and all of them are different).

EDIT 2: Removed, Edit 3 contains better code:

EDIT 3: HTML into a variable:

$HTML = "<table border='1' >";
$HTML .= "<tr>";
$HTML .= "<td align=center> <b>Column1</b></td>";
$HTML .= "<td align=center><b>Column2</b></td>";
$HTML .= "<td align=center><b>Column3</b></td>";

// Get all records
while ( $row = $results->fetch_assoc())
{

    $HTML .= '<tr>';
    $HTML .= '<td align=center>'.$row["COLUMN1"].'</td>';
    $HTML .= '<td align=center>'.$row["COLUMN2"].'</td>';
    $HTML .= '<td align=center>'.$row["COLUMN3"].'</td>';
    $HTML .= '</tr>';
}
$mysqli->close();
$HTML .=  "</table>";
echo $HTML;

echo json_encode( $HTML );

 ?>

I am (I think): url: './php/search.php' but unfortunately it doesn't do anything on index.html. I am using Fiddler and it looks like the data is being returned (I can see search.php is being called)but it isn't being displayed - the success section of my ajax/jquery is wrong? — n34_panda
– n34_panda, Commented Jun 1, 2014 at 10:05

Hammad · Accepted Answer · 2014-06-01 11:32:31Z

1

In this specific example just to demonstrate you can change the content of #result div by just using this in your success function:

$('#result').html(rows);

But normally you would create a some HTML structure in your search.php file and generate some HTML dynamically and then replace target #result div with that of returned by search.php.

Also you need to change the file extension of index.html to index.php

Edit: The cause is to remove datatype: json.

edited Jun 1, 2014 at 11:32

answered Jun 1, 2014 at 10:08

Hammad

2,1373 gold badges29 silver badges48 bronze badges

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

7 Comments

n34_panda Over a year ago

Thanks I have tried that - I have edited my original post with my later attempt to simply add a table into the div. What should I be "echoing" in return at the end of search.php, is it correct?

Hammad Over a year ago

Yes you are on right track now but there is little modification need to be done. Yes, you will echo the HTML but only once at the end, when you have finished processing. So better store all your HTML in a variable by successive concatenation as you are currently doing and at the end, just echo the variable.

n34_panda Over a year ago

I've changed it to the third edit above but it is still not pulling data to the index.php...If I call the search.php directly that works fine and I can see the table with all the results but unfortunately the ajax just isn't displaying it?

n34_panda Over a year ago

Edit I found the problem, if I change the AJAX to not use the Datatype of Json it displays the data.

Hammad Over a year ago

Oh yes. Also you dont need to echo json_enchode($HTML) in php code. Cheers!

|

Collectives™ on Stack Overflow

How to return mysql data to webpage without refresh (ajax/jquery/php/mysql)

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related