[Resolved] Problems with transfering and dates

[rgermain]

Here is the deal. I have a database containing info with a date field. When originally created the date field was set to varchar. Now, later down the road it is causing problems with trying to make a search for the info. So I am trying to convert it into the field type date. I am transfering a couple of files to a test table so I can do some testing with administration part before I make the big leap, but in the transfer it it converts the date over fine, but they all end up being the same which is a big problem.

Here is the part that works:

$q1 = "SELECT * FROM story_tbl WHERE id <= 100";
$r1 = mysql_query($q1);

while($row = mysql_fetch_array($r1))
{
$story = $row[story];
$oldDate = $row[cdate];
$head = $row[head];
$q2 = "insert into test_tbl (story,cdate,head) values ('$story','$oldDate','$head')";
$r2 = mysql_query($q2);
echo "Story has been transfered!!<BR>";

Now here is the part where I switch the date to look right so when the field gets converted to the proper type it will come out right(yyyy-mm-dd):

$q2 = "SELECT * FROM test_tbl";
$r2 = mysql_query($q2);
while($row = mysql_fetch_array($r2))
{
$oldDate = $row[cdate];
$newMonth = substr($oldDate,0,2);
$newDay = substr($oldDate,3,2);
$newYear = substr($oldDate,6);
$newDate = $newYear .  $newMonth  . $newDay;
$q2 = "update test_tbl set cdate = $newDate";
mysql_query($q2);
echo "Story with the id of " . $row[id] . " has been fixed.<BR>";

If you see anything that would cause all the dates to end up being the same please let me know.

Dates come over to test_tbl like 03/02/2003, 02/02/2003, 09/12/2003

What the they should look like after conversion 20030302, 20030202, 20030912

What the dates end up looking like 20030302, 20030302, 20030302.

Sorry if really confusing! Thanks for any help giving!

[rgermain]

Thanks to anyone who attemped to figure this out, but I got it to work. In case anyone cares here is what I did.

If you look in the first post you will see a line that looks like this:

$q2 = "update test_tbl set cdate = $newDate";

That is where the underlying problem was, I had to add one extra line and change that line to the following.

$id = $row[id];
$q2 = "update test_tbl set cdate = $newDate where id = $id";

[stolzyboy]

<ot>is this the r. germain that i am thinking of</ot>

[rgermain]

Sure is stolzyboy!!

Hey, I was just wondering, I am new to this forum so when I clicked on resolved when I replyed to my own answer how do I know that it was set to resolve. I guess I mean what should I be looking for to know if a post was resolved.

[stolzyboy]

if you click the resolved (which doesn't work after you post), the subject will turn into something like this:

[Resolved] Problems with transfering and dates

and only the thread starter or a moderator can mark it resolved

to get around the above problem, just go out and navigate to your thread and when you hover over the mark thread resolved that it says in the addy bar threadid=6548961(somenumber)&is_resolved=1

if it works it will go to the post and change the subject line, i will mark this one so you kow what it looks like