Unable to INSERT into mySQL (using PHP) but can SELECT

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

Unable to INSERT into mySQL (using PHP) but can SELECT

Post by mcccy005 »

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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

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());
mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

Post by mcccy005 »

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

Code: Select all

$db_connection = new mysqli($host_name, $username, $password, $database_name);
And this is how I submit the query (which works for SELECT but not INSERT):

Code: Select all

$db_connection->query($sql_query) or die("Unable to submit query in <b>'hb_info.php'</b>.");
I will post the output of the above code later on when I get home but thought the above might be of some use.
mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

Post by mcccy005 »

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
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

(at this point I really wish I had Feyd's diagnostic script).

What version of PHP are you using, and does MySQL show up if you execute a page that has <?php phpinfo(); ?>
mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

Post by mcccy005 »

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??
mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

Post by mcccy005 »

Maybe this might help. This is my SQL query and this is me trying to connect:

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>.");
But I still get the error displayed in the die( ) function.
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

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.
mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

Post by mcccy005 »

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)??
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

Maybe that's the problem. If you're using mysqli, and use one of the functions, you have to use mysqli_query, not mysql_query (which was what I saw up there). But the behavior is still puzzling. :?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

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

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);
}
?>
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).
mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

Post by mcccy005 »

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!!
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Ambush Commander wrote:(at this point I really wish I had Feyd's diagnostic script).
Now it's part of a GPLed greasemonkey script. I wouldn't post the link without feyd's permission, but you may have the script if you want.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

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?
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: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).
Doesn't really make sense, but I am sure it can be done. 8)

Congrats on getting it to work.
Post Reply