[SOLVED] MySQL Insert Error

eRott · June 22, 2007

Ok, so heres the problem. I've been working on this system all day and it was working perfectly. I got to the point where I decided i needed to add the user id of the forum member posting the application and thats where it all began to go wrong. I removed the code I added, reviewed EVERY line of code, and I event wrote a bunch of it over again. It just wont work anymore and I am a tad bit confused. Can anyone spot any problem with the code. Perhaps some fresh eyes may help. (also, my DB settings are "AAA" because i just removed them so you wouldn't be able to see it).

<?php include_once('forums/SSI.php'); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

/* ... rest of website here... */

<?php
if ($context['user']['is_logged'])
{ //start the if user is logged
?>
So your looking to join NAME_HIDDEN? In order to make applying a little easier, we have created this tool to automatically do it for you. Please completely fill in the required fields and post your application. You application will then automatically be posted in the Recruitment section in the forums. If you are accepted, you will be contacted.<br /><br /> <font color="red">Fields marked with a <b>*</b> will <b>not</b> be shown on the application. This information is for our records only. Before applying, please read the guild <a href="http://www.MYDOMIAN.com/forums/index.php/topic,66.0.html">requirements</a>.</font><br /><br />
<?php
if(isset($_POST['apply']))
{ //start if apply

require_once('forums/Sources/Subs-Post.php');

$topicOptions = array(
'id'		=> 0,
'board'		=> 2.0,
'mark_as_read'	=> false,
);
$posterOptions = array(
'id'		=> (isset($ID_MEMBER) ? $ID_MEMBER : 0),
'name'		=> $_POST["acctNAME"],
'email'		=> $_POST["acctEMAIL"],
'ip'		=> $_SERVER['REMOTE_ADDR'],
'update_post_count'	=> true,
);
$msgOptions = array(
'id'		=> 0,
'subject'	=> $_POST["charNAME"] . ', ' . $_POST["charLEVEL"] . ' ' . $_POST["charCLASS"],
'body'		=> '[b][color=red]Name:[/color][/b] ' . $_POST["appNAME"] . '\n[b][color=red]Character Name:[/color][/b] ' . $_POST["charNAME"] . '\n[b][color=red]Character Race:[/color][/b] ' . $_POST["charRACE"] . '\n[b][color=red]Character Class:[/color][/b] ' . $_POST["charCLASS"] . '\n[b][color=red]Character Level:[/color][/b] ' . $_POST["charLEVEL"] . '\n[b][color=red]Message:[/color][/b]\n' . $_POST["appMSG"],
);

createPost ($msgOptions,$topicOptions,$posterOptions);

$dbhost = 'AAA';
$dbuser = 'AAA';
$dbpass = 'AAA';
$dbname = 'AAA';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die(mysql_error());
$db = mysql_select_db($dbname, $conn) or die(mysql_error());

$db_appNAME = $_POST['appNAME'];
$db_appEMAIL = $_POST['appEMAIL'];

$db_acctNAME = $_POST['acctNAME'];
$db_acctEMAIL = $_POST['acctEMAIL'];
$db_acctIP = $_POST['acctIP'];

$db_charNAME = $_POST['charNAME'];
$db_charRACE = $_POST['charRACE'];
$db_charCLASS = $_POST['charCLASS'];
$db_charLEVEL = $_POST['charLEVEL'];

$db_message = $_POST['appMSG'];

$show = "yes";
$approved = "nil";

$query = "INSERT INTO applications (appname, appemail, acctname, acctemail, acctip, charname, charrace, charclass, charlevel, message, show, approved) VALUES ('$db_appNAME', '$db_appEMAIL', '$db_acctNAME', '$db_acctEMAIL', '$db_acctIP', '$db_charNAME', '$db_charRACE', '$db_charCLASS', '$db_charLEVEL', '$db_message', '$show', '$approved')";
mysql_query($query) or die('Error, your application could not be processed');
?>
<font color="red"><b>Application successfully sent!</b></font><meta http-equiv="refresh" content="1;url=http://www.MYDOMAIN.com/forums/index.php/board,2.0.html">
<?php
} //end if apply
else {
?>
<form method="post">
<table width="100%" cellspacing="0" cellpadding="0" border="0">
<tr>
<td colspan="2"><b>PERSONAL INFORMATION</b></td>
</tr>
<tr>
<td>Real Name:</td>
<td><input type="text" name="appNAME"></td>
</tr>
<tr>
<td>*Email Address:</td>
<td><input type="text" name="appEMAIL"></td>
</tr>

<tr><td> </td></tr>

<tr>
<td colspan="2"><b>FORUM ACCOUNT INFORMATION</b></td>
</tr>
<tr>
<td>*Account Name:</td>
<td><input type="text" name="acctNAME" value="<?php echo '', $context['user']['name'] ,''; ?>" disabled></td>
</tr>
<tr>
<td>*Account Email:</td>
<td><input type="text" name="acctEMAIL" value="<?php echo '', $context['user']['email'] ,''; ?>" disabled></td>
</tr>
<tr>
<td>*Account IP:</td>
<td><input type="text" name="acctIP" value="<?php echo '', $user_info['ip'] ,''; ?>" disabled></td>
</tr>

<tr><td> </td></tr>

<tr>
<td colspan="2"><b>CHARACTER INFORMATION</b></td>
</tr>
<tr>
<td>Character Name:</td>
<td><input type="text" name="charNAME"></td>
</tr>
<tr>
<td>Character Race:</td>
<td><select name="charRACE">
<option selected>--RACE--</option>
<option value="Draenei">Draenei</option>
<option value="Dwarf">Dwarf</option>
<option value="Gnome">Gnome</option>
<option value="Human">Human</option>
<option value="Night Elf">Night Elf</option>
</select></td>
</tr>
<tr>
<td>Character Class:</td>
<td><select name="charCLASS">
<option selected>--CLASS--</option>
<option value="Druid">Druid</option>
<option value="Hunter">Hunter</option>
<option value="Mage">Mage</option>
<option value="Paladin">Paladin</option>
<option value="Priest">Priest</option>
<option value="Rogue">Rogue</option>
<option value="Shaman">Shaman</option>
<option value="Warlock">Warlock</option>
<option value="Warrior">Warrior</option>
</select></td>
</tr>
<tr>
<td>Character Level:</td>
<td><input type="text" name="charLEVEL" size="2" maxLength="2"></td>
</tr>

<tr><td> </td></tr>

<tr>
<td colspan="2"><b>APPLICATION MESSAGE</b></td>
</tr>
<tr>
<td valign="top">Message:<br /><font color="#666666"><small><i>
Be sure to include:<br /><br />
<ol>
<li>Brief history of guilds and servers</li>
<li>Give us a general idea of your talent builds and gear (we will check the armory if necessary)</li>
<li>Relevant keys and attunements on your main character</li>
<li>Days and times you are able play</li>
</ol>
Also, don't be afraid to tell us about yourself. Why do you want to join NAME_HIDDEN? Do you know anyone in NAME_HIDDEN? What experience do you have with PvE end game content? What are you hoping to gain from joining NAME_HIDDEN? What events are you keyed for?
</i></small></font></td>
<td><textarea rows="15" cols="30" name="appMSG"></textarea></td>
</tr>

<tr><td> </td></tr>

<tr>
<td></td>
<td><input type="submit" name="apply" value="Apply"></td>
</tr>

</table>
</form>
<?php
}
} //end the if user is logged
else {
?>
Please <a href="http://www.MYDOMAIN.com/forums/index.php?action=login">Login</a> or <a href="http://www.MYDOMAIN.com/forums/index.php?action=register">Register</a> first.
<?php
}
?>

/* ... rest of website here... */

teng84 · June 22, 2007

'".$db_appEMAIL."'

try your variables that way

eRott · June 23, 2007

Nope. No effect. I just COMPLETELY rewrote the code again, leaving only what was necessary to write the data to the database but still it doesn't work. Could there be something wrong with my table structure? I have double checked, the field names match up, but would the field type have anything to do with it?

pocobueno1388 · June 23, 2007

Why don't you try catching the error?

Change this:

<?php

mysql_query($query) or die('Error, your application could not be processed');

?>

To this:

<?php

mysql_query($query) or die(mysql_error());

?>

You were getting this error, right?

"Error, your application could not be processed"

eRott · June 23, 2007

YOU good sir. Are a god damn GENIUS in all rights . I changed it that way in case if a user encountered an error, it would be a bit friendlier.

Anywho, here's the 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 'show, approved) VALUES ('FakeFirst', 'fake@gmail.com', 'JonDoe', 'JonDoe@gma' at line 1

Any ideas?

eRott · June 23, 2007

Ok,

So I've discovered the problem. It has to do with this specific code:

//Declare various variables
$ishow = "yes";
$iapproved = "nil";

$query = "INSERT INTO applications (appname, appemail, acctname, acctemail, acctip, charname, charrace, charclass, charlevel, message, show, approved) VALUES ('$db_appNAME', '$db_appEMAIL', '$db_acctNAME', '$db_acctEMAIL', '$db_acctIP', '$db_charNAME', '$db_charRACE', '$db_charCLASS', '$db_charLEVEL', '$db_message', '$show', '$approved')";
mysql_query($query) or die('Error, your application could not be processed');

It would appear the format I use for the two variables, $ishow and $iapproved is flawed. So fixing this should be no problem. Thanks for the help!!!

pocobueno1388 · June 23, 2007

If your still getting the error, try using mysql_real_escape_string() on all your variables you are inserting into the database.

http://www.php.net/mysql_real_escape_string

eRott · June 23, 2007

Jeesh. This is getting irritating. I tried using hidden input fields with the values set, I even directly input the text. I am unsure how to use that mysql_real_escape_string() or even what it does for that matter. How would I go about inserting that data.

pocobueno1388 · June 23, 2007

Are you getting any different errors with the "or die(mysql_error())" tacked on to the end of your query?

To use mysql_real_escape_string, just do this:

<?php

$db_appNAME = mysql_real_escape_string($_POST['appNAME']);

?>

This function will escape any special characters to get the variable ready to insert into the DB.

This function is not only useful for preventing mysql injections, but it will also escape apostrophes. If you have an apostrophe in any one of your variables, it will throw an error. So that might be your problem.

eRott · June 23, 2007

Ahh. Well, in that case, I do not think it would be of any use in this particular situation. As for the error codes. See Reply #'s 4 and 5. I kept getting an error in regards to the last part of my insert query. I removed the $ishow and $iapproved variables and that solved the problem. However, i need those in there, so getting rid of them is not an option. So then i tried using hidden input fields with the values set, and even directly inserting the data (in which case $sshow = "yes" and $iapproved = "nil")

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 'show, approved) VALUES ('FakeFirst', 'fake@gmail.com', 'JonDoe', 'JonDoe@gma' at line 1

SPECIFIC PROBLEM (had to do with the ishow and iapproved variables

//Declare various variables
$ishow = "yes";
$iapproved = "nil";

$query = "INSERT INTO applications (appname, appemail, acctname, acctemail, acctip, charname, charrace, charclass, charlevel, message, show, approved) VALUES ('$db_appNAME', '$db_appEMAIL', '$db_acctNAME', '$db_acctEMAIL', '$db_acctIP', '$db_charNAME', '$db_charRACE', '$db_charCLASS', '$db_charLEVEL', '$db_message', '$show', '$approved')";
mysql_query($query) or die('Error, your application could not be processed');

So how would i insert the words "yes" and "nil" into the database because its really weird.

mmarif4u · June 23, 2007

What is ur datatype in mysql db for fields yes and nil.

eRott · June 23, 2007

varchar with the length/value set to 3

eRott · June 23, 2007

Im so confussed /cry :'(

mmarif4u · June 23, 2007

Dont cry i think try this snd it will be sloved.

$ishow = "yes";
$iapproved = "nil";

$query = "INSERT INTO applications (appname, appemail, acctname, acctemail, acctip, charname, charrace, charclass, charlevel, message, show, approved) VALUES ('$db_appNAME', '$db_appEMAIL', '$db_acctNAME', '$db_acctEMAIL', '$db_acctIP', '$db_charNAME', '$db_charRACE', '$db_charCLASS', '$db_charLEVEL', '$db_message', '$ishow', '$iapproved')";
mysql_query($query) or die('Error, your application could not be processed');

And let me know.

mmarif4u · June 23, 2007

Dont cry... i think try this and it will be sloved.

$ishow = "yes";
$iapproved = "nil";

$query = "INSERT INTO applications (appname, appemail, acctname, acctemail, acctip, charname, charrace, charclass, charlevel, message, show, approved) VALUES ('$db_appNAME', '$db_appEMAIL', '$db_acctNAME', '$db_acctEMAIL', '$db_acctIP', '$db_charNAME', '$db_charRACE', '$db_charCLASS', '$db_charLEVEL', '$db_message', '$ishow', '$iapproved')";
mysql_query($query) or die('Error, your application could not be processed');

And let me know.

eRott · June 23, 2007

If i am not mistaking, I have tried that already and it still doesn't work. Oh, forgot to mention, i changed all my variable a while back. This is what I have right now:

//Declare various variables
$iSHOW = "yes";
$iAPPROVED = "nil";

//Insert the data posted from the form into the database
$query = "INSERT INTO $dbtable (appname, appemail, acctuid, acctname, acctemail, acctip, charname, charrace, charclass, charlevel, message, show, approved) VALUES ('$iAPP_name', '$iAPP_email', '$iACCT_uid', '$iACCT_name', '$iACCT_email', '$iACCT_ip', '$iCHAR_name', '$iCHAR_race', '$iCHAR_class', '$iCHAR_level', '$iAPP_message', '$iSHOW', '$iAPPROVED')";
mysql_query($query) or die(mysql_error());

mmarif4u · June 23, 2007

Sorry for double post.

eRott · June 23, 2007

There is no god.

mmarif4u · June 23, 2007

So its working now or still u getting th error.

eRott · June 23, 2007

No no. It's still not working.

mmarif4u · June 23, 2007

can u post ur updated complete code with varaibles.

eRott · June 23, 2007

Here you go:

*snip*

rcorlew · June 23, 2007

Change this:

$query = "INSERT INTO $dbtable

Into this:

$query = "INSERT INTO '$dbtable'

You need to tell mysql that you are using a variable in the query string.

eRott · June 23, 2007

It still works with or without the single quotes. That however, is not the problem. But thank you!

eRott · June 23, 2007

EDIT: I SOLVED IT! Finally. I just ended up changing the name of the field. Apparently, MySQL must use this name for something or whatever. One way or the other, it didn't like the fact that I had a field with the name "show".

Sign In

[SOLVED] MySQL Insert Error

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information