Date_format()?

[lukeurtnowski]

is this right?
$sql="INSERT into activity VALUES('','DATE_FORMAT('$activity_date', '%m/%d/%Y')',
'$activity_description','$c_id','$next_step')";
If im trying to convert the date format to mm/dd/yyyy?

[oraclable]

You can try, but..
Every DBMS gives a structure to insert values, this is what we call "datatype definition"
if you want to play with the data, the best way to do it is in the "select" clause

for more about that go ot : http://dev.mysql.com/doc/mysql/en/da...ime-types.html

[lukeurtnowski]

ok, I put it in a select statment (you're soo helpful, thank you)
select DATE_FORMAT('a_date', '%d/%m/%Y') from activity order by a_date;
but it doesn't return anything?

[oraclable]

select date_format(a_date,'%d/%m/%Y') from activity

Do not use a field_names between quotes..

[lukeurtnowski]

thank you, but for some reason it's not returning anything.
http://www.lukesplace.com/Padilla/createreport.php
Did I mess up something? (a_date is a field of date type)
Do you want to see my phpmyadmin, where the activity table is?

[lukeurtnowski]

For some reason, /I get an error when this php code is run? I cant figure out why
if ((isset($activity_id)) && (isset($company_id))) {
$sql4="SELECT c_name from company WHERE c_id = $_GET[company_id]";
$query4=mysql_query($sql4);
$result4 = mysql_fetch_array($query4);
$sql1="SELECT next, medivas, a_desc, date_format(a_date,'%d/%m/%Y') AS a_date_formatted from activity WHERE a_id = $_GET[activity_id]";
$query1=mysql_query($sql1);
$result1 = mysql_fetch_array($query1);
heres the error I get (Line 27 is the 2nd to last line.)

Warning: Supplied argument is not a valid MySQL result resource in /home/domains/lukeu/lukesplace.com/Padilla/deleteactivity.php on line 27

[oraclable]

for the date issue
I think you forgot to put the value between quotes, because date type works like string type.

Try this out:
if ((isset(date)) {
$p_date=$_GET[date];
}
$slq="select * from activity where a_date='".$p_date."'";

because the below is working..
http://www.lukesplace.com/Padilla/search_by_date.php?date='01/01/2005'

[oraclable]

I would suggest you to try sql1 and sql4 separately...

or tell me you want to do..

[lukeurtnowski]

OK, I have everything figured (thanks to you), on one of my pages I have a form where users enter a date of a certain activity (mm/dd/yyyy) format. The problem is this, when the date is written to the mysql table, it goes in the format (yyyy-mm-dd). The date is not being transferred to the table in the correct form once its format is altered, do you know how to correct this?
Thanks

[Liam27]

Ok, I'm not entirely sure what you're saying, but I'm going to answer it 2 different ways, and hopefully one of the answers is what you are looking for.

Firstly, if you mean that in your insert statement, you input "INSERT INTO table VALUE '07/15/2004'" and when you do a select, the value is "2004-07-15" then that's not a problem, as this is an "abstraction" of the actual value in the table. The "real" value is a timestamp that is just an integer, and is converted into something that's meaningful to us when pulled from the database. You can format it any way you want (see the MySQL manual for information).

But, if you mean that you want to change what the user inputs (mm/dd/yyyy) in PHP before the insert into a different date format (yyyy-mm-dd), then that's easily enough done. I have to do it every time I have a form that has a date since where I am from, the standard date format is dd/mm/yyyy and the days and months can easily be mixed up by programs that were built for an American audience.

Anyway, the PHP code to reformat the date is something like this:

$date = $_POST['date'];
if($date == "")
	$errs = $errs."<li>No date supplied"
if(ereg("([0-9]{2})/([0-9]{2})/([0-9]{4})",$date,$date_arr))
	$date = $date_arr[3]."-".$date_arr[1]."-".$date_arr[2];
else
	$errs = $errs."<li>Invalid Date Format:$date";

This code does 2 things:

1. Checks to see if a date was entered and if not, generates an error message.

2. The ereg function checks the string ($date) against a regular expression to see if the date is in the expected format (mm/dd/yyyy), and then "explodes" it to an array (the 3rd argument). The array has the original string in the [0] index, the contents of the month in the [1] index, the contents of the day in the [2] index, and the contents of the year in the [3] index. So, you can use this re-contruct the string into a yyyy-mm-dd format. If the pattern expected by the regular expression doesn't match, then it goes to the else branch and creates the appropriate error message (which are echoed further down my script if there are any errors btw).

If you check the PHP manual for the ereg function, it uses this kind of date reformatting as its example. Check it out if you want to really understand what this function does.

Good luck!

[oraclable]

This is something that you have to be careful with.
What ever the date format you have at the front-end you must make sure that you convert it to the format accepted by the Back-end.
Otherwise, you could have lot of wong data in the DB.
So, if you get this from the user "mm/dd/yyyy" make sure you convert it to "yyyy/mm/dd" before you insert it in the DB.
For instance, If you have 3 combos (c_mm,c_dd,c_yyyy --> $c_mm,$c_dd,$c_yyyy):
$v_date=$c_yyy.'-'.$c_mm.'-'.$c_dd
Now $v_date should be the date value to be inserted.