Solved

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

Posted on 2016-08-16
7
56 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 51

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 51

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 51

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 51

Expert Comment

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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

706 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now