I'm pretty new to coding with php and SQL, so I'm probably going to have a lot of questions. But as the title states, I'm getting this error...
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
I'm not sure what this is referring to. I've gone over the code as much as I can, but I can't find a syntax error. Maybe it's something I just don't know yet.
<?php
// including the database connection file
include_once("config.php");
if(isset($_POST['update']) && isset($_GET['site']))
{
$sitenumber = $_POST['sitenumber'];
$videolink = $_POST['videolink'];
$daynight = $_POST['daynight'];
$maxtents = $_POST['maxtents'];
$maxpersons = $_POST['maxpersons'];
$geography = $_POST['geography'];
$view = $_POST['view'];
$forestcover = $_POST['forestcover'];
$waterfront = $_POST['waterfront'];
$firepit = $_POST['firepit'];
$description = $_POST['description'];
$reslink = $_POST['reslink'];
// checking empty fields
if(empty($sitenumber) || empty($videolink) || empty($daynight) ||
empty($maxtents) || empty($maxpersons) || empty($geography) ||
empty($view) || empty($forestcover) || empty($waterfront) ||
empty($firepit) || empty($description) || empty($reslink)) {
if(empty($sitenumber)) {
echo "<font color='red'>Site Number field is empty.</font><br/>";
}
if(empty($videolink)) {
echo "<font color='red'>YouTube Link field is empty.</font><br/>";
}
if(empty($daynight)) {
echo "<font color='red'>Day or overnight field is empty.</font>
<br/>";
}
if(empty($maxtents)) {
echo "<font color='red'>Maximum Tents field is empty.</font><br/>";
}
if(empty($maxpersons)) {
echo "<font color='red'>Maximum Persons field is empty.</font>
<br/>";
}
if(empty($geography)) {
echo "<font color='red'>Geography field is empty.</font><br/>";
}
if(empty($view)) {
echo "<font color='red'>View field is empty.</font><br/>";
}
if(empty($forestcover)) {
echo "<font color='red'>Forest Cover field is empty.</font><br/>";
}
if(empty($waterfront)) {
echo "<font color='red'>Waterfront Access field is empty.</font>
<br/>";
}
if(empty($firepit)) {
echo "<font color='red'>Firepit field is empty.</font><br/>";
}
if(empty($description)) {
echo "<font color='red'>Description field is empty.</font><br/>";
}
if(empty($reslink)) {
echo "<font color='red'>Reservation Link Access field is empty.
</font><br/>";
}
} else {
//updating the table
$result = mysqli_query($mysqli, "UPDATE sites SET
sitenumber='$sitenumber',videolink='$videolink',daynight='$daynight',
maxtents='$maxtents',maxpersons='$maxpersons',geography='$geography',
view='$view',forestcover='$forestcover',waterfront='$waterfront',
firepit='$firepit',description='$description',reslink='$reslink' WHERE
sitenumber=$sitenumber");
//redirectig to the display page. In our case, it is index.php
//header("Location: index.php");
}
}
echo mysqli_error($mysqli);
?>
<?php
//getting id from url
$sitenumber = $_GET['site'];
//selecting data associated with this particular id
$result = mysqli_query($mysqli, "SELECT * FROM sites WHERE
sitenumber=$sitenumber");
while($res = mysqli_fetch_array($result))
{
$sitenumber = $res['sitenumber'];
$videolink = $res['videolink'];
$daynight = $res['daynight'];
$maxtents = $res['maxtents'];
$maxpersons = $res['maxpersons'];
$geography = $res['geography'];
$view = $res['view'];
$forestcover = $res['forestcover'];
$waterfront = $res['waterfront'];
$firepit = $res['firepit'];
$description = $res['description'];
$reslink = $res['reslink'];
}
echo mysqli_error($mysqli);
?>
<html>
<head>
<title>Edit Data</title>
</head>
<body>
<a href="index.php">Home</a>
<br/><br/>
<form name="form1" method="post" action="edit.php">
<table border="0">
<tr>
<td>Site Number</td>
<td><input type="number" name="sitenumber" value="<?php echo
$sitenumber;?>"></td>
</tr>
<tr>
<td>YouTube Link</td>
<td><input type="url" name="videolink" value="<?php echo
$videolink;?>"></td>
</tr>
<tr>
<td>Day or Overnight</td>
<td><select name="daynight" value="<?php echo $daynight;?>">
<option value="Day">Day</option>
<option value="Overnight">Overnight</option></td>
</tr>
<tr>
<td>Maximum Tents</td>
<td><input type="number" name="maxtents" value="<?php echo
$maxtents;?>"></td>
</tr>
<tr>
<td>Maximum Persons</td>
<td><input type="number" name="maxpersons" value="<?php echo
$maxpersons;?>"></td>
</tr>
<tr>
<td>Geography</td>
<td><input type="text" name="geography" value="<?php echo
$geography;?>"></td>
</tr>
<tr>
<td>View</td>
<td><input type="text" name="view" value="<?php echo $view;?>">
</td>
</tr>
<tr>
<td>Forest Cover</td>
<td><input type="text" name="forestcover" value="<?php echo
$forestcover;?>"></td>
</tr
<tr>
<td>Waterfront Access</td>
<td><select name="waterfront" value="<?php echo $waterfront;?>">
<option value="Yes">Yes</option>
<option value="No">No</option></td>
</tr>
<tr>
<td>Firepit Availability</td>
<td><select name="firepit" value="<?php echo $firepit;?>">
<option value="Yes">Yes</option>
<option value="No">No</option></td>
</tr>
<tr>
<td>Site Description</td>
<td><input type="text" name="description" value="<?php echo
$description;?>"></td>
</tr>
<tr>
<td>Reservation Link</td>
<td><input type="url" name="reslink" value="<?php echo $reslink;?
>"></td>
</tr>
<td><input type="hidden" name="site" value="<?php echo
$_GET['site'];?>"></td>
<td><input type="submit" name="update" value="Update"></td>
</tr>
</table>
</form>
</body>
</html>
Sorry for the long code here, but I felt it was a little necessary to see the full context here.
There is also a break somewhere with the variables. The sitenumber variable isn't updating, and every variable after that is getting this error...
Notice: Undefined variable: videolink in C:\wamp\www\code\edit.php on line 124
So, this is kind of a two pronged problem. Help would be greatly appreciated.
Correct this :
$result = mysqli_query($mysqli, "SELECT * FROM sites WHERE sitenumber='".$sitenumber."' ");
And this :
$result = mysqli_query($mysqli, "UPDATE sites SET
sitenumber='$sitenumber',videolink='$videolink',daynight='$daynight',
maxtents='$maxtents',maxpersons='$maxpersons',geography='$geography',
view='$view',forestcover='$forestcover',waterfront='$waterfront',
firepit='$firepit',description='$description',reslink='$reslink' WHERE
sitenumber='$sitenumber'");
Your SQL query seems good, but the problem can come from the values of your variables.
Since your query is not escaped properly (and it should be for better security), I would advise you to debug your query before executing. This way you will be able to understand what is going to be executed in your database.
If you don't use xdebug, you can just put your query into a variable and then dump it using var_dump.
Then, open phpmyadmin (I assume you have an access to it, at least), and paste the value of your variable (which is your query) into the SQL editor. Then execute it and you should have a message explaining where the error is. It will help you understand why it is important to use prepared statement by seeing which variable has a wrong value (meaning it includes a ' or a ", for instance).
I hope it will help