Problem inserting into MySQL table

PHP
1

Hi

I have this problem with inserting data into MySQL table.

$query1 = "INSERT INTO clients (firstname, lastname, address) VALUES ('$firstname', '$lastname', '$address')";

mysqli_query($dbc, $query1) or die('Data not inserted, clients');

Now this doesn’t work, but when I go directly to PHP-myadmin, remove the double quotes around the SQL statement and insert the statement directly into the sql console and it works.

I did check the variables $firstname etc, and the variables are not empty.

What could be the problem?

2

I did what you said and the result is.

“MySQL Error: ()”

So that means it doesn’t get the database connection variable?
I don’t see why would this be, I have included the variable in global $dbc,
and in another function it seems the same.

Thank you for the method of debugging.

3

I would suspect that your $dbc does not refer to a database connection.

4

Do a search for $dbc in your files, I think it is overwritten somewhere.


$dbc = mysqli_connect( ... );

// some code


$dbc = somethingElse();

mysqli_query($query1, $dbc); // doesn't work
5

I echod the query and then I tried inserting it into the SQL console in php-myadmin. So I guess, the querys are ok, so there must be a problem, with the connectio

I have another query I ask from MySQL before in the page, it is an select query,
It works with no errors. Now when I try to use this query in place of query1, then it dies just the same.

6

If you close it and then want to use it again you should call mysqli_connect again.
However, in my opinion, you can leave the MySQL open to avoid the problems you just described, and forces you to do a lot of book keeping to see if the connection is open each time. Connecting/disconnection every time also takes time, making the script take longer that it would if you just leave the connection open.

PS. If you don’t close the connection, it is closed by PHP automatically after the script ends.

7

Thanks :slight_smile:

I read this book where it was told, that you should close the connection when not using it.

Basically I have 2 functions with the need for connection.
One is for getting products from the database,
the second is for inserting an order.

I figured I would close the connection in the end of both of these functions.
Is this necessary, and how could this be done if mysqli_close() unsets the connection variable? mysqli_kill()?

8

If you replace


mysqli_query($dbc, $query1) or die('Data not inserted, clients');

with


mysqli_query($dbc, $query1) or die('MySQL Error: '.mysqli_error($dbc).' ('.mysqli_errno($dbc).')');

What does it say?

9

You say “remove double quotes around the SQL statement” but that is not the sql statement that is being executed. the variables $firstname etc are replace by the values they contain.

Two things to consider.

  1. Does the variable values contain a quote.
  2. Are you sure you have a connection to the database with insert privileges.
10

This is my connection code.


define('DB_HOST', ' ');
define('DB_NAME', ' ');
define('DB_USER', ' ');
define('DB_PASSWORD', ' ');
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die('Error connecting to MySQL server.');
11

Yes, and it also makes PHP disconnect from MySQL. No wonder it didn’t work :slight_smile:
Glad to see you got it resolved though :tup:

12

I searched the script and I didn’t find a duplicate or overwritten dbc.
I connect in my first function which does succeed in gettin data from the table, then I closed the connection with mysqli_close($dbc).

Now I tried removing this line, closing the connection.
And it inserts the data.

Could it be that mysqli_close($dbc) unsets the variable?

13

Inbetween them two lines add this line:

echo $query1;

Does anything stand out as being amiss with the echoed query?