Sensation Energy

Tutorials


Content


Advertising


Function 7

PDO PHP MySql



Id Name Email Year Actiona

PDO PHP MySql

PDO PHP is a Database Access Abstraction Layer. It also connects to the database.

Search (search.php)

Search allows us to search for words we have previously entered via the form.


<?php
$server = 'localhost';
$name = '7_2021';
$user = 'root';
$pass = 'root';
try {
$con = new PDO("mysql:host={$server};dbname={$name}", $user, $pass);
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Setting Error Mode as Exception
} catch(PDOException $e) {
echo $e->getMessage();
}
?>
<table>
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Email</a></th>
<th>Year</th>
</tr>
</thead>
<tbody>
<?php
$search=$_POST['search'];
$query = $con->prepare("select * from users where name LIKE '%$search%' OR year LIKE '%$search%' OR email LIKE '%$search%' LIMIT 0 , 10");
$query->bindValue(1, "%$search%", PDO::PARAM_STR);
$query->execute();
if (!$query->rowCount() == 0) {
while ($results = $query->fetch()) {
?>
<tr>
<td><?php echo $results['user_id']; ?></td>
<td><?php echo $results['name']; ?></td>
<td><?php echo $results['email']; ?></td>
<td><?php echo $results['year']; ?></td>
</tr>
<?php
}
} else {
echo 'Nothing found';
}
?>
</tbody>
</table>

Search Form (search_form.php)

Search form is clasic form which is used to enter the keywords that we will search.


<form action="rezult.php" method="GET">
<input type="text" name="search" class="form-input"><br><br>
<input type="submit" value="Search" class="btn-7">
</form>

Database

Database a database containing a table with fields that we will use in our CRUD. it is necessary to create a database named CRUD 2021 in PHPMyAdmin. When you do that, a new page will open and in the upper tab you should click on another SQL field where a page will open in which you will insert the previously copied lower code. And that’s how you made the base.


CREATE TABLE `users` (
`user_id` int(20) NOT NULL,
`name` varchar(100) NOT NULL,
`email` varchar(100) NOT NULL,
`year` int(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `users`
ADD PRIMARY KEY (`user_id`);
ALTER TABLE `users`
MODIFY `user_id` int(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=102;


Connect (conn.php)

Connect allows us to connect to the base.


<?php
$server = 'localhost';
$name = '7_2021';
$user = 'root';
$pass = 'root';
try {
$con = new PDO("mysql:host={$server};dbname={$name}", $user, $pass);
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Setting Error Mode as Exception
} catch(PDOException $e) {
echo $e->getMessage();
}
?>

Select (index.php)

Select allows us to list all the data in the table. Here, all columns from the table are selected unconditionally and printed


<table>
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Email</a></th>
<th>Year</th>
<th>Actiona</th>
</tr>
</thead>
<tbody>
<?php
require_once "conn.php";
$u = "SELECT * FROM users";
$pdo = $con->prepare($u);
$pdo->execute();
$r = $pdo->fetchAll();
foreach($r  as $row) {
?>
<tr>
<td><?php echo $row['user_id']; ?></td>
<td><?php echo $row['name'] ?></td>
<td><?php echo $row['email']; ?></td>
<td><?php echo $row['year'];?></td>
<td><a href="view.php?user_id=<?php echo $row['user_id']; ?>">View</a>
<a href="edit.php?user_id=<?php echo $row['user_id']; ?>">Edit</a>
<a href="delete.php?user_id=<?php echo $row['user_id']; ?>">Delete></a></td>
</tr>
<?php
}
?>
</tbody>
</table>

Insert (add.php)

Insert allows us to insert data into the database. It is important that the name = "add" long and ame = "name" input from the form match if (isset ($ _ POST ['add'])) {and $ name = $ _POST ['name'] ;, insert jus those values we set.


<?php
require_once 'conn.php';
if(isset($_POST['add'])) {
$name =  $_POST['name'];
$year = $_POST['year'];
$email =  $_POST['email'];
$sql = 'INSERT INTO users (name, year, email) VALUES(:name, :age, :email)';
$v= $con->prepare($sql);
if ($v->execute([':name' => $name,':year' => $year, ':email' => $email])) {
echo "<script>window.history.go(-2);</script>";
} else{
echo "<font color='red'>Error<br> $sql. " . $mysqli->error;
}
}
?>
<form action="" method="post">
<div class="form-group">
<label>Name:</label>
<input type="text" name="name" class="form-input" />
</div>
<div class="form-group">
<label>Email:</label>
<input type="text" name="email" class="form-input">
</div>
<div class="form-group">
<label>Year</label>
<input type="number" name="year" class="form-input">
</div>
<div class="btn-group">
<button type="submit" name="add">Add</button>
</div>
</form>


View (view.php)

The view allows us to see only the individual values from the table via id. From select.php, click View, and then go to the View.php page. It is important to emphasize that this ID is important if the value of id 1 shows all data related to 1.


<table>
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Email</th>
<th>Year</th>
</tr>
</thead>
<tbody>
<?php
require_once 'conn.php';
$user_id = $_GET['user_id'];
$sql = "SELECT * from users WHERE user_id=:user_id";
$v = $povezivanje->prepare($sql);
$v->execute([':user_id' => $user_id ]);
$l = $v->fetchAll();
foreach($l as $o) {
$id = $o['id'];
$name = $o['name'];
$year = $o['year'];
$email = $o['email'];
?>
<tr>
<td><?php echo $id ?></td>
<td><?php echo $name ?></td>
<td><?php echo $yaer ?></td>
<td><?php echo $email ?></td>
</tr>
<?php } ?>
</tbody>
</table>

Edit (edit.php)

Edit lets we edit data from the table using ID. Also from selekt.php we chose edit.php and in the value of the ID it shows all the data related to that ID.


<?php
require_once 'conn.php';
$user_id = $_GET['user_id'];
$sql = "SELECT * from users where user_id=:user_id";
$vlada = $con->prepare($sql);
$vlada->execute([':user_id' => $user_id ]);
while($value = $vlada->fetch()){
$user_id = $value['user_id'];
$name = $value['name'];
$year = $value['year'];
$email = $value['email'];
?>
<form  action="" method="post" enctype="multipart/form-data">
<div class="form-group">
<label>Name</label>
<input type="text" class="form-input" name="name" value="<?php echo $name;?>">
</div>
<div class="form-group">
<label>Yaer</label>
<input type="number" class="form-input"  name="year" value="<?php echo $year;?>">
</div>
<div class="form-group">
<label>Email</label>
<input type="text" class="form-input"  name="email" value="<?php echo $email;?>">
</div>
<div class="form-group">
<input type="hidden" name="user_id" value=<?php echo $_GET['user_id'];?>>
<input type="submit" value="submit" name="edit">
</div>
</form>
<?php }?>
<?php
if(isset($_POST['edit'])) {
$name =  $_POST['name'];
$year = $_POST['year'];
$email =  $_POST['email'];
$user_id =  $_POST['user_id'];
$sql = "UPDATE users set name=:name, email=:email, year=:year WHERE user_id=:user_id";
$statement = $con->prepare($sql);
if ($statement->execute([':name' => $name, ':email' => $email, ':year' => $year ':user_id' => $user_id])) {
echo '<script>window.history.go(-2);</script>';
} else{
echo "<font color='red'>greska<br> $sql. " . $mysqli->error;
}
}
?>

Delete (delete.php)

Delete allows us to delete data from the table. Of course via id. From select.php we delete and click delete and all values related to the ID we selected will be deleted.


<?php
require_once 'conn.php';
if (isset( $_GET['user_id'])){
$user_id= $_GET['user_id'];
$d = 'DELETE FROM users WHERE user_id=:user_id';
$v = $con->prepare($d);
if ($v ->execute([':user_id' => $user_id])) {
echo "<script>window.history.back();</script>";
}
}
?>