Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2016-08-16
7
Medium Priority
?
86 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 61

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 61

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 61

Accepted Solution

by:
Julian Hansen earned 2000 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 61

Expert Comment

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
The viewer will learn how to dynamically set the form action using jQuery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

572 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