Jump to content

The variable birthday and telephone are not INSERT INTO


co.ador

Recommended Posts

<?php 

"INSERT INTO costumers
(firstname,lastname,birthdate,product_name,price,details,category,subcategory,city,state,zipcode,country)            VALUES('$fname','$lname','$birthday','$itemname','$price','$details','$category','$subcategory','$city','$state','$zipcode','$country')";
?>

 

 

What am I missing in the INSERT query above that is not inserting the values of birthday.?

Link to comment
Share on other sites




SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


--
-- Table structure for table `costumers`
--

CREATE TABLE IF NOT EXISTS `costumers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` char(50) CHARACTER SET utf8 NOT NULL,
  `lastname` char(50) CHARACTER SET utf8 NOT NULL,
  `birthdate` date NOT NULL,
  `telephone` varchar(12) CHARACTER SET utf8 NOT NULL,
  `product_name` varchar(225) CHARACTER SET utf8 NOT NULL,
  `price` varchar(12) CHARACTER SET utf8 NOT NULL,
  `details` varchar(225) CHARACTER SET utf8 NOT NULL,
  `category` varchar(100) CHARACTER SET utf8 NOT NULL,
  `subcategory` varchar(100) CHARACTER SET utf8 NOT NULL,
  `City` varchar(50) CHARACTER SET utf8 NOT NULL,
  `state` varchar(50) CHARACTER SET ucs2 NOT NULL,
  `zipcode` varchar(10) CHARACTER SET utf8 NOT NULL,
  `Country` char(25) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

 

I think it might be the database field structures.

Link to comment
Share on other sites

 
"INSERT INTO costumers
(firstname,lastname,birthdate,telephone,product_name,price,details,category,subcategory,city,state,zipcode, country)            
VALUES('$fname','$lname', (TO_DATE ('$birthdate','m.d.y')), '$telephone','$itemname','$price','$details','$category','$subcategory','$city','$state','$zipcode','$country')";

 

 

As you can see I am trying to format the date to 'm.d.y' and insert the date as well. It is not working as it is.

 

The Statement above is returning the following error syntax. I don't know if TO_DATE is supposed to be inside ().

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' 978-987-2393','solofo','23.12','asi es la cosa','volvo','volvo','city hersy','' at line 3

 

Link to comment
Share on other sites

try this

"INSERT INTO costumers
(firstname,lastname,birthdate,telephone,product_name,price,details,category,subcategory,city,state,zipcode, country)            
VALUES('$fname','$lname', STR_TO_DATE('$birthdate','%d,%m,%Y'), '$telephone','$itemname','$price','$details','$category','$subcategory','$city','$state','$zipcode','$country')";

to note this presumes your using $birthdate as "20,03,1979" if your using "20/03/1979" change the above to '%d/%m/%Y' etc etc

Link to comment
Share on other sites

I have a question when formating a variable in a sql statement we don't wrap the function such as TO_DATE or the one you used STR_TO_DATE in parenthesis right? like

 

(STR_TO_DATE('$birthdate','%d,%m,%Y')),

 

 

Now it is trowing a error like

 

Column 'birthdate' cannot be null

 

the table structure for that colum is NOT NULL I don't know why is returning that error

Link to comment
Share on other sites

it doesnt really matter about the brackets, i dont, but i know it is done.

 

the error is coming because you called the column header but probably defined no data ie insert into `table` (`id`,`data`)value('',) where it should be like insert into `table` (`id`,`data`)value('','')

 

whats the value of birthdate if you echo it outside the sql?

Link to comment
Share on other sites

it is grabbing the value so it means that the value of birthdate pass to the insertForm.php but then at the point of the INSERT query it won't ENter in the database let me try putting the id and the data values

 

When I put the INSERT( id, data)  VALUES ('','') it will say

 

data field doesn't exist

 

When I get the data field out and leave "id" then it will come back to the error in the previous post

 

birthdate colum can not be null.

 

what is happening?

 

The birthday values are passing through the form action to InsertForm.php the name of file where I pass the file from the form...

 

HELP!

Link to comment
Share on other sites

INSERT INTO costumers (id,firstname,lastname,birthdate,telephone,product_name,price,details,category,subcategory,city,state,zipcode, country) VALUES('','pedro','prueba', STR_TO_DATE('01-23-1987','%m,%d,%Y'),' 978-349-1234','coloso','34','prueba','volvo','volvo','prueba','prueba','23848','United States')

 

01-23-1987Column 'birthdate' cannot be null

 

the above is the print_r($query) everything is passing but why is not inserting?

Link to comment
Share on other sites

I am having this problem Column 'birthdate' cannot be null, I have set up the  birthdate field to NULL, the screen runs good but in the database there is no data in the birthdate field. I did debug by echoing the birthdate from the form to the site receiving the values and it outputs what was input in the forms but it won't go farther that such as inserting it into the database.

 

I have solved the format of the date like:

 

STR_TO_DATE('$birthdate','%m,%d,%Y'),

 

But it won't insert it in the database.

Link to comment
Share on other sites

I inserted like YYYY-MM-DD It is a little bit uncomfortable for the user because I know it is all the way around how user feels comfortable MM-DD-YYYY, any way I put a message in the form how it is expected from them to enter the date.

 

thank you that way I don't get any errors. I can see formating the date and time is when returning the data from the database to front end. I take that into account when returning data for the user.

Link to comment
Share on other sites

When the date format coming from the text is MM/DD/YYYY, I usually convert it to YYYY-MM-DD so that MySQL can accept the input. I normally do this:

 

Ex.

 

txtbirthday = "10/29/1985";

 

$birthday = $_GET['txtbirthday'];

$brthday = substr($birthday,6,4) . "-" . substr($birthday,0,2) . "-" . substr($birthday,3,2);

 

Hope this helps.

 

Chill.

 

 

Link to comment
Share on other sites

its real easy, its how your passing the format rules to str_to_date

INSERT INTO costumers (id,firstname,lastname,birthdate,telephone,product_name,price,details,category,subcategory,city,state,zipcode, country) VALUES('','pedro','prueba', STR_TO_DATE('01-23-1987','%m,%d,%Y'),' 978-349-1234','coloso','34','prueba','volvo','volvo','prueba','prueba','23848','United States')

your using hyphons so change the str_to_date to STR_TO_DATE('$birthdate','%m-%d-%Y')

INSERT INTO costumers (id,firstname,lastname,birthdate,telephone,product_name,price,details,category,subcategory,city,state,zipcode, country) VALUES('','pedro','prueba', STR_TO_DATE('01-23-1987','%m-%d-%Y'),' 978-349-1234','coloso','34','prueba','volvo','volvo','prueba','prueba','23848','United States')

 

:)

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.