Show records by date using php mysqli

Hello Guys i want to show record by date
here is my myqli table structure

CREATE TABLE `record` (
  `id` int(11) NOT NULL,
  `date` varchar(255) NOT NULL,
  `month` varchar(255) NOT NULL,
  `year` varchar(255) NOT NULL,
  `record1` varchar(255) NOT NULL,
  `record2` varchar(255) NOT NULL,
  `record3` varchar(255) NOT NULL,
  `record4` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

PHP

	foreach($data as $rows){
			
				echo'<tr><td>'.$rows['date'].'</td>
				<td>'.$rows['record1'].'</td>
				<td>'.$rows['record2'].'</td>
				<td>'.$rows['record3'].'</td>
				<td>'.$rows['record4'].'</td></tr>';
		}
		echo '</table></table>

I want to filter all record by month like if month change 10 to 11 get user can easily find records by month.

Rather than using 3 separate varchar fields for date, month and year, it is better to use a single DATE field.

Where is your SELECT query? That is where you can select and sort via your different fields.

Thanks for reply i want to filter using $_GET but i dont have idea how to filter
like www.example.com/?moth=10&year=16

If you are using $_GET then you must first sanitise the input.

if(isset($_GET['month'])) { $month = preg_replace('#[^0-9]#', '', $_GET['month']); }
else { // month is not set!! }

That will check the variable is there and strip it down to an integer.
It’s so long since I used mysqli that I don’t remember the syntax, I use pdo now.
But I would suggest prepared statements for your select query since it is getting data from get. The preg_replace should make it safe, but I’m “belt & braces” when it comes to security.

okay but how to filter it

I’m not clear about what you are trying to do, so can only take a wild guess at what you want.
Can you explain more clearly exactly what your intention is? Are you selecting just one entry from the database, or a group of entries?
Can you show a select query that you have tried already, to give some clues as to what you want, and if it did not work as expected, what was your expectation.

To filter by month sounds something like:-

SELECT * FROM record WHERE month = '10'

But that seems too simple to ask about, so I think I’m missing something.

$sql5 = "SELECT * FROM record";
		$result5 = mysqli_query($mysqli,$sql5) or die(mysqli_error());
		while($row= mysqli_fetch_assoc($result5))
		{
		
		$data[] = $row;
		
		}

Thanks Is working perfect but i want to know what is the security risk and and how preg_replace safe this cods

This will select everything from the table. So adding the WHERE clause will filter the results.

"SELECT * FROM record WHERE month = '$month'"

Be aware that this is not a prepared statement. To be honest, I don’t know how to with mysqli, because I use PDO all the time. But I recommend you do use a prepared statement for this.

If not using the prepared statement it is very important that you filter the $_GET data, otherwise you will be a sitting duck for sql injection.

The preg_replace will remove any thing that is not a number from the query.
So if someone altered the url variable to:-

www.example.com/?month=8'SomeHarmfulCode123'

Then the result after preg_replace would be $month = 8123 because only numbers remain.

Okay thanks for the info

You can use my example I posted in this topic.

https://www.sitepoint.com/community/t/php-errors-are-not-showing-when-i-uncomment-html/238363/5?u=spaceshiptrooper

In addition the the sanitisation, you may want to add further validation checks. For example to check the resulting string is not empty and that it’s a number from 1 to 12. This will help avoid errors.

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.