Be a Supporter!

Data Into A Database: Not Inserting

  • 366 Views
  • 21 Replies
New Topic Respond to this Topic
DannyIsOnFire
DannyIsOnFire
  • Member since: Apr. 14, 2005
  • Offline.
Forum Stats
Member
Level 21
Movie Buff
Data Into A Database: Not Inserting Jul. 1st, 2008 @ 01:49 PM Reply

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?


|| Portfolio || Facebook || Twitter ||

BBS Signature
elbekko
elbekko
  • Member since: Jul. 23, 2004
  • Offline.
Forum Stats
Member
Level 16
Blank Slate
Response to Data Into A Database: Not Inserting Jul. 1st, 2008 @ 01:55 PM Reply

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']).="'";

"My software never has bugs. It just develops random features. " - Unknown

[ FluxBB developer | Quickmarks 0.5.1 | Strings & Ints - my blog ]

BBS Signature
elbekko
elbekko
  • Member since: Jul. 23, 2004
  • Offline.
Forum Stats
Member
Level 16
Blank Slate
Response to Data Into A Database: Not Inserting Jul. 1st, 2008 @ 01:57 PM Reply

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.


"My software never has bugs. It just develops random features. " - Unknown

[ FluxBB developer | Quickmarks 0.5.1 | Strings & Ints - my blog ]

BBS Signature
DannyIsOnFire
DannyIsOnFire
  • Member since: Apr. 14, 2005
  • Offline.
Forum Stats
Member
Level 21
Movie Buff
Response to Data Into A Database: Not Inserting Jul. 1st, 2008 @ 02:01 PM Reply

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

Alas, it did not.


|| Portfolio || Facebook || Twitter ||

BBS Signature
notsquid
notsquid
  • Member since: Jun. 24, 2008
  • Offline.
Forum Stats
Member
Level 02
Blank Slate
Response to Data Into A Database: Not Inserting Jul. 1st, 2008 @ 02:06 PM Reply

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
BoneIdol
  • Member since: Aug. 14, 2006
  • Offline.
Forum Stats
Member
Level 05
Blank Slate
Response to Data Into A Database: Not Inserting Jul. 1st, 2008 @ 02:15 PM Reply

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.


Sufficiently advanced incompetence is indistinguishable from malice.

BoneIdol
BoneIdol
  • Member since: Aug. 14, 2006
  • Offline.
Forum Stats
Member
Level 05
Blank Slate
Response to Data Into A Database: Not Inserting Jul. 1st, 2008 @ 02:18 PM Reply

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...


Sufficiently advanced incompetence is indistinguishable from malice.

DannyIsOnFire
DannyIsOnFire
  • Member since: Apr. 14, 2005
  • Offline.
Forum Stats
Member
Level 21
Movie Buff
Response to Data Into A Database: Not Inserting Jul. 1st, 2008 @ 02:21 PM Reply

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...


|| Portfolio || Facebook || Twitter ||

BBS Signature
BoneIdol
BoneIdol
  • Member since: Aug. 14, 2006
  • Offline.
Forum Stats
Member
Level 05
Blank Slate
Response to Data Into A Database: Not Inserting Jul. 1st, 2008 @ 02:27 PM Reply

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.


Sufficiently advanced incompetence is indistinguishable from malice.

henke37
henke37
  • Member since: Sep. 10, 2004
  • Offline.
Forum Stats
Member
Level 30
Blank Slate
Response to Data Into A Database: Not Inserting Jul. 1st, 2008 @ 02:31 PM Reply

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


Each time someone abuses hittest, God kills a kitten. Please, learn real collision testing.

notsquid
notsquid
  • Member since: Jun. 24, 2008
  • Offline.
Forum Stats
Member
Level 02
Blank Slate
Response to Data Into A Database: Not Inserting Jul. 1st, 2008 @ 02:41 PM Reply

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
DannyIsOnFire
  • Member since: Apr. 14, 2005
  • Offline.
Forum Stats
Member
Level 21
Movie Buff
Response to Data Into A Database: Not Inserting Jul. 1st, 2008 @ 02:44 PM Reply

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.


|| Portfolio || Facebook || Twitter ||

BBS Signature
notsquid
notsquid
  • Member since: Jun. 24, 2008
  • Offline.
Forum Stats
Member
Level 02
Blank Slate
Response to Data Into A Database: Not Inserting Jul. 1st, 2008 @ 02:45 PM Reply

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

Missing a '?

DannyIsOnFire
DannyIsOnFire
  • Member since: Apr. 14, 2005
  • Offline.
Forum Stats
Member
Level 21
Movie Buff
Response to Data Into A Database: Not Inserting Jul. 1st, 2008 @ 02:57 PM Reply

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

Missing a '?

Nope.

'(

|| Portfolio || Facebook || Twitter ||

BBS Signature
elbekko
elbekko
  • Member since: Jul. 23, 2004
  • Offline.
Forum Stats
Member
Level 16
Blank Slate
Response to Data Into A Database: Not Inserting Jul. 1st, 2008 @ 03:16 PM Reply

Let's put it this way:

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

Then tell us what it says.


"My software never has bugs. It just develops random features. " - Unknown

[ FluxBB developer | Quickmarks 0.5.1 | Strings & Ints - my blog ]

BBS Signature
DannyIsOnFire
DannyIsOnFire
  • Member since: Apr. 14, 2005
  • Offline.
Forum Stats
Member
Level 21
Movie Buff
Response to Data Into A Database: Not Inserting Jul. 1st, 2008 @ 03:27 PM Reply

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


|| Portfolio || Facebook || Twitter ||

BBS Signature
elbekko
elbekko
  • Member since: Jul. 23, 2004
  • Offline.
Forum Stats
Member
Level 16
Blank Slate
Response to Data Into A Database: Not Inserting Jul. 1st, 2008 @ 07:00 PM Reply

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


"My software never has bugs. It just develops random features. " - Unknown

[ FluxBB developer | Quickmarks 0.5.1 | Strings & Ints - my blog ]

BBS Signature
DannyIsOnFire
DannyIsOnFire
  • Member since: Apr. 14, 2005
  • Offline.
Forum Stats
Member
Level 21
Movie Buff
Response to Data Into A Database: Not Inserting Jul. 2nd, 2008 @ 05:37 AM Reply

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 :)


|| Portfolio || Facebook || Twitter ||

BBS Signature
BoneIdol
BoneIdol
  • Member since: Aug. 14, 2006
  • Offline.
Forum Stats
Member
Level 05
Blank Slate
Response to Data Into A Database: Not Inserting Jul. 2nd, 2008 @ 05:47 AM Reply

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


Sufficiently advanced incompetence is indistinguishable from malice.

DannyIsOnFire
DannyIsOnFire
  • Member since: Apr. 14, 2005
  • Offline.
Forum Stats
Member
Level 21
Movie Buff
Response to Data Into A Database: Not Inserting Jul. 2nd, 2008 @ 08:00 AM Reply

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.


|| Portfolio || Facebook || Twitter ||

BBS Signature
BoneIdol
BoneIdol
  • Member since: Aug. 14, 2006
  • Offline.
Forum Stats
Member
Level 05
Blank Slate
Response to Data Into A Database: Not Inserting Jul. 2nd, 2008 @ 08:04 AM Reply

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

Post your update query please.


Sufficiently advanced incompetence is indistinguishable from malice.

DannyIsOnFire
DannyIsOnFire
  • Member since: Apr. 14, 2005
  • Offline.
Forum Stats
Member
Level 21
Movie Buff
Response to Data Into A Database: Not Inserting Jul. 2nd, 2008 @ 08:09 AM Reply

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!


|| Portfolio || Facebook || Twitter ||

BBS Signature