Database Error. SQL query is not being executed

Question

I have a form that takes two values as input and parses them using post method to the next file where I have this piece of code. This should save the two values in the MySQL database. But strangely it doesn't .. and in the output error I get is this: 0: 0:

$service_name = $_POST['service_name'];
$service_price = $_POST['service_price'];

$link = mysql_connect("localhost", "db_user", "pa$$");

mysql_select_db("database", $link);
echo mysql_errno($link) . ": " . mysql_error($link). "\n";

mysql_select_db("database", $link);
mysql_query("INSERT INTO service_tbl(id_service, service_name, service_price) VALUES(NULL,'$service_name','$service_price')", $link);
echo mysql_errno($link) . ": " . mysql_error($link) . "\n";

In my database table service_tbl, id_serviceis auto_increment and the other two columns are VARCHAR. What am I doing wrong here?

Consider using `mysqli` or `PDO` and not `mysql_*'. – Ofir Baruch May 18 '13 at 19:30 — Ofir Baruch, May 18 '13 at 19:30

score 1 · Answer 1 · edited May 18 '13 at 19:33

1

mysql_query("INSERT INTO service_tbl( service_name, service_price)     VALUES('{$service_name}','{$service_price}')", $link);

use this as Id_service is auto-increement you cannot insert it.

edited May 18 '13 at 19:33

Sina R.

1,781
2
19
37

answered May 18 '13 at 19:29

Rahul11

264
1
10

doesn't work. Still getting **0: 0:** as output! :/ – Hossain Ahmed Saiman May 18 '13 at 19:33
check your mysql connection is established properly. $link = mysql_connect("localhost", "db_user", "pa$$"); "pa$$" may be causing problems. – Rahul11 May 18 '13 at 19:38
check your mysql connection is established properly. $link = mysql_connect("localhost", "db_user", "pa$$"); "pa$$" may be causing problems. Use $link = mysql_connect("localhost", "db_user", "pa$$") or die("Unable to connect to MySQL"); – Rahul11 May 18 '13 at 19:44
password is not exactly 'pa$$' I just used it here for the sake of it! – Hossain Ahmed Saiman May 18 '13 at 19:44
@Hossain you can easily debub if there is connection problem or database selection problem. store the query in a variable and print the variable also try running the query explicitly.This should certainly help solve your problem. – Rahul11 May 18 '13 at 19:55

score 1 · Answer 2 · edited May 23 '17 at 11:49

1

You are trying to insert NULL in an auto increment column, wich is not possible, simply change to

mysql_query("INSERT INTO service_tbl( service_name, service_price)     VALUES('$service_name','$service_price')", $link);

Then I would like to remember you that mysql_ functions are deprecated so i would advise you to switch to mysqli or PDO and indeed you are at risk of sql injection, have a look here How can I prevent SQL injection in PHP?. You should use prepared statment to avoid any risk

edited May 23 '17 at 11:49

Community

1
1

answered May 18 '13 at 19:32

Fabio

23,183
12
55
64

doesn't work. Still getting **0: 0:** as output! :/ – Hossain Ahmed Saiman May 18 '13 at 19:33
@hossainahmedsaiman try to `echo $service_name;`, what does it print? – Fabio May 18 '13 at 19:35
it prints the parsed values alright! – Hossain Ahmed Saiman May 18 '13 at 19:38
I know the vulnerability issue. But SQL injection is not a big issue for me right now. – Hossain Ahmed Saiman May 18 '13 at 19:39
@Hossainahmedsaiman Did you check data is not inserted in your table? – Fabio May 18 '13 at 19:42
I checked. It's not being inserted. – Hossain Ahmed Saiman May 18 '13 at 19:43

nibra · Answer 3 · 2013-05-21T13:43:37.737

Some ammendments:

As it was already said in the other answers/comments, don't use the deprecated mysql_* functions.
There is no need to pass a value for the auto-increment field.
Change your debug output into real error checking.
Sanitize user input.

The revised code:

$service_name  = $_POST['service_name'];
$service_price = $_POST['service_price'];

$mysqli = new MySQLi('localhost', 'db_user', 'pa$$', 'database');
if ($mysqli->connect_errno) {
    throw new ErrorException("Failed to connect to MySQL: " . $mysqli->connect_error);
}

$query = sprintf("INSERT INTO service_tbl (service_name, service_price) VALUES ('%s', $f)",
    $mysqli->real_escape_string($service_name),
    (float)$service_name
);
// At this point, you can echo the query to test it in eg. phpMyAdmin
$result = $mysqli->query($query);
if ($result === false) {
    throw new ErrorException("Query failed: " . $mysqli->error);
}

printf("Select returned %d rows.\n", $result->num_rows);

while ($obj = $result->fetch_object()) {
    print_r($obj);
}

// Free result set
$result->close();

Database Error. SQL query is not being executed

3 Answers3