Keep failing on insert

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Balveda
Forum Newbie
Posts: 17
Joined: Wed Jun 30, 2010 9:20 pm

Keep failing on insert

Post by Balveda »

Im kinda new to php and mysql, but have been trying to get a submission form working to add data to a mysql db.

Its hosted on a Hostgator server, and I've been on to them already to make sure there are no limitations on the account with regards the functionality I need.

With the code below it always returns MySQL Insertion Failure, so Im assuming the connect.php script and the form script are working as planned. Any idea's?

Thanks,

Balveda

Code: Select all

<?php
if (isset($_POST['submit'])) {
}

$char1 = $_POST['char1'];
$rank = $_POST['rank'];
$class = $_POST['class'];
$spec1 = $_POST['spec1'];
$spec2 = $_POST['spec2'];
$comments = $_POST['comments'];

$error = FALSE;
if ($rank == "none") {
$error = TRUE;
}
if ($class == "none") {
$error = TRUE;
}
if (isset($comments)) {
$comments = trim($comments);
$comments = strip_tags($comments);
}
if (isset($char1) && 
isset($rank) && 
isset($class) && 
isset($spec1) && 
isset($spec2) && 
isset($comments) && 
$error == FALSE) {
$process = TRUE;
} else {
$process = FALSE;
}

include ("connect.php");

$query = "INSERT INTO character VALUES ('','$char1','$rank','$class','$spec1','$spec2','$comments','now()')";
$q = mysql_query($query);

if (!$q) {
exit("<p>MySQL Insertion failure.</p>");
} else {
mysql_close();
//for testing only
echo "<p>MySQL Insertion Successful</p>";
}

?>
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Keep failing on insert

Post by mikosiko »

assuming that your connection is working..

some advices:
a) surround the table name (character) with backticks in this way : `character` because that is a mysql reserved word and almost sure is causing the insert to fail.

b) modify this line in your code to control errors:
Balveda wrote:$q = mysql_query($query);
to this:

Code: Select all

$q = mysql_query($query) or die( mysql_error());
c) Is good practice to name each field in your INSERT... in this way:

Code: Select all

    INSERT INTO table_name (field1, field2,.... fieldn) VALUES (value1, value2, ... valuen)
Balveda
Forum Newbie
Posts: 17
Joined: Wed Jun 30, 2010 9:20 pm

Re: Keep failing on insert

Post by Balveda »

I changes the insert lines to

Code: Select all

$query = "INSERT INTO 'character' (id,char1,rank,class,spec1,spec2,comments) VALUES ('','$char1','$rank','$class','$spec1','$spec2','$comments','now()')";
$q  = mysql_query($query) or die( mysql_error());
and get 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 ''character' (id,char1,rank,class,spec1,spec2,comments) VALUES ('','test','Champi' at line 1
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Keep failing on insert

Post by mikosiko »

you must use backticks ` to sourround character... this.... `character` ... is not the same than this... 'character'
Balveda
Forum Newbie
Posts: 17
Joined: Wed Jun 30, 2010 9:20 pm

Re: Keep failing on insert

Post by Balveda »

mikosiko wrote:you must use backticks ` to sourround character... this.... `character` ... is not the same than this... 'character'
Thanks dude - I nearly asked where the backtick was on my kb :oops:

Still getting an error, but getting somewhere at least:

Column count doesn't match value count at row 1

I've tried takind id and '' out of the line, but still getting the error.
Balveda
Forum Newbie
Posts: 17
Joined: Wed Jun 30, 2010 9:20 pm

Re: Keep failing on insert

Post by Balveda »

I worked it out :D

Thanks for your help mikosiko
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Keep failing on insert

Post by VladSun »

Your code is vulnerable to SQL injection attacks.
Use mysql_real_escape_string() to escape *ALL* data you want to insert.

E.g.

Code: Select all

$char1 = mysql_real_escape_string($_POST['char1']);
$rank = mysql_real_escape_string($_POST['rank']);
...
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply