Build CRUD Application PHP & Mysql. Add information to database
We continue to build php+mysql web application. In this lesson we realize mechanism of adding information to database through interface.
First of all we will add form to players.php. There will two fields in our form – player name and country (dropdown list).
I notice, dropdown list can be written manually, but it’s not productive. We automate this process, writing getAllCountries function.
I notice, dropdown list can be written manually, but it’s not productive. We automate this process, writing getAllCountries function.
Next we will have to accept the data from our html form. I notice, I didn’t specify action attribute in form. If it so, form will submit data to itself page.
Function addPlayer will work with using INSERT INTO query.
At last homework for you:
- To check form for empty
- Print message, if nothing entered
- Add “team” field
- Make js/jquery validation
Code lesson (players.php)
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Игроки</title>
<link href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" rel="stylesheet">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
<script src="//maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
</head>
<body>
<header>
<nav class="navbar navbar-default" role="navigation">
<div class="container-fluid">
<div class="navbar-header">
<button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-ex1-collapse">
<span class="sr-only">Toggle navigation</span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
<a class="navbar-brand" href="#">Sport CRM</a>
</div>
<div class="collapse navbar-collapse navbar-ex1-collapse">
<ul class="nav navbar-nav">
<li><a href="index.php">Главная</a></li>
<li><a href="players.php">Игроки</a></li>
<li><a href="teams.php">Команды</a></li>
<li><a href="countries.php">Страны</a></li>
</ul>
</div>
</div>
</nav>
</header>
<div id="content">
<div class="container-fluid">
<?php include 'db.php'; ?>
<?php include 'api.php'; ?>
<?php
$players = getAllPlayers($db);
?>
<table class="table table-bordered">
<tr>
<th>Игрок</th>
<th>Команда</th>
<th>Страна</th>
</tr>
<?php foreach ($players as $player) { ?>
<tr>
<td><a href="edit.php?player_id=<?php echo $player['player_id'];?>"><?php echo $player['player_name']; ?></a></td>
<td><?php echo $player['team_name']; ?></td>
<td><?php echo $player['country_name']; ?></td>
</tr>
<?php } ?>
</table>
<button id="addButton" class="btn btn-default">Добавить игрока</button>
<form action="" method="POST" role="form" style="display: none; margin-top: 20px;">
<div class="form-group">
<label for="">Введите имя</label>
<input type="text" class="form-control" id="name" name="name" placeholder="Введите имя">
</div>
<div class="form-group">
<select name="country" class="form-control" id="country">
<?php
$countries = getAllCountries($db);
foreach ($countries as $key => $value) {
echo "<option value=".$value['country_id'].">".$value['country_name']."</option>";
}
?>
</select>
</div>
<button type="submit" class="btn btn-default">Добавить</button>
</form>
</div>
<?php
if(isset($_POST['name']) && $_POST['name'] != '') {
$name = $_POST['name'];
$countryId = $_POST['country'];
addPlayer($db, $name, $countryId);
}
?>
</div>
<footer>
</footer>
<script>
$("#addButton").click(function(){
$("form").slideDown();
});
</script>
</body>
</html>Code lesson (api.php)
<?php
function getAllPlayers($db) {
$sql = "SELECT * FROM players
LEFT JOIN teams ON players.team_id = teams.team_id
LEFT JOIN countries ON players.country_id = countries.country_id;
";
$result = array();
$stmt = $db->prepare($sql);
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$result[$row['player_id']] = $row;
}
return $result;
}
function getAllTeams($db) {
$sql = "SELECT * FROM teams
INNER JOIN countries ON teams.country_id = countries.country_id;
";
$result = array();
$stmt = $db->prepare($sql);
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$result[$row['team_id']] = $row;
}
return $result;
}
function getPlayerById($db, $id) {
$sql = "SELECT * FROM players
WHERE player_id = :player_id
";
$stmt = $db->prepare($sql);
$stmt->bindValue('player_id', $id, PDO::PARAM_INT);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
return $row;
}
function savePlayer($db, $name, $id) {
$sql = " UPDATE players
SET player_name = :player_name
WHERE player_id = :player_id
";
$stmt = $db->prepare($sql);
$stmt->bindValue(':player_name', $name, PDO::PARAM_STR);
$stmt->bindValue(':player_id', $id, PDO::PARAM_INT);
$stmt->execute();
}
function getAllCountries($db) {
$sql = "SELECT * FROM countries";
$res = array();
$stmt = $db->prepare($sql);
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$res[$row['country_id']] = $row;
}
return $res;
}
function addPlayer($db, $playerName, $countryId) {
$sql = "INSERT INTO players(player_name, country_id)
VALUES(:player_name, :country_id)
";
$stmt = $db->prepare($sql);
$stmt->bindValue(':player_name', $playerName, PDO::PARAM_STR);
$stmt->bindValue(':country_id', $countryId, PDO::PARAM_INT);
$stmt->execute();
}
0 Comments