PHP PDO MYSQL SELECT query data
PHP PDO mysql connection and query data
CREATE TABLE IF NOT EXISTS `demo_user` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `email` varchar(100) NOT NULL, PRIMARY KEY (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; INSERT INTO `demo_user` (`user_id`, `name`, `email`) VALUES (1, 'Lucy', 'lucy@fakemail.com'), (2, 'Brooke', 'brooke@fakemail.com'), (3, 'Anna', 'anna@fakemail.com'), (4, 'Mariya', 'mariya@fakemail.com'), (5, 'Jack', 'jack@fakemail.com'), (6, 'Jack', 'jack123@fakemail.com'), (7, 'Anna', 'anna123@fakemail.com');
<?php define('DBHOST','localhost'); define('DBUSER','root'); //DB username define('DBPASS',''); //DB password define('DBNAME','demo_table'); //DB table name try { $db_conn = new PDO("mysql:host=".DBHOST.";charset=utf8mb4;dbname=".DBNAME, DBUSER, DBPASS); $db_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //connection success } catch(PDOException $e) { //connection error die("Error connection ".$e->getMessage()); } ?>
<!DOCTYPE html> <html> <head> <title>User Table</title> <style> #myTable{ font-family: 'helvetica neue',helvetica,arial,'lucida grande',sans-serif; border-collapse: collapse; } #myTable td{ border: solid 1px #C9C9C9; padding: 10px; font-size: 12px; text-align: center; } #myTable tr{ color: #000000; } #myTable th{ border: solid 1px #BFBFBF; padding: 10px; color: #000000; background-color: #f5f5f5; text-align: center; font-size: 12px; } </style> </head> <body> <form action="<?php echo $_SERVER['PHP_SELF'] ?>" method="post"> <div>Search user name</div><br> <div>Name <input type="text" name="user_name" required=""></div><br> <div><input type="submit" name="submit" value="submit"></div><br> </form> <?php if (isset($_POST['submit']) && isset($_POST['user_name'])) { require 'db_connection.php'; $name = $_POST['user_name']; $stmt = $db_conn->prepare('SELECT `user_id`, `name`, `email` FROM `demo_user` WHERE `name` = :name'); $stmt->bindValue(':name', $name, PDO::PARAM_STR); $stmt->execute(); $row = $stmt->fetchAll(PDO::FETCH_ASSOC); //check if row exist if ($row) { echo '<table id="myTable" width="100%"> <thead> <tr> <th>User ID</th> <th>Name</th> <th>Email</th> </tr> </thead>'; foreach ($row as $value) { echo '<tbody> <tr> <td>'.$value['user_id'].'</td> <td>'.$value['name'].'</td> <td>'.$value['email'].'</td> </tr>'; } echo ' </tbody> </table>'; } else { echo "No Data Found"; } } //close connection $db_conn = null; ?> <script> //prevent resubmission post if ( window.history.replaceState ) { window.history.replaceState( null, null, window.location.href ); } </script> </body> </html>