A remark: Your statement preparation is wrongly applied and looses its purpose: to avoid sql injection. No values should be directly passed into the sql statement. Instead, parameter markers (named or not) should be defined in the statement - as placeholders. For each of these markers the corresponding value must be passed either by calling the bindValue method, or the bindParam method, or by defining it as an element value in an array passed directly as argument to the PDOStatement::execute method.
Some suggestions:
- You might also want to read this and this articles on how to apply error/exception handling and this article on applying prepared statements.
- Avoid creating html code from PHP. Separate the php code from the html code.
- Instead of mixing db fetching code with html code (like you did with
while($ds = $sql->fetch()){...}), you should fetch all db data into an array (in the php code part) and iterate through it further (in the html code part).
Below is a code version in which I implement a solution to your task/question. I used my own naming/coding conventions (inclusive for the db table) though - so, as I would apply them in my own project.
Since you didn't specified which library you are using (PDO or mysqli) and because only PDO has the PDOStatement::errorInfo method, I deducted that you are using the PDO library. Therefore, my code uses PDO.
kunden.php
<?php
require 'connection.php';
if (isset($_POST['submit'])) {
$nachname = isset($_POST['nachname']) ? $_POST['nachname'] : '';
$vorname = isset($_POST['vorname']) ? $_POST['vorname'] : '';
if (empty($nachname) && empty($vorname)) {
$errors[] = 'Please provide either the first name, or the last name, or both.';
}
if (!isset($errors)) {
// Array used for creating the WHERE conditions in the sql statement.
$whereConditions = [];
/*
* Used for injecting the proper values for the named parameter markers found
* in the sql statement. It is passed as argument to the PDOStatement::execute method.
*/
$inputParameters = [];
if (!empty($nachname)) {
$whereConditions[] = 'nachname LIKE :nachname';
$inputParameters[] = '%' . $nachname . '%';
}
if (!empty($vorname)) {
$whereConditions[] = 'vorname LIKE :vorname';
$inputParameters[] = '%' . $vorname . '%';
}
$sql = sprintf(
'SELECT kunde_id, nachname, vorname FROM kunden WHERE %s'
, implode(' OR ', $whereConditions)
);
$statement = $connection->prepare($sql);
$statement->execute($inputParameters);
$kunden = $statement->fetchAll(PDO::FETCH_ASSOC);
if (!$kunden) {
$errors[] = 'No clients found for your request.';
}
}
}
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
<meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes" />
<meta charset="UTF-8" />
<!-- The above 3 meta tags must come first in the head -->
<title>Demo</title>
<script src="https://code.jquery.com/jquery-3.2.1.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function () {
$('#nachname').focus();
});
</script>
<style type="text/css">
body {
padding: 30px;
}
label {
/*display: block;*/
font-weight: 400;
}
input[type="text"] {
display: block;
margin-bottom: 20px;
}
button {
display: block;
padding: 7px 10px;
background-color: #8daf15;
color: #fff;
border: none;
}
.messages {
margin-bottom: 20px;
}
.messages .error {
color: #c00;
}
.kunden-list {
margin-top: 20px;
border-collapse: separate;
}
.kunden-list thead th {
padding: 10px;
background-color: #ccc;
}
.kunden-list tbody td {
padding: 10px;
}
</style>
</head>
<body>
<div class="messages">
<?php
if (isset($errors)) {
foreach ($errors as $error) {
?>
<div class="error">
<?php echo $error; ?>
</div>
<?php
}
}
?>
</div>
<div class="form-container">
<form action="" method="post">
<label for="nachname">Nachname:</label>
<input type="text" id="nachname" name="nachname" value="<?php echo isset($nachname) ? $nachname : ''; ?>">
<label for="vorname">Vorname:</label>
<input type="text" id="vorname" name="vorname" value="<?php echo isset($vorname) ? $vorname : ''; ?>">
<button type="submit" name="submit" value="submit">
Senden
</button>
</form>
</div>
<?php
if (isset($kunden) && $kunden) {
?>
<table class="kunden-list">
<thead>
<tr>
<th>ID</th>
<th>Nachname</th>
<th>Vorname</th>
</tr>
</thead>
<tbody>
<?php
foreach ($kunden as $kunde) {
$kundeId = $kunde['kunde_id'];
$nachname = $kunde['nachname'];
$vorname = $kunde['vorname'];
?>
<tr>
<td><?php echo $kundeId; ?></td>
<td><?php echo $nachname; ?></td>
<td><?php echo $vorname; ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
<?php
}
?>
</body>
</html>
connection.php
<?php
// Db configs.
define('HOST', 'localhost');
define('PORT', 3306);
define('DATABASE', 'yourDb');
define('USERNAME', 'yourUser');
define('PASSWORD', 'yourPassword');
define('CHARSET', 'utf8');
/*
* Create a PDO instance as db connection to db.
*
* @link http://php.net/manual/en/class.pdo.php
* @link http://php.net/manual/en/pdo.constants.php
* @link http://php.net/manual/en/pdo.error-handling.php
* @link http://php.net/manual/en/pdo.connections.php
*/
$connection = new PDO(
sprintf('mysql:host=%s;port=%s;dbname=%s;charset=%s', HOST, PORT, DATABASE, CHARSET)
, USERNAME
, PASSWORD
, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => FALSE,
PDO::ATTR_PERSISTENT => FALSE,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]
);
Create table syntax
CREATE TABLE `kunden` (
`kunde_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`nachname` varchar(100) DEFAULT NULL,
`vorname` varchar(255) DEFAULT NULL,
PRIMARY KEY (`kunde_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
` tag outside the table, or prevent SQL-injection? There's no end of corrections you could make.
– KIKO Software Mar 11 '18 at 10:05