MySQL INSERT ==>> NOT INSERTING

MySQL 'INSERT INTO' Statement not inserting data into Database.

[GlobalFusion]

Hi,

This is absoluptly driving me up the wall.

I am sure it is the smallest of things i have missed..

SCENARIO:

Form >> PHP Variables >> DB

PROBLEM:

Data is successfully sent to PHP file (shows on output), but fails to INSERT INTO the database.

MY INSERT CODE:

// hostname or ip of server
$servername='localhost';

// username and password to log onto db server
$dbusername='por_por';
$dbpassword='********';

// name of database
$dbname='por_petition';
$dbtable = $petition;

connecttodb($servername,$dbname,$dbusername,$dbpassword);
function connecttodb($servername,$dbname,$dbuser,$dbpassword)
{
global $link;
$link=mysql_connect ("$servername","$dbuser","$dbpassword");
if(!$link){die("Could not connect to MySQL");}
mysql_select_db("$dbname",$link) or die ("could not open db".mysql_error());
}

//set query
$query = "insert INTO $dbtable (ID, firstname, lastname, email, area, comments, date, time, ip) VALUES ('','$firstname','$lastname','$email','$area','$comments','$date','$time','$ip')";

//insert data into DB
mysql_query($query);

--------------------------
TABLE STRUCTURE
--------------------------
use por_petition;
SET FOREIGN_KEY_CHECKS=0;
#----------------------------
# Table structure for 001
#----------------------------
drop table if exists `001`;
CREATE TABLE `001` (
`ID` tinyint(11) unsigned NOT NULL auto_increment,
`firstname` varchar(255) NOT NULL default '',
`lastname` varchar(255) NOT NULL default '',
`email` varchar(255) NOT NULL default '',
`area` varchar(255) NOT NULL default '',
`comments` varchar(255) NOT NULL default '',
`date` varchar(255) NOT NULL default '',
`time` varchar(255) NOT NULL default '',
`ip` varchar(255) NOT NULL default '',
PRIMARY KEY (`ID`)
) TYPE=MyISAM;
#----------------------------
# Records for table 001
#----------------------------

Any and all help is appreciatted.
---------------------------------

[Dreamquick]

Add an error trap after mysql_query and see what happens, because as far as I can see from your sample that's the one point where you're not looking for errors.

- Tony

[GlobalFusion]

i did.

echo "the db error is: ".mysql_error($query);

it gave me this:

Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in /home/por/public_html/petition/submit_petition.php on line 72

the db error is:

so.. *shrugs* no idea.

[dreamcatcher]

Does the $dbtable variable have a value?

I notice earlier you have this:

$dbtable = $petition;

So, now does $petition hold any value to pass to the new variable? And my third questions is, why assign something to $dbtable? Why not just the $petition variable in your query?

And welcome to WebmasterWorld GlobalFusion :)

dc

[frizhard]

you have it in the warning:

Not a valid MySQL link resource.

You are not sending the query to the database, as the connection is not valid.

Try adding the connection handle in the query:

mysql_query($query, $link);

You could also var_dump($link) to check if it is a MySQL resource.

Hope this can help you

[GlobalFusion]

Hi.

$dbtable DOES hav a value, and the reason it isnt HARDCODED, is because it is part of a dynamic form that uses a different table for different forms.

I added:
//insert data into DB
mysql_query($query, $link);
var_dump($link);

Which gave:
resource(2) of type (mysql link)

[volatilegx]

Try this syntax:

$result = mysql_query($query);

[GlobalFusion]

ok. that didnt work. (not how i used it anyway)

------------------------------------------
FORM PAGE GOES TO 'SUBMIT_PETITION.PHP'
------------------------------------------

-------------------------------------
Heres The Code (SUBMIT_PETITION.PHP)
-------------------------------------

connecttodb($servername,$dbname,$dbusername,$dbpassword);
function connecttodb($servername,$dbname,$dbuser,$dbpassword)
{
global $link;
$link=mysql_connect ("$servername","$dbuser","$dbpassword");
if(!$link){die("Could not connect to MySQL");}
mysql_select_db("$dbname",$link) or die ("could not open db".mysql_error());
}

//set query
$query = "insert INTO $dbtable (ID, firstname, lastname, email, area, comments, date, time, ip) VALUES ('','$firstname','$lastname','$email','$area','$comments','$date','$time','$ip')";

//insert data into DB
$result = mysql_query($query);

echo "the db error is: ".mysql_error($query);
echo '<br />--------------<br /> '.$query.'<br />--------------------<br /><br />';

mysql_close();

//redirect to confirmation page
include('submit_confirm.php');

------------------------------------------
OUTPUT SHOWN ON 'SUBMIT_CONFIRM.PHP'
------------------------------------------

OutPut Shows as:

Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in /home/por/public_html/petition/submit_petition.php on line 41
the db error is:
--------------
insert INTO 001 (ID, firstname, lastname, email, area, comments, date, time, ip) VALUES ('','John','Doe','SomeWhere','[e-mail]','In the end.. this will work..','Sunday 20th of March 2005','02:57:25 PM','202.#*$!.xxx.xxx')
--------------------

$petition = 001
$date = Sunday 20th of March 2005
$time = 02:57:25 PM
$ip = 202.172.123.199
$firstname = John
$lastname = Doe
$email = SomeWhere
$area = [e-mail]
$comments = In the end.. this will work..

[gardenguy]

Add one more line to the end of connecttodb(blah):

return $link;

[GlobalFusion]

ok. tried that.

---------------------
connecttodb($servername,$dbname,$dbusername,$dbpassword);
function connecttodb($servername,$dbname,$dbuser,$dbpassword)
{
global $link;
$link=mysql_connect ("$servername","$dbuser","$dbpassword");
if(!$link){die("Could not connect to MySQL");}
mysql_select_db("$dbname",$link) or die ("could not open db".mysql_error());
}
return $link;
---------------------

And i tried in the closing bracket too. but still nothing.

The (out of bracket attempt) prevented my 'output' page from showing the data.

*currently running with the 'return $link' inside the brackets.

[edited by: coopster at 6:39 pm (utc) on Mar. 20, 2005]
[edit reason] removed url per TOS [webmasterworld.com] [/edit]

[zivkovicp]

Take a minute and recheck your script for the following:

if you are enclosing a variable name in a single quote, '$var' then try this instead: (enclose in double quotes)

$query = "INSERT INTO.... VALUES ('". $var1 ."', '". $var2 ."');";

Your code should work without any problems, but I find that sometimes this method is better (even if it's more difficult to look at).

No harm in trying... I've never had problems doing it this way.

Pete

[Dinkar]

GlobalFusion, I guess the field "ID" has 'auto-increment' value. If this is the case then you should NOT use it in your SQL query.

[gardenguy]

It surely must have the return inside the function (curly bracket). I agree with the above that ID should be removed from the INSERT since you have no value picked up from the form.

[GlobalFusion]

Im listing the 'ID' as only the field name. leaving the 'value' blank, so the DB auto fills/increments it.

Tried this:
-----------
//set query
$query = "INSERT INTO '$dbtable' ('ID', 'firstname', 'lastname', 'email', 'area', 'comments', 'date', 'time', 'ip') VALUES ('', '".$firstname."', '".$lastname."', '".$email."', '".$area."', '".$comments."', '".$date."', '".$time."', '".$ip."')";
//insert data into DB
mysql_query($query);

To No Avail either.. :(
------------------------

And This:
---------

//set query
$query = "INSERT INTO '$dbtable' ('firstname', 'lastname', 'email', 'area', 'comments', 'date', 'time', 'ip') VALUES ('".$firstname."', '".$lastname."', '".$email."', '".$area."', '".$comments."', '".$date."', '".$time."', '".$ip."')";
//insert data into DB
mysql_query($query);

Neither Worked..
----------------

:(

The only time it succeded.. was when i made an insert via PhpMyAdmin.

This is the code it produced:

PHPMYADMIN
__________________________________________________
SQL-query:
INSERT INTO `001` ( `ID` , `firstname` , `lastname` , `email` , `area` , `comments` , `date` , `time` , `ip` )
VALUES (
'', 'test', 'user', '[e-mail]', 'this area', 'some comments', 'some date', 'some time', '#*$!.#*$!.xxx.xxx'
);
__________________________________________________

But if i use that EXACT same code in my php file.. it doesnt INSERT. :( could it be something else?

Does anyone have a better connection string?

[dreamcatcher]

I doubt INSERT INTO '$dbtable' will work with apostrophes. Have you tried it with grave accents, a la your PHPMYADMIN code?

$query = "INSERT INTO `$dbtable` (`firstname`, `lastname`, `email`, `area`, `comments`, `date`, `time`, `ip`) VALUES ('".$firstname."', '".$lastname."', '".$email."', '".$area."', '".$comments."', '".$date."', '".$time."', '".$ip."')";
//insert data into DB
mysql_query($query);

Also, with regards to an earlier post, the return $link; should have been INSIDE the function.

dc

[GlobalFusion]

Woot!

well, i think it was a combination of several things..

But it works now!

Thankyou soooo much for all your help guys!

Kudos to you all! :)

[zivkovicp]

It's great that you got it working...

I have only one thing to add (ask), is the code you posted EXACTLY as it is in your program? If it is then this is probably your problem:

Look at the END of your insert:

.$ip."')";

Do you see whats missing? At the end of every MySQL query you MUST include a ';' semi-colon... your code doesn't. You have one only for the PHP script, you need one inside the quotes:

.$ip."');";

See? Am I crazy or is this the fix?

[coopster]

Only from the command line, zivkovicp. Not within a PHP query statement:

The query string should not end with a semicolon.

mysql_query() [php.net]

[zivkovicp]

oops! :p

Well as long as it's working now, that's all that matters!

[coopster]

no worries, lots of folks miss that little blurb in the manual pages ;)