Jump to content

You Have An Error In Your Sql Syntax


JeffH13

Recommended Posts

I was working with this topic.

http://www.killersites.com/community/index.php?/topic/1969-basic-php-system-vieweditdeleteadd-records/

Which I found very helpful. I was able to get the view, view-page and new to work.

But I'm getting an error on edit and delete.

 

Here is the error I'm getting.

 

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

 

 

Here is my code...

 

<?php
/* 
EDIT.PHP
Allows user to edit specific entry in database
*/

// creates the edit record form
// since this form is used multiple times in this file, I have made it a function that is easily reusable
function renderForm($cwid, $cwyears, $cwname, $cwwins, $cwlosses, $cwties, $cwpercent, $error)
{
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<title>Edit Coach Wins Record</title>
</head>
<body>
<?php 
// if there are any errors, display them
if ($error != '')
{
echo '<div style="padding:4px; border:1px solid red; color:red;">'.$error.'</div>';
}
?> 

<form action="" method="post">
<input type="hidden" name="cwid" value="<?php echo $cwid; ?>"/>
<div>
<p><strong>ID:</strong> <?php echo $cwid; ?></p>
<strong>Years: *</strong> <input type="text" name="cwyears" value="<?php echo $cwyears; ?>"/><br/>
<strong>Coach Name: *</strong> <input type="text" name="cwname" value="<?php echo $cwname; ?>"/><br/>
<strong>Wins: *</strong> <input type="text" name="cwname" value="<?php echo $cwwins; ?>"/><br/>
<strong>Losses: *</strong> <input type="text" name="cwname" value="<?php echo $cwlosses; ?>"/><br/>
<strong>Ties: *</strong> <input type="text" name="cwname" value="<?php echo $cwties; ?>"/><br/>
<strong>Percent: *</strong> <input type="text" name="cwname" value="<?php echo $cwpercent; ?>"/><br/>
<p>* Required</p>
<input type="submit" name="submit" value="Submit">
</div>
</form> 
</body>
</html> 
<?php
}



// connect to the database
include('connect-db.php');

// check if the form has been submitted. If it has, process the form and save it to the database
if (isset($_POST['submit']))
{ 
// confirm that the 'id' value is a valid integer before getting the form data
if (is_numeric($_POST['cwid']))
{
// get form data, making sure it is valid
$id = $_POST['cwid'];
$cwyears = mysql_real_escape_string(htmlspecialchars($_POST['cwyears']));
$cwname = mysql_real_escape_string(htmlspecialchars($_POST['cwname']));
$cwwins = mysql_real_escape_string(htmlspecialchars($_POST['cwwins']));
$cwlosses = mysql_real_escape_string(htmlspecialchars($_POST['cwlosses']));
$cwties = mysql_real_escape_string(htmlspecialchars($_POST['cwties']));
$cwpercent = mysql_real_escape_string(htmlspecialchars($_POST['cwpercent']));

// check that firstname/lastname fields are both filled in
if ($cwyears == '' || $cwname == '' || $cwwins == '' || $cwlosses == '' || $cwties == '' || $cwpercent == '')
{
// generate error message
$error = 'ERROR: Please fill in all required fields!';

//error, display form
renderForm($cwid, $cwyears, $cwname, $cwwins, $cwlosses, $cwties, $cwpercent, $error);
}
else
{
// save the data to the database
mysql_query("UPDATE coachwin SET cwyears='$cwyears', cwname='$cwname', cwwins='$cwwins', cwlosses='$cwlosses', cwties='$cwties', cwpercent='$cwpercent' WHERE cwid='$cwid'")
or die(mysql_error()); 

// once saved, redirect back to the view page
header("Location: coachwin-view.php"); 
}
}
else
{
// if the 'id' isn't valid, display an error
echo 'Error!';
}
}
else
// if the form hasn't been submitted, get the data from the db and display the form
{

// get the 'id' value from the URL (if it exists), making sure that it is valid (checing that it is numeric/larger than 0)
if (isset($_GET['cwid']) && is_numeric($_GET['cwid']) && $_GET['cwid'] > 0)
{
// query db
$id = $_GET['cwid'];
$result = mysql_query("SELECT * FROM coachwin WHERE cwid=$cwid")
or die(mysql_error()); 
$row = mysql_fetch_array($result);

// check that the 'id' matches up with a row in the databse
if($row)
{

// get data from db
$cwyears = $row['cwyears'];
$cwname = $row['cwname'];
$cwwins = $row['cwwins'];
$cwlosses = $row['cwlosses'];
$cwties = $row['cwties'];
$cwpercent = $row['cwpercent'];

// show form
renderForm($cwid, $cwyears, $cwname, $cwwins, $cwlosses, $cwties, $cwpercent, '');
}
else
// if no match, display result
{
echo "No results!";
}
}
else
// if the 'id' in the URL isn't valid, or if there is no 'id' value, display an error
{
echo 'Error!';
}
}
?>

 

Any help would be greatly appreciated...

Link to comment
Share on other sites

Sometimes the error that displays in the browser isn't super helpful, so I'd suggest doing this...

 

Just before this line in your code:

 

 mysql_query("UPDATE coachwin SET cwyears='$cwyears', cwname='$cwname', cwwins='$cwwins', cwlosses='$cwlosses', cwties='$cwties', cwpercent='$cwpercent' WHERE cwid='$cwid'")
or die(mysql_error()); 

add the lines:

 

echo "UPDATE coachwin SET cwyears='$cwyears', cwname='$cwname', cwwins='$cwwins', cwlosses='$cwlosses', cwties='$cwties', cwpercent='$cwpercent' WHERE cwid='$cwid'";
exit;

Test the code, and act like you are trying to update a record. Instead of updating, the browser should show you the query you are trying to use, then exit. Copy that query, then open up PHPMyAdmin and select your database. Next, select the "SQL" tab, and paste in your large text box. Click the "Go" button to run the query. You should get an error message, but hopefully a more specific error than the one you are currently seeing. Use that error to debug the query. (or, if you are still having trouble, paste what PHPMyAdmin tells you here, and I'll see if there is anything I can do).

 

Personally, I'm not seeing anything that immediately stands out to me as questionable, though I'm obviously missing something if you are getting an error message. You might double check that all your column names match (perhaps you mis-named something when creating the database?), and keep in mind that column names are case sensitive.

Link to comment
Share on other sites

Actually, a quick follow up... I just realized you have two SQL queries in your code -- one to select an element from the database, and one to update that element in the database. When are you getting this error? When you first load the page? Or when you enter information and submit the form?

 

Again, I'm not really seeing anything incorrect with either query, so my guess would be a misspelling or typo in a column name. Maybe when you made the table originally, you named misspelled "cwwins" (maybe "cwins" instead) due to the double "w"s?

Link to comment
Share on other sites

Until you have posted enough to show you are not a spammer, as a new member you are moderated and one of us must approve your posts... naturally we must be online to do that, that is why nothing showed. I approved the last two as they had pertinent information and deleted the first two that would have been just repeats.

 

Sorry for the problem, but popular sites are popular with spammers so we have to do this this way.

 

I do not know the limit, forgot, but it is rather low so you should be cleared soon enough or one of the managers may clear you early. I will try to keep an eye on you to approve your posts as soon as I can.

Link to comment
Share on other sites

Just realized -- this is incorrect:

 

$id = $_GET['cwid'];

$result = mysql_query("SELECT * FROM coachwin WHERE cwid=$cwid")

 

You never create an $cwid variable, meaning you get an incorrect query where "cwid" doesn't equal anything. Change it to:

 

$cwid = $_GET['cwid'];

$result = mysql_query("SELECT * FROM coachwin WHERE cwid=$cwid")

 

and hopefully the issue will go away. You might have the same issue on the delete page -- make sure you are setting the variables used in the mysql query.

Link to comment
Share on other sites

Thanks Ben... I got past that point.

I knew it had to be something stupid...

 

Now if I can get past this it would be great...

 

I click on records to be edit it brings up this screen.

 

post-54397-041071800 1356056176_thumb.jpg

 

This is what i change it to.

 

post-54397-095926600 1356056152_thumb.jpg

 

This is what comes up after I hit submit.

 

post-54397-049022000 1356056231_thumb.jpg

 

Ok I'm kinda at a lost...

 

Thank for all your help.

Link to comment
Share on other sites

That's because your inputs aren't named properly:

 

<strong>Years: *</strong> <input type="text" name="cwyears" value="<?php echo $cwyears; ?>"/><br/>

<strong>Coach Name: *</strong> <input type="text" name="cwname" value="<?php echo $cwname; ?>"/><br/>

<strong>Wins: *</strong> <input type="text" name="cwname" value="<?php echo $cwwins; ?>"/><br/>

<strong>Losses: *</strong> <input type="text" name="cwname" value="<?php echo $cwlosses; ?>"/><br/>

<strong>Ties: *</strong> <input type="text" name="cwname" value="<?php echo $cwties; ?>"/><br/>

<strong>Percent: *</strong> <input type="text" name="cwname" value="<?php echo $cwpercent; ?>"/><br/>

 

Note how they are all named "cwname"?

 

For example, to use $_POST['cwpercent'], you need to have an input named "cwpercent".

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...