PHP Page Does not Display Data Table

Question

I have a search box that displays the search results in a table. The search box uses a simple search query to get the data from a database.

below is the code for the search box

 <form id="search-form" mmethod="post" action="search.php">
  <input name="searcher" id="search-bar" type="search" placeholder="Type to Search">
  <input id="search-button" type="submit" value="Find">
</form

The PHP:

$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password=""; // Mysql password 
$db_name="datacentre"; // Database name 
$tbl_name="data_centre_users"; // Table name 
$server_name="localhost";


if(isset($_POST['submit'])) {
  $searchword = $_POST['seacher'];  

// Create connection
$con = new mysqli($server_name, $username, $password, $db_name , 3306);

if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}  

// Retrieve data from database 
$sql="SELECT * FROM $tbl_name WHERE first_name='$searchword' OR last_name='$searchword' ";
$result = $con->query($sql);

$rows = $result->fetch_assoc();          

?>
<section id="sidebar">

</section>

<section id="content">

<div id="scroll-table">
<table >
<caption>
           Search Results
            </caption>
            <tr>
                <th class="center"><strong>ID</strong></th>
                <th class="center"><strong>FirstName</strong></th>
                <th class="center"><strong>Lastname</strong></th>
                <th class="center"><strong>Request</strong></th>
                <th class="center"><strong>Purpose</strong></th>
                <th class="center"><strong>Description</strong></th>
                <th class="center"><strong>Booking Time</strong></th>
                <th class="center"><strong>Access Time</strong></th>
                <th class="center"><strong>Exit Time</strong></th>
                <th class="center"><strong>Approved</strong></th>
                <th class="center"><strong>Approved By</strong></th>
                <th class="center"><strong>Update</strong></th>
            </tr>
            <?php
            if($result->num_rows > 0){
                // output data of each row
                while($rows = $result->fetch_assoc()){ ?>
                    <tr>
                        <td class="center"><?php echo $rows['id']; ?></td>
                        <td class="center"><?php echo $rows['fisrt_name']; ?></td>
                        <td class="center"><?php echo $rows['last_name']; ?></td>
                        <td class="center"><?php echo $rows['request']; ?></td>
                        <td class="center"><?php echo $rows['purpose']; ?></td>
                        <td class="center"><?php echo $rows['description']; ?></td>
                        <td class="center"><?php echo $rows['booking_time']; ?></td>
                        <td class="center"><?php echo $rows['access_time']; ?></td>
                        <td class="center"><?php echo $rows['exit_time']; ?></td>
                        <td class="center"><?php echo $rows['approved']; ?></td>
                        <td class="center"><?php echo $rows['approved_by']; ?></td>
                        <td class="center" ><a href="update.php?id=<?php echo $rows['id']; ?>">update</a></td>
                    </tr>

                    <?php
                }
            }       
      ?> 
</table>
</div>
</section>
<

<aside></aside>

<?php
$con->close();
}
include('footer.php');
?>

When I run the code the page displayed is empty.

Please remove $rows = $result->fetch_assoc(); and then it will be working. — Naveed Ramzan
– Naveed Ramzan, Commented Jul 18, 2015 at 17:25

Juank · Accepted Answer · 2015-07-18 17:25:30Z

1

Check out the following:

<form id="search-form" mmethod="post" action="search.php">

Should be:

<form id="search-form" method="post" action="search.php">

You need to escape it. The way it is now you are wide open to SQL-injection

$searchword = $_POST['seacher'];

So something like below. Also note the error : seacher / searcher in the $_POST

  $searchword = $con->real_escape_string( $_POST['searcher'] );

Put (``) backticks around table and column names to prevent "mysql reserved word error"

// Retrieve data from database 
$sql="SELECT * FROM `$tbl_name` WHERE `first_name` = '$searchword' OR `last_name` = '$searchword' ";

Remove the first fetch because it will interfere with the other

$rows = $result->fetch_assoc();

Just keep the one just before your table

while($rows = $result->fetch_assoc()){

Note the error in your table

<td class="center"><?php echo $rows['first_name']; ?></td> <!-- ['fisrt_name'] -->

For your

if($result->num_rows > 0){

You could add the following:

} else {
  echo 'Nothing found'; 
}

edited Jul 18, 2015 at 17:25

Juank

6,2661 gold badge30 silver badges29 bronze badges

answered Jul 18, 2015 at 16:50

SuperDJ

7,69911 gold badges44 silver badges80 bronze badges

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

4 Comments

Wee Zel Over a year ago

he can't call real_escape_string() until $con is instantiated, so he needs to move the new mysqli() line up - I'd given up by the time I'd got to this, it would have been quicker to rewrite the scripts

SuperDJ Over a year ago

@WeeZel Yes I know but he atleast should escape the user input with something. And in this case I was just giving a option as there are many more options to escape user input. Also note that it shouldn''t make much of a difference for him to move the connection a few lines up as he already declares the credentials needed at the top of the file

Wee Zel Over a year ago

sadly with errors in both the html and the php code I think this one really needs to be hand held all the way

SuperDJ Over a year ago

That's up to the OP but I think with all the answers here most of the errors in this script should be fixed.

Wee Zel · Accepted Answer · 2015-07-18 16:42:23Z

sorry dude, but there are a couple of issues here

firstly fix your form: method="post" - not mmethod; type="text" - not type="search"; </form> - not </form

<form id="search-form" method="post" action="search.php">
  <input name="searcher" id="search-bar" type="text" placeholder="Type to Search">
  <input id="search-button" type="submit" value="Find">
</form>

secondly change:

if (isset($_POST['searcher'])) { // changed from submit

thirdly handle any errors in your query:

if (($result = $con->query($sql)) === false) {
  die("error: ".$con->error); // todo: improve error handling
}

fourthly remove this line:

$rows = $result->fetch_assoc();

finally, for my sanity only, please remove this line (you really don't need it):

if($result->num_rows > 0){

and it's matching:

and don't even get me started on escaping user input in case of SQL injection!

good luck, I hope you get this running

Shehary · Accepted Answer · 2015-07-18 16:46:09Z

In you HTML, Input must has a name that you are trying to post.

<form id="search-form" method="post" action="search.php"> //spelling correction as you have type mistake mmethod
  <input name="searcher" id="search-bar" type="text" placeholder="Type to Search">
  <input id="search-button" type="submit" name="submit" value="Find">
</form>

and in your PHP make sure you are posting;

if(isset($_POST['submit']) && $_POST['submit']=="Find"){

AND try like this;

$sql="SELECT * FROM '$tbl_name' WHERE first_name='$searchword' OR last_name='$searchword' ";

OR

$sql="SELECT * FROM $tbl_name WHERE first_name='$searchword' OR last_name='$searchword' ";

AND

// Retrieve data from database 
$sql="SELECT * FROM $tbl_name WHERE first_name='$searchword' OR last_name='$searchword' ";
$result = $con->query($sql);

$rows = $result->fetch_assoc();   //Remove this you don't need it
?>

Because later you are running a loop here

<?php
if($result->num_rows > 0){
// output data of each row
while($rows = $result->fetch_assoc()){ ?>

Vishal Patel · Accepted Answer · 2015-07-18 16:47:15Z

0

You have used $result->fetch_assoc() this line two times in your code so your query has only 1 result then 1 result is comes in first statment then when you tries second time used before html of your table you get nothing.

answered Jul 18, 2015 at 16:47

Vishal Patel

1,74517 silver badges26 bronze badges

Collectives™ on Stack Overflow

PHP Page Does not Display Data Table

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related