MySQL/PHP Combining 3 vars into 1

[bodgekaloopie]

I have a database that is functioning properly, but I would like to change the format in which the date is entered.

Currently both the database and the form have the date as "order_date". However I would like to change the form that inserts the record into the database so that there are three separate fields:

month <list/menu>
day <list/menu>
year <textfield>

that become one field (order_date) when inserted into the database.

I've tried several different methods that have not succeeded. The database updates everything except for the date, which defaults to 0000-00-00.

Does anyone know what the correct MySQL language is to achieve this?

[php~pro]

$date = ".$year."-".$month."-".$day."";

Would do it assuming you database is using column in date(13) format. Though you did not specify which time format you display to your users on the input form, just re-arrange the vars accordingly.

[bodgekaloopie]

Thanks for the timely reply.

I understand how this will work, but I'm afraid I am unsure of the correct syntax.

Where in the PHP document so I insert this statement in order for it to work?

Pertinent parts of of my code:

//Connect to db

//Handle form

//Define query
$query1 = "INSERT INTO orders (order_date) VALUES ('{$_POST['order_date']}')";

//Code for month, day, year vars inserted here

<?php $order_date=".$year."-".$month."-".$day."; ?>

I've tried a few locations - sample above - without success.

[steve0]

You will need to define the date BEFORE the INSERT query

[php~pro]

/Connect to db

//Handle form

//define the date
$order_date=".$year."-".$month."-".$day."; 
//Define query
$query1 = "INSERT INTO orders (order_date) VALUES ( '$order_date' )";

NOT

$query1 = "INSERT INTO orders (order_date) VALUES ('{$_POST['order_date']}')";

Because in your code you were trying to enter a value from a $_POST['variable']. In the snippet above the value is made up into a single variable $order_date. Replace your posted code with the code inserted above. It works well. Tested. It will also work in date, date(13), or varchar. MySQL is easy going on date inputs to a date formatted column, ie even if the value you inserted was 2004/09/06 the entered value would be 2004-09-06. Same applies for many other +,\, _, ~

[redcircle]

what data type is order_date in the database. Is it just a varchar? or a datetime, bingint, etc.

[bodgekaloopie]

It's just DATE.

[php~pro]

month <list/menu>
day <list/menu>
year <textfield>

that become one field (order_date) when inserted into the database.

If its just date then just change the var $order_date to $date. Its so simple !!

If you applied some effort, didnt expect everything done for you and visited the php manual now and again you would learn something. Why not post your code and ask users to write a full working copy of amazon for you.

[bodgekaloopie]

Sorry I ticked you off. I'm really not a total idiot, and I NEVER expect anyone to code for me.

This is my first time using this forum, and I didn't realize that it didn't default to automatically notifying me of new posts.

Accordingly, I missed your Tue Aug 31, 2004 6:38 pm post with all of the details in it until I went back to the forum today. You were right - it was easy, and I appreciate your help.

[php~pro]

No probs.

Most coding is easy its just the knowing how, and the syntaxing. Main thing is you have now learned something. There will be many times when you are stuck, and need a tutorial or manual for something you feel may be very complex. Once you see the answer you wil often look in disbelief at how easy the solution was.

[bodgekaloopie]

Thanks for understanding. This is my first major project working with PHP, and while php.net and a couple of PHP books are at my side constantly, sometimes I think I get too immersed in the learning aspect of it and need to step back for a few minutes and clear my head.

And you're right about the disbelief part. It happened when I implemented your suggestion!

[bodgekaloopie]

What about doing the same thing but using UPDATE in lieu of INSERT?

I have tried using the same syntax and several variations, but have been unsuccessful in making it work.

This is one version of what I have attempted:

//Handle date issue
$map_eff_date=".$myear."-".$mmonth."-".$mday.";

// Define query
$query = "UPDATE orders SET map_eff_date='.$myear."-".$mmonth."-".$mday.', LIMIT 1";

I have also tried:

$query = "UPDATE orders SET map_eff_date='$map_eff_date', LIMIT 1";

and:

$query = "UPDATE orders SET map_eff_date='($map_eff_date)', LIMIT 1";

[php~pro]

Take note of the = operator position. Just shft to leave at least one space. A common pitfal.


Also note that you tried to escape and notate at the beginning of your string definition

//Handle date issue
$map_eff_date=".$myear."-".$mmonth."-".$mday.";

should be

//Handle date issue
$map_eff_date = $myear."-".$mmonth."-".$mday;

$myear is in itself a variable. No escape to insert any delimiter required.

[bodgekaloopie]

Thanks! I have corrected the syntax in that line, but I still can't get it to update. All of the other fields will update, but not the date.

Is the problem with the syntax in my UPDATE query?
I've checked my book, MySQL manual, and have tried all of the formats listed in my previous post, but nothing seems to work.

[php~pro]

//Handle date issue
$map_eff_date = $myear."-".$mmonth."-".$mday;


$query = ("UPDATE orders SET map_eff_date='$map_eff_date'");

Watch your syntax. Compare the query. Also you do not need to apply any LIMIT on an update.

[bodgekaloopie]

Son of a gun! I really thought that I had tried this particular syntax, but of course I must not have because this time it worked.

You are a peach. Thanks for your help! :D