DarkPrince2005 Posted September 2, 2007 Share Posted September 2, 2007 I am trying to grab a field from a mysql database and calculate the difference of that date and the current date and then inserting it into a database field. Here is the code: text-decoration:none; } a:visited{font-family:'Times New Roman'; font-size:10pt; color:'#ffffff'; font-weight:700; text-decoration:None; } a:hover{font-family:'Times New Roman'; font-size:10pt; text-decoration:none; color:'#B37A04'; font-weight:700; } a:active{font-family:'Times New Roman'; font-size:10pt; color:'#ffffff'; font-weight:700; } </style></head><body bgcolor='#FDCC67' topmargin='0' bottommargin='0' leftmargin='0' rightmargin='0'> <center><table width="100%" height="100%" cellpadding="0" cellspacing="0" border="0"> <tr><td valign='top' width='140' bgcolor='#000000'><br> <a href='home.html'>Home</a><br><br> <a href='view.php'>View Records</a><br><br> <a href='add.html'>Add Record</a><br><br> <a href='edit1.php'>Edit Record</a><br><br> <a href='delete1.php'>Delete Record</a><br><br> <a href='report.php'>View Report</a><br><br> <a href='q.php'>Query Report</a><br><br> <a href="search1.php">Search</a></td><td valign="top" align="center"><table><tr> <td align="right" valign="top"><form action="search2.php" method="post"><b>Search by Persal Number:</b> <input type="text" name="search"> <input type="submit" value="Search"></form></td></tr> <tr><td align="center" valign="top"><h1>View Disciplinary Records</h1></td></tr> <tr><td align="center" valign="top"></td></tr> <tr><td align="center" valign="top"><br><?= $html ?></td></tr></table></td></tr></table> </body> Quote Link to comment https://forums.phpfreaks.com/topic/67619-solved-query/ Share on other sites More sharing options...
trq Posted September 2, 2007 Share Posted September 2, 2007 Sorry, there is np php amongst that html. Post the relvent code. Quote Link to comment https://forums.phpfreaks.com/topic/67619-solved-query/#findComment-339679 Share on other sites More sharing options...
DarkPrince2005 Posted September 2, 2007 Author Share Posted September 2, 2007 sorry, seems it didn't copy the entire code:| <?php mysql_connect("localhost","root",""); mysql_select_db("npa"); $date = date("Y-m-d"); $date1 = mysql_query("SELECT Date_Recieved, CURDATE(), (YEAR(CURDATE())-YEAR(Date_Recieved)) - (RIGHT(CURDATE(),5)<RIGHT(Date_Recieved,5)) from dis"); $r = mysql_query("Update dis set Date_Added='$date1'"); $q = mysql_query("SELECT * from dis"); while ($row = mysql_fetch_assoc($q)) { $html .= "<center><form> <table cellpadding='0' border='0' cellspacing='0'><tr> <td valign='top'><table width='400'> <tr><td><b>File Ref</td><td> </td> <td>$row[File_Ref]</td></tr> <tr><td><b>Employee Firstname</td><td> </td> <td>$row[Employee_Firstname]</td></tr> <tr><td><b>Employee Lastname</td><td> </td> <td>$row[Employee_Lastname]</td></tr> <tr><td><b>Persal Number</td><td> </td> <td>$row[Persal_Number]</td></tr> <tr><td><b>Occupation</td><td> </td> <td>$row[Occupation]</td</tr> <tr><td><b>Level</td><td> </td> <td>$row[Level]</td</tr> <tr><td><b>Gender</td><td> </td> <td>$row[Gender]</td</tr> <tr><td><b>Race</td><td> </td> <td>$row[Race]</td</tr> <tr><td><b>Suspension</td><td> </td> <td>$row[suspensions]</td</tr> <tr><td><b>Period</td><td> </td> <td>$row[Period]</td></tr> <tr><td><b>Date Recieved</td><td> </td> <td>$row[Date_Recieved]</td</tr> <tr><td><b>Unit</td><td> </td> <td>$row[unit]</td</tr> <tr><td><b>Region</td><td> </td> <td>$row[Region]</td</tr> <tr><td><b>Date Of DC</td><td> </td> <td>$row[Date_Of_DC]</td</tr> <tr><td><b>Status</td><td> </td> <td>$row[status]</td</tr> <tr><td><b>Category</td><td> </td> <td>$row[Category]</td></tr> <tr><td><b>Outcome</td><td> </td> <td>$row[Outcome]</td></tr></table></td><td> </td><td valign='top'><table width='400'> <tr><td valign='top'><b>Status Report</td><td> </td> <td width='250'>$row[status_Report]</td></tr> <tr><td>$row[Date_Added]</td></tr> </table></table> </form>"; }; ?> <html><head><title></title><link rel='stylesheet' href='stylesheet.css' type='text/css'> <style> a:link{font-family:'Times New Roman'; font-size:10pt; color:'#ffffff'; font-weight:700; text-decoration:none; } a:visited{font-family:'Times New Roman'; font-size:10pt; color:'#ffffff'; font-weight:700; text-decoration:None; } a:hover{font-family:'Times New Roman'; font-size:10pt; text-decoration:none; color:'#B37A04'; font-weight:700; } a:active{font-family:'Times New Roman'; font-size:10pt; color:'#ffffff'; font-weight:700; } </style></head><body bgcolor='#FDCC67' topmargin='0' bottommargin='0' leftmargin='0' rightmargin='0'> <center><table width="100%" height="100%" cellpadding="0" cellspacing="0" border="0"> <tr><td valign='top' width='140' bgcolor='#000000'><br> <a href='home.html'>Home</a><br><br> <a href='view.php'>View Records</a><br><br> <a href='add.html'>Add Record</a><br><br> <a href='edit1.php'>Edit Record</a><br><br> <a href='delete1.php'>Delete Record</a><br><br> <a href='report.php'>View Report</a><br><br> <a href='q.php'>Query Report</a><br><br> <a href="search1.php">Search</a></td><td valign="top" align="center"><table><tr> <td align="right" valign="top"><form action="search2.php" method="post"><b>Search by Persal Number:</b> <input type="text" name="search"> <input type="submit" value="Search"></form></td></tr> <tr><td align="center" valign="top"><h1>View Disciplinary Records</h1></td></tr> <tr><td align="center" valign="top"></td></tr> <tr><td align="center" valign="top"><br><?= $html ?></td></tr></table></td></tr></table> </body> Quote Link to comment https://forums.phpfreaks.com/topic/67619-solved-query/#findComment-339685 Share on other sites More sharing options...
DarkPrince2005 Posted September 2, 2007 Author Share Posted September 2, 2007 It only seems to insert the value "Resource id #4" into the designated field. Quote Link to comment https://forums.phpfreaks.com/topic/67619-solved-query/#findComment-339688 Share on other sites More sharing options...
trq Posted September 2, 2007 Share Posted September 2, 2007 Because mysql_query() returns a result resource. So, $date1 is a result resource. Quote Link to comment https://forums.phpfreaks.com/topic/67619-solved-query/#findComment-339690 Share on other sites More sharing options...
DarkPrince2005 Posted September 2, 2007 Author Share Posted September 2, 2007 So what do i do to change it? Quote Link to comment https://forums.phpfreaks.com/topic/67619-solved-query/#findComment-339698 Share on other sites More sharing options...
Ken2k7 Posted September 2, 2007 Share Posted September 2, 2007 I don't consider myself a php expert, but I don't think you can query this: $date1 = mysql_query("SELECT Date_Recieved, CURDATE(), (YEAR(CURDATE())-YEAR(Date_Recieved)) - (RIGHT(CURDATE(),5)<RIGHT(Date_Recieved,5)) from dis"); You can't do math in a query. And you can call $row['something'] in a string like that. Quote Link to comment https://forums.phpfreaks.com/topic/67619-solved-query/#findComment-339701 Share on other sites More sharing options...
DarkPrince2005 Posted September 2, 2007 Author Share Posted September 2, 2007 I am sure you can do calculations in a query, or is this different when using php? and if so, how am I gonna get it to work? Quote Link to comment https://forums.phpfreaks.com/topic/67619-solved-query/#findComment-339704 Share on other sites More sharing options...
Ken2k7 Posted September 2, 2007 Share Posted September 2, 2007 You can't do math inside a query. All a query does is tell what information column and results you want from the database. You can then get the values and do math. Quote Link to comment https://forums.phpfreaks.com/topic/67619-solved-query/#findComment-339711 Share on other sites More sharing options...
trq Posted September 2, 2007 Share Posted September 2, 2007 Of course you can do calculations within queries. If all you want to do is UPDATE the Date_Added to the value of a SELECT statement you should be able to do something like.... UPDATE dis SET Date_Added='(SELECT YEAR(CURDATE())-YEAR(Date_Recieved)) - (RIGHT(CURDATE(),5)<RIGHT(Date_Recieved,5))'; Quote Link to comment https://forums.phpfreaks.com/topic/67619-solved-query/#findComment-339712 Share on other sites More sharing options...
Ken2k7 Posted September 2, 2007 Share Posted September 2, 2007 Of course you can do calculations within queries. If all you want to do is UPDATE the Date_Added to the value of a SELECT statement you should be able to do something like.... UPDATE dis SET Date_Added='(SELECT YEAR(CURDATE())-YEAR(Date_Recieved)) - (RIGHT(CURDATE(),5)<RIGHT(Date_Recieved,5))'; I stand corrected. thanks thorpe. But I was referring to the $date1. Can you do math there? Quote Link to comment https://forums.phpfreaks.com/topic/67619-solved-query/#findComment-339716 Share on other sites More sharing options...
trq Posted September 2, 2007 Share Posted September 2, 2007 Can you do math there? Yes. Quote Link to comment https://forums.phpfreaks.com/topic/67619-solved-query/#findComment-339718 Share on other sites More sharing options...
Ken2k7 Posted September 2, 2007 Share Posted September 2, 2007 Can you do math there? Yes. Wow, I'm curious to see how his database is set up. Thanks for clearing that up thorpe. Quote Link to comment https://forums.phpfreaks.com/topic/67619-solved-query/#findComment-339719 Share on other sites More sharing options...
DarkPrince2005 Posted September 2, 2007 Author Share Posted September 2, 2007 thanx guys, seems to be working now, So, whenever your in Johannesburg, South Africa drop me a line and I'll stick you for a drink.... Quote Link to comment https://forums.phpfreaks.com/topic/67619-solved-query/#findComment-339734 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.