Databases
StackOverflow2013
Postsdbo
POCalculate days between 2 dates and another criterial PHP MYSQL
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCalculate days between 2 dates and another criterial PHP MYSQL
    text
    copied!<p>I'm new newbie to programming and I've got an error that i wasn't able to solve after hours searching.</p> <p>I'm using a form to perform a search and display in a table the data, that part was fairly easy... but I also want to calculate how many days are in the final result, not in the dates I using for the first query.</p> <p>The query searches for the result in days between 2 dates and from a specified company, the problem is in the final result it calculates how many days are from my result and not from the days i searched. </p> <p>Example: If i search 2 dates between February 11 and February 20 Where the company = 1</p> <p>If there is only 3 days result for Company 1, I want it to calculate only 3 Days. </p> <p>I know my code is Wrong in that part but i just can't get it to do what i want.</p> <p>Here is a little piece of my code to explain it: </p> <pre><code>if (isset($_POST['search'])) { $date1 = mysql_prep($_POST['date1']); $date2 = mysql_prep($_POST['date2']); $latte = mysql_prep($_POST['latte']); $query = "SELECT * FROM payroll WHERE (day BETWEEN '{$date1}' AND '{$date2}') AND (company = '{$latte}') ORDER BY day ASC "; $result = mysql_query($query, $connection); $woof = "SELECT SUM(hours) FROM (SELECT * FROM payroll WHERE (day BETWEEN '{$date1}' AND '{$date2}') AND (company = '{$latte}') ORDER BY day ASC) AS subt "; $raw = mysql_query($woof, $connection); if(!$raw) { die(mysql_error());} $meow = mysql_result($raw, 0, 0); $diff = abs(strtotime($date2) - strtotime($date1)); $years = floor($diff / (365*60*60*24)); $months = floor(($diff - $years * 365*60*60*24) / (30*60*60*24)); $days = floor(($diff - $years * 365*60*60*24 - $months*30*60*60*24)/ (60*60*24) +1); if(!$result) { echo "FAIL"; } else { echo "&lt;table&gt; &lt;tr&gt; &lt;th&gt; Date &lt;/th&gt; &lt;th&gt; Hours &lt;/th&gt; &lt;th&gt; Job Title &lt;/th&gt; &lt;th&gt; Job Description &lt;/th&gt; &lt;th&gt; Paid &lt;/th&gt; &lt;th&gt; For &lt;/th&gt; &lt;/tr&gt;"; while($row = mysql_fetch_array($result)) { $company = $row['company']; if($company == 0) { $company = "Wilson Electric"; } if($company == 1) { $company = "Wilson Rental"; } if ($company == 2) { $company = "Church of Christ"; } echo "&lt;tr&gt;"; echo "&lt;td class=\"center\"&gt;" . $row['day'] . "&lt;/td&gt;"; echo "&lt;td class=\"center\"&gt;" . $row['hours'] . "&lt;/td&gt;"; echo "&lt;td style=\"padding:5px;\"&gt;" . $row['job_title'] . "&lt;/td&gt;"; echo "&lt;td style=\"padding:5px;\"&gt;" . $row['job_description'] . "&lt;/td&gt;"; echo "&lt;td class=\"center\"&gt;" . $row['paid'] . "&lt;/td&gt;"; echo "&lt;td style=\"padding:5px;\"&gt;" . $company . "&lt;/td&gt;"; echo "&lt;/tr&gt;"; } echo "&lt;tr&gt;"; echo "&lt;td class=\"center\"&gt; Total Days: " . $days . "&lt;/td&gt;"; echo "&lt;td class=\"center\"&gt; Total Hours: " . $meow . "&lt;/td&gt;"; echo "&lt;/tr&gt;"; } } </code></pre> <p>Thanks for Reading it, I hope someone can give me a solution, i know must be something really simple but I just couldn't figure out.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload