Solved

PHP code to display database records in a table on different pages

Posted on 2016-08-16
7
61 Views
Last Modified: 2016-08-16
Hi Experts:

I have a MySQL database table with the following columns: id (primary key), type, title, description

I want to display the the records in a table on a page based on the type.  If type = A display on page 1 and if type = B display on page 2.

I have the below code but it does not pull any data.

<!doctype html>
    <html lang="en">
    <head>
      <meta charset="UTF-8">
      <title>database connections</title>
    </head>
    <body>
      <?php 
		$link = mysql_connect('website.hostingmysql.com', 'user_name', 'password'); 
		if (!$link) { 
			die('Could not connect: ' . mysql_error()); 
		} 
		echo 'Connected successfully'; 
		mysql_select_db(people); 

      
      //execute the SQL query and return records
      $result = mysql_query("SELECT * FROM persons");
      ?>
      <table border="2" style= "background-color: #84ed86; color: #761a9b; margin: 0 auto;" >
      <thead>
        <tr>
          <th>Employee_id</th>
          <th>Employee_Name</th>
          <th>Employee_dob</th>
          <th>Employee_Adress</th>
          <th>Employee_dept</th>
          <td>Employee_salary</td>
        </tr>
      </thead>
      <tbody>
        <?php
          while( $row = mysql_fetch_assoc( $result ) ){
            echo
            "<tr>
              <td>{$row\['id'\]}</td>
              <td>{$row\['title'\]}</td>
			  <td>{$row\['description'\]}</td>
			  <td>{$row\['type'\]}</td>
            </tr>\n";
          }
        ?>
      </tbody>
    </table>
     <?php mysql_close($connector); ?>
    </body>
    </html>

Open in new window

0
Comment
Question by:imranasif17
  • 4
  • 3
7 Comments
 
LVL 55

Expert Comment

by:Julian Hansen
ID: 41757900
Ok lets take this one step at a time.

Firstly - you are using the MySQL library - which has been deprecated. Consider moving over to MySQLi

Secondly - when you say you want your types on different pages - how does that work - do you have to click a link for the type to see those types?

Thirdly, your column definitions in your <thead> don't seem to match the rows you are creating

Forth, why are you escaping your array brackets i.e.
<td>{$row\['id'\]}</td>

Open in new window

Should be
[u]<td>{$row['id']}</td>[/u]

Open in new window

The { } containers in the string will sort out the insertion of the variable for you.

Lets concentrate on these things first.
0
 
LVL 55

Expert Comment

by:Julian Hansen
ID: 41757917
... and this line is also incorrect
mysql_select_db(people); 

Open in new window

should be
mysql_select_db('people');

Open in new window

It looks like you have error reporting off - there are errors that you should be seeing on this page that are not showing.

Add the following to the top of your script
<?php
error_reporting(E_ALL);
?>

Open in new window


Here is your code modified to fix the errors mentioned and to use the MySQLi library
<?php
error_reporting(E_ALL);
?>
<!doctype html>
    <html lang="en">
    <head>
      <meta charset="UTF-8">
      <title>database connections</title>
    </head>
    <body>
<?php 
$mysqli = new mysqli('website.hostingmysql.com', 'user_name', 'password', 'people');
		
if ($mysqli->connect_error) {
	die('Connect Error (' . $mysqli->connect_errno . ') '
		. $mysqli->connect_error);
}
	  
echo 'Connected successfully'; 
     
//execute the SQL query and return records
$result = $mysqli->query("SELECT * FROM persons");
?>
      <table border="2" style= "background-color: #84ed86; color: #761a9b; margin: 0 auto;" >
      <thead>
        <tr>
          <th>Employee_id</th>
          <th>Employee_Title</th>
          <th>Employee_Description</th>
          <th>Employee_Type</th>
        </tr>
      </thead>
      <tbody>
        <?php
          while( $row = $result->fetch_assoc() ){
            echo
            "<tr>
              <td>{$row['id']}</td>
              <td>{$row['title']}</td>
			  <td>{$row['description']}</td>
			  <td>{$row['type']}</td>
            </tr>\n";
          }
        ?>
      </tbody>
    </table>
<?php $mysqli->close(); ?>
    </body>
    </html>

Open in new window

0
 

Author Comment

by:imranasif17
ID: 41757987
Hi Julian:

Thanks that works like a charm.  one last thing.

Secondly - when you say you want your types on different pages - how does that work - do you have to click a link for the type to see those types?

Yes, I will have to click on a link for a type to see those types.  Can you please help me with that?

Type A must display on page 1 (link1) and Type B must display on page 2 (link2).  Basically filter based on the Type and display on respective pages.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 55

Accepted Solution

by:
Julian Hansen earned 500 total points
ID: 41758014
Just include that in the link so something like this

<a href="mypage.php?type=A">Type A</a>
<a href="mypage.php?type=B">Type B</a>
<a href="mypage.php?type=C">Type C</a>

Then in your PHP you can just add that filter to your query so ...

...
// GET THE PARAMETER
$type = isset($_GET['type']) ? $_GET['type'] : '';
// SANITIZE THE INPUT
$type = $mysqli->real_escape_string($type);
$query = "SELECT * FROM people";
// IF WE HAVE A TYPE THEN FILTER ON IT OTHERWISE
// DEFAULT TO FULL TABLE
if (!empty($type)) {
  $query .= "  WHERE `type`='{$type}'";
}
$result = $mysqli->query($query);
...

Open in new window

Full source
<?php
error_reporting(E_ALL);
?><!doctype html>
    <html lang="en">
    <head>
      <meta charset="UTF-8">
      <title>database connections</title>
    </head>
    <body>
<?php 
$mysqli = new mysqli('website.hostingmysql.com', 'user', 'password', 'people');
    
if ($mysqli->connect_error) {
  die('Connect Error (' . $mysqli->connect_errno . ') '
    . $mysqli->connect_error);
}
    
echo 'Connected successfully';
 
// RETRIEVE THE type PARAMETER MAKING ALLOWANCES FOR
// THE CASE WHERE IT IS NOT PRESENT
$type = isset($_GET['type']) ? $_GET['type'] : '';

// SANITIZE THE INPUT - PREVENT SQL INJETION
// ATTACKS
$type = $mysqli->real_escape_string($type);

// CREATE THE QUERY
$query = "SELECT * FROM person";

// IF WE HAVE A TYPE THEN FILTER ON IT OTHERWISE
// DEFAULT TO FULL TABLE
if (!empty($type)) {
  $query .= "  WHERE `type`='{$type}'";
}

// RUN THE QUERY
$result = $mysqli->query($query);

?>
<a href="t1490a.php?type=A">Type A</a>
<a href="t1490a.php?type=B">Type B</a>
<a href="t1490a.php?type=C">Type C</a>
      <table border="2" style= "background-color: #84ed86; color: #761a9b; margin: 0 auto;" >
      <thead>
        <tr>
          <th>Employee_id</th>
          <th>Employee_Title</th>
          <th>Employee_Description</th>
          <th>Employee_Type</th>
        </tr>
      </thead>
      <tbody>
        <?php
          while( $row = $result->fetch_assoc() ){
            echo
            "<tr>
              <td>{$row['id']}</td>
              <td>{$row['title']}</td>
        <td>{$row['description']}</td>
        <td>{$row['type']}</td>
            </tr>\n";
          }
        ?>
      </tbody>
    </table>
<?php $mysqli->close(); ?>
    </body>
    </html>

Open in new window

Working sample here
0
 

Author Comment

by:imranasif17
ID: 41758054
Outstanding.  You are the best.
0
 

Author Closing Comment

by:imranasif17
ID: 41758058
Thanks thanks and lots of thanks.
0
 
LVL 55

Expert Comment

by:Julian Hansen
ID: 41758061
You are most welcome.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question