return values not working (converting a date)

[jeff5656]

I'm trying to convert dates in my varchar field to a more sql-friendly format, as suggested in a previous post (subject "date field")

 

This is the script:

<?php include "connectdb.php"; 


$query = mysql_query("SELECT * FROM ph_consults")or die(mysql_error());
while($info = mysql_fetch_array( $query ))
{
$id_incr[] = $info['id_incr'];
$rcf_date[] = $info['rcf_date'];
echo "rcf_date: ";
echo $rcf_date;
echo $id_incr;
echo "<br>";

}
for ($x = 0; $x < sizeof($rcf_date); $x++) {
$newDate = split("/", $rcf_date[$x]);
$day = $newDate[0];
$month = $newDate[1];
$year = $newDate[2];
if ($year > 50) {
$year = "19" . $year;
} else {
$year = "20" . $year;
}
$rcf_date[$x] = $year . "-" . $month . "-" . $day;
}


?>

But the output is:

rcf_date: ArrayArray

rcf_date: ArrayArray

rcf_date: ArrayArray

rcf_date: ArrayArray

rcf_date: ArrayArray

rcf_date: ArrayArray

rcf_date: ArrayArray

rcf_date: ArrayArray

[kenrbnsn]

You can't echo an array directly, you need to use one of print_r, var_dump, or var_export:

<?php
echo '<pre>rcf_date:' . print_r($rcf_date,true) . '</pre>';
echo '<pre>id_incr:' . print_r($id_incr,true) . '</pre>';
?>

BTW, these echo's probably should be outside of the while loop.

 

As for the rest of your code, you can shorten it a little:

<?php
for ($x = 0; $x < sizeof($rcf_date); $x++) {
     $list($day,$month,$year) = split("/", $rcf_date[$x]);
     $year = ($year > 50)?'19' . $year: '20' . $year;
     $rcf_date[$x] = $year . "-" . $month . "-" . $day;
}?>

 

Ken

 

[jeff5656]

I just realized that after I getr this to work, I need to write  a line to put the converted fields back into the database, like

UPDATE ph_consults SET.

 

And I have to do this before the next array so that rcf_date doesn't get replaced by the next record right?

So I would put an update ph_consult in the middle of that loop?

[blackcell]

Also, depending on if you have any date format checks like forcing yyyy-mm-dd when user enters mm-dd-yy, I have used strtotime to get a unix timestamp and use that timestamp to output the date a standard way.

[kenrbnsn]

You can do it all in one loop. Use a function to return the new date:

<?php
function convert_date($dt) {
     $list($d,$m,$y) = split("/", $dt);
     $year = ($y > 50)?'19' . $y: '20' . $y;
     return($y . "-" . $m . "-" . $d);
}

$query = mysql_query("SELECT * FROM ph_consults")or die(mysql_error());
while($info = mysql_fetch_array( $query ))
{
   $upq = "update ph_consults set rcf_date = '" . convert_date($info['rcf_date') . "' where id_incr = '" . $info['id_incr'] . "'";
   $rs = mysql_query($upq) or die("Problem with the update query: $upq<br>" . mysql_error());
}
?>

 

Ken

[PFMaBiSmAd]

You can do this all in an UPDATE query using the mysql STR_TO_DATE() to set your new DATE or DATETIME column to the equivalent of what is in your existing column.

 

UPDATE your_table SET new_datetime_column = STR_TO_DATE(existing_column, 'format string that matches existing column')

 

See the mysql manual (date and time functions section) for what you should put in - 'format string that matches existing column'

[jeff5656]

You can do it all in one loop. Use a function to return the new date:

When I ran that I get this:

 

Fatal error: Can't use function return value in write context in C:\wamp\www\consults\convertdate-2.php on line 6

 

Here is code you gave me:

<?php include "connectdb.php"; 



function convert_date($dt) {
     $list($d,$m,$y) = split("/", $dt);
     $year = ($y > 50)?'19' . $y: '20' . $y;
     return($y . "-" . $m . "-" . $d);
}

$query = mysql_query("SELECT * FROM ph_consults")or die(mysql_error());
while($info = mysql_fetch_array( $query ))
{
   $upq = "update ph_consults set rcf_date = '" . convert_date($info['rcf_date') . "' where id_incr = '" . $info['id_incr'] . "'";
   $rs = mysql_query($upq) or die("Problem with the update query: $upq<br>" . mysql_error());
}
?>

[PFMaBiSmAd]

No need for loops, two queries, functions, php code and php code errors. See my post above. It only takes one query to update all the rows in the database.

[kenrbnsn]

My mistake, this:

<?php
function convert_date($dt) {
     $list($d,$m,$y) = split("/", $dt);
     $year = ($y > 50)?'19' . $y: '20' . $y;
     return($y . "-" . $m . "-" . $d);
}?>

should be

<?php
function convert_date($dt) {
     list($d,$m,$y) = split("/", $dt);
     $y = ($y > 50)?'19' . $y: '20' . $y;
     return($y . "-" . $m . "-" . $d);
}
?>

 

Ken

 

 

[jeff5656]

UPDATE your_table SET new_datetime_column = STR_TO_DATE(existing_column, 'format string that matches existing column')

 

Now I'm not lazy and I WANT to learn php and eventually be an independent programmer, but there is no way I will ever figure out how to do the above and get it to work.

Aside from actually how to code it, I am also not sure about some of the general concepts:

1. If I have 900 entries, how does one line solve all that?  I thought you need an array to do that?

2. When you say existing_column, is that something different than the name of the field (in my example do I put rcf_date there?

 

If someone could tell me how to write the code to convert values in all 900 records to the correct date format that would be great and then I will learn how to do it in the future.

 

Also, what happens when the code comes across a record like "9999" or "weekend" (since it's varchar it takes anything)?

[jeff5656]

Ok thanks Ken I'll try that.

See above - apparently this can all be done on one line, but if I am to go with your code, how do I UPDATE the database once the date has been converted.

What (and where) code do I use to ignore or at least convert to 0000-00-00 things likje "999" or "weeked"?

[jeff5656]

I put in that code and still got a fatal error

<?php include "connectdb.php"; 


function convert_date($dt) {
     list($d,$m,$y) = split("/", $dt);
     $y = ($y > 50)?'19' . $y: '20' . $y;
     return($y . "-" . $m . "-" . $d);
}

$query = mysql_query("SELECT * FROM ph_consults")or die(mysql_error());
while($info = mysql_fetch_array( $query ))
{
   $upq = "update ph_consults set rcf_date = '" . convert_date($info['rcf_date') . "' where id_incr = '" . $info['id_incr'] . "'";
   $rs = mysql_query($upq) or die("Problem with the update query: $upq<br>" . mysql_error());
}
?>

[kenrbnsn]

Missing "]" in this line

<?php
$upq = "update ph_consults set rcf_date = '" . convert_date($info['rcf_date') . "' where id_incr = '" . $info['id_incr'] . "'";
?>

should be

<?php
$upq = "update ph_consults set rcf_date = '" . convert_date($info['rcf_date']) . "' where id_incr = '" . $info['id_incr'] . "'";
?>

 

As you can see, I don't alway proof my code before I post it, so "buyer beware".

 

Ken

[jeff5656]

Thanks that works!  Well, almost.  Now I know this may be a stupid question but why do I get this:

 

192008-17-04

 

when the date was 04/17/2008.

 

I see in your code where that comes into play, but not sure how to safely get rid of that.

[kenrbnsn]

You're checking whether the year is greater than 50 and 2008 is. You need an additional check to see if the year is less than 100.

 

<?php
$y = ($y > 50 && $y < 100)?'19' . $y: '20' . $y;
?>

 

Ken

[jeff5656]

Ok it pretty much works but now when I run the code and it converts it (i.e the new disply shows 2008-17-04 instead of 4/17/08), then I change the table from varchar to date and the display now reads 000-00-00!

 

here is the code again:

<?php include "connectdb.php"; 


function convert_date($dt) {
     list($d,$m,$y) = split("/", $dt);
     $y = ($y > 50 && $y < 100)?'19' . $y: '20' . $y;
     return($y . "-" . $m . "-" . $d);
}

$query = mysql_query("SELECT * FROM ph_consults")or die(mysql_error());
while($info = mysql_fetch_array( $query ))
{
$upq = "update ph_consults set rcf_date = '" . convert_date($info['rcf_date']) . "' where id_incr = '" . $info['id_incr'] . "'";
   $rs = mysql_query($upq) or die("Problem with the update query: $upq<br>" . mysql_error());
}
?>

[PFMaBiSmAd]

I see you started a new thread for this problem, but the reason it is not working is you have the month and day fields reversed.

 

The format is yyyy-mm-dd. You have yyyy-dd-mm