mySQL insert just won't work, but will with other table

Question

I have a mySQL issue that has me stumped for hours!

Context: I am creating a web-application to teach children different musical symbols on touch devices

Problem: after running this individual script, no row is created although last echo argument works.

Wierd: this is modeled after other scripts I have working.

EDIT: I will re-furnish the code with prepared statements after we find the problem.

Here is the code:

<?php

$database_connect = mysql_connect('localhost','ragstudi','*****');

if (!$database_connect) {die ('Not connected to SQL' . mysql_error());};

mysql_select_db('ragstudi_musicGameScores') or die ('db doesnt exist');


$userName = "Jimmy";//$_POST["username"];
$gameLevel = 1;//$_POST["level"];
$gameScore = 2300;//$_POST["score"];
$userGroup = "RagWway";

$highScores = mysql_query("INSERT INTO highScores (name, groupBy, level, score) values('".$userName."','".$userGroup."','".$gameLevel."','".$gameScore."')");
if (!$highScores){
    die(
        mysql_error()
        )
};

The database _musicGameScores is set up like this Id, primary, auto increment, integer name, varchar groupBy, varchar level, integer score, integer

The password is Star'ed out. Thank you all in advance!

[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](http://j.mp/XqV7Lp). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. — Kermit, Apr 05 '13 at 00:58
Thank you for this. I have not dealt with a lot of mySQL yet, but when I first did, it was not this way. I will implement this after this issue is resolved. Thanks! — Stephen Raghunath, Apr 05 '13 at 01:29

score 4 · Accepted Answer · edited May 23 '17 at 10:25

4

GROUP is a reserved keyword. You must properly escape it so it will not generate an error.

INSERT INTO highScores (id, user, `group`, level, score) VALUES(...)

MySQL Reserved Keywords List

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

edited May 23 '17 at 10:25

Community

1
1

answered Apr 05 '13 at 00:58

John Woo

258,903
69
498
492

It won't generate an error because the OP is not using error handlers. – Kermit Apr 05 '13 at 00:59
@FreshPrinceOfSO how about the server? Will it allow the query to be inserted when it has a syntax error? – John Woo Apr 05 '13 at 00:59
We don't know if `error_reporting` is enabled. – Kermit Apr 05 '13 at 01:00
I was unaware that mysql_ is now deprecated. I will look into the implementation of mysqli. However the escape did not work. Thank you for your time in helping me sort this out. – Stephen Raghunath Apr 05 '13 at 01:11
after the line of `mysql_qery(...)`, add this line `if (!$highScores){die(mysql_error())};` to see what is the error and post it here. – John Woo Apr 05 '13 at 01:15
the log says [04-Apr-2013 19:16:19] PHP Parse error: syntax error, unexpected '}' in /home2/ragstudi/public_html/game/wp-content/themes/responsive/php/saveScores.php on line 19. In my code, this is error handler line. – Stephen Raghunath Apr 05 '13 at 01:18
the entire code is posted in my OP. The date is from the error log. – Stephen Raghunath Apr 05 '13 at 01:22
is column `ID` auto_incremented? – John Woo Apr 05 '13 at 01:25
Yes, it is auto incremented. – Stephen Raghunath Apr 05 '13 at 01:28
pass `null` value on it or remove `ID` from the statement, eg `INSERT INTO(name, groupBy, level, score) VALUES (....)` – John Woo Apr 05 '13 at 01:30
Still no luck. I updated the code in the op to reflect what it is now. Once again thank you for all your time and effort! – Stephen Raghunath Apr 05 '13 at 01:39
@JW I also updated to show the table layout. – Stephen Raghunath Apr 05 '13 at 10:16
the Column Name was the reason it wasn't working. Thanks! "Name" should be put on that list then, because it was not. – Stephen Raghunath Apr 07 '13 at 23:21

score 0 · Answer 2 · answered Apr 05 '13 at 00:59

0

Try to add `` in every column, maybe theres a reserve keyword out there.

mysql_query("INSERT INTO highScores (`id`, `user`, `group`, `level`, `score`) values('','".$userName."','".$userGroup."','".$gameLevel."','".$gameScore."')");

answered Apr 05 '13 at 00:59

Jhonathan H.

2,734
1
19
28

This answer does not add anything new; further you don't need backticks around every column. – Kermit Apr 05 '13 at 01:00
@FreshPrinceOfSO did see J W answer while posting this. – Jhonathan H. Apr 05 '13 at 01:02

score 0 · Answer 3 · answered Apr 05 '13 at 01:02

0

It should be like this. Cheers! :D

$highScores = mysql_query("INSERT INTO highScores (id, user, group, level, score) values('','".$userName."','".$userGroup."','".$gameLevel."','".$gameScore."')");

answered Apr 05 '13 at 01:02

Ain Ronquillo

197
1
3
6

mySQL insert just won't work, but will with other table

3 Answers3