Created a search page where you can extract data from DB.(index.php)
However, even if I specify conditions, I can't pull records according to those conditions from DB well...
[What do you want to do?]
I would like to display the record in my browser to match the criteria I searched using the search form (index.html) below.
[What's going well]
print$sql.";<br>".PHP_EOL;
print_r($data);
The above results are displayed in the browser as follows:(Province ->Tokyo, Classification ->Nursery school)
SELECT* FROM zenkoku where 1 AND perfect=? AND type = ?;
Array([0]=>Tokyo[1]=> Nursery school)
[Something that doesn't work]
The following error will appear in the above [what's going well] and in other cases:
Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that responses to your MySQL server version for the right syntax to use 'prefecture='Tokyo' AND type='Nursery school' at line 1
[What do you want me to tell you?]
Could you tell me why the error is occurring and why?
[Environment]
Local Development Environment, MacOSX, mysql
index.html
<!DOCTYPE html>
<html lang="ja">
<head>
<metacharset="UTF-8">
<tittle>Input Form</title>
</head>
<body>
<form method="post" action="SEARCH_Show.php">br>
Name <br>
<input type="text" name="name" size="60" maxlength="30" value="">br>
Phone <br>
<input type="tel" name="tel" size="30" maxlength="12" value="">br>
Contact Name <br>
<input type="text" name="personon_name" size="30" maxlength="20"
value="><br>
State <br>
<select name="prefect"><br>
<option value="All">All</option>
<option value="Tokyo">Tokyo</option>
<option value="Kanagawa Prefecture">Kanagawa Prefecture</option>
<option value="Chiba prefecture">Chiba prefecture</option>
<option value="Saitama Prefecture">Saitama Prefecture</option>
<option value="Hokkaido">Hokkaido</option>
<option value="Miyagi Prefecture">Miyagi Prefecture</option>
<option value="Aichi prefecture">Aichi prefecture</option>
<option value="Osaka Prefecture">Osaka Prefecture</option>
<option value="Fukuoka Prefecture">Fukuoka Prefecture</option>
</select><br>
City <br>
<input type="text" name="city" size="30" maxlength="30" value="">br>
Classification <br>
<select name="type"><br>
<option value="All">All</option>
<option value="nursery school">nursery school</option>
<option value="Kindergarten">Kindergarten</option>
<option value="circle">circle</option>
<option value="Other">Other</option>
</select><br>
<Button type="submit" class="">Search</button>
</form>
</body>
</html>
index.php
<?php
require 'Manager.php';
require 'Escape.php';
ini_set('display_errors',1);
?>
<!DOCTYPE html>
<html lang="ja">
<head>
<metacharset="UTF-8">
<tittle>Registration Page</tittle>
</head>
<body>
<table border='1' class="TableStyle1" width="1000">
<tr>
<th>Name</th>
<th>State </th>
<th>City>/th>
<th>Address </th>
<th>Category </th>
<th>Phone number </th>
<th>Contact Name </th>
<th>Number of copies</th>
<th>Installation/Distribution</th>
<th> Erase </th>
</tr>
<?php
try{
$db = connect();
$name = filter_input(INPUT_POST, 'name');
$tel=filter_input(INPUT_POST, 'tel');
$person_name=filter_input(INPUT_POST, 'person_name');
$prefecture=filter_input(INPUT_POST, 'prefect');
$city=filter_input(INPUT_POST, 'city');
$type=filter_input(INPUT_POST, 'type');
if(!is_null($name)){
$sql = "SELECT* FROM zenkoku where 1";
$data=[];
}
if($name!==""){
$sql.="AND name=?";
$data[] = $name;
}
if($tel!==""){
$sql.="AND tel=?";
$data[] = $tel;
}
if($person_name!==""){
$sql.="AND personon_name=?";
$data[]=$person_name;
}
if($prefect!=="All"){
$sql.="AND perfect=?";
$data[] = $prefecture;
}
if($city!==""){
$sql.="AND city=?";
$data[] = $city;
}
if($type!==""){
$sql.="AND type=?";
$data[] = $type;
}
print$sql.";<br>".PHP_EOL;
print_r($data);
$stmt = $db->prepare($sql);
$stmt->execute($data);
while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
?>
<tr>
<td><?phpes($row['name']);?>/td>
<td><?phpes($row['prefecture']);?>/td>
<td><?phpes($row['city']);?>/td>
<td><?phpes($row['address']);?>/td>
<td><?phpes($row['type']);?>/td>
<td><?phpes($row['tel']);?>/td>
<td><?phpes($row['personon_name']);?>/td>
<td><?phpes($row['copies']);?>/td>
<td><?phpes($row['set_type']);?>/td>
</tr>
<?php
}
}catch(PDOException$e){
echo$e->getMessage();
exit;
}
?>
</table>
</body>
</html>
If you look at the SQL that is being spouted, it seems that there is no space separation between 1AND and 1AND.
If you add spaces before and after everything in the molding part of SQL of PHP, I think you can issue the correct SQL.
$sql="SELECT* FROM zenkoku where 1";
© 2024 OneMinuteCode. All rights reserved.