Data Into A Database: Not Inserting

[DannyIsOnFire]

I have the following snippet of code;

function addComment($id) {
global $db;

if(($_SESSION['security_code'] == $_POST['security_code']) && (!empty($_SESSION['security_code'])) ) {

$query = "INSERT INTO comments " . "VALUES{$_POST['name']}'," . "'{$_POST['rating']}'," . "'{$_POST['comment']}";
mysql_query($query);

echo "<div class=\"content-left-box\"><div class=\"left\">Success!</div><div class=\"right\"></div><div class=\"clear\"></div></div>";
echo "<div class=\"content-left-box-none\">";
echo "Your comment has been posted, thanks! Click <a href=\"{$_SERVER['PHP_SELF']}" . "?action=show&amp;id=$id\">here</a> to go back and see it.";
echo "</div>";
unset($_SESSION['security_code']);
}

else {

echo "<div class=\"content-left-box\"><div class=\"left\">Error</div><div class=\"right\"></div><div class=\"clear\"></div></div>";
echo "<div class=\"content-left-box-none\">";
echo "Your comment has not been posted. Please <a href=\"{$_SERVER['PHP_SELF']}" . "?action=show&amp;id=$id\">go back</a> and fill in all of the required fields.";
echo "</div>";
}
}

Pastebin version just in case.

It throws up no errors, works perfectly, just simply dosen't insert the data into the database. The weird thing is, when I remove all traces of the rating part, it works just fine. Not a clue why, although I am absoloutley shattered. Spend last night watch gangster films, lol. Goodfellas is fantastic. Anyway, any ideas?

[elbekko]

Well, that nearly made me cry.

$query = "INSERT INTO comments " . "VALUES('".mysql_real_escape_string($_POST['name'])."', '".mysql_real_escape_string($_POST['rating'])."', '".mysql_real_escape_string($_POST['comment']).="'";

[elbekko]

Woops, not fully done cleaning that up yet -.-

$query = "INSERT INTO comments VALUES ('".mysql_real_escape_string($_POST['name'])."', '".mysql_real_escape_string($_POST['rating'])."', '".mysql_real_escape_string($_POST['comment'])."')";

That should do.

[DannyIsOnFire]

At 7/1/08 01:57 PM, elbekko wrote: That should do.

Alas, it did not.

[notsquid]

Surely it's simpler to do;
$variable = mysql_real_escape_string($_POST['variabl e']);

At the top of your script, making it so much easier to read?

[BoneIdol]

At 7/1/08 02:06 PM, notsquid wrote: Surely it's simpler to do;
$variable = mysql_real_escape_string($_POST['variabl e']);

At the top of your script, making it so much easier to read?

It depends. It's a coding style thing. I personally do not, prefering to break my insert/updates into several lines and tab across to make my code tidy.

Anyway, if mysql_error() is returning false, have you considered that they are being inserted and it's your show comments type scripts that are wrong? If you use a group by it's very easy to feed it an attribute that will make it fetch only one result.

Also, as a thought, since this is functioned off, are you echoing mysql_error() outside the function or inside it? I have a sneaking suspicion that the mysql_error() is getting unset as the function ends. In theory it shouldn't do that, mind.

[BoneIdol]

Also, another thought occurs to me (sorry for double post).

Why are you making $db (which I assume is your current mysql connection) global then not using it? Assuming you only have one database connection that code should work anyway but as I said, it's just a thought...

[DannyIsOnFire]

Ok, if I remove the rating part from the script I posted and just insert name and comment into a database without a rating row, it works no problem. If I add a rating row to my database, it dosen't insert, which has me confused since it shouldn't make a difference if I have extra rows in a database. That's got me thinking it's a problem somewhere else in my code now...

[BoneIdol]

At 7/1/08 02:21 PM, DannyIsOnFire wrote: Ok, if I remove the rating part from the script I posted and just insert name and comment into a database without a rating row, it works no problem. If I add a rating row to my database, it dosen't insert, which has me confused since it shouldn't make a difference if I have extra rows in a database. That's got me thinking it's a problem somewhere else in my code now...

Try using a SET notation insert rather than a VALUES() one. People say they're bloated but they make for such easier to read code and stop so many problems.

INSERT INTO table SET attrinute1 = 'foo', attribute2 = 'bar';

VALUES() inserts cause all sorts of niggly little problems like that in my experience. Especially when you delete or add an attribute to a table.

[henke37]

You can try naming the fields that you are inserting in. It makes the query much easier to troubleshoot.

[notsquid]

At 7/1/08 02:15 PM, BoneIdol wrote:

At 7/1/08 02:06 PM, notsquid wrote: Surely it's simpler to do;
$variable = mysql_real_escape_string($_POST['variabl e']);

At the top of your script, making it so much easier to read?

It depends. It's a coding style thing. I personally do not, prefering to break my insert/updates into several lines and tab across to make my code tidy.

Surely at a time like this, when trouble shooting is needed, it would be best? If he has loads of stuff mixed in, he's a lot more likely to be missing a ", ' etc?

[DannyIsOnFire]

At 7/1/08 02:27 PM, BoneIdol wrote: SET

Tried that, no difference.
I just went into PHPMyAdmin and directly entered some data. It showed up no problem so it's definatley a problem with the insertion script.

[notsquid]

"'{$_POST['comment']}";

Missing a '?

[DannyIsOnFire]

At 7/1/08 02:45 PM, notsquid wrote: "'{$_POST['comment']}";

Missing a '?

Nope.

'(

[elbekko]

Let's put it this way:

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

Then tell us what it says.

[DannyIsOnFire]

uh'oh.

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

[elbekko]

Well, that's quite obvious then ;) That variable isn't set.

[DannyIsOnFire]

At 7/1/08 07:00 PM, elbekko wrote: Well, that's quite obvious then ;) That variable isn't set.

Huh? The rating variable not set? I was almost certain it was :/ Ok, final quick question, how do I (re)set it? I know the basics of SQL like setting up a database, but when it comes to editing stuff after I've stuck the code into PHPMyAdmin I'm lost.

This has been doing my head in for a while now. I want to get it sorted and move on to something else. I'm discovering that if you code in a certain language for too long, you start to get confuzelled. Thanks for all the help though 'bekko. And the rest of you that gave it a go :)

[BoneIdol]

Use an UPDATE query. It's exactly like an insert, but you give it a where clause as well (if you don't it affects every row in a table).

UPDATE table
  SET atrribute = value (or VALUES( ) if you prefer)
WHERE primary_key = 1

See http://www.w3schools.com/Sql/sql_update.
asp

[DannyIsOnFire]

At 7/2/08 05:47 AM, BoneIdol wrote: Use an UPDATE query. It's exactly like an insert, but you give it a where clause as well (if you don't it affects every row in a table).

UPDATE table
SET atrribute = value (or VALUES( ) if you prefer)
WHERE primary_key = 1

See http://www.w3schools.com/Sql/sql_update.
asp

Nothing, same error.

[BoneIdol]

At 7/2/08 08:00 AM, DannyIsOnFire wrote: Nothing, same error.

Post your update query please.

[DannyIsOnFire]

At 7/2/08 08:04 AM, BoneIdol wrote:

At 7/2/08 08:00 AM, DannyIsOnFire wrote: Nothing, same error.

Post your update query please.

No problem, I've done it. You have no idea how happy I am right now.
Turns out I'd accidentally deleted a fairly important bit of code that was stopping everything else working.
Went back to my original script, added 6 or 7 characters and boom, problem solved.

YAY!