how to pull a date using php

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

Post Reply
mally
Forum Newbie
Posts: 6
Joined: Tue Oct 18, 2005 3:46 am

how to pull a date using php

Post by mally »

I have seen such date formatting what I'd like to know is how to display it using PHP in simple terms! As I'm neither a php guru nor a MySQL expert, I have a date field in the MySQL database which holds the date as 2005-01-19, 2005-01-20 and so on.

What I would like to do is pull that information on to the page using PHP but on the first return Line just pull January then on the 2nd, 3rd, 4th lines and so on display just 19 - 2005, 20 -2005
So it displays like this>
Date Hour User
<b>January</b>
19 - 2005 12 username
20 - 2005 12 username
21 - 2005 12 username
<b>February</b>
19 - 2005 12 username
20 - 2005 12 username
21 - 2005 12 username
And not like this>
January 19 2005 12 username
January 20 2005 12 username
January 21 2005 12 username
February 19 2005 12 username
February 20 2005 12 username
February 21 2005 12 username
The date from a single Date field the hour and username I have no problem with Just like to display the date this way so it splits the months up then pulls the days for that month in under the Month

If any one has done such a thing using PHP I'd appreciate it if the could show me how to do this with a bit of sample code or if there are any tutorials on how to achieve this on the net could you please post links
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

mally
Forum Newbie
Posts: 6
Joined: Tue Oct 18, 2005 3:46 am

Post by mally »

feyd wrote:DATE_FORMAT()
I can look at this till i'm blue in the face? still make no sense read my signature
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Date and Time Functions wrote:DATE_FORMAT(date,format)

Formats the date value according to the format string.

The following specifiers may be used in the format string:

Code: Select all

Specifier 	Description
%a 	Abbreviated weekday name (Sun..Sat)
%b 	Abbreviated month name (Jan..Dec)
%c 	Month, numeric (0..12)
%D 	Day of the month with English suffix (0th, 1st, 2nd, 3rd, ...)
%d 	Day of the month, numeric (00..31)
%e 	Day of the month, numeric (0..31)
%f 	Microseconds (000000..999999)
%H 	Hour (00..23)
%h 	Hour (01..12)
%I 	Hour (01..12)
%i 	Minutes, numeric (00..59)
%j 	Day of year (001..366)
%k 	Hour (0..23)
%l 	Hour (1..12)
%M 	Month name (January..December)
%m 	Month, numeric (00..12)
%p 	AM or PM
%r 	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S 	Seconds (00..59)
%s 	Seconds (00..59)
%T 	Time, 24-hour (hh:mm:ss)
%U 	Week (00..53), where Sunday is the first day of the week
%u 	Week (00..53), where Monday is the first day of the week
%V 	Week (01..53), where Sunday is the first day of the week; used with %X
%v 	Week (01..53), where Monday is the first day of the week; used with %x
%W 	Weekday name (Sunday..Saturday)
%w 	Day of the week (0=Sunday..6=Saturday)
%X 	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x 	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y 	Year, numeric, four digits
%y 	Year, numeric (two digits)
%% 	A literal ‘%’ character
All other characters are copied to the result without interpretation.

Note that the ‘%’ character is required before format specifier characters.

Ranges for the month and day specifiers begin with zero due to the fact that MySQL allows the storing of incomplete dates such as '2004-00-00'.

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
-> 'Saturday October 1997'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
'%D %y %a %d %m %b %j');
-> '4th 97 Sat 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
'%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52'
mally
Forum Newbie
Posts: 6
Joined: Tue Oct 18, 2005 3:46 am

think you miss understand me

Post by mally »

I know that %m = 01 and %M = January that is not the question i asked?


http://www.smartdesignz.co.uk/results.html the one in brown is done using ASP MSSQL I'm rewritting it using php & MySQL I can pull the data displaying it

record one i can display it anyway i like 2005- 21-04 or 21 - April - 2005 so on

what i'd like to do is display it like the one in brown

any usful sugestions?
Charles256
DevNet Resident
Posts: 1375
Joined: Fri Sep 16, 2005 9:06 pm

Post by Charles256 »

um..pull the date from the database and then use date_format to modify it to how you want it to read...you don't have to manually type in that date,it can be a variable;) maybe i just don't understand your question..
mally
Forum Newbie
Posts: 6
Joined: Tue Oct 18, 2005 3:46 am

Post by mally »

Charles256 wrote:um..pull the date from the database and then use date_format to modify it to how you want it to read...you don't have to manually type in that date,it can be a variable;) maybe i just don't understand your question..
basically I have in the mysql database a date field which when it is sat in mysql the format is 2005-01-01
I myself with very litle knowlege of php & mysql want to display the data on the webpage like the first image on
http://www.smartdesignz.co.uk/results.html but at present can only get it to display like the second image

Is this plain enough>>>

My results show full date on every line with no breaks within the months the old one which uses asp produced a line which displayed the month then the date thus seperating the Months look at the two images and notice the difference with what happens with the date!!!!


If you accually have an answer or usful suggestions the post away if not save it foryour self after all this is suppossed to be a forum that helps the thick gits like me out if it will help i shall post my php code for you to tell me how to achive my goal i can not show you a working copy because it is held on a internal intrannet system.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

You want to display the date as DD/MM/YYYY, and seperated into different months right?

Use mysql's built-in functions to format the date as you need it, and to select the month as a field.

Code: Select all

select DATE_FORMAT(date,"%d/%m/%Y") as formatted_date, MONTH(date) as month from table
Then, when you're looping through the records..

Code: Select all

while ($record = mysql_fetch_object($result)) {

  if ($record->month != $previousmonth) {
    echo "<tr><td>Different month now..</td></tr>";
    $previousmonth = $record->month;
  }

  echo "<tr><td>".$record->formatted_date."</td></tr>";

}
Obviously that's very rough and you'll need to integrate it into your own code, but it should give you a rough idea.
mally
Forum Newbie
Posts: 6
Joined: Tue Oct 18, 2005 3:46 am

Post by mally »

Code: Select all

<?php
session_start();
if (!isset($_SESSION['MM_Username'])){
header("location: sorry.php");
}
// technocurve arc 3 php mv block1/3 start
$mocolor1 = "#FFFFFF";
$mocolor2 = "#e9f0f8";
$mocolor3 = "#b6cee7";
$mocolor = $mocolor1;
// technocurve arc 3 php mv block1/3 end
?>
<?php require_once('../../Connections/jobbook.php'); ?>
<?php
//MX Table Sort Functions
require_once('../../includes/MXTableSort/MXTableSort_functions.inc.php'); 
$colname_contacts = "-1";
if (isset($_GET['clientid'])) {
  $colname_contacts = (get_magic_quotes_gpc()) ? $_GET['clientid'] : addslashes($_GET['clientid']);
}
mysql_select_db($database_jobbook, $jobbook);
$query_contacts = sprintf("SELECT * FROM contacts WHERE clientid = %s", $colname_contacts);
$contacts = mysql_query($query_contacts, $jobbook) or die(mysql_error());
$row_contacts = mysql_fetch_assoc($contacts);
$totalRows_contacts = mysql_num_rows($contacts);

$colname_hours = "-1";
if (isset($_GET['jobnoID'])) {
  $colname_hours = (get_magic_quotes_gpc()) ? $_GET['jobnoID'] : addslashes($_GET['jobnoID']);
}
$orderParam_hours = "employee";
if (isset($_GET['order_hours'])) {
  $orderParam_hours = (get_magic_quotes_gpc()) ? $_GET['order_hours'] : addslashes($_GET['order_hours']);
}
mysql_select_db($database_jobbook, $jobbook);
$query_hours = sprintf("SELECT hours.id, hours.jobnoID, hours.jobid, hours.employeeid, hours.sdate, hours.hours, hours.overtime,  
hours.jobnoID, employees.Title FROM hours LEFT JOIN (employees) ON (hours.employeeid = employees.employeeid) WHERE hours.jobnoID ='%s' ORDER BY sdate DESC", $colname_hours,$orderParam_hours);
$hours = mysql_query($query_hours, $jobbook) or die(mysql_error());
$row_hours = mysql_fetch_assoc($hours);
$totalRows_hours = mysql_num_rows($hours);

$MM_paramName = ""; 

// *** Go To Record and Move To Record: create strings for maintaining URL and Form parameters
// create the list of parameters which should not be maintained
$MM_removeList = "&index=";
if ($MM_paramName != "") $MM_removeList .= "&".strtolower($MM_paramName)."=";
$MM_keepURL="";
$MM_keepForm="";
$MM_keepBoth="";
$MM_keepNone="";
// add the URL parameters to the MM_keepURL string
reset ($HTTP_GET_VARS);
while (list ($key, $val) = each ($HTTP_GET_VARS)) {
	$nextItem = "&".strtolower($key)."=";
	if (!stristr($MM_removeList, $nextItem)) {
		$MM_keepURL .= "&".$key."=".urlencode($val);
	}
}
// add the Form parameters to the MM_keepURL string
if(isset($HTTP_POST_VARS)){
	reset ($HTTP_POST_VARS);
	while (list ($key, $val) = each ($HTTP_POST_VARS)) {
		$nextItem = "&".strtolower($key)."=";
		if (!stristr($MM_removeList, $nextItem)) {
			$MM_keepForm .= "&".$key."=".urlencode($val);
		}
	}
}
// create the Form + URL string and remove the intial '&' from each of the strings
$MM_keepBoth = $MM_keepURL."&".$MM_keepForm;
if (strlen($MM_keepBoth) > 0) $MM_keepBoth = substr($MM_keepBoth, 1);
if (strlen($MM_keepURL) > 0)  $MM_keepURL = substr($MM_keepURL, 1);
if (strlen($MM_keepForm) > 0) $MM_keepForm = substr($MM_keepForm, 1);
?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Isle Intranet</title>
<link href="../../css/image.css" rel="stylesheet" type="text/css">
</head>

<body>
<div id="top"><img src="../../img/logo.jpg" width="284" height="60"><strong>
</strong></div>
<div id="nav"><p><a href="../company/search.php">Search</a><a href="#">Logout</a></p>
</div>
<div id="main">
<table width="100%"  border="0" cellspacing="0" cellpadding="0">
<tr>
<td height="50"><h1>Hours for job number <strong><?php echo $row_hours['jobnoID']; ?></strong></h1></td>
<td width="200" align="right"><p><strong><?php echo $_SESSION['MM_Username'];?></strong> you are logged in </p></td>
</tr>
</table>
<div id="subnav">
<p><a href="../contacts/details.php?<?php echo $MM_keepNone.(($MM_keepNone!="")?"&":"")."clientid=".urlencode($row_contacts['clientid']) ?>">Contact Details</a><a href="../jobs/all_jobs.php?<?php echo $MM_keepNone.(($MM_keepNone!="")?"&":"")."clientid=".urlencode($row_contacts['clientid']) ?>">Jobs</a><a href="../web/details.php?<?php echo $MM_keepNone.(($MM_keepNone!="")?"&":"")."clientid=".urlencode($row_contacts['clientid']) ?>">Web Details</a><a href="#">Assets</a></p>
<br>
</div>
<table width="100%" border="0" cellpadding="4" cellspacing="1" bgcolor="#6699CC" id="results">
<tr bgcolor="#93B7DB">
<td><p><strong><a href="<?php echo getSortLink('hours','employee'); ?>">Employee</a><?php echo getSortIcon('hours','employee'); ?></strong></p></td>
<td width="100" align="center"><p><strong><a href="<?php echo getSortLink('hours','date'); ?>">Date</a><?php echo getSortIcon('hours','date'); ?></strong></p></td>
<td width="37" align="center"><p><strong>Hours</strong></p></td>
<td width="37" align="center"><p><strong>Overtime</strong></p></td>
</tr>
<?php 

$sum = 0; do { ?>
<tr <?php 
// technocurve arc 3 php mv block2/3 start
echo " style=\"background-color:$mocolor\" onMouseOver=\"this.style.backgroundColor='$mocolor3'\" onMouseOut=\"this.style.backgroundColor='$mocolor'\"";
// technocurve arc 3 php mv block2/3 end
?>>
<td><p><?php echo $row_hours['Title']; ?></p></td>
<td align="center"><p>  
<?php echo $row_hours['sdate']; ?></p></td>
<td align="center"><p><?php echo $row_hours['hours']; ?></p></td>
<td align="center"><?php echo $row_hours['overtime']; ?>
<?php 
$sum = $sum + $row_hours['hours'] + $row_hours['overtime']; 
?></td>
</tr>
<?php } while ($row_hours = mysql_fetch_assoc($hours)); ?>
<?php 
// technocurve arc 3 php mv block3/3 start
if ($mocolor == $mocolor1) {
	$mocolor = $mocolor2;
} else {
	$mocolor = $mocolor1;
}
// technocurve arc 3 php mv block3/3 end
?>
</table>
<p><strong>
<?php
print "Total is $sum";
?>
</strong></p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p><strong>
    <?php
print "Total is $sum";
?>
</strong></p>
</div>
</body>
</html>
<?php
mysql_free_result($contacts);
mysql_free_result($hours);
?>
this is the PHP file


and this is the asp file if any one knows asp? to help me translate what i'm trying to do

Code: Select all

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%> 
<!--#include file="../Connections/Intranet.asp" -->
<%
' *** Restrict Access To Page: Grant or deny access to this page
MM_authorizedUsers="Admin,Manager,User"
MM_authFailedURL="../notauth.asp"
MM_grantAccess=false
If Session("MM_Username") <> "" Then
  If (false Or CStr(Session("MM_UserAuthorization"))="") Or _
         (InStr(1,MM_authorizedUsers,Session("MM_UserAuthorization"))>=1) Then
    MM_grantAccess = true
  End If
End If
If Not MM_grantAccess Then
  MM_qsChar = "?"
  If (InStr(1,MM_authFailedURL,"?") >= 1) Then MM_qsChar = "&"
  MM_referrer = Request.ServerVariables("URL")
  if (Len(Request.QueryString()) > 0) Then MM_referrer = MM_referrer & "?" & Request.QueryString()
  MM_authFailedURL = MM_authFailedURL & MM_qsChar & "accessdenied=" & Server.URLEncode(MM_referrer)
  Response.Redirect(MM_authFailedURL)
End If
%>
<%
Dim Job__MMColParam
Job__MMColParam = "1"
If (Request.QueryString("JobNumberID") <> "") Then 
  Job__MMColParam = Request.QueryString("JobNumberID")
End If
%>
<%
Dim Job
Dim Job_numRows

Set Job = Server.CreateObject("ADODB.Recordset")
Job.ActiveConnection = MM_Intranet_STRING
Job.Source = "SELECT * FROM dbo.JobBook WHERE JobNumberID = " + Replace(Job__MMColParam, "'", "''") + ""
Job.CursorType = 0
Job.CursorLocation = 2
Job.LockType = 1
Job.Open()

Job_numRows = 0
%>
<%
Dim hourstotal__MMColParam
hourstotal__MMColParam = "1"
If (Request.QueryString("JobNumberID") <> "") Then 
  hourstotal__MMColParam = Request.QueryString("JobNumberID")
End If
%>
<%
Dim hourstotal
Dim hourstotal_numRows

Set hourstotal = Server.CreateObject("ADODB.Recordset")
hourstotal.ActiveConnection = MM_Intranet_STRING
hourstotal.Source = "SELECT * FROM dbo.jobnumberID_totalHours WHERE JobNumberID = " + Replace(hourstotal__MMColParam, "'", "''") + ""
hourstotal.CursorType = 0
hourstotal.CursorLocation = 2
hourstotal.LockType = 1
hourstotal.Open()

hourstotal_numRows = 0
%>
<%
Dim Hours__MMColParam
Hours__MMColParam = "1"
If (Request.QueryString("JobNumberID") <> "") Then 
  Hours__MMColParam = Request.QueryString("JobNumberID")
End If
%>
<%
Dim Hours
Dim Hours_numRows

Set Hours = Server.CreateObject("ADODB.Recordset")
Hours.ActiveConnection = MM_Intranet_STRING
Hours.Source = "SELECT * FROM dbo.hoursworked2 WHERE JobNumberID = " + Replace(Hours__MMColParam, "'", "''") + " ORDER BY Date2 DESC"
Hours.CursorType = 0
Hours.CursorLocation = 2
Hours.LockType = 1
Hours.Open()

Hours_numRows = 0
%>
<%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
Hours_numRows = Hours_numRows + Repeat1__numRows
%>
<SCRIPT RUNAT=SERVER LANGUAGE=VBSCRIPT>					
function DoDateTime(str, nNamedFormat, nLCID)				
	dim strRet								
	dim nOldLCID								
										
	strRet = str								
	If (nLCID > -1) Then							
		oldLCID = Session.LCID						
	End If									
										
	On Error Resume Next							
										
	If (nLCID > -1) Then							
		Session.LCID = nLCID						
	End If									
										
	If ((nLCID < 0) Or (Session.LCID = nLCID)) Then				
		strRet = FormatDateTime(str, nNamedFormat)			
	End If									
										
	If (nLCID > -1) Then							
		Session.LCID = oldLCID						
	End If									
										
	DoDateTime = strRet							
End Function									
</SCRIPT>
<html>
<head>
<title>isle intranet</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="../styles/page_layout.css" type="text/css">
</head>
<body bgcolor="#FFFFFF" text="#000000" class="bakmain" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<p>&nbsp; 
  <% if (not hours.eof) then %>
</p>
<table width="700" border="0">
  <tr> 
    <td width="42%"><b><font face="Arial, Helvetica, sans-serif" color="#996633">Job 
      Number</font></b></td>
    <td width="58%"><b><font face="Arial, Helvetica, sans-serif" color="#996633">Book 
      Reference</font></b></td>
    <td width="58%"><b></b></td>
  </tr>
  <tr> 
    <td width="42%"><font face="Arial, Helvetica, sans-serif" color="#996633"><%=(Hours.Fields.Item("JobNumberID").Value)%></font></td>
    <td width="58%"><font color="#996633" face="Arial, Helvetica, sans-serif"><%=(Job.Fields.Item("JobNumber").Value)%></font></td>
    <td width="58%">&nbsp;</td>
  </tr>
  <tr> 
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr> 
    <td width="42%"><b><font face="Arial, Helvetica, sans-serif" color="#996633">Description</font></b></td>
    <td width="58%"><font face="Arial, Helvetica, sans-serif" color="#996633"><%=(Job.Fields.Item("Description").Value)%></font></td>
    <td width="58%">&nbsp;</td>
  </tr>
  <tr> 
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr> 
    <td width="42%"><b><font face="Arial, Helvetica, sans-serif" color="#996633">Date</font></b></td>
    <td width="58%"><b><font face="Arial, Helvetica, sans-serif" color="#996633">Hours</font></b></td>
    <td width="58%"><b><font face="Arial, Helvetica, sans-serif" color="#996633">Artist</font></b></td>
  </tr>
  <% 
  monthhours = 0
do while not hours.eof 
%>
<%
if not skipmonth = "YES" then
months = monthname(month(cdate(hours.Fields("Date2"))))
Response.Write "<tr bgcolor=#cccccc><td><font face=""Arial, Helvetica, sans-serif"">"&months&"</font></td><td></td></tr>"
skipmonth = "YES"
end if
%>
  <tr> 
    <td width="42%"><font face="Arial, Helvetica, sans-serif" color="#996633"><%= DoDateTime((Hours.Fields.Item("Date2").Value), 2, 2057) %></font></td>
    <td width="58%"><font face="Arial, Helvetica, sans-serif" color="#996633"><%=(Hours.Fields.Item("HoursWorked").Value)%></font></td>
    <td width="58%"><font color="#996633" face="Arial, Helvetica, sans-serif"><%=(Hours.Fields.Item("firstname").Value)%></font></td>
  </tr><% 
  totalhours = totalhours + (Hours.Fields.Item("HoursWorked").Value)
  monthhours = monthhours + (Hours.Fields.Item("HoursWorked").Value)
  hours.MoveNext()
  if not hours.EOF then 
  tempmonths = monthname(month(cdate(hours.Fields("Date2"))))
  end if
  if not tempmonths = months or hours.eof then 
  Response.Write "<tr><td><font face=""Arial, Helvetica, sans-serif"">Total for "&months&"</font></td><td>"&monthhours&"</td></tr>"
  Response.Write "<tr><td> </td><td> </td></tr>"
  monthhours = 0
  skipmonth = "NO"
  end if %>
  <% 
  loop
%>
  
</table>
<table width="700" border="0">
  <tr> 
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr> 
    <td width="42%"><b><font face="Arial, Helvetica, sans-serif" color="#996633">Total 
      Hours </font></b></td>
    <td width="58%"><font color="#996633" face="Arial, Helvetica, sans-serif"><%=totalhours%></font></td>
  </tr>
</table>
<p>
  <% else %>
</p>
<p><b><font face="Arial, Helvetica, sans-serif" color="#996633">No Data Input</font></b></p>
<% end if %>
</body>
</html>
<%
Job.Close()
Set Job = Nothing
%>
<%
hourstotal.Close()
Set hourstotal = Nothing
%>
<%
Hours.Close()
Set Hours = Nothing
%>
mally
Forum Newbie
Posts: 6
Joined: Tue Oct 18, 2005 3:46 am

Post by mally »

onion2k wrote:You want to display the date as DD/MM/YYYY, and seperated into different months right?

Use mysql's built-in functions to format the date as you need it, and to select the month as a field.

Code: Select all

select DATE_FORMAT(date,"%d/%m/%Y") as formatted_date, MONTH(date) as month from table
Then, when you're looping through the records..

Code: Select all

while ($record = mysql_fetch_object($result)) {

  if ($record->month != $previousmonth) {
    echo "<tr><td>Different month now..</td></tr>";
    $previousmonth = $record->month;
  }

  echo "<tr><td>".$record->formatted_date."</td></tr>";

}
Obviously that's very rough and you'll need to integrate it into your own code, but it should give you a rough idea.
thanks for that

so how do i put it in the select statment?

Code: Select all

mysql_select_db($database_jobbook, $jobbook);
$query_hours = sprintf("SELECT hours.id, hours.jobnoID, hours.jobid, hours.employeeid, hours.sdate, hours.hours, hours.overtime,  
hours.jobnoID, employees.Title FROM hours LEFT JOIN (employees) ON (hours.employeeid = employees.employeeid) WHERE hours.jobnoID ='%s' ORDER BY sdate DESC", $colname_hours,$orderParam_hours);
$hours = mysql_query($query_hours, $jobbook) or die(mysql_error());
$row_hours = mysql_fetch_assoc($hours);
$totalRows_hours = mysql_num_rows($hours);
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

mally wrote:so how do i put it in the select statment?

Code: Select all

mysql_select_db($database_jobbook, $jobbook);
$query_hours = sprintf("SELECT hours.id, hours.jobnoID, hours.jobid, hours.employeeid, hours.sdate, hours.hours, hours.overtime,  
hours.jobnoID, employees.Title FROM hours LEFT JOIN (employees) ON (hours.employeeid = employees.employeeid) WHERE hours.jobnoID ='%s' ORDER BY sdate DESC", $colname_hours,$orderParam_hours);
$hours = mysql_query($query_hours, $jobbook) or die(mysql_error());
$row_hours = mysql_fetch_assoc($hours);
$totalRows_hours = mysql_num_rows($hours);
Assuming sdate is the column that holds the date..

Code: Select all

$query_hours = sprintf("SELECT hours.id, hours.jobnoID, hours.jobid, hours.employeeid, hours.sdate, DATE_FORMAT(hours.sdate,"%d,%m,%Y") as formatted_date, MONTH(sdate) as month, hours.hours, hours.overtime,  
hours.jobnoID, employees.Title FROM hours LEFT JOIN (employees) ON (hours.employeeid = employees.employeeid) WHERE hours.jobnoID ='%s' ORDER BY sdate DESC", $colname_hours,$orderParam_hours);
Now, thats the SQL statement .. but .. I've a nasty feeling that the %d/%m/%Y bit in DATE_FORMAT is going to screw up your sprintf() function.You might need to remove the sprintf and embed the PHP variables in the string instead (eg, rather than putting '%s' you'd put '$colname_hours').
Post Reply