...

View Full Version : Simple Insert won't work



kenwvs
08-20-2007, 04:06 AM
I am getting data from a form and trying to add it to a db. I am really lost as to where I have gone wrong. It should be straight forward, but obviously I am wrong.

I believe that the DB, tablename and password is set up correctly as I am able to retrieve info from a different table that supplies some drop down boxes with info.

Here is a sample of part of the form



<?php

if (!isset($_POST['Submit']))
{
?>
<form action="" method="post" enctype="multipart/form-data">
<input type="hidden" name="MAX_FILE_SIZE" value="1572864">
<center>
<div>

<B>Program:</B> <select name='program'>

<?php

$result = mysql_query("SELECT * FROM Program") or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
echo "<option>$row[Program]</option>";
}
?>

more of the same above... and then

<B>Contact Phone #:</B>
<input type="text" size="14" maxlength="14" name="ConPhone">
<B>Contact Email</B>
<input type="text" size="25" maxlength="25" name="ConEmail">
</div><BR>
<div>
<B>Comments:</B>
<textarea rows="1" name="ConComm" cols="80"></textarea><BR>

</div><BR>

</center>
<div><center><BR><BR>
<input type="submit" name="Submit" value="Submit New Meeting"> </center>
</div>
<BR><BR><BR>
</div>
</form>


and for the code to get it to the DB, I have this



<?php
}else {
$Program=$_POST['Program'];
$City=$_POST['City'];
$Day=$_POST['Day'];
$Time=$_POST['Time'];
$Address=$_POST['Address'];
$Building=$_POST['Building'];
$Type=$_POST['Type'];
$Group=$_POST['Group'];
$Notes=$_POST['Notes'];
$Other=$_POST['Other'];
$ConName=$_POST['ConName'];
$ConAdd=$_POST['ConAdd'];
$ConPhone=$_POST['ConPhone'];
$ConEmail=$_POST['ConEmail'];
$ConComm=$_POST['ConComm'];

$mysql_query=("INSERT INTO `Meetings`(Program, City, Day, Time, Address, Building, Type, Group, Notes, Other, ConName, ConADD, ConPhone, ConEmail, ConComm)
VALUES ('$Program', '$City', '$Day', '$Time', '$Address', '$Building', '$Type', '$Group', '$Notes', '$Other', '$ConName', '$ConAdd',
'$ConPhone', '$ConEmail', '$ConComm')" or die(mysql_error()));
$result=mysql_query($sql);
Echo "Your information has been successfully added to the Meeting List.";
flush();
sleep(6);
print '<script type="text/javascript">location.replace(\'index.php\');</script>';
}
?>

[/code]

_Aerospace_Eng_
08-20-2007, 06:17 AM
You have some parentheses where they shouldn't be. And you are trying to pass a variable called $sql but I don't even see this variable in your code. Change this

$mysql_query=("INSERT INTO `Meetings`(Program, City, Day, Time, Address, Building, Type, Group, Notes, Other, ConName, ConADD, ConPhone, ConEmail, ConComm)
VALUES ('$Program', '$City', '$Day', '$Time', '$Address', '$Building', '$Type', '$Group', '$Notes', '$Other', '$ConName', '$ConAdd',
'$ConPhone', '$ConEmail', '$ConComm')" or die(mysql_error()));
$result=mysql_query($sql);
to this

$sql="INSERT INTO `Meetings`(Program, City, Day, Time, Address, Building, Type, Group, Notes, Other, ConName, ConADD, ConPhone, ConEmail, ConComm)
VALUES ('$Program', '$City', '$Day', '$Time', '$Address', '$Building', '$Type', '$Group', '$Notes', '$Other', '$ConName', '$ConAdd',
'$ConPhone', '$ConEmail', '$ConComm')";
$result=mysql_query($sql) or die(mysql_error());

kenwvs
08-20-2007, 07:10 AM
Thanks for the help. I am thinking I am much closer, but getting an error, and not sure what it is saying. I am using MySQL version 5.0.27 standard

I have changed the code as you have suggested, changed one spelling error and getting this error. Any ideas?

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 'Group, Notes, Other, ConName, ConAdd, ConPhone, ConEmail, ConComm) VALUES ('', '' at line 1

_Aerospace_Eng_
08-20-2007, 07:16 AM
Change this

$sql="INSERT INTO `Meetings`(Program, City, Day, Time, Address, Building, Type, Group, Notes, Other, ConName, ConADD, ConPhone, ConEmail, ConComm)
VALUES ('$Program', '$City', '$Day', '$Time', '$Address', '$Building', '$Type', '$Group', '$Notes', '$Other', '$ConName', '$ConAdd',
'$ConPhone', '$ConEmail', '$ConComm')";
$result=mysql_query($sql) or die(mysql_error());
to this


return mysql_real_escape_string($data, $dbc);
}
$sql="INSERT INTO `Meetings`(Program, City, Day, Time, Address, Building, Type, Group, Notes, Other, ConName, ConADD, ConPhone, ConEmail, ConComm)
VALUES ('$Program', '$City', '$Day', '$Time', '$Address', '$Building', '$Type', '$Group', '$Notes', '$Other', '$ConName', '$ConAdd',
'$ConPhone', '$ConEmail', '$ConComm')";
$result=mysql_query($sql) or die('The mysql error was: ' . mysql_error() .'<br>The query was: ' + $sql);
Post what the error you get.

kenwvs
08-20-2007, 07:24 AM
This is the error I am getting

Warning: mysql_real_escape_string() expects parameter 2 to be resource, null given in /test/test/test/NewMeeting.php on line 162

"test" is just changed for security

_Aerospace_Eng_
08-20-2007, 08:35 AM
You must have changed your code because no where in your code are you using mysql_real_escape_string. Post your new code.

kenwvs
08-20-2007, 08:44 AM
This is the code I am using. Is this line "return mysql_real_escape_string($data, $dbc);" causing the problem? This was in the one change you suggested I make.



<?php
}else {
$Program=$_POST['Program'];
$City=$_POST['City'];
$Day=$_POST['Day'];
$Time=$_POST['Time'];
$Address=$_POST['Address'];
$Building=$_POST['Building'];
$Type=$_POST['Type'];
$Group=$_POST['Group'];
$Notes=$_POST['Notes'];
$Other=$_POST['Other'];
$ConName=$_POST['ConName'];
$ConAdd=$_POST['ConAdd'];
$ConPhone=$_POST['ConPhone'];
$ConEmail=$_POST['ConEmail'];
$ConComm=$_POST['ConComm'];

return mysql_real_escape_string($data, $dbc);
}
$sql="INSERT INTO `Meetings`(Program, City, Day, Time, Address, Building, Type, Group, Notes, Other, ConName, ConAdd, ConPhone, ConEmail, ConComm)
VALUES ('$Program', '$City', '$Day', '$Time', '$Address', '$Building', '$Type', '$Group', '$Notes', '$Other', '$ConName', '$ConAdd',
'$ConPhone', '$ConEmail', '$ConComm')";
$result=mysql_query($sql) or die('The mysql error was: ' . mysql_error() .'<br>The query was: ' + $sql);


Echo "Your information has been successfully added to the Meeting List. You will automatically
be redirected to the Home Page in 8 Seconds";
echo "<meta http-equiv=Refresh content=7;url=index.php>";


?>

_Aerospace_Eng_
08-20-2007, 08:49 AM
Yes that line is part of the problem. You aren't passing any data to it. It requires a string to escape and the optional variable for the database connection. Remove that line and do what I told you to do in my second post.

You are likely on the right track in that you need to escape your data but you need to do it correctly. I didn't tell you to use mysql_real_escape_string. You have to create a custom function because magic_quotes could be enabled and the data will be escaped twice and when retrieved from the database it will have unneeded slashes in it.

I just noticed a mistake in the previous code I posted. Change this

$sql="INSERT INTO `Meetings`(Program, City, Day, Time, Address, Building, Type, Group, Notes, Other, ConName, ConAdd, ConPhone, ConEmail, ConComm)
VALUES ('$Program', '$City', '$Day', '$Time', '$Address', '$Building', '$Type', '$Group', '$Notes', '$Other', '$ConName', '$ConAdd',
'$ConPhone', '$ConEmail', '$ConComm')";
$result=mysql_query($sql) or die('The mysql error was: ' . mysql_error() .'<br>The query was: ' + $sql);

to this

$sql="INSERT INTO `Meetings`(Program, City, Day, Time, Address, Building, Type, Group, Notes, Other, ConName, ConAdd, ConPhone, ConEmail, ConComm)
VALUES ('$Program', '$City', '$Day', '$Time', '$Address', '$Building', '$Type', '$Group', '$Notes', '$Other', '$ConName', '$ConAdd',
'$ConPhone', '$ConEmail', '$ConComm')";
$result=mysql_query($sql) or die('The mysql error was: ' . mysql_error() .'<br>The query was: ' . $sql);

Also as I said before remove that line you added. You aren't using it correctly.

kenwvs
08-20-2007, 09:25 AM
When I change the code as you show, this is the error I get...



The mysql error was: 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 'Group, Notes, Other, ConName, ConAdd, ConPhone, ConEmail, ConComm) VALUES ('', ' at line 1
The query was: INSERT INTO `Meetings`(Program, City, Day, Time, Address, Building, Type, Group, Notes, Other, ConName, ConAdd, ConPhone, ConEmail, ConComm) VALUES ('', '', '', '', '', '', '', '', '', '', '', '', '', '', '')


This has me thinking that it may be a result of the form I have created. Hence, I am going to show my form below. I tried to run the form, and noticed that the first two colums don't get filled in, which is the info that is originally being pulled from another table.

Hope this makes sense.



<?php

if (!isset($_POST['Submit']))
{
?>
<form action="<?php echo $PHP_SELF ?>" method="post" enctype="multipart/form-data">
<input type="hidden" name="MAX_FILE_SIZE" value="1572864">
<center>
<div>

<B>Program:</B> <select name='program'>

<?php

$result = mysql_query("SELECT * FROM Program") or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
echo "<option>$row[Program]</option>";
}
?>

</select>


<B>City / Town:</B> <select name='city'>

<?php

$result = mysql_query("SELECT * FROM City") or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
echo "<option>$row[City]</option>";
}

?>

</select><BR><BR>
</div>
<div>
<B>Day of Week:</b><select name="Day">
<option value="No Selection Made">Select Day</option>
<option value="Sunday">Sunday</option>
<option value="Monday">Monday</option>
<option value="Tuesday">Tuesday</option>
<option value="Wednesday">Wednesday</option>
<option value="Thursday">Thursday</option>
<option value="Friday">Friday</option>
<option value="Saturday">Saturday</option>
</select>
<B>Start Time:</B><input type="text" size="8" maxlength="10" name="time"><BR>


<BR><BR>
</div>
<div>
<B>Group Name:</B>
<textarea rows="1" name="Group" cols="80"></textarea><BR>

I Have Removed a Bunch of the FORM as it is the same as the other rows regarding type of textrows, etc....

</div><BR>
</div><BR>

</center>
<div><center><BR><BR>
<input type="submit" name="Submit" value="Submit New Meeting"> </center>
</div>
<BR><BR><BR>
</div>
</form>

CFMaBiSmAd
08-20-2007, 03:26 PM
That extra line of code is something Aerospace_Eng pasted from somewhere on his system in post #4. It was never present in the OP's code.

kenwvs, when code is posted, you should at least read it (and re-read it until you understand it) instead of blindly using it to make sure it does what you intend. Most of the time code that is posted is untested and could actually cause inadvertent operation (for example deleting all rows in your database instead of deleted the intended row.) Unless posted code includes a statement that it has been tested, it should always be looked at as a suggestion to try (on test data) or as just giving direction to take with your own code.

The current error is because GROUP is a reserved mysql keyword. Either rename your column to something else (recommended), or enclose it in back-ticks in the query (not recommended for future compatibility reasons.)

kenwvs
08-20-2007, 03:40 PM
CF

Thank You for your comments, I appreciate them. I actaully did read the code, and do try to figure out what should happen. I did see where that line was pasted, but thought somehow I had did that, and didn't want to argue with someone taking there time to help me.

Other than knowing it is a restricted word, does that error say that somewhere. Isthere a guide that would explan that somewhere?

CFMaBiSmAd
08-20-2007, 03:51 PM
For syntax errors, the starting portion of the query listed in the error message, right after the single-quote, is the point where the parser could not figure out what to do -

... use near 'Group, Notes, Other ... For a list of reserved words - http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum