Tony, the updated code shown below, it won't let me comment this so i've edited my previous post, this code below, doesn't return any results or errors, it just reloads the page. The only issue i can see with the code, is that i need to be able to search, by all table headers, Rating, Name, Area etc. I had a datatable script that live updated the tables to the result, but this doesn't work with the PHP table and i'm not sure how i'm meant to make it work with datatables, as i've never used it before:
updated code:
<html>
<head>
<body>
<style>
table {
color: #333; /* Lighten up font color */
font-family: Helvetica, Arial, sans-serif; /* Nicer font */
width: 100%;
border-collapse:
collapse; border-spacing: 0;
}
td, th { border: 1px solid #00000; height: 30px; } /* Make cells a bit taller */
th {
background: #F3F3F3; /* Light grey background */
font-weight: bold; /* Make sure they're bold */
}
td {
background: #FAFAFA; /* Lighter grey background */
text-align: center; /* Center our text */
}
</style>
<script type="text/javascript" language="javascript" src="http://wcfcourier.com/app/special/data_tables/media/js/jquery.js"></script>
<script type="text/javascript" language="javascript" src="http://wcfcourier.com/app/special/data_tables/media/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" charset="iso-8859-1">
$(document).ready( function(){
$('#five_year').dataTable({
"iDisplayLength": 300,
"aLengthMenu": [[5, 10, 25, 50, -1], [5, 10, 25, 50, "All"]]
});
$(document).ready(function() {
// Setup - add a text input to each footer cell
$('#five-year tfoot th').each( function () {
var title = $('#five_year thead th').eq( $(this).index() ).text();
$(this).html( '<input type="text" placeholder="Search '+title+'" />' );
} );
// DataTable
var table = $('#five_year').DataTable();
// Apply the search
table.columns().eq( 0 ).each( function ( colIdx ) {
$( 'input', table.column( colIdx ).footer() ).on( 'keyup change', function () {
table
.column( colIdx )
.search( this.value )
.draw();
} );
} );
});
} );
$().ready(function() {
var regEx = /(\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*)/;
$("table td").filter(function() {
return $(this).html().match(regEx);
}).each(function() {
$(this).html($(this).html().replace(regEx, "<a href=\"mailto:$1\">$1</a>"));
});
});
</script>
<form action="index.php" method="post">
<input type="text" name="search" placeholder="Search...." />
<input type="submit" value=">>" />
</form>
<?php
$con=mysqli_connect("localhost","root","windows11","main");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($con,"SELECT * FROM `users` ORDER BY ID asc, Name asc") or die(mysqli_error($con));
echo
"<table border=1>
<tr>
<th>Rating</th>
<th>Name</th>
<th>Discipline</th>
<th>Rate</th>
<th>Area</th>
<th>Number</th>
<th>Email</th>
</tr>";
while($row = mysqli_fetch_array($result)){
echo "<tr>";
echo "<td>" . $row['Rating'] . "</td>";
echo "<td>" . $row['Name'] . "</td>";
echo "<td>" . $row['Discipline'] . "</td>";
echo "<td>" . $row['Rate'] . "</td>";
echo "<td>" . $row['Area'] . "</td>";
echo "<td>" . $row['Number'] . "</td>";
echo "<td>" . $row['Email'] . "</td>";
echo "</tr>";
echo "</form>";
}
// If there is a search variable try to search database
if( isset( $_POST['search'] ) ) {
$searchq = $_POST['search'];
$searchq = preg_replace( "#[^0-9a-z]#i", "", $searchq );
$sql = "SELECT * FROM `users` WHERE `Rating` LIKE '%$searchq%';";
if ( $result = mysqli_query( $conn, $sql ) ) {
if ( mysqli_num_rows( $result ) > 0 ) {
echo '
<table class="hoverTable">
<tr>
<th>Rating</th>
<th>Name</th>
<th>Discipline</th>
<th>Rate</th>
<th>Area</th>
<th>Number</th>
<th>Email</th>
</tr>';
while( $row = $result->fetch_assoc() ) {
echo "
<tr>
<td>".$row["rating"]."</td>
<td>".$row["name"]."</td>
<td>".$row["discipline"]."</td>
<td>".$row["rate"]."</td>
<td>".$row["area"]."</td>
<td>".$row["number"]."</td>
<td>".$row["email"]."</td>
</tr>";
}
echo '
</table>';
} else {
$message = "0 results";
}
}
mysqli_free_result( $result );
}
$conn->close();
?>
</body>
</head>
</html>
This is also the Datatables script that works with a HTML table (all the data split out into the table rows using HTML not echo'd out by the PHP script:
<script type="text/javascript" language="javascript" src="http://wcfcourier.com/app/special/data_tables/media/js/jquery.js"></script>
<script type="text/javascript" language="javascript" src="http://wcfcourier.com/app/special/data_tables/media/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" charset="iso-8859-1">
$(document).ready( function(){
$('#five_year').dataTable({
"iDisplayLength": 300,
"aLengthMenu": [[5, 10, 25, 50, -1], [5, 10, 25, 50, "All"]]
});
$(document).ready(function() {
// Setup - add a text input to each footer cell
$('#five-year tfoot th').each( function () {
var title = $('#five_year thead th').eq( $(this).index() ).text();
$(this).html( '<input type="text" placeholder="Search '+title+'" />' );
} );
// DataTable
var table = $('#five_year').DataTable();
// Apply the search
table.columns().eq( 0 ).each( function ( colIdx ) {
$( 'input', table.column( colIdx ).footer() ).on( 'keyup change', function () {
table
.column( colIdx )
.search( this.value )
.draw();
} );
} );
});
} );
$().ready(function() {
var regEx = /(\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*)/;
$("table td").filter(function() {
return $(this).html().match(regEx);
}).each(function() {
$(this).html($(this).html().replace(regEx, "<a href=\"mailto:$1\">$1</a>"));
});
});
</script>
I can see that this datatables code needs a table ID to reference, but i can't assign a table ID to the PHP code as it just crashes the script and page when i try.
thanks for the help.
