Unable to INSERT into mySQL (using PHP) but can SELECT
Moderator: General Moderators
Unable to INSERT into mySQL (using PHP) but can SELECT
Hi there;
I can connect to my mySQL database using PHP and SELECT data which I have manually entered into the database from the mySQL interface.
However, when I try to INSERT data into the database using the same connection, it won't work.
I connect using the object-oriented approach; and I SELECT data using mysql_query(SQL_QUERY). However if I use the same function to INSERT data, it does nothing.
I've tried capturing any errors/error numbers etc. and if I use mysql_query( ) or die("ERROR"); "ERROR" is displayed but if I try to display any errors/error numbers, no error numbers are printed to the screen.
I can use the exact same query in the mysql application when I login but it won't work from the webpage using PHP.
Any ideas?? (I've exhausted all the possible words I can use in google but can't seem to find the solution or anybody with the same problem.)
Thanks
I can connect to my mySQL database using PHP and SELECT data which I have manually entered into the database from the mySQL interface.
However, when I try to INSERT data into the database using the same connection, it won't work.
I connect using the object-oriented approach; and I SELECT data using mysql_query(SQL_QUERY). However if I use the same function to INSERT data, it does nothing.
I've tried capturing any errors/error numbers etc. and if I use mysql_query( ) or die("ERROR"); "ERROR" is displayed but if I try to display any errors/error numbers, no error numbers are printed to the screen.
I can use the exact same query in the mysql application when I login but it won't work from the webpage using PHP.
Any ideas?? (I've exhausted all the possible words I can use in google but can't seem to find the solution or anybody with the same problem.)
Thanks
What's the output of the following?
Code: Select all
ini_set('error_reporting', E_ALL);
ini_set('display_errors', TRUE);
mysql_query('your query here...') or die(mysql_error());Hi there;
Not too sure on that output thing as I'm not at my home computer right now; but will try it tonight.
I did manage to get a hold of my source code if this helps.
This is how I connect (succesfully):
And this is how I submit the query (which works for SELECT but not INSERT):
I will post the output of the above code later on when I get home but thought the above might be of some use.
Not too sure on that output thing as I'm not at my home computer right now; but will try it tonight.
I did manage to get a hold of my source code if this helps.
This is how I connect (succesfully):
Code: Select all
$db_connection = new mysqli($host_name, $username, $password, $database_name);Code: Select all
$db_connection->query($sql_query) or die("Unable to submit query in <b>'hb_info.php'</b>.");In reply to the code request that I try, I get the following error:
Fatal error: Call to undefined function mysql_query() in D:\XXXX\XXXinfo.php on line 84
At this point the code stops - anything after this line is not executed.
This error also occurred before when I tried to use mysql_query( ) without the "ini_set( )" functions.
I have the following in my php.ini file:
extension=php_mysqli.dll
However, when I try to include the following in the php.ini file:
extension=php_mysql.dll
I get this error:
PHP Warning: PHP Startup: Unable to load dynamic library './php_mysql.dll' - The specified module could not be found. in Unknown on line 0
I have copied "libmysql.dll" and "php_mysqli.dll" into the directory where php is installed also.
Thanks
Fatal error: Call to undefined function mysql_query() in D:\XXXX\XXXinfo.php on line 84
At this point the code stops - anything after this line is not executed.
This error also occurred before when I tried to use mysql_query( ) without the "ini_set( )" functions.
I have the following in my php.ini file:
extension=php_mysqli.dll
However, when I try to include the following in the php.ini file:
extension=php_mysql.dll
I get this error:
PHP Warning: PHP Startup: Unable to load dynamic library './php_mysql.dll' - The specified module could not be found. in Unknown on line 0
I have copied "libmysql.dll" and "php_mysqli.dll" into the directory where php is installed also.
Thanks
- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US
Im using PHP version 5.1.2;
I get a section (in bold) called "MYSQLI" with info as follows:
Client API version: 5.0.20
MYSQLI_SOCKET: /tmp/mysql.sock
THEN SOMETHING CALLED DIRECTIVES:
DIRECTIVE LOCAL VALUE MASTER VALUE
mysqli.default_host no value no value
mysqli.default_port 3306 3306
mysqli.default_pw no value no value
mysqli.default_socket no value no value
mysqli.default_user no value no value
mysqli.max_links Unlimited Unlimited
mysqli.reconnect Off Off
I still get the following down the bottom of the page with "extension=php_mysql.dll" uncommented in "php.ini".
PHP Warning: PHP Startup: Unable to load dynamic library './php_mysql.dll' - The specified module could not be found. in Unknown on line 0
Not sure if it matters but PHP.ini is in c:\windows\ whereas I have installed PHP and MySQL into d:\web_server\PHP and \MySQL??
I get a section (in bold) called "MYSQLI" with info as follows:
Client API version: 5.0.20
MYSQLI_SOCKET: /tmp/mysql.sock
THEN SOMETHING CALLED DIRECTIVES:
DIRECTIVE LOCAL VALUE MASTER VALUE
mysqli.default_host no value no value
mysqli.default_port 3306 3306
mysqli.default_pw no value no value
mysqli.default_socket no value no value
mysqli.default_user no value no value
mysqli.max_links Unlimited Unlimited
mysqli.reconnect Off Off
I still get the following down the bottom of the page with "extension=php_mysql.dll" uncommented in "php.ini".
PHP Warning: PHP Startup: Unable to load dynamic library './php_mysql.dll' - The specified module could not be found. in Unknown on line 0
Not sure if it matters but PHP.ini is in c:\windows\ whereas I have installed PHP and MySQL into d:\web_server\PHP and \MySQL??
Maybe this might help. This is my SQL query and this is me trying to connect:
But I still get the error displayed in the die( ) function.
Code: Select all
$sql_query='insert into regions (region_name, state) values ("'.$_REQUEST["region_name"].'", '.$_REQUEST["state"].'")';
$db_connection->query($sql_query) or die("Unable to submit query in <b>'region_info.php'</b>.");- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US
Aha. Find libmysql.dll (from your PHP distribution) and put it in your path. See http://us2.php.net/manual/en/ref.mysql.php
mysqli is a MySQL Improved: if that's working, you might want to try using that (you won't have the PASSWORD mismatch problem either). In addition, the two generally don't work together.
mysqli is a MySQL Improved: if that's working, you might want to try using that (you won't have the PASSWORD mismatch problem either). In addition, the two generally don't work together.
Sorry; but I have no idea what you mean by all of that!!
1. Do I just put libmysql.dll in that path as per the tutorial thingy on that link?
2. What is the PASSWORD mismatch problem??
3. Aren't I already using the mysqli?? I'm using mysqli to connect; and in the mysqli library thingy; it says to use $mysqli->query(SQL)??
1. Do I just put libmysql.dll in that path as per the tutorial thingy on that link?
2. What is the PASSWORD mismatch problem??
3. Aren't I already using the mysqli?? I'm using mysqli to connect; and in the mysqli library thingy; it says to use $mysqli->query(SQL)??
- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Couple of things I see. If you can connect with mysqli_connect, throw out the idea of using mysql_* functions and stay with mysqli_* functions.
Next, take a close look at your code. Your query syntax was a bit off and I believe that you should be reading the query result into a var for use later, ala...
There is a pretty decent example in the PHP Manual page on mysqli_query.
And one last thing, there should be some sort of validation in your request vars. I would also recommend using either $_GET or $_POST if you know the value will come from one or the other. It is a bit safer (in my opinion).
Next, take a close look at your code. Your query syntax was a bit off and I believe that you should be reading the query result into a var for use later, ala...
Code: Select all
<?php
$sql_query = "insert into regions (region_name, state) values ('{$_REQUEST["region_name"]}', '{$_REQUEST["state"]}')";
if (!$result = $db_connect->query($sql_query))
{
die("There was an error: " . $db_connect->error);
}
?>And one last thing, there should be some sort of validation in your request vars. I would also recommend using either $_GET or $_POST if you know the value will come from one or the other. It is a bit safer (in my opinion).
Well I changed the code to what you said Everah and everything works a treat!!!
This is such a relief!!!
One question though...why do I want to put the result into a variable when its an "INSERT" function?? What use would I have for that?
Also thanks re: the $_POST/$_GET - I had wondered about that and eventually want to set it up so that the variable is dynamic....don't suppose you know how to do that off the top of your head??
(ie. I create a new form with a submit type and store this as a variable. Thus $submit_type="$_POST"; and I would like to have in my code: {$_REQUEST["state"]} where $_REQUEST is the value of $submit_type.
(If that makes any sense).
I havnet touched the libmysql.dll file since everything is working at the moment.
Thanks guys. This has been a couple of weeks on and off trying to work out!!
This is such a relief!!!
One question though...why do I want to put the result into a variable when its an "INSERT" function?? What use would I have for that?
Also thanks re: the $_POST/$_GET - I had wondered about that and eventually want to set it up so that the variable is dynamic....don't suppose you know how to do that off the top of your head??
(ie. I create a new form with a submit type and store this as a variable. Thus $submit_type="$_POST"; and I would like to have in my code: {$_REQUEST["state"]} where $_REQUEST is the value of $submit_type.
(If that makes any sense).
I havnet touched the libmysql.dll file since everything is working at the moment.
Thanks guys. This has been a couple of weeks on and off trying to work out!!
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
I suppose you don't have to. Probably habit for me. Though I would suggest at the very least running it through mysql_affected_rows as a check to make sure all was well.mcccy005 wrote:One question though...why do I want to put the result into a variable when its an "INSERT" function?? What use would I have for that?
Doesn't really make sense, but I am sure it can be done.mcccy005 wrote:Also thanks re: the $_POST/$_GET - I had wondered about that and eventually want to set it up so that the variable is dynamic....don't suppose you know how to do that off the top of your head??
(ie. I create a new form with a submit type and store this as a variable. Thus $submit_type="$_POST"; and I would like to have in my code: {$_REQUEST["state"]} where $_REQUEST is the value of $submit_type.
(If that makes any sense).
Congrats on getting it to work.