how to eliminate a certain value.

[tonynoriega]

this querys a field in my table that is the date a client has returned as a repeat visit....in YYYY-MM-DD format.

this query is returning the correct results, but by default if a user does not enter a value, it defaults to 0000-00-00....

which in this case, i do not want....how can i say "AND WHERE last_visit IS NOT "0000-00-00"

(in case anyone is wondering, i tried that above and it does not work)

$sql = 'SELECT COUNT(last_visit) AS last_visit, (SELECT COUNT(*) FROM registration_table WHERE DATE_SUB(CURDATE(),INTERVAL 6 DAY) <= entry_time) AS totalcount FROM registration_table WHERE DATE_SUB(CURDATE(),INTERVAL 6 DAY) <= entry_time';

[LifeinAsia]

Probably better to make the field nullable and ignore NULL values.

[tonynoriega]

i set the field to "NULL" in my php admin...

Now, if a user does not enter a value the next time, will it remain blank and "NULL" or will i have to modify each record to reflect NULL for no value...

[LifeinAsia]

Going forward, the will have NULL values if the user doesn't enter anything. But you will have to manually NULL any existing values you want to get rid of.

[tonynoriega]

well, here is what happened...

i set the column to allow NULL in phpmyadmin.

then set the 5 records i was trying to query to a value of "NULL"

it worked fine, when i queried them..they did not show in the resultset of the query...great.

Then, i went into the registration modification form, set the field to have an actual date within the query, and it showed up just fine...

Went back, modified the form, deleted the value, resubmitted the form and the table was repopulated with 0000-00-00....it did not fill the field with NULL...

how can i fix that?

[LifeinAsia]

Um, that was expected.

If a field is not nullable, then the DB has to fill it with a value (or throw an error). When you set the filed to not nullable, the DB put data in all the fields that were previously NULL. Now you change it back to nullable, but the DB won't go back and remove data- you have to do that manually.

[Oops- sorry. Misunderstood your post the first time.]
Please post your code where you enter the data from the form. It sounds like you're forcing a value insert even when none was entered.

[edited by: LifeinAsia at 7:23 pm (utc) on Aug. 21, 2007]

[tonynoriega]

this is the first blank form that a user enters the lead to:

<tr>
<td>Return Visit Date: </td>
<td><input name="last_visit" type="text" class="text" id="last_visit" size="20" /> (ex. YYYY-MM-DD)
</td>
</tr>

then when you want to modify the record, which you query base on the record ID number:

if ($rec_id = $_POST['rec_id']){
$display .= 'You have selected Record:' . $rec_id . '<br /><br />';
$result=mysql_query("SELECT * FROM registration_table WHERE rec_id=$rec_id");
$num=mysql_num_rows($result);
$i=0;
while ($i < $num) {
$nhm_associate=mysql_result($result,$i,"nhm_associate");
$lead_date=mysql_result($result,$i,"lead_date");
$last_visit=mysql_result($result,$i,"last_visit"); //...etc the rest of the fields...

Then the field is populated with the table value:

<tr>
<td>Return Visit Date: </td>
<td><input name="last_visit" type="text" class="text" id="last_visit" size="20" value="' . $last_visit . '"> (ex. YYYY-MM-DD)
</td>
</tr>

Then the modif.php form, where it gets resubmitted to the dbase..

if ($security_admin === true ¦¦ $security_agent === true) {
$display = '';
$rec_id=$_POST['rec_id'];
if ($rec_id == "") {
$display .= 'No record ID identifier';
}
else{

$nhm_associate = $_POST['nhm_associate'];
$lead_date = $_POST['lead_date'];
$last_visit = $_POST['last_visit']; //..etc the rest of the fields...

then the UPDATE statement:

$sql=("UPDATE registration_table SET nhm_associate='$nhm_associate',lead_date='$lead_date',last_visit='$last_visit',//the rest of the fields...

[LifeinAsia]

I don't know PHP, but it looks like you're posting a value, even if it's blank. And as strange as it may seem, a blank value is not always a NULL. :)

A better way is to check to see if a date was entered. If not, don't enter it.

An even better way is to make sure it's a valid date before entering the value.