CODEWITHSUNDEEP
phpjquerymysqlajax
Nicklas
Nicklas

Reputation: 57

Creating a table with mysql, php and ajax (with jquery)

For my new project i want the so modern approach of not needing to reload a page on every database request. :) I want the script to query the database and create a table with the query information.

I have tried different scripts i have found on the internet. The one below was closest to my needs.

index.php

<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>
<html xmlns='http://www.w3.org/1999/xhtml'>
<head>
<title>Display Page</title>
<meta http-equiv='Content-Type' content='text/html; charset=utf-8' />
<script language='JavaScript' type='text/javascript' src='https://ajax.googleapis.com/ajax/libs/jquery/1.6.4/jquery.min.js'></script>
</head>

<body>
<button type='button' name='getdata' id='getdata'>Get Data.</button>

<div id='result_table'>

</div>

<script type='text/javascript' language='javascript'>
$('#getdata').click(function(){

$.ajax({
        url: 'getdata.php',
        type:'POST',
        dataType: 'json',
        success: function(output_string){
                $('#result_table').append(output_string);
            } // End of success function of ajax form
        }); // End of ajax call    

});
</script>
</body>
</html>

getdata.php

<?php
include('conn.inc.php');
//Query of facebook database
$facebook = mysql_query('SELECT * FROM users')
or die(mysql_error());

//Output results
if(!$facebook)
{
    mysql_close();
    echo json_encode('There was an error running the query: ' . mysql_error());
}
elseif(!mysql_num_rows($facebook))
{
    mysql_close();
    echo json_encode('No results returned');
}
else
{
    $output_string = '';
    $output_string .=  '<table border="1">';
    while($row = mysql_fetch_assoc($facebook))
    {
        $output_string .= '<tr>';
        foreach($row as $value)
        {
            $output_string .= '<td>{$value}</td>';
        }
        $output_string .= '</tr>';
    }
    $output_string .= '</table>';
}

mysql_close();
// This echo for jquery 
echo json_encode($output_string);

?>

But i only get a table with a bunch of {$value} inside the table. I've tried with only $value but got a bunch of zeros.

I tried a simple script

$query = "SELECT users_name, users_password FROM users WHERE users_name = 'name'";
$result = mysql_query($query) or die(mysql_error());

$row = mysql_fetch_array($result);

echo $row['users_name'];

And then i get som results but with this script i have to refresh the page on every search. To be clear i want to be able to create a table with the information from a mysql database and display it on screen with reloading the page.

Any ideas?

Upvotes: 4

Views: 8849

Answers (4)

David Thekiso
David Thekiso

Reputation: 1

100% WORKING Just remove closing tags. E.g when opening a tag and store it in $output_string NEVER include the closing part...

Then don't include $value in quotation marks... Put it outside quotation mark and then separate $value and closing quotation mark with dot the semi colon.

Upvotes: 0

pocesar
pocesar

Reputation: 7050

You may invert the logic, and do it on client side, using a library that does that automatically, like http://phery-php-ajax.net Instead of creating the table on the server side, send it as JSON to the browser, which is faster, and build your table:

Phery::instance()->set(array(
'load' => function(){
  /* rest of mysql code */
  $rows = array();
  while($row = mysql_fetch_assoc($facebook)) { $rows[] = $row; }
  return PheryResponse::factory()->json($rows);
})->process();

then in the client side inside $(function(){});

$(function(){
  var $result_table = $('#result_table'); 
  $result_table.phery('make', 'load');
  $result_table.bind('phery:json', function(e, data){
    var $this = $(this);
    for (var i = 0; i < data.length; i++) {
      $this.append($('<tr/>', {
        'html': '<td>' + data[i].row_name + '</td>'
      }));
    }
  });
  $result_table.phery('remote');
});

Upvotes: 0

Pang
Pang

Reputation: 10110

Instead of

$output_string .= '<td>{$value}</td>';

try

$output_string .= "<td>{$value}</td>";

i.e. replace the single quotes with double quotes.

See the doc here, which says:

When a string is specified in double quotes ... variables are parsed within it.

and

variables ... will not be expanded when they occur in single quoted strings.

Upvotes: 0

Joseph
Joseph

Reputation: 238

You should just use $value instead of {$value}. You don't need another foreach loop inside the while loop.

$output_string = '';
$output_string .=  '<table border="1">';
while($row = mysql_fetch_assoc($facebook))
{
    $output_string .= '<tr>';
    $output_string .= '<td>'.$row['Your table column name here'].'</td>';
    $output_string .= '</tr>';
}
$output_string .= '</table>';

Upvotes: 2

Related Questions