Fetch data from a database in PHP faster, in this article, we will use an example that shows how we can write an optimized database query. And also check how we can create a database structure which used to run DB queries faster.
5+ Steps to Fetch Data From a Database in PHP Faster
Here are some very important steps which help to optimize the DB query and show data faster,
- Create a good DB structure by specifying the necessary Length or Values and type.
- Do not increase unnecessary Lengths or Values in table fields.
- Assign keys, like primary, unique, and foreign key constraints (if necessary).
- Avoid a maximum of three or more table joins (MySQL Joins).
- Never use select * (if not necessary).
- Only use table field name with SELECT clause, like
SELECT id, first_name, last_name, username FROM userinfo. - Use MySQL – INDEXES which can improve the speed of DB operations.
Now we will create a simple MySQL operation where we first set up the DB connectivity via PHP then we get the values from DB.
Database Structure
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
-- -- Table structure for table `userinfo` -- CREATE TABLE `userinfo` ( `id` int(11) NOT NULL, `first_name` varchar(10) NOT NULL, `last_name` varchar(10) NOT NULL, `username` varchar(50) NOT NULL, `password` varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `userinfo` -- INSERT INTO `userinfo` (`id`, `first_name`, `last_name`, `username`, `password`) VALUES (1, 'Jesse', 'Pinkman', 'superadmin123', 'e807f1fcf82d132f9bb018ca6738a19f'), (4, 'John', 'Doe', 'johndoe123', 'e807f1fcf82d132f9bb018ca6738a19f'); -- -- Indexes for dumped tables -- -- -- Indexes for table `userinfo` -- ALTER TABLE `userinfo` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `userinfo` -- ALTER TABLE `userinfo` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5; COMMIT; |
Also Read: Complete Guide About Create PHP With MySQL Connection
Create a MySQL Database Connection
We use MySQLi Procedural way to connect the database and also use PHP MySQLi function mysqli_connect.
mysqli_connect the function takes 4 parameters,
- Host name (required)
- Database username (required)
- Database password (if set)
- Database name (required)
|
1 2 3 4 5 6 7 |
<?php //MySQLi Procedural $conn = mysqli_connect("localhost","root","","user_details"); if (!$conn) { die("Connection failed: " . mysqli_connect_error()); } ?> |
Create a Web Page to Show Data Using MySQLi Query
Here we just create a web page where we take the complete data from the database by using PHP MySQLi functions.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
<table class="table table-striped table-bordered table-hover"> <thead> <th>ID</th> <th>First Name</th> <th>Last Name</th> <th>Username</th> </thead> <tbody> <?php include('conn.php'); $query = mysqli_query($conn,"SELECT id, first_name, last_name, username FROM `userinfo`"); while($row=mysqli_fetch_array($query)){ ?> <tr> <td><?php echo ucwords($row['id']); ?></td> <td><?php echo ucwords($row['first_name']); ?></td> <td><?php echo ucwords($row['last_name']); ?></td> <td><?php echo $row['username']; ?></td> </tr> <?php } ?> </tbody> </table> |
Also Read: What Does int(11) mean in MySQL With Example
Code Highlights:
Here we create a table with some required rows. Then we include the database connection file include(‘conn.php’). Which we create in the above steps.
Now, here we are using mysqli_query the function, which is used to run the complete MySQL DB query.
mysqli_query the function takes 2 parameters, one is the connectivity variable and the second is the MySQL query.
In the query, you can see we just use specific fields to get data SELECT id, first_name, last_name, username not using SELECT *.
And also use the primary key in the DB table structure.
At last, we use mysqli_fetch_array the function to get all the data, which takes only one parameter which is mysqli_query stored variable.
It is used inside the loop to get multiple data from the database.
Conclusion
In this complete article, we learned how we can Fetch Data From a Database in PHP Faster with the help of some DB modification steps. We create a small example where we get the data from the database in an optimized way.
I hope you understand the complete guide. Please let me know if you want to know more.
Happy Coding..!
[…] Also Read: How to Fetch Data From a Database in PHP Faster? […]
[…] How to Fetch Data From a Database in PHP Faster? […]