Editing/Adding MYSQL errors

imdead · March 16, 2015

Hello everyone,

Please bear with me, this is really messy

I have a few questions, i've tried to render 3 different pages, Default, Edit and Delete, however the edit and default page, keep messing with each other and screwing the forms.


					<?php 
					if (isset($_SESSION['username'])) {
					if ($_SESSION['userlevel'] = '3'){
					  $action = $_GET["action"];
////DELETE A JOB ////
if ($action == "delete"){
$delid = $_GET['delid'];
$query = "DELETE FROM jobs WHERE id=".$delid." LIMIT 1";
$sql = mysql_query($query);
echo("Job succesfully deleted! [ <a href='add_jobs.php'>Back</a> ]");
}
////EDITING A JOB/////
if ($action == "edit"){
print("<strong>Editing a Job:</strong>");
if ($_POST) {
$editid = $_GET['editid'];
$job_title = htmlspecialchars($_POST['job_title']);
$job_description = $_POST['job_description'];
$job_type = $_POST['job_type'];
$job_area = $_POST['job_area'];
$hot = $_POST['hot'];
$nurse_vet = $_POST['nurse_vet'];
$query2 = "UPDATE jobs SET job_title='$job_title', job_description='$job_description', job_type='$job_type', job_area='$job_area', hot_job='$hot', nurse_vet='$nurse_vet' WHERE id='$editid' LIMIT 1"; 
$sql = mysql_query($query2) or die ('Error: '.mysql_error () . " IN $query2");
}else {
$editid = $_GET['editid'];
$s = "SELECT * FROM jobs WHERE id=".$editid." LIMIT 1";
$sql = mysql_query(htmlspecialchars($s)) or die ('Error: '.mysql_error () . " IN $s");
while ($row = mysql_fetch_array($sql)) {
$id = $row['id'];
$job_title = htmlspecialchars($row["job_title"]);
$job_description = $row['job_description'];
$job_type = $row['job_type'];
$job_area = $row['job_area'];
$hot = $row['hot'];
$nurse_vet = $row['nurse_vet'];
echo("<form name='add' method='post' action='?action=edit&editid=$id'>");
echo("<input type='hidden' name='editid' value='$editid'>");
}
}
}
////ADDING A JOB////
if ($action == "add"){
$add = $_POST['add'];
$job_title = $_POST['job_title'];
$job_description = $_POST['job_description'];
$job_type = $_POST['job_type'];
$job_area = $_POST['job_area'];
$hot = $_POST['hot'];
$nurse_vet = $_POST['nurse_vet'];
$id = mysql_insert_id();
$query = "INSERT INTO jobs (id, job_title, job_description, job_type, job_area, hot_job, nurse_vet) VALUES ('$id', '$job_title', '$job_description', '$job_type', '$job_area', '$hot', '$nurse_vet')";
$sql = htmlspecialchars(mysql_query($query)) or die (mysql_error());
}
///EMPTY ////
if ($action == ""){
$job_title="";
$job_description="";
}
?>
<strong>Add A New Job!</strong>
<br />
<br />
<form name='add' method='post' action='?action=add'>
<input type='hidden' name='?action=add'>
Job Title:<br /><input type='text' size='50' name='job_title' value='<?php echo htmlspecialchars($job_title); ?>'><br />
Job Description: <br /><textarea  rows='10' cols='50' name='job_description'><?php echo $job_description; ?></textarea><br />
Job Type: <br /><select name='job_type'><option>Permanent</option><option>Locum or Contract</option></SELECT><br />
Hot Job?<br /> Yes <input type='radio' name='hot' value='Yes'> No <input type='radio' name='hot' value='no' checked><br />
Nurse or Vet job?<br />Vet <input type='radio' name='nurse_vet' value='Vet'> Nurse <input type='radio' name='nurse_vet' value='Nurse' checked><br />
Job Area:<br /><select name='job_area'><option>East Anglia</option><option>All UK</option><option>London / South East</option><option>Midlands</option><option>North West</option><option>Northern Ireland</option><option>Scotland</option><option>South</option><option>South West</option><option>Southern Ireland</option><option>Wales</option><option>Yorkshire / North East</option></SELECT><br />
<input type='Submit'></div>
<?php
if($success == TRUE) {
print("<strong>Success!</strong>");
}
echo("<br>");
echo("</form>");
print("<strong>Existing Jobs:</strong>");
print("<br />");
print("<br />");
echo("<table class=main cellspacing=20 cellpadding=20>");
if(isset($_GET["desc"])){
$query = "SELECT * FROM jobs WHERE 1=1 ORDER by ID DESC";
echo("<td><a href=add_jobs.php>Ref#:</td><td>Title:</td><td>Description:</td><td>Type:</td><td>Area:</td><td>Nurse/Vet:</td><td>Edit:</td><td>Delete:</td><td>Hot:</td>");
}else{
$query = "SELECT * FROM jobs WHERE 1=1 ORDER by ID ASC";
echo("<td><a href=add_jobs.php?desc>Ref#:</td><td>Title:</td><td>Description:</td><td>Type:</td><td>Area:</td><td>Nurse/Vet:</td><td>Edit:</td><td>Delete:</td><td>Hot:</td>");
}
?>
<form name='hotbox' action='hot_update.php' method='POST'>
<?php
$sql = mysql_query($query);
while ($row = mysql_fetch_array($sql)) {
$id = $row['id'];
$job_title = htmlspecialchars($row['job_title']);
$job_description = $row['job_description'];
$job_type = $row['job_type'];
$job_area = $row['job_area'];
$nurse_vet = $row['nurse_vet'];
$hotbox = $row['hot_job'];
$position=18;
$job_description2 = substr($job_description, 0, $position); 
?>
<tr><td><strong><?php echo $id; ?></strong></td><td><strong><?php echo $job_title; ?></strong></td><td><strong><?php echo $job_description2; ?>...</strong></td><td><strong><?php echo $job_type; ?></strong></td><td><strong><?php echo $job_area; ?></strong></td><td><strong><?php echo $nurse_vet; ?></strong></td><td><a href='add_jobs.php?action=edit&editid=<?php echo $id; ?>'>Edit</a></td><td><a href='add_jobs.php?action=delete&delid=<?php echo $id; ?>'>Delete</a></td><td><input name="ONOFF[]" type="checkbox" value="<?php echo $row['id']; ?>" <?php if($row['hot_job'] == 'YES') { echo "checked='checked' "; } ?>/></td></tr>
<?php
}
		?></p><input type='Submit' value='Update'></form></table>
					  </div>
					<?php }else{echo'Stop hacking';}}
?>

< Also you see how the Title wont display the whole title due to the " ' " How do i prevent this?

This is how the page renders

Barand · March 16, 2015

Use double quotes instead of single quotes for the attribute value

value="<?php echo $job_title ?>"

Edited March 16, 2015 by Barand

imdead · March 16, 2015

Use double quotes instead of single quotes for the attribute value

value="<?php echo $job_title ?>"

Thanks very much, that worked for displaying the title,

although when editing I still get this error if there is an apostrophe

 Editing a Job: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 'r', job_description='tyrtyff', job_type='Permanent', job_area='East Anglia', hot' at line 1 IN UPDATE jobs SET job_title='twetwe ' r', job_description='tyrtyff', job_type='Permanent', job_area='East Anglia', hot_job='no', nurse_vet='Nurse' WHERE id='473' LIMIT 1

ginerjm · March 16, 2015

So many things I don't like but I took the time to re-format your code and add some comments to describe what I see wrong.

<?php
session_start();
// ALWAYS TURN ON ERROR CHECKING DURING DEVELOPMENT!!!
error_reporting(E_ALL | E_NOTICE);
ini_set('display_errors', '1');
//***********************
if (isset($_SESSION['username']))
{
if ($_SESSION['userlevel'] = '3') // ***BAD CONDITIONAL STATEMENT
{
$action = $_GET["action"];
////DELETE A JOB ////
if ($action == "delete")
{
$delid = $_GET['delid'];
$query = "DELETE FROM jobs WHERE id=".$delid." LIMIT 1";
$sql = mysql_query($query);
echo("Job succesfully deleted! [ <a href='add_jobs.php'>Back</a> ]");
}
////EDITING A JOB/////
if ($action == "edit")
{
echo("<strong>Editing a Job:</strong>");
if ($_POST) // *** A POST AND A GET ARRAY IN THE SAME PROCESS ???? CONFUSING
{
$editid = $_GET['editid'];
$job_title = htmlspecialchars($_POST['job_title']);
$job_description = $_POST['job_description'];
$job_type = $_POST['job_type'];
$job_area = $_POST['job_area'];
$hot = $_POST['hot'];
$nurse_vet = $_POST['nurse_vet'];
$query2 = "UPDATE jobs SET job_title='$job_title', job_description='$job_description',
job_type='$job_type', job_area='$job_area', hot_job='$hot', nurse_vet='$nurse_vet'
WHERE id='$editid' LIMIT 1";
$sql = mysql_query($query2) or die ('Error: '.mysql_error () . " IN $query2");
}
else
{
$editid = $_GET['editid'];
$s = "SELECT * FROM jobs WHERE id=".$editid." LIMIT 1";
$sql = mysql_query(htmlspecialchars($s)) or die ('Error: '.mysql_error () . " IN $s");
while ($row = mysql_fetch_array($sql))
{
$id = $row['id'];
$job_title = htmlspecialchars($row["job_title"]);
$job_description = $row['job_description'];
$job_type = $row['job_type'];
$job_area = $row['job_area'];
$hot = $row['hot'];
$nurse_vet = $row['nurse_vet'];
// *** YOU WILL HAVE BOTH A GET AND POST ELEMENT FOR 'EDITID' - MORE CONFUSING
echo("<form name='add' method='post' action='?action=edit&editid=$id'>");
echo("<input type='hidden' name='editid' value='$editid'>");
}
}
}
////ADDING A JOB////
if ($action == "add")
{
$add = $_POST['add'];
$job_title = $_POST['job_title'];
$job_description = $_POST['job_description'];
$job_type = $_POST['job_type'];
$job_area = $_POST['job_area'];
$hot = $_POST['hot'];
$nurse_vet = $_POST['nurse_vet'];
$id = mysql_insert_id(); // *** YOU DON'T GET THE INSERTED ID UNTIL *AFTER* THE INSERT OCCURS.
$query = "INSERT INTO jobs (id, job_title, job_description, job_type, job_area, hot_job, nurse_vet) VALUES ('$id', '$job_title', '$job_description', '$job_type', '$job_area', '$hot', '$nurse_vet')";
$sql = htmlspecialchars(mysql_query($query)) or die (mysql_error()); // *** YOU'RE DOING THE HTML.. FUNCTION ON THE RESULTS OF THE QUERY, NOT ON THE STATEMENT, SILLY AS IT IS.
}
///EMPTY ////
if ($action == "")
{
$job_title="";
$job_description="";
}
?>
<strong>Add A New Job!</strong>
<br />
<br />
<form name='add' method='post' action='?action=add'>
<input type='hidden' name='?action=add'> // *** WHAT IS THIS?? AN ELEMENT WITH A QUERY STRING FOR A NAME?
Job Title:<br />
<input type='text' size='50' name='job_title' value='<?php echo htmlspecialchars($job_title); ?>'>
<br />
Job Description:
<br />
<textarea rows='10' cols='50' name='job_description'><?php echo $job_description; ?></textarea>
<br />
Job Type:
<br />
<select name='job_type'>
<option>Permanent</option>
<option>Locum or Contract</option>
</SELECT><br />
Hot Job?
<br />
Yes
<input type='radio' name='hot' value='Yes'>
No
<input type='radio' name='hot' value='no' checked>
<br />
Nurse or Vet job?
<br />
Vet
<input type='radio' name='nurse_vet' value='Vet'>
Nurse
<input type='radio' name='nurse_vet' value='Nurse' checked>
<br />
Job Area:
<br />
<select name='job_area'>
<option>East Anglia</option>
<option>All UK</option>
<option>London / South East</option>
<option>Midlands</option>
<option>North West</option>
<option>Northern Ireland</option>
<option>Scotland</option>
<option>South</option>
<option>South West</option>
<option>Southern Ireland</option>
<option>Wales</option>
<option>Yorkshire / North East</option>
</SELECT>
<br />
<input type='Submit'> // *** THIS SUBMIT HAS NO VALUE SO YOU CANNOT CHECK THAT THIS BUTTON WAS THE ONE CLICKED
</div>
<?php
if($success == TRUE) // *** THIS VAR NOT SET IN THIS CODE
{
print("<strong>Success!</strong>");
}
echo("<br>");
echo("</form>");
print("<strong>Existing Jobs:</strong>");
print("<br />");
print("<br />");
echo("<table class=main cellspacing=20 cellpadding=20>"); // *** CLASS='MAIN'
if(isset($_GET["desc"]))
{
$query = "SELECT * FROM jobs WHERE 1=1 ORDER by ID DESC";
echo "<td><a href=add_jobs.php>Ref#:</td>";
echo "<td>Title:</td>";
echo "<td>Description:</td>";
echo "<td>Type:</td>";
echo "<td>Area:</td>";
echo "<td>Nurse/Vet:</td>";
echo "<td>Edit:</td>";
echo "<td>Delete:</td>";
echo "<td>Hot:</td>";
}
else
{
// *** YOU DUPLICATE ALL THIS JUST TO SORT IN A DIFF ORDER???? SILLY
$query = "SELECT * FROM jobs WHERE 1=1 ORDER by ID ASC";
echo "<td>";
echo "<a href=add_jobs.php?desc>Ref#:</td>";
echo "<td>Title:</td>";
echo "<td>Description:</td>";
echo "<td>Type:</td>";
echo "<td>Area:</td>";
echo "<td>Nurse/Vet:</td>";
echo "<td>Edit:</td>";
echo "<td>Delete:</td>";
echo "<td>Hot:</td>";
}
?>
<form name='hotbox' action='hot_update.php' method='POST'>
<?php
$sql = mysql_query($query);
while ($row = mysql_fetch_array($sql))
{
$id = $row['id'];
$job_title = htmlspecialchars($row['job_title']);
$job_description = $row['job_description'];
$job_type = $row['job_type'];
$job_area = $row['job_area'];
$nurse_vet = $row['nurse_vet'];
$hotbox = $row['hot_job'];
$position=18;
$job_description2 = substr($job_description, 0, $position);
// **** ALL THE FOLLOWING WOULD BE MUCH EASIER AS AN ECHO IN PHP MODE
// ECHO "<td><strong>$id></strong></td>";
?>
<tr>
<td><strong><?php echo $id; ?></strong></td>
<td><strong><?php echo $job_title; ?></strong></td>
<td><strong><?php echo $job_description2; ?>...</strong></td>
<td><strong><?php echo $job_type; ?></strong></td>
<td><strong><?php echo $job_area; ?></strong></td>
<td><strong><?php echo $nurse_vet; ?></strong></td>
<td><a href='add_jobs.php?action=edit&editid=<?php echo $id; ?>'>Edit</a></td>
<td><a href='add_jobs.php?action=delete&delid=<?php echo $id; ?>'>Delete</a></td>
<td><input name="ONOFF[]" type="checkbox" value="<?php echo $row['id']; ?>" <?php if($row['hot_job'] == 'YES') { echo "checked='checked' "; } ?>/></td>
</tr>
<?php
}
?>
</p>
<input type='Submit' value='Update'>
</form>
</table>
</div>
<?php
}
else
{
echo'Stop hacking';
}
}
?>

You should really separate all that html code from the logic and just load var strings for your blocks of generated output and echo them out in the html section. Mixing the two makes it so hard to do things the easy way.

My comments are all preceded with ***

imdead · March 16, 2015

@ginerjm Thankyou, I understand that it's horribley messy and ugly. I've implemented some of your suggestions so far, although i'm still getting stuck on the SQL errors if i add any apostrophes to the statement

<?php 
include"header.php";
error_reporting(E_ALL | E_NOTICE);
ini_set('display_errors', '1');
$success="";
?>
	<div id="info">
					<?php 
					if (isset($_SESSION['username'])) {
					  $action = $_GET["action"];
////DELETE A JOB ////
if ($action == "delete"){
$delid = $_GET['delid'];
$query = "DELETE FROM jobs WHERE id=".$delid." LIMIT 1";
$sql = mysql_query($query);
echo("Job succesfully deleted! [ <a href='add_jobs.php'>Back</a> ]");
}
////EDITING A JOB/////
if ($action == "edit"){
print("<strong>Editing a Job:</strong>");
if ($_POST) {
$editid = $_GET['editid'];
$job_title = htmlspecialchars($_POST["job_title"]);
$job_description = htmlspecialchars($_POST["job_description"]);
$job_type = $_POST["job_type"];
$job_area = $_POST["job_area"];
$hot_job = $_POST["hot_job"];
$nurse_vet = $_POST["nurse_vet"];
$query2 = "UPDATE jobs SET job_title='$job_title', job_description='$job_description', job_type='$job_type', job_area='$job_area', hot_job='$hot_job', nurse_vet='$nurse_vet'
WHERE id='$editid' LIMIT 1";
$sql = mysql_query($query2) or die ('Error: '.mysql_error () . " IN $query2");
}else {
$editid = $_GET['editid'];
$s = "SELECT * FROM jobs WHERE id=".$editid." LIMIT 1";
$sql = mysql_query($s) or die ('Error: '.mysql_error () . " IN $s");
while ($row = mysql_fetch_array($sql)) {
$id = $row['id'];
$job_title = htmlspecialchars($row["job_title"]);
$job_description = htmlspecialchars($row['job_description']);
$job_type = $row['job_type'];
$job_area = $row['job_area'];
$hot_job = $row['hot_job'];
$nurse_vet = $row['nurse_vet'];
echo("<form name='add' method='post' action='?action=edit&editid=$id'>");
echo("<input type='hidden' name='editid' value='$editid'>");
}
}
}
////ADDING A JOB////
if ($action == "add"){
$add = $_POST["add"];
$job_title = htmlspecialchars($_POST["job_title"]);
$job_description = htmlspecialchars($_POST["job_description"]);
$job_type = $_POST["job_type"];
$job_area = $_POST["job_area"];
$hot_job = $_POST["hot_job"];
$nurse_vet = $_POST["nurse_vet"];
$query = "INSERT INTO jobs (job_title, job_description, job_type, job_area, hot_job, nurse_vet) VALUES ('$job_title', '$job_description', '$job_type', '$job_area', '$hot_job', '$nurse_vet')";
$sql = mysql_query($query) or die (mysql_error());
}
///EMPTY ////
if ($action == ""){
$job_title="";
$job_description="";
$job_type="";
$hot_job="";
$nurse_vet="";
$job_area="";
}
?>
<form name='add' method='post' action='?action=add'>
Job Title:<br /><input type='text' size='50' name='job_title' value="<?php echo htmlspecialchars($job_title); ?>"><br />
Job Description: <br /><textarea  rows='10' cols='50' name='job_description'><?php echo htmlspecialchars($job_description); ?></textarea><br />
Job Type: <br /><select name='job_type'><option <?php if($job_type=="Permanent") echo 'selected="selected"'; ?>>Permanent</option><option <?php if($job_type=="Locum or Contract") echo 'selected="selected"'; ?>>Locum or Contract</option></SELECT><br />
Hot Job?<br /> Yes <input type='radio' name='hot_job' value='Yes' <?php if($hot_job=="YES") echo 'checked'; ?>> No <input type='radio' name='hot_job' value='no' <?php if($hot_job=="NO") echo 'checked'; ?>><br />
Nurse or Vet job?<br />Vet <input type='radio' name='nurse_vet' value='Vet' <?php if($nurse_vet=="Vet") echo 'checked'; ?>> Nurse <input type='radio' name='nurse_vet' value='Nurse' <?php if($nurse_vet=="Nurse") echo 'checked'; ?>><br />
Job Area:<br /><select name='job_area'><option <?php if($job_area=="East Anglia") echo 'selected="selected"'; ?>>East Anglia</option><option <?php if($job_area=="All UK") echo 'selected="selected"'; ?>>All UK</option><option <?php if($job_area=="London / South East") echo 'selected="selected"'; ?>>London / South East</option><option <?php if($job_area=="Midlands") echo 'selected="selected"'; ?>>Midlands</option><option <?php if($job_area=="North West") echo 'selected="selected"'; ?>>North West</option><option <?php if($job_area=="Northern Ireland") echo 'selected="selected"'; ?>>Northern Ireland</option><option <?php if($job_area=="Scotland") echo 'selected="selected"'; ?>>Scotland</option><option <?php if($job_area=="South") echo 'selected="selected"'; ?>>South</option><option <?php if($job_area=="South West") echo 'selected="selected"'; ?>>South West</option><option <?php if($job_area=="Southern Ireland") echo 'selected="selected"'; ?>>Southern Ireland</option><option <?php if($job_area=="Wales") echo 'selected="selected"'; ?>>Wales</option><option <?php if($job_area=="Yorkshire / North East") echo 'selected="selected"'; ?>>Yorkshire / North East</option></SELECT><br />
<input type='Submit' value='Submit'></div>
<?php
if($success == TRUE) {
print("<strong>Success!</strong>");
}
echo("<br>");
echo("</form>");
print("<strong>Existing Jobs:</strong>");
print("<br />");
print("<br />");
echo("<table cellspacing=20 cellpadding=20>");
if(isset($_GET["desc"])){
$query = "SELECT * FROM jobs WHERE 1=1 ORDER by ID DESC";
}else{
$query = "SELECT * FROM jobs WHERE 1=1 ORDER by ID ASC";
}
echo("<td><a href=add_jobs.php?desc>Ref#:</td><td>Title:</td><td>Description:</td><td>Type:</td><td>Area:</td><td>Nurse/Vet:</td><td>Edit:</td><td>Delete:</td><td>Hot:</td>");
?>
<form name='hotbox' action='hot_update.php' method='POST'>
<?php
$sql = mysql_query($query);
while ($row = mysql_fetch_array($sql)) {
$id = $row['id'];
$job_title = htmlspecialchars($row['job_title']);
$job_description = $row['job_description'];
$job_type = $row['job_type'];
$job_area = $row['job_area'];
$nurse_vet = $row['nurse_vet'];
$hotbox = $row['hot_job'];
$position=18;
$job_description2 = substr($job_description, 0, $position); 

ECHO "<tr><td><strong>$id</strong></td>";
ECHO "<td><strong>$job_title</strong></td>";
ECHO "<td><strong>$job_description2 ...</strong></td>";
ECHO "<td><strong>$job_type</strong></td>";
ECHO "<td><strong>$job_area</strong></td>";
ECHO "<td><strong>$nurse_vet</strong></td>";
ECHO "<td><a href='add_jobs.php?action=edit&editid=$id'>Edit</a></td>";
ECHO "<td><a href='add_jobs.php?action=delete&delid=$id'>Delete</a></td>";
?>
<td><input name="ONOFF[]" type="checkbox" value="<?php echo $row['id']; ?>" <?php if($row['hot_job'] == 'YES') { echo "checked='checked' "; } ?>/></td></tr>
<?php
}
		?></p><input type='Submit' value='Update'></form></table>
					  </div>
					<?php }else{echo'Stop hacking';}
include"footer.php";
?>

ginerjm · March 16, 2015

I frankly don't see that you did a lot of changing. Don't know what you mean about adding apostrophes either.

Get rid of the used of both $_POST and $_GET. Get your act straight and decide how you want to pass parms.

Barand · March 16, 2015

You should never put data provided by users (ie $_POST, $_GET) directly into a query, it leaves you wide open to SQL injection attacks. You should use mysql_real_escape_string() on the data first. This will also cure your problems with apostrophes in the data.

Better still, stop using the deprecated mysql_ functions and use mysqli or PDO instead with prepared queries.

Sign In

Editing/Adding MYSQL errors

Recommended Posts

imdead

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

imdead

Link to comment

Share on other sites

ginerjm

Link to comment

Share on other sites

imdead

Link to comment

Share on other sites

ginerjm

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information