Writing to an external database

scottiegirl · March 2nd, 2004, 01:22 PM

Hello again all! Problem: Writing data gathered from an online form to a database on a server not at my location. The only info the server company can give me is Perl, not PHP.

Here is the table (table1):
id, mediumint(10),not null, primary key, auto_increment
date, date, '0000-00-00'
pagefrom, enum('A','B','C','D'),default=NULL
contact varchar(50), default=NULL
company varchar(255), default=NULL
address varchar(100)
address2, varchar(100), default=NULL
city, varchar(100)
state, char(2), default=NULL
zipcode, mediumint(10), default='0'
telephone, varchar(20), default=NULL
email, varchar(255)
ndtype, enum('A','B','C','D','E'), default=NULL
ndspecs, varchar(255), default=NULL
cpareqd, double, default='0'
cpcond, varchar(255), default=NULL
cpdamt, double, default='0'
cpdspecs, varchar(255), default=NULL
approved, enum('Y','N'), default='N'

Here is the code:

Code:

<?php
include "./common.inc";

if !$ndspecs {
    $ndspecs = "NULL";
}
if !$cpareqd {
    $cpareqd = "0";
}
if !$cpcond {
    $cpcond = "NULL";
}
if !$cpdamt {
    $cpdamt = "0";
}
if !$cpdspecs {
    $cpdspecs = "NULL";
}

// Connect to db
 $dbcnx = mysql_connect($dbhost,$dbusername,$dbuserpassword)
     or die("Could not connect: " . mysql_error());

// Select db
 mysql_select_db($default_dbname, $dbcnx);

// Input info to table
 $query = "INSERT INTO table1 VALUES(NULL,curdate(),'$pagefrom','$contact','$company','$address','$address2','$city','$state','$zipcode','$telephone','$email','$ndtype','$ndspecs','$cpareqd','$cpcond','$cpdamt','$cpdspecs','$approved')";
 $result = mysql_query($query);
 if (!$result) {
    die ('<p>Error performing insertion: ' . mysql_error() . '</p>');
 }

//Close db
 mysql_close($dbcnx);
?>

Doesn't work. I realize it's something stupid that I'm just not seeing. I have a lot of experience in this area, believe me.

I thought it might be the single quotes around the variables in the INSERT INTO statement, but Ch. 13, pg. 471 of the Beginning PHP4 book shows them.

Any ideas are welcome. Thanks!

HollyAnn
aka Scottiegirl

"I was put on Earth to accomplish a certain number of things. Right now I am so far behind, I will never die." - Calvin, Calvin and Hobbs

happygv · March 2nd, 2004, 01:57 PM

Yes, you are really missing something there. My previous post for your query also was similar to this one. I would suggest you to go through the PHP manual for dealing with variables and MYSQL manual for getting used to the syntax of Query statements.

I think you are mixing up with dealing variables in your code this time too.

Any variable to substitiute its value should be used outside the single quotes and used with DOT operator which concatenates the variable's value to the prefixed/suffixed string, else it is taken as literals. In your case you are trying to insert values to the table1 as '$address' itself instead of substituting the value $address holds. Your query should look like this, to work.

$query = "INSERT INTO table1 VALUES(NULL,curdate(),'" . $pagefrom . "','" . $contact . "','" . $company . "','" . $address . "','" . $address2 . "','" . $city . "','" . $state . "','" . $zipcode . "','" . $telephone . "'," . $email . "','" . $ndtype . "','" . $ndspecs . "','" . $cpareqd . ",'" . $cpcond . "'," . $cpdamt . ",'" . $cpdspecs . "','" . $approved . "')";

print $query; //Copy and execute the query in MYSQL db, that resulted
//in the above print and see if that inserts data
//successfully.

Try to print the value of $query and see. Execute the same in Mysql db, to see what error you get and check if the constructing of SQL Query is correct. If the construction of Query String is wrong, this way you can find where you go wrong. This is how you can debug when you are not sure how to go about.

As $cpdamt and $cpareqd are defined as double it is not embedded in single quotes.

Cheers,

-Vijay G

scottiegirl · March 2nd, 2004, 03:00 PM

You know, Vijay, I'll get this yet.

Maybe a sledgehammer would work better.....

Question: Do I remove the following code if I put in the "print" coding?

Code:

 $result = mysql_query($query);
 if (!$result) {
    die ('<p>Error performing insertion: ' . mysql_error() . '</p>');
 }

Thanks!!!!

HollyAnn
aka Scottiegirl

"I was put on Earth to accomplish a certain number of things. Right now I am so far behind, I will never die." - Calvin, Calvin and Hobbs

happygv · March 2nd, 2004, 03:04 PM

yes, you got to comment the code that you gave in your last mail and put the print statement. browse that page to see if the query is constructed properly... run the same in mysql db and see if the value is inserted. If so, you are through.

// Print to be put here...

//$result = mysql_query($query);
// if (!$result) {
// die ('<p>Error performing insertion: ' . mysql_error() . '</p>');
//{

Cheers,

-Vijay G

scottiegirl · March 2nd, 2004, 04:02 PM

:D Vijay, you are a godsend. Everything printed with all of the proper single quotes AND all gets inserted into the db properly.

I vow to review my PHP variables and MySQL query syntax!

Ever in your debt...

HollyAnn
aka Scottiegirl

"I was put on Earth to accomplish a certain number of things. Right now I am so far behind, I will never die." - Calvin, Calvin and Hobbs