Need help

[topflight]

I have a table called "members" and in that members table it has a couple of fields. However the main two I am trying to deal with now is the last_report. I want to create a script to show all the members who hasn't filed a report within the last 30 days of the report date in the database. The fields are dates (e.g mm.dd.yyyy)

 

Please help thanks in advanced.

[947740]

That will be relatively problematic due to the way you have stored your dates in the database.  Here is my recommendation.

 

Change the mysql to where the last_report is a DATE field.  It will need to be stored as YYYY-MM-DD.

 

Once you do that, you can do something similar to this:

 

$now = date("Y-M-D");
$before = date("Y-M-D",strtotime("-30 days"));
$query = "SELECT * FROM members WHERE last_report BETWEEN $now AND $before";

[topflight]

That will be relatively problematic due to the way you have stored your dates in the database.  Here is my recommendation.

 

Change the mysql to where the last_report is a DATE field.  It will need to be stored as YYYY-MM-DD.

 

Once you do that, you can do something similar to this:

 

$now = date("Y-M-D");
$before = date("Y-M-D",strtotime("-30 days"));
$query = "SELECT * FROM members WHERE last_report BETWEEN $now AND $before";

 

How can I change it without loosing or messing up the current dates that are stored in there?

Also I  shouldn't before date be variable since I have taht date in the datbase. As everymember report date will be different depending on the date they fileed it.

[Ken2k7]

Create a new column with type timestamp and then run an update SQL.

[947740]

Ken replied, but I already typed this out

 

$query = "SELECT * FROM members";
$result = mysqli_query($cxn,$query);
$number = mysqli_num_rows($result);
     for($n = 1;$n <= $number;$n++) {
     $old_date = "SELECT last_report from members WHERE ID = {$n}";
     $result = mysqli_query($cxn,$old_date);
     $odate = mysqli_fetch_row($result);
     $odate = $odate[0];
     $ndate = date("Y-M-D",strtotime($odate));
     $query = "UPDATE members SET last_report = $ndate WHERE ID = {$n}";
     $result = mysqli_query($cxn,$query);
}

That is quite roughshod...but it *should* work.

[Ken2k7]

You don't even need to SELECT them. MySQL has some useful string functions like LEFT(), RIGHT(), LOCATE().

[947740]

Hrrrm, I'm afraid I'm not as good at mysql as I am at PHP (which I guess I could argue I'm not good at).

[topflight]

How can I change it without loosing or messing up the current dates that are stored in there?

Also I  shouldn't before date be variable since I have taht date in the datbase. As everymember report date will be different depending on the date they fileed it.

[947740]

The code I provided you would accomplish what you wish in reformatting the dates.

 

Also I  shouldn't before date be variable since I have taht date in the datbase. As everymember report date will be different depending on the date they fileed it.

 

I don't understand what you are saying.  The code I posted does not insert the same date over and over - it pulls the date from the database, reformats it, and puts it back in.

[topflight]

The code I provided you would accomplish what you wish in reformatting the dates.

 

Also I  shouldn't before date be variable since I have taht date in the datbase. As everymember report date will be different depending on the date they fileed it.

 

I don't understand what you are saying.  The code I posted does not insert the same date over and over - it pulls the date from the database, reformats it, and puts it back in.

 

I apologize for the improper grammar lol. I was saying shouldn't I get the date from the database instead of having the date as today's date. Every member will have a different report date. However what I am trying to accomplish is to see which members haven't filed a report within 30 days of their last report date.

[947740]

I understand the end goal - the script I wrote was to change the date format in the database.  In that script, it never references the current date.  It uses the date pulled from the database.

 

The first script I wrote was incorrect, as I misunderstood.  It would be

WHERE last_report > $before

[topflight]

So I am confused which code should I use the 1st one or second and what should I replace?

[Ken2k7]

So I am confused which code should I use the 1st one or second and what should I replace?

You're confused because you're depending on us to write the code perfectly and 100% working for you rather than trying to understand the logic on how you would go about doing it. Obviously if you don't understand the logic, nothing we post will make much sense as evident in this topic.

 

So allow me to break down everything in this topic for you to clear up that confusion.

 

Your problem: The dates are in the format of "mm.dd.yyyy" and you want to select all records within the last 30 days. The issue is that "mm.dd.yyyy" is not in a valid date format; so we know the type of the column field in the database is not a valid date type. Due to that, it's hard to write an efficient SQL that selects what you want.

 

Solution: The suggestion was made to convert that into a proper date type. I suggested to create a new column with the type timestamp. Then you run an UPDATE SQL that fills in the values for the column you just created. All the UPDATE SQL should do is fill in the data for the new column you created. It should not change the value of the "mm.dd.yyyy" data whatsoever.

 

Example:

 

> Create a new column of type timestamp. I'll call it new_date.

+-------+---------------+--------------+
|   id  |   your_date   |   new_date   |
+-------+---------------+--------------+
|   5   |  05.12.2009   |              |
+-------+---------------+--------------+

 

> Run an UPDATE SQL that gets the value in your_date and re-write it in the proper date format and store it into new_date. Please refer to my post on a few key and useful MySQL string functions that should help you in transforming the text.

+-------+---------------+--------------+
|   id  |   your_date   |   new_date   |
+-------+---------------+--------------+
|   5   |  05.12.2009   |  2009-05-12  |
+-------+---------------+--------------+

 

 

 

Then after you have that fixed, you can run a SELECT SQL similar to the one 947740 posted in the first reply in this topic.

[topflight]

I have tried to do it however I created another column called last_report_n with a date value. And I ranned the following  code and it makes the new table say 2010-05-03. However I have 63 rows and they all have the same value which they all should be different because the last report date is different.

 

<?php

$get = mysql_query("SELECT `last_pirep` FROM members");
while($row = mysql_fetch_assoc($get)){


$od = $row["last_pirep"];

mysql_query("UPDATE members SET last_report_n='$od'WHERE last_report_n='0000-00-00'");

}

echo'UPDATED Sucessfully!'

?>

[Ken2k7]

A valid date type is YYYY-MM-DD as you have it set. So assuming last_pirep is YYYY.MM.DD, why are you updating last_report_n to be YYYY.MM.DD? Think dude.

[topflight]

last_pirep is mm.dd.yyyy and I am trying to convert it YYYY-MM-DD. So my script can work. I am just lost. lol

[Andy-H]

<?php

$get = mysql_query("SELECT id, `last_pirep` FROM members");
while($row = mysql_fetch_assoc($get)){

$id = $row['id'];
$od = explode('.', $row['last_pirep']);
$od = $od[2] . '-' . $od[0] . '-' . $od[1];

mysql_query("UPDATE members SET last_report_n='" . $od . "' WHERE id = " . $id . " LIMIT 1");

}

echo'UPDATED Sucessfully!'

?>

[Ken2k7]

So if it's mm.dd.yyyy, chop it up into yyyy-mm-dd. What's confusing about it? =\

 

<?php

$get = mysql_query("SELECT id, `last_pirep` FROM members");
while($row = mysql_fetch_assoc($get)){

$id = $row['id'];
$od = str_replace('.', '-', strrev($row['last_pirep']));

mysql_query("UPDATE members SET last_report_n='" . $od . "' WHERE id = " . $id . " LIMIT 1");

}

echo'UPDATED Sucessfully!'

?>

That would be backwards lol, well the values that is.

[Andy-H]

So if it's mm.dd.yyyy, chop it up into yyyy-mm-dd. What's confusing about it? =\

 

<?php

$get = mysql_query("SELECT id, `last_pirep` FROM members");
while($row = mysql_fetch_assoc($get)){

$id = $row['id'];
$od = str_replace('.', '-', strrev($row['last_pirep']));

mysql_query("UPDATE members SET last_report_n='" . $od . "' WHERE id = " . $id . " LIMIT 1");

}

echo'UPDATED Sucessfully!'

?>

That would be backwards lol, well the values that is.

 

 

I noticed and edited it before you posted. Misread his format.

[topflight]

The code above makes the coloum say 2005-01-20

[Andy-H]

<?php

$get = mysql_query("SELECT id, `last_pirep` FROM members");
while($row = mysql_fetch_assoc($get)){

$id = $row['id'];
$od = explode('.', $row['last_pirep']);
$od = $od[2] . '-' . $od[0] . '-' . $od[1];

mysql_query("UPDATE members SET last_report_n='" . $od . "' WHERE id = " . $id . " LIMIT 1");

}

echo'UPDATED Sucessfully!'

?>

 

 

Use the edited one...

[Ken2k7]

I wonder if this will work:

 

UPDATE members SET last_report_n = CONCAT_WS('-', RIGHT(last_pirep, 4), LEFT(last_pirep, 2), RIGHT(LEFT(last_pirep, LOCATE('.', last_pirep, 3), 2));

[Andy-H]

I wonder if this will work:

 

UPDATE members SET last_report_n = CONCAT_WS('-', RIGHT(last_pirep, 4), LEFT(last_pirep, 2), RIGHT(LEFT(last_pirep, LOCATE('.', last_pirep, 3), 2));

 

 

Elegant 

[topflight]

Still no luck

[PFMaBiSmAd]

You can (simply) use the mysql STR_TO_DATE() function in the UPDATE query to convert any textual format date into a mysql DATE value - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date