Date sorting from input in php from sql?

Question

I'm doing a sales recording for my own small shop using the combination of html and php.
I want to have a time selecting input (something like March 2014, April 2014 when selecting)
Here is my index.php

<?php
$con=mysqli_connect("192.168.1.248","a","a","services");
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($con,"SELECT * FROM pcsales");
echo "<h3><marquee><b>====PC Sales====</b></marquee></h3>";
echo "<button type='button' name='add' onClick='add()'>Add</button>";
echo "<script type='text/javascript'>
function add()
{
window.location='./edit'; 
}
</script>";
echo "<button type='button' name='edit' onClick='edit()'>Edit</button>";
echo "<script type='text/javascript'>
function edit()
{
window.location='./edit/edit.html'; 
}
</script>";
echo "<table border='1'>
<tr>
<th>ID</th>
<th>Type</th>
<th>If Others</th>
<th>Brand</th>
<th>Description</th>
<th>Date Sold</th>
<th>Serial No.</th>
</tr>";
while($row = mysqli_fetch_array($result)) {
  echo "<tr>";
  echo "<td><p style='font-size:12px'>" . $row['ID'] . "</p></td>";
  echo "<td>" . $row['Type'] . "</td>";
  echo "<td>" . $row['Types'] . "</td>";
  echo "<td>" . $row['Brand'] . "</td>";
  echo "<td><p style='font-size:12px'>" . $row['Description'] . "</p></td>";
  echo "<td><p style='font-size:12px'>" . $row['Selldate'] . "</p></td>";
  echo "<td><p style='font-size:12px'>" . $row['Serial'] . "</p></td>";
  echo "</tr>";
}

echo "</table>";
echo "<h3><marquee direction=right><b>====PC Sales====</b></marquee></h3>";
mysqli_close($con);
?>

I'm using TIMESTAMP in mysql. Here is the output

ID  Type    If Others   Brand       Description           Date Sold            Serial No.
1 Notebook              Acer E1     Add 2GB DDR3 1600 RAM 2014-06-25 11:57:58  123456789

2 Others    AIO         Asus        N/A                   2014-07-25 12:52:12  987654321

3 Desktop               Trendsonic  Full spec listed.     2014-07-30 09:55:10  N/A

When I put a range on a textbox (more expedient if selectable) Example:

July 2014

Then the output shall be

ID  Type    If Others   Brand       Description           Date Sold            Serial No.
2 Others    AIO         Asus        N/A                   2014-07-25 12:52:12  987654321

3 Desktop               Trendsonic  Full spec listed.     2014-07-30 09:55:10  N/A

If only

is inserted, then it shall output everything from Year 2014 Is it possible to do that?

Note: I don't care about exploits, as it is used internally.

"Note: I don't care about exploits, as it is used internally." because no company has ever had a disgruntled employee. — user557846
– user557846, Commented Jul 30, 2014 at 4:20
Well, I have no real employees, as all the "workers" are my family members :D — ProtoAES256
– ProtoAES256, Commented Jul 30, 2014 at 4:22

Atmiya Kolsawala · Accepted Answer · 2017-03-11 10:17:53Z

1

Better to take datepicker and fetch date from there in variable and make query some how like this. SELECT * FROM TABLENAME WHERE MONTH(dateColumnname) = $month AND YEAR(dateColumnname) = $year; EXAMPLE: SELECT * FROM testerpractice WHERE MONTH(datess) = '03' AND YEAR(datess) = '2017'; Best OF Luck...

answered Mar 11, 2017 at 10:17

Atmiya Kolsawala

4854 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

LHristov · Accepted Answer · 2014-07-30 05:01:20Z

0

Better use dropdown lists ( tag) for day, month and year (with defaults 01 for day, Jan for month, current year for the year) , and concatenate their values to get the search string. You'll have expediency and will be able to construct the query easy.

answered Jul 30, 2014 at 5:01

LHristov

1,1217 silver badges16 bronze badges

3 Comments

ProtoAES256 Over a year ago

Do you have examples? because I'm quite new in php and mysql D:

ProtoAES256 Over a year ago

Like POST on url that sorts out clearly index.php?month=jan&year=2014 something like this?

LHristov Over a year ago

No, got no PHP examples. But after POST you should be able to get values from the dropdown lists. That index.php?month=01&year=2014 is GET, not POST.

ProtoAES256 · Accepted Answer · 2014-07-31 04:01:53Z

0

-Problem Solved- My solution(not that "professional") I do a sort.html and a sort.php

The sort.html contains

Year:<input type="text" value="" name="year"/>
Month:<input type="text" value="" name="month"/>

While the sort.php contains

$year = mysqli_real_escape_string($con, $_POST['year']);
$month = mysqli_real_escape_string($con, $_POST['month']);
$result = mysqli_query($con,"SELECT * FROM pcsales WHERE Selldate >= '$year-$month-01 00:00:00' AND Selldate <= '$year-$month-31 23:59:59'");

This might be a reference for other people who might encounter the same problem as me~

answered Jul 31, 2014 at 4:01

ProtoAES256

286 bronze badges

Collectives™ on Stack Overflow

Date sorting from input in php from sql?

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related