PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
Moderator: General Moderators
icesolid
Forum Regular
Posts: 502 Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY
Post
by icesolid » Thu Jan 26, 2006 4:11 pm
I have a date stored in a database in this format ("m-d-Y"). I want to print out the date in a table but only showing the month and day ex: ("m-d"). The code bellow is my attempt so far, not working obviously, the date does not print out:
Code: Select all
<table align="right" width="520" border="0" cellpadding="0" cellspacing="1">
<tr>
<td align="center" height="30" class="topBar"><b>CONTROL #</b></td>
<td align="center" height="30" class="topBar"><b>DATE ORD</b></td>
<td align="center" height="30" class="topBar"><b>CUST</b></td>
<td align="center" height="30" class="topBar"><b>INSURED</b></td>
<td align="center" height="30" class="topBar"><b>LOCATION</b></td>
<td align="center" height="30" class="topBar"><b>POLICY #</b></td>
<td align="center" height="30" class="topBar"><b>RT</b></td>
<td align="center" height="30" class="topBar"><b>ASSGN</b></td>
</tr>
<?php
$result = mysql_query("SELECT control_number,date_ordered,user_code,name_of_insured,location_city,policy_number,survey_type,DATE_FORMAT(date_assigned, '%m-%d') AS date_assigned FROM cases WHERE inspector_code='" . $_POST["inspector_code"] . "' AND sent_to_reviewer='false' ORDER BY control_number ASC");
while($row = mysql_fetch_array($result)) {
if($row["rush"] == true) {
$class = "redBar";
} else {
$class = "greenBar";
}
?>
<tr>
<td align="center" height="30" class="<?php echo $class; ?>"><b><?php echo $row["control_number"]; ?></b></td>
<td align="center" class="<?php echo $class; ?>"><?php echo $row["date_ordered"]; ?></td>
<td align="center" class="<?php echo $class; ?>"><?php echo $row["user_code"]; ?></td>
<td align="center" class="<?php echo $class; ?>"><?php echo substr($row["name_of_insured"], "0", "15"); ?></td>
<td align="center" class="<?php echo $class; ?>"><?php echo substr($row["location_city"], "0", "10"); ?></td>
<td align="center" class="<?php echo $class; ?>"><?php echo $row["policy_number"]; ?></td>
<td align="center" class="<?php echo $class; ?>"><?php echo substr($row["survey_type"], "0", "2"); ?></td>
<td align="center" class="<?php echo $class; ?>">
<?php echo $date_assigned; ?>
</td>
</tr>
<?php
}
?>
<tr>
<td colspan="8"><br> </td>
</tr>
</table>
raghavan20
DevNet Resident
Posts: 1451 Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:
Post
by raghavan20 » Thu Jan 26, 2006 4:17 pm
Code: Select all
mysql> show create table test3;
+-------+---------------------------------------------------------------------------------------------------------------
----------------------------------+
| Table | Create Table
|
+-------+---------------------------------------------------------------------------------------------------------------
----------------------------------+
| test3 | CREATE TABLE `test3` (
`date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------
----------------------------------+
1 row in set (0.03 sec)
mysql> insert into test3 values(NOW()), (NOW()), (NOW());
Query OK, 3 rows affected (0.13 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test3;
+---------------------+
| date |
+---------------------+
| 2006-01-26 22:16:49 |
| 2006-01-26 22:16:49 |
| 2006-01-26 22:16:49 |
+---------------------+
3 rows in set (0.03 sec)
mysql> select month(date), year(date) from test3;
+-------------+------------+
| month(date) | year(date) |
+-------------+------------+
| 1 | 2006 |
| 1 | 2006 |
| 1 | 2006 |
+-------------+------------+
3 rows in set (0.01 sec)
icesolid
Forum Regular
Posts: 502 Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY
Post
by icesolid » Thu Jan 26, 2006 4:18 pm
Whats that supposed to mean?
I'm not talking about sql command prompt. I'm talking about printing it out onto the web in a new format other than the way it was stored!
duk
Forum Contributor
Posts: 199 Joined: Wed May 19, 2004 8:45 am
Location: London
Post
by duk » Thu Jan 26, 2006 4:23 pm
raghavan20
DevNet Resident
Posts: 1451 Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:
Post
by raghavan20 » Thu Jan 26, 2006 4:28 pm
Code: Select all
Altered Query:
"SELECT control_number,date_ordered,user_code,name_of_insured,location_city,policy_number,survey_type, day(`date_assigned`) as `day`, month(`date_assigned`) as `month` FROM cases WHERE inspector_code='" . $_POST["inspector_code"] . "' AND sent_to_reviewer='false' ORDER BY control_number ASC"
use $row["day"], $row["month"]
icesolid
Forum Regular
Posts: 502 Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY
Post
by icesolid » Thu Jan 26, 2006 4:42 pm
Am I speaking English?
I want to reformat the result $row["date_assigned"];
I swear people do not read on this forum.
Oh and by the way that "Altered SQL" causes an error, it's no good. Help Please!!!
raghavan20
DevNet Resident
Posts: 1451 Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:
Post
by raghavan20 » Thu Jan 26, 2006 4:56 pm
sorry, I thought you had timestamp field...if you are storing date as a string in m-d-y format...here is the code to extract day and month from it....
Code: Select all
<?php
$sampleDate = "12-04-2005";
$sampleDate1 = "3-05-2006";
$tempArray = explode("-", $sampleDate);
$tempArray1 = explode("-", $sampleDate1);
echo "<br />Day: {$tempArray[0]}";
echo "<br />Month: {$tempArray[1]}";
echo "<br /><br />Day: {$tempArray1[0]}";
echo "<br />Month: {$tempArray1[1]}";
?>
pickle
Briney Mod
Posts: 6445 Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:
Post
by pickle » Thu Jan 26, 2006 5:00 pm
We're all just trying to help - no need to get offensive. You get more bees with honey...
~raghavan20's solution was to modify your query to produce the desired results rather than use PHP to hack it apart. Slightly modifying his final query on his first entry, you should get what you want:
Code: Select all
mysql> select CONCAT(month(date),'-',year(date)) as 'date_assigned' from test3;
+---------------+
| date_assigned |
+---------------+
| 1-2006 |
| 1-2006 |
| 1-2006 |
+---------------+
If, for some reason, you are unable to do that, you can of course pull it apart with PHP:
Code: Select all
$date_returned = "1-26-2006";
list($month,$day,$year) = explode('-',$date_returned);
$formatted_date = $month.'-'.$day;
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
icesolid
Forum Regular
Posts: 502 Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY
Post
by icesolid » Thu Jan 26, 2006 5:13 pm
The code above still causes an error.
I want to use MySQL to format it, I know there is a way you just do not know how. I am looking for someone who does. I will just wait until tomorrow.
Using something like the code below I know there is a way:
Code: Select all
$result = mysql_query("SELECT control_number,date_ordered,user_code,name_of_insured,location_city,policy_number,survey_type,DATE_FORMAT(date_assigned, '%m-%d') AS date_assigned FROM cases WHERE inspector_code='" . $_POST["inspector_code"] . "' AND sent_to_reviewer='false' ORDER BY control_number ASC");
wtf
Forum Contributor
Posts: 331 Joined: Thu Nov 03, 2005 5:27 pm
Post
by wtf » Thu Jan 26, 2006 5:18 pm
actually I think duk is correct
in your code you're outputing $date_assigned where it should be $row['date_assigned']
icesolid
Forum Regular
Posts: 502 Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY
Post
by icesolid » Thu Jan 26, 2006 6:30 pm
wtf wrote: actually I think duk is correct
in your code you're outputing $date_assigned where it should be $row['date_assigned']
WHAT BRO?
Uhh...can anyone intelligent help me here?
I WANT TO
REFORMAT THE ROW $row["date_assigned"]; FROM ITS
CURRENT FORMAT TO A
NEW FORMAT
AND THEN PRINT IT OUT. SO NO I DO NOT WANT TO PRINT OUT $row["date_assigned"].
Am I the retarded one?
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Thu Jan 26, 2006 6:40 pm
that didn't require or call for caps...
Help me understand something.. Why do you store the date in a nonstandard way?
Anyways, the
explode() routes given by raghavan20 and pickle allow for you to do the format you wish.
John Cartwright
Site Admin
Posts: 11470 Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:
Post
by John Cartwright » Thu Jan 26, 2006 6:58 pm
icesolid check your PM.
THIS IS YOUR FINAL WARNING.
raghavan20
DevNet Resident
Posts: 1451 Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:
Post
by raghavan20 » Fri Jan 27, 2006 6:48 am
Code: Select all
mysql> select substring('05-23-1999', 1,2) as `month`, substring('05-23-1999',4,2) as `day`;
+-------+-----+
| month | day |
+-------+-----+
| 05 | 23 |
+-------+-----+
1 row in set (0.01 sec)
mysql> select concat(substring('05-23-1999', 1,2), '-', substring('05-23-1999',4,2));
+------------------------------------------------------------------------+
| concat(substring('05-23-1999', 1,2), '-', substring('05-23-1999',4,2)) |
+------------------------------------------------------------------------+
| 05-23 |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)
wtf
Forum Contributor
Posts: 331 Joined: Thu Nov 03, 2005 5:27 pm
Post
by wtf » Fri Jan 27, 2006 9:55 am
I have a date stored in a database in this format ("m-d-Y"). I want to print out the date in a table but only showing the month and day ex: ("m-d").
Code: Select all
...DATE_FORMAT(date_assigned, '%m-%d') AS date_assigned...
will format date as you want it. It's already a part of your sql statement, therefore $row['date_assigned'] will come out in the format you want.