Unable to Write to Database

MsShelle · Dec 15, 2002

Hi All,

Not sure if I am posting in the right area of the forum but I am hoping to get educated quickly by some of the forum's members.

I am a relative newbie to MySQL and php so please bare with me.
I am developing my site and it will use a MySQL database. The site will be maintained by a content management system (PostNuke).

What I am trying to do (quite unsucessfully at this point might I add ) is create a table within the database for a small network of agent to post their available freight load information (and ultimately other users of the site can search for posted available freight loads) I've managed to create the table and dumped some data into but when called to do that from my site (in a php script) the data entered into the form is not being written to the database. I have checked several things, including using my form and script in a html page outside of the postnuke site and still the same results.

Here is the script I am using:

<?php
$DBhost = "localhost";
$DBuser = "***";
$DBpass = "***";
$DBName = "mwlweb";
$table = "loads";
mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");

@mysql_select_db("$DBName") or die("Unable to select
database $DBName");

$sqlquery = "INSERT INTO $table
VALUES('$load_number','$IBA_Number','$load_date','$Origin_City','$Origin_State','$Origin_Zip','$Destination_City','$Destination_State','$Destination_Zip','$Miles','$Product','$Equipment','$weight','$Rate','$Dispatch_Number','$Contact','$Comments')";

$results = mysql_query($sqlquery);

mysql_close();

print "<HTML><TITLE> Thanks </TITLE><BODY
BGCOLOR=\"#FFFFFF\"><center><table border=\"0\"
width=\"500\"><tr><td>";
print "<p><font face=\"verdana\" size=\"+0\"> <center>Thank you for posting your load. Update your posted loads by editing your profile.<br><br>
</td></tr></table>
</center></BODY></HTML>";
?>

I am sure that some of the more experienced coders can quickly identify what it is I have done to that code. If some one can help me and you need more information, just let me know or email me at msshelle@midwestlogisticsllc.com and in the meanwhile I'll keep reading.

Thanks in advance
MsShelle:

ahundiak · Dec 15, 2002

Can you post the schema for the loads table?

The problem might be that only character data should be surrounded by by 's in the insert statement.

So if $load_number is an integer then the insert statement will fail.

You can also try

mysql_query($sqlquery);
if (mysql_errno()) die(mysql_error());

This will show any sql syntax problems.

jerdo · Dec 15, 2002

You might try specifying which fields you are inserting into as well

$sqlquery = "INSERT INTO $table (all the database fields comma separated that you are trying to insert) VALUES ('$load_number','$IBA_Number','$load_date','$Origin_City','$Origin_State','$Origin_Zip','$Destination_City','$Destination_State','$Destination_Zip','$Miles','$Product','$Equipment','$weight','$Rate','$Dispatch_Number','$Contact','$Comments')";

MsShelle · Dec 16, 2002

Originally posted by ahundiak
Can you post the schema for the loads table?

Here it is:

phpMyAdmin MySQL-Dump

version 2.2.4

http://phpwizard.net/phpMyAdmin/

http://phpmyadmin.sourceforge.net/ (download page)

#

Host: localhost

Generation Time: Dec 16, 2002 at 12:11 AM

Server version: 3.23.51

PHP Version: 4.2.3

Database : `mwlweb`

--------------------------------------------------------

#

Table structure for table `loads`

#

CREATE TABLE loads (
id int(11) NOT NULL auto_increment,
load_number varchar(255) default NULL,
IBA_Number varchar(255) default NULL,
load_date date NOT NULL default '2000-00-00',
Origin_City varchar(55) default NULL,
Origin_State varchar(50) default NULL,
Origin_Zip varchar(10) default NULL,
Destination_City varchar(50) default NULL,
Destination_State varchar(55) default NULL,
Destination_Zip varchar(10) default NULL,
Miles varchar(5) default NULL,
Product varchar(55) default NULL,
Equipment varchar(80) default NULL,
Weight varchar(9) default NULL,
Rate varchar(6) default NULL,
Dispatch_Number varchar(20) default NULL,
Contact varchar(45) default NULL,
Comments text NOT NULL,
timestamp datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (id)
) TYPE=MyISAM;

#

Dumping data for table `loads`

#

INSERT INTO loads VALUES (1, '2228100', '2228', '0000-00-00', 'Dallas', 'Texas', '75205', 'Miami', 'Florida', '33013', '1450', 'Frozen Meat', '53 Reefer', '38,500', '$1.55', '214-555-1212', 'Ms Shelle', '', '0000-00-00 00:00:00');
INSERT INTO loads VALUES (2, '1213101', '1213', '0000-00-00', 'Houston', 'Texas', '77052', 'Los Angeles', 'California', '90086', '1550', 'can goods', 'Van', '44,500', '1.40 p', '214-521-0451', 'Marino', '', '0000-00-00 00:00:00');
INSERT INTO loads VALUES (3, '1952100', '1952', '0000-00-00', 'St.Louis', 'Missouri', '63166', 'Seattle', 'Washington', '98060', '2149', 'Steel Coils', 'Flatbed w/Tarps', '45,000', '2350 f', '816-650-6787', 'Jan', '', '0000-00-00 00:00:00');
INSERT INTO loads VALUES (4, '1231100', '1231', '0000-00-00', 'Independence', 'Missouri', '64050', 'Philadelphia', 'Pennsylvania', '19104', '1130', 'Frozen Bread', 'Reefer', '44,000', '1.38 p', '816-456-2911', 'Richard', 'have a return load available', '0000-00-00 00:00:00');
INSERT INTO loads VALUES (5, '1231101', '1231', '0000-00-00', 'Philadelphia', 'Pennsylvania', '19104', 'Independence', 'Missouri', '64050', '1130', 'frozen meat', 'Reefer', '42,000', '1.15 p', '816-456-2911', 'Richard', '1 p/up in Reading,Pa', '0000-00-00 00:00:00');
INSERT INTO loads VALUES (6, '2228101', '2228', '0000-00-00', 'Dallas', 'Texas', '75221', 'Atlanta', 'Georgia', '30301', '780', 'dry goods', 'Van', '35,000', '1.25 p', '214-555-1212', 'MsShelle', 'will have several every week', '0000-00-00 00:00:00');
INSERT INTO loads VALUES (7, '1952101', '1952', '0000-00-00', 'Kansas City', 'Missouri', '64101', 'Chicago', 'Illinois', '60607', '538', 'frozen veggies', 'Reefer', '40,000', '1.18', '816-650-6787', 'Jan', '20 pallet xchange', '0000-00-00 00:00:00');
INSERT INTO loads VALUES (8, '1952102', '1952', '0000-00-00', 'Kansas City', 'Missouri', '64101', 'Indianapolis', 'Indiana', '46206', '485', 'frozen meat', 'Reefer', '32,500', '1.36 p', '816-650-6787', 'Jan', '20 pallet xchange', '0000-00-00 00:00:00');
INSERT INTO loads VALUES (9, '1213102', '1213', '0000-00-00', 'Portland', 'Oregon', '97208', 'Dallas', 'Texas', '75205', '2130', 'dry goods', '53 Van', '43,500', '1.31 p', '214-521-0451', 'Marino', 'have 3 available', '0000-00-00 00:00:00');

My dates are not being showing correctly either
Thanks In Advance
MsShelle

MsShelle · Dec 23, 2002

Hello All,

I have managed to fix the form and it is now writing to the db but the date and timestamp are entered as shown in the dump.

Can anyone see why the date and timestamp are not writing to the database or should I say, why they appear as zeroes?

Thanks In Advance
MsShelle

adrian_quah · Dec 23, 2002

MsShelle,
I think its because u're writing the values 0000-00-00:00:00:00 values as indicated in ur SQL queries. For mine, i use...

         

       $today = getdate();
       $year = $today['year'];
       $month = $today['mon'];
       $mday = $today['mday'];
       $todays_date = $year ."-". $month ."-". $mday;  // date in MySQL date format     $today = getdate();

then insert $todays_date into the SQL query..Pls let us know if u find it working or not

MsShelle · Dec 24, 2002

Thanx Adrian for replying!

Although I understand your point, I think I should have tried to be more concise as to the problem I am experiencing.

When I wrote the dump data to test my db I used the format as it appears but with current dates and then using MyPhpAdmin I executed the file. I didn't notice that it had changed until I ran the schema that I posted.

So far what I have read led me to believe that when I used the date or timestamp, that MySQL would use the default but maybe I am misunderstanding it.

Thanks for any help provided

MsShelle