homepage Welcome to WebmasterWorld Guest from 54.166.66.204
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
MySQL INSERT ==>> NOT INSERTING
MySQL 'INSERT INTO' Statement not inserting data into Database.
GlobalFusion




msg:1246845
 4:53 pm on Mar 19, 2005 (gmt 0)

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




msg:1246846
 5:23 pm on Mar 19, 2005 (gmt 0)

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




msg:1246847
 6:10 pm on Mar 19, 2005 (gmt 0)

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




msg:1246848
 6:28 pm on Mar 19, 2005 (gmt 0)

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




msg:1246849
 12:02 am on Mar 20, 2005 (gmt 0)

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




msg:1246850
 2:08 am on Mar 20, 2005 (gmt 0)

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




msg:1246851
 3:30 am on Mar 20, 2005 (gmt 0)

Try this syntax:

$result = mysql_query($query);

GlobalFusion




msg:1246852
 4:55 am on Mar 20, 2005 (gmt 0)

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','johndoe@nonames.com','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 = johndoe@nonames.com
$comments = In the end.. this will work..

gardenguy




msg:1246853
 12:03 pm on Mar 20, 2005 (gmt 0)

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

return $link;

GlobalFusion




msg:1246854
 3:16 pm on Mar 20, 2005 (gmt 0)

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




msg:1246855
 3:31 pm on Mar 20, 2005 (gmt 0)

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




msg:1246856
 3:38 pm on Mar 20, 2005 (gmt 0)

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




msg:1246857
 3:59 pm on Mar 20, 2005 (gmt 0)

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




msg:1246858
 5:01 pm on Mar 20, 2005 (gmt 0)

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', 'test@user.com', '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




msg:1246859
 5:23 pm on Mar 20, 2005 (gmt 0)

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




msg:1246860
 5:33 pm on Mar 20, 2005 (gmt 0)

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




msg:1246861
 7:58 pm on Mar 20, 2005 (gmt 0)

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




msg:1246862
 8:35 pm on Mar 20, 2005 (gmt 0)

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




msg:1246863
 8:50 pm on Mar 20, 2005 (gmt 0)

oops! :p

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

coopster




msg:1246864
 10:17 pm on Mar 20, 2005 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved