CODEWITHSUNDEEP
phpajaxsearchjquery-mobilehtml-table
Adam.13
Adam.13

Reputation: 41

PHP and AJAX search results into a table

I am trying to make a webapp and I have used a php, ajax and mysql search function from another site.

It currently allows me to search the database and return what I want. The only problem I am having is that it returns the results in a simple text box. I want to be able to return the results in a table.

The search simply searches the database for forename, surname, address etc... This is the code I have.

php:

<?php

include('config.php');

if(isset($_GET['search_word']))
{
$search_word=$_GET['search_word'];

$sql=mysql_query("SELECT * FROM info WHERE CONCAT(Forename,' ',Surname) LIKE '%$search_word%'or Address1 LIKE '%$search_word%' or Address2 LIKE '%$search_word%' or Postcode LIKE '%$search_word%' or DOB LIKE '%$search_word%' ORDER BY ID DESC LIMIT 20 ");
$count=mysql_num_rows($sql);

if($count > 0)
{

while($row=mysql_fetch_array($sql))
{
$result = $row['Forename'].' '.$row['Surname'].' '.$row['DOB'].' '.$row['Address1'].' '.$row['Address2'].' '.$row['Postcode'];
$bold_word='<b>'.$search_word.'</b>';
$final_msg = str_ireplace($search_word, $bold_word, $result);
?>

<li><?php echo $final_msg; ?></li>
<?php
}
}
else
{

echo "<li>No Results</li>";

}


}
?>

This is connecting into the database and pulling out the results. I then have my html etc...:

script:

$(function() {
//-------------- Update Button-----------------


$(".search_button").click(function() {
    var search_word = $("#search_box").val();
    var dataString = 'search_word='+ search_word;

    if(search_word=='')
    {
    }
    else
    {
    $.ajax({
    type: "GET",
    url: "searchdata.php",
    data: dataString,
    cache: false,
    beforeSend: function(html) {

    document.getElementById("insert_search").innerHTML = '';

    $("#flash").show();
    $("#searchword").show();
     $(".searchword").html(search_word);
    $("#flash").html('<img src="ajax-loader.gif" align="absmiddle">&nbsp;Loading Results...');



            },
  success: function(html){
    $("#insert_search").show();
    $("#insert_search").append(html);
    $("#flash").hide();
   // $("#MainTable").append(data);
    if(html.length > 0)
            {
                $("#MainTable tr:not(:first-child)").remove();
            }
            for(var i = 0; i < html.length; i++)
            {
                var date = new Date(html[i]["Timestamp"]*1000);
                html[i]["Timestamp"] = date.getHours()+":"+date.getMinutes();
                $("#MainTable").append("<tr><td><a href='#' id='info"+data[i]["ID"]+"' data-role='button' data-theme='b' data-icon='check' data-iconpos='notext' class='id'></a></td><td>"+data[i]["Timestamp"]+"</td><td>"+data[i]["ID"]+"</td><td>"+data[i]["Forename"]+" "+data[i]["Surname"]+"</td><td class='hidden'>"+data[i]["ID"]+"</td><td>"+data[i]["DOB"]+"</td><td class='hidden'>"+data[i]["Address2"]+"</td><td class='hidden'>"+data[i]["Town"]+"</td><td class='hidden'>"+data[i]["City"]+"</td><td class='hidden'>"+data[i]["County"]+"</td><td>"+data[i]["Address1"]+"</td><td>"+data[i]["Postcode"]+"</td><td class='hidden'>"+data[i]["Phone2"]+"</td><td class='hidden'>"+data[i]["DOB"]+"</td></tr>");                                
                if(data[i]["Completed"] == "1")
                {
                    $("#MainTable tr:last-child td").addClass("lineThrough");
                }
                $("#MainTable tr:last td:first a, #MainTable tr:last td:last a").button();

}




  }
});

    }




    return false;
    });
//---------------- Delete Button----------------


});

I have tried a few different things such as changing the +Data+ in the table script to html or tried adding append(html) but I am having no luck and when I search I am instead getting undefined in my table or it is blank completely.

This is the HTML for my table:

<table data-role="table" id="MainTable" data-mode="columntoggle">
                    <tr><th>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</th><th>ID Number</th><th>Name</th><th>DOB</th><th>Address</th><th>Postcode</th></tr>
                    <tr><td colspan='7'>No data currently available, connect to the internet to fetch the latest appointments.</td></tr>
            </table>

Would appreciate any help with this. Hope this makes sense.

Upvotes: 0

Views: 1947

Answers (2)

Nil&#39;z
Nil&#39;z

Reputation: 7475

Changes:

if($count > 0)
{
    while($row=mysql_fetch_array($sql))
    {
    $result = $row['Forename'].' '.$row['Surname'].' '.$row['DOB'].' '.$row['Address1'].' '.$row['Address2'].' '.$row['Postcode'];
    $bold_word='<b>'.$search_word.'</b>';
    $final_msg = str_ireplace($search_word, $bold_word, $result);
    ?>
    <tr>
        <td>&nbsp;</td>
        <td><?=$row['id']?></td>
        <td><?=$final_msg?></td>
        <td><?=$row['address']?></td>
        <td><?=$row['postcode']?></td>
    </tr>

    <?php
    }
}
else
{

    echo "<li>No Results</li>";

}

Changes in the JS

$.ajax({
    type: "GET",
    url: "searchdata.php",
    data: dataString,
    cache: false,
    beforeSend: function(html) {
        document.getElementById("insert_search").innerHTML = '';

        $("#flash").show();
        $("#searchword").show();
        $(".searchword").html(search_word);
        $("#flash").html('<img src="ajax-loader.gif" align="absmiddle">&nbsp;Loading Results...');
    },
    success: function(html){
        $("#insert_search").show();
        $("#insert_search").append(html);
        $("#flash").hide();
        $("#MainTable").append(html); //<-- append the data from the ajax
    }
});

Upvotes: 0

JohnnyFaldo
JohnnyFaldo

Reputation: 4161

Instead of outputting this in your php page: 

 while($row=mysql_fetch_array($sql))
 {
 $result = $row['Forename'].' '.$row['Surname'].' '.$row['DOB'].' '.$row['Address1'].' '.$row['Address2'].' '.$row['Postcode'];
 $bold_word='<b>'.$search_word.'</b>';
 $final_msg = str_ireplace($search_word, $bold_word, $result);
 ?>

 <li><?php echo $final_msg; ?></li>

Loop through the results outputting them in the table format you need, e.g: 

echo "<tr>";
echo "<td>".$row['Forename']."</td>"; 
echo "<td>".$row['Surname']."</td>";
//etc
echo "</tr>"

Then take that response and fill the containing table with (jquery AJAX) something like: 

 .done(function( response ) {
    $('#MainTable').html(response);
 }

Upvotes: 1

Related Questions