We have seen how to display records of a mysql table here. Now we will learn how to display one record in a single page. This is required where full details of a record are to be shown in a page. Usually the records
unique id is used to collect the details from the table. So same php page is used and the value of unique id of the record is taken as a variable. Before that links to different records are displayed and on click the full details are shown. So we will fist start with displaying a group of records with link to individual records.
Show
Connecting database and executing QueryTo manage data we have to connect to MySQL database and execute query to get our date. Here there are two ways to use PHP drivers to connect to MySQL and execute the functions for getting records. One is using
Portable Data Object ( PDO ) We will learn both here. We will first use PDO and at the end we will use MySQLI. You can use any one for your script. Displaying Links to individual recordsWe will use our student table where we have added two more columns ( fields ) to store address and image. As we have more records so to restrict number of records we will display records of a particular class. The query is here. Using the above query we will display the records by keeping them inside an html table. At the top of the script we will connect to MySQL database.Here is the code. You can see we have used the name field to display a hyper link and by clicking that
the individual record can be displayed in details.php page. We have formatted the hyper link to carry the student id in query string. The output of above code is here
Displaying individual records in pageBy clicking the hyper link the address bar will carry the student id to details.php page to use inside our query. But before using this student id we have to sanitize this data as it can be changed to carry out injection attack. There are different ways to do this but we will check if the data is numeric or not and exit the page execution if we found non-numeric value is stored in id variable. Here is the code to this part. Now we can use this id value inside our query. In above query we have collected the data for the individual record. Now we will display all the fields of the record like this.
Displaying records using MySQLI functionsFirst we will display all records and then on user clicking of link the full details of the student can be displayed.<?Php //////////////////////////////////////////// require "config.php"; // MySQL connection string $count="SELECT name,id,class,mark,sex FROM student LIMIT 10"; if($stmt = $connection->query($count)){ echo "<table>"; while ($row = $stmt->fetch_assoc()) { echo "<tr><td><a href=details.php?id=$row[id]>$row[name]</a></td> <td>$row[class]</td></tr>"; } echo "</table>"; }else{ echo $connection->error; } ?> Displaying all details of single record<?Php //////////////////////////////////////////// // Collecting data from query string $id=$_GET['id']; // Checking data it is a number or not if(!is_numeric($id)){ echo "Data Error"; exit; } // MySQL connection string require "config.php"; $count="SELECT * FROM student where id=?"; if($stmt = $connection->prepare($count)){ $stmt->bind_param('i',$id); $stmt->execute(); $result = $stmt->get_result(); echo "No of records : ".$result->num_rows."<br>"; $row=$result->fetch_object(); echo "<table>"; echo "<tr ><td><b>Name</b></td><td>$row->name</td></tr> <tr><td><b>Class</b></td><td>$row->class</td></tr> <tr ><td><b>Mark</b></td><td>$row->mark</td></tr> <tr><td><b>Address</b></td><td>$row->address</td></tr> <tr ><td><b>Image</b></td><td>$row->img</td></tr> "; echo "</table>"; }else{ echo $connection->error; } ?>MySQLi connection bind_param() MySQLi select query to get data Displaying Image stored in MySQL tableUsually images are stored in any folder or directory but the name of the image file is stored in a field so to display the photo we will format the image file name by using html img tag. Here is an example to display the photo in place of file name ( replace $row->img with this code ). To know more about how to
store file name in table and manage images along with thumbnail using MySQL database , read photo gallery script Here is the dump of the student table ( with address and image column ) How can I get single data from MySQL in php?Data can be fetched from MySQL tables by executing SQL SELECT statement through PHP function mysql_query. You have several options to fetch data from MySQL. The most frequently used option is to use function mysql_fetch_array(). This function returns row as an associative array, a numeric array, or both.
How can I get single record in php?The fetch_row() / mysqli_fetch_row() function fetches one row from a result-set and returns it as an enumerated array.
How do I get only one record in MySQL?To return only the first row that matches your SELECT query, you need to add the LIMIT clause to your SELECT statement. The LIMIT clause is used to control the number of rows returned by your query. When you add LIMIT 1 to the SELECT statement, then only one row will be returned.
How do I find a specific data in MySQL?MySQL Workbench
There is a Schemas tab on the side menu bar, click on the Schemas tab, then double click on a database to select the database you want to search. Then go to menu Database - Search Data, and enter the text you are searching for, click on Start Search.
|