Empty cell in MySQL

[outdoorxtreme1]

How do I output the HTML code   if a cell in MySQL has no data in it?

[outdoorxtreme1]

yes, in MySQl

[jatar_k]

well that is a bit over zealous ;)

TEXT[ (M) ]
A TEXT column with a maximum length of 65,535 (216 – 1) characters.

a bit large really but I guess varchar is the same after 5.0.3

what size are they? do you know?

I am just trying to see if it is even worth changing. Removing the three date fileds and making it one single one would make things much easier and store a timestamp in the db, not a mysql timestamp column.

this would cause a few things to be changed, actually a fair number but would be a much better way to go.

[directrix]

If TSpickMonth is a character field, then April will be the first month and September the last!

One option to sort on a numeric month is to create a table with two columns:

create table MonthLink (
MonthName char(15) not null primary key,
MonthNo int unsigned not null
);

Populate this table with all month pairs:
('January', 1), ('February', 2), ... and so on.

If not all your months are in this standard format (eg. some may be all lowercase?), run a separate query first to identify all the distinct month values:

select distinct TSpickMonth from TripLog;

Then insert ('Jan.', 1), ('january', 1), etc. as required.

Having done that, you can rerun the original query:

select TripLog.* from TripLog,MonthLink
where TSpickMonth = MonthName
order by SpickYear, MonthNo, SpickDay;

Be careful that you've added all distinct values of TSpickMonth to MonthLink, or the join will not include all records from the primary file.

That's one option, assuming your month field is character.

[edited by: directrix at 9:04 pm (utc) on Nov. 8, 2005]

[outdoorxtreme1]

The length is not defined.

Also. They are numeric.

[outdoorxtreme1]

Would the date stamp still work if I have trip start on one date and trip end on another date?

[jatar_k]

one for each

you would store a string of numbers for each date

you could create these using the mktime function we used before. You would then be able to skip a step when you get them from the database since you are using mktime to convert the dates for output to the browser. That step would no longer be required.

selection and sorting based on those dates would also be much easier ;)

[outdoorxtreme1]

I would use mktime on my form page? Would the date still be selectable as far as Month, Day and Year?

[NomikOS]

outdoorxtreme1 I think that it is still time for switch to unix timestamp.

This can give you a start [webmasterworld.com...] (attention on msg #4)

If you do it, you can have a selector like:

[month] [day] [year] with <select> HTML tags in your page very easily, besides of the order facilities.

what do you think?

---

(It was a mystery for me the lenght of the super thread "Trip Report PHP". Not any more)

[outdoorxtreme1]

I guess it would be worth a try. I am not sure on the commands I need to use it the way I want to.
Currently on my form I have Trip Start date. The month, day and year are drop down menus where you can select the date needed. I have Trip Finish that is the same way.

[outdoorxtreme1]

It is sorting by year using the following line of code but the months are out of order.

$sql = 'SELECT * FROM trip_log ORDER BY TSpickYear, TSpickMonth, TSpickDay asc';
$result = mysql_query($sql) or die ('<p>select died: ' . mysql_error());

[jatar_k]

in your form you would take the year month and day and then use them with the mktime function and store the result in the db column.

just like you did when you pulled them out inside the date function. Then when you pull them out you could skip that step and just use the result with the date function.

[outdoorxtreme1]

I don't know were to start on this. I am not sure what to get rid of and where to put the mktime in this. This is what I currently have.

<select name="TSpickMonth" size="1">
<? for ($i=1;$i<=12;$i++) {?><? echo "<option value=\"$i\""; if ($i == $month) echo "selected"; echo ">" . $mtharr[$i-1] . "</option>"; } ?>
</select>

<select name="TSpickDay" size="1">
<? for ($i=1;$i<=31;$i++) {?><? echo "<option value=\"$i\""; if ($i == $day) echo " selected"; echo ">$i</option>"; } ?>
</select>

<select name="TSpickYear" size="1">
<? for ($i=2002;$i<=2010;$i++) {?><? echo "<option value=\"$i\""; if ($i == $year) echo " selected"; echo ">$i</option>"; } ?>
</select>

[jatar_k]

you take those values once they are submitted and create a timestamp using mktime, you then store that timestamp in your database.

just like when you select it you take the values you got from the db and throw them through mktime and then through date() so you can display the date properly.

[outdoorxtreme1]

Would I use the mktime in submit.php? Also, will I still have 3 columns for the date on MySQL or will I only need one column for the date stamp?

[jatar_k]

yes I believe it is submit.php it should be in, the one that checks the input and then inserts into mysql

you will have only one column then, well, one for start and one for end instead of six total

[outdoorxtreme1]

startdate = date("F d, Y", mktime(0,0,0,$TSpickMonth,$TSpickDay,$TSpickYear));
enddate = date("F d, Y", mktime(0,0,0,$TFpickMonth,$TFpickDay,$TFpickYear));

I have this so far. Do I need some kind of other commands to do this?

[outdoorxtreme1]

Should these lines be reversed?

date("F d, Y", mktime(0,0,0,$TSpickMonth,$TSpickDay,$TSpickYear)) = startdate;
date("F d, Y", mktime(0,0,0,$TFpickMonth,$TFpickDay,$TFpickYear)) = enddate;

[jatar_k]

all you'll need for this part is just the mktime

$startdate = mktime(0,0,0,$TSpickMonth,$TSpickDay,$TSpickYear));
$enddate = mktime(0,0,0,$TFpickMonth,$TFpickDay,$TFpickYear);

and yes, they were backwards

[outdoorxtreme1]

Should I make the fields in MySQL a timestamp type?

[jatar_k]

no, you could make them int or varchar, making sure either is big enough to hold your timestamps

[outdoorxtreme1]

Ok I have it set to int(11) When I submit and I look at the data in the startdate and enddate fields on MySQL they are 0.

[jatar_k]

is your insert actually working

maybe try a varchar(50) col instead

[outdoorxtreme1]

Look like it is working. Does the number reflect an actual date or is it a code?

[outdoorxtreme1]

Do I use date( ) to show the values on my report? How do I just display a certain row?

[jatar_k]

the number reflects an actual date

yes, you use the date function like you did before but you can just feed it the value out of mysql, you don't need the mktime function in it lie you had previously.

[outdoorxtreme1]

The date shows up good on my report list but when I view an actual report, the start date is right and the end date is always: December, 31 1969

Here is the code I have for the trip report.

while ($row = mysql_fetch_array($result)) {
$startdate = date("F d, Y",$row['startdate']);
$enddate = date("F d, Y",$row['enddate']);
echo '<strong>',$row['triplocation'],', ',$row['state'],'</strong>';
echo '<br>';
echo $startdate;
if ($enddate!= $startdate) echo ' to ',$enddate;
echo '<br>';

[outdoorxtreme1]

I got it working. Thanks for all the help.

[outdoorxtreme1]

If I want to make the date like this:

November 11 - 13, 2005

Instead of November 11, 2005 to November 13, 2005

Do I have to make seperate lines like the following?

$startmonth = date("F",$row['startdate']);
$startday = date("d",$row['startdate']);

etc...

[jatar_k]

I wouldnt do that personally, it is a lot of coding for somthing that is fairly trivial

[outdoorxtreme1]

Is there an easier way to do this?