Solved

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

Posted on 2016-08-16
7
69 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 57

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 57

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
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!

 
LVL 57

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 57

Expert Comment

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

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PHP substring 3 52
MySQL 6 48
Undefined variable with $_POST in PHP 5 39
Last 4 numbers of a variable 9 21
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

739 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