The variable birthday and telephone are not INSERT INTO

[co.ador]

<?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.?

[jcbones]

What is the value of $birthday?

[co.ador]

in the form?

 

<label for="Birthdate">Birthdate: </label><input type="text" name="birthdate" />

 

In the form I don't have a value assigned to it. Am I supposed to put a value?

[co.ador]

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.

[co.ador]

by mistake I have entered

 

" 2019-08-22 "

 

and it inserted into the database which it means that the format it is accepting is Year, month, day How can I change the format to accept Month, day, Year?

 

Thanks

[co.ador]

and for the price name value I had "lastname" instead of "price" that's why the price was being inserted in the lastname field...

 

Thanks.

[dawsba]

can you send the insert statement?

[co.ador]

 
"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

 

[jcbones]

MySQL accepts date's as Y-m-d only.  You can submit the date in any format you wish, but you must change it to reflect an acceptable MySQL format.  Which is YYYY-mm-dd.

 

Look at:

date()

strtotime()

[dawsba]

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

[co.ador]

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

[dawsba]

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?

[co.ador]

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!

[co.ador]

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?

[co.ador]

I would like to know how to debug a function or print_R or print_a a function such as STR_TO_DATE.

 

Thanks

[Pikachu2000]

What do you mean? Can you provide an example?

[Pikachu2000]

A DATE data type field expects the date to be formatted as YYYY-MM-DD. How are you getting the date from the form?

[PFMaBiSmAd]

If you would stop making new threads for your current problem (this is like the 4th one in a series), you wouldn't need to keep explaining what you are doing or showing the code you are trying to make work.

[Pikachu2000]

The threads that were essentially all duplicates of the same problem have been merged here.

[co.ador]

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.

[Pikachu2000]

You can't insert into a date field in that format.

 

A DATE data type field expects the date to be formatted as YYYY-MM-DD. How are you getting the date from the form?

[co.ador]

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.

[kenrbnsn]

The format for storing the date really has no relation to how you present it to the user. Present it to the user the way they are use to seeing it.

 

Ken

[jpacs]

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.

 

 

[dawsba]

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')