Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

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

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 58

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 58

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 58

Expert Comment

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

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

In this article, we’ll look at how to deploy ProxySQL.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
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…

718 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