Hello, I’m stuck on my webpage.
My webpage works as this: User selects a categorie, a cost, a date and enters some info which is stored to the database.
If they go to the page costs.php they can see all their data based on user id.
This works excellent but then i want people to select 2 dates and the table updates the information only showing all data between those 2 dates. This is what i got so far:
//Filter
<form action="costs.php" method="post">
<input type="date" name="selected_date1" value="<?php echo date('d-m-Y'); ?>" />
<input type="date" name="selected_date2" value="<?php echo date('d-m-Y'); ?>" />
<input type="submit" name="filter_date" value="Filter">
</form>
//Store filter dates in variables.
$date1_raw = $_POST['selected_date1'];
$date2_raw = $_POST['selected_date2'];
//Query for selecting all costs of the date filter.
$sql_datefilter = "SELECT * FROM costs WHERE costdate BETWEEN '$date1_raw' AND 'date2_raw' AND userid='".$_SESSION['id']."'";
$result_dates = mysqli_query($conn, $sql_datefilter);
Now i’m stuck on how to update the existing table that is created when the user first visits the page:
// selects when user visits the page
$sql_costs = "SELECT * FROM costs WHERE userid='".$_SESSION['id']."'";
$result_costs = mysqli_query($conn, $sql_costs);
//Echo's the query in a table
<?php
while($row = $result_costs->fetch_assoc()) { ?>
<tr>
<td><?php echo $row['subcategory']; ?></td>
<td><?php echo $row['costname']; ?></td>
<!-- Added "€" sign infront of price !-->
<td><?php echo "€ " . $row['price']; ?></td>
<td><?php echo $row['info']; ?></td>
<!-- Added Date Function to convert format !-->
<td><?php echo date( "d/m/y",strtotime ($row['costdate'])); }?></td>
</tr>
How do i update this table if someone selects the 2 dates and filters? Really stuck on this for several days…
You have to optionally append the between part of your SQL query depending on the users choice. Another method would be to set default date values to be used in the statement and only overwrite them on form submission.
Note: please use prepared statements for your mysqli queries to gain extra security, this is really simple, just follow the examples in the documentation
Thanks for the quick answer!
I will get starded on the prepared statements as we speak but i’m really not sure what you mean with appending the between parts or the other method…
I’m a beginner in php and i really don’t know how i would update the table that has been created when the page loads …
Is all the code you showed in the same costs.php file? If so, I’d do something like:
// check for whether the form variables are filled in
// if they are, build a query using the userid and the dates they selected
// if they are not, just use the basic query you have for userid
// draw the form again, filling in the date boxes if they were entered before
// loop through the results and display them
That way, you’re using the same code to display the html table whether they selected dates or not.
Some other thoughts / comments
Remember that you need to make it clear when talking about html tables as opposed to database tables - “update the table” in the PHP board often suggests updating database records.
Check for typos - you’re missing a $ to denote a variable name in the first query
Think about date formats - I think if you’re using a date column in the database, you’ll need to switch the d/m/Y fields around from what the user enters otherwise BETWEEN won’t work. You’ll also need to validate the dates.
Once you’ve got it working, you can make it nicer by maybe using Ajax to remove the dates that aren’t between your selected dates without having to re-draw the page, that kind of stuff, but get it working first. I take your point on prepared statements, but that can often make it easier to get working in the first place, by removing the need to worry so much about quotes within the query. It’s a good habit to get into, and once you get it working without, you either won’t want to disturb working code, or won’t have time to re-do something that works. Worth doing from the start, IMO.
Thanks for the Clear and well explained answer!
I tried what you said and this is what i currently have:
<?php
if (!empty(date1_raw && !empty(date2_raw)) {
//Query for selecting all costs of the date filter.
$sql_datefilter = "SELECT * FROM costs WHERE costdate BETWEEN '$date1_raw' AND 'date2_raw' AND userid='".$_SESSION['id']."'";
$result_dates = mysqli_query($conn, $sql_datefilter);
?>
<?php
while($row = $result_dates->fetch_assoc()) { ?>
<tr>
<td><?php echo $row['subcategory']; ?></td>
<td><?php echo $row['costname']; ?></td>
<!-- Added "€" sign infront of price !-->
<td><?php echo "€ " . $row['price']; ?></td>
<td><?php echo $row['info']; ?></td>
<!-- Added Date Function to convert format !-->
<td><?php echo date( "d/m/y",strtotime ($row['costdate'])); ?></td>
</tr>
<?php }
} else {
// costslist define mysql
$sql_costs = "SELECT * FROM costs WHERE userid='".$_SESSION['id']."'";
$result_costs = mysqli_query($conn, $sql_costs);
<?php
while($row = $result_costs->fetch_assoc()) { ?>
<tr>
<td><?php echo $row['subcategory']; ?></td>
<td><?php echo $row['costname']; ?></td>
<!-- Added "€" sign infront of price !-->
<td><?php echo "€ " . $row['price']; ?></td>
<td><?php echo $row['info']; ?></td>
<!-- Added Date Function to convert format !-->
td><?php echo date( "d/m/y",strtotime ($row['costdate'])); ?></td>
</tr>
}?>
}
Is this in the right direction?
I know that’s why i thought that adding “on client side” would be declaring that my bad .[quote=“droopsnoot, post:4, topic:243740”]
Think about date formats - I think if you’re using a date column in the database, you’ll need to switch the d/m/Y fields around from what the user enters otherwise BETWEEN won’t work. You’ll also need to validate the dates.
[/quote]
do you mean something like this?: $SelectedDate = new DateTime($date1_raw);
You’re in the right direction, but it can be further optimised. Remember, the only thing that varies if you have selected a date range is the query itself - everything else is the same.
<?php
$date1_raw = $_POST['selected_date1'];
$date2_raw = $_POST['selected_date2'];
if (!empty($date1_raw && !empty($date2_raw)) {
//Query for selecting all costs of the date filter.
$sql = "SELECT * FROM costs WHERE costdate BETWEEN '$date1_raw' AND '$date2_raw' AND userid='".$_SESSION['id']."'";
}
else {
$sql = "SELECT * FROM costs WHERE userid='".$_SESSION['id']."'";
}
$result = mysqli_query($conn, $sql);
while($row = $result->fetch_assoc()) { ?>
<tr>
<td><?php echo $row['subcategory']; ?></td>
<td><?php echo $row['costname']; ?></td>
<!-- Added "€" sign infront of price !-->
<td><?php echo "€ " . $row['price']; ?></td>
<td><?php echo $row['info']; ?></td>
<!-- Added Date Function to convert format !-->
<td><?php echo date( "d/m/y",strtotime ($row['costdate'])); ?></td>
</tr>
<?php }
}
Sorry, I didn’t notice the “on client side” in the title, my bad as well - I tend to not read the titles too much.
I’m not sure whether creating a DateTime object will help, or whether it’s just a case of extracting the d m and y fields from the string and formatting them backwards - that will depend on what type of column your costdate field is. I can’t remember off the top of my head, this is something I’d experiment with. Might be nothing more than making it “YYYY/MM/DD” for the query.
And the Euro-symbol probably won’t work like that, you should use $euro; in HTML. Personally I’d change all that output section so that instead of dropping in and out between PHP and HTML, you just have your PHP echo the various html tags.
<?php
if (!empty(date1_raw && !empty(date2_raw))) {
//Query for selecting all costs of the date filter.
$sql_datefilter = "SELECT * FROM costs WHERE costdate BETWEEN '$date1_raw' AND 'date2_raw' AND userid='".$_SESSION['id']."'";
$result_costs = mysqli_query($conn, $sql_datefilter);
} else {
// costslist define mysql
$sql_costs = "SELECT * FROM costs WHERE userid='".$_SESSION['id']."'";
$result_costs = mysqli_query($conn, $sql_costs);
}
?>
But the problem i encounter now is that nothing is displayed.
Does that have to do with the date format?
I just noticed i got these 2 errors:
The specified value “16-11-2016” does not conform to the required format, “yyyy-MM-dd”.
costs.php:180
The specified value “16-11-2016” does not conform to the required format, “yyyy-MM-dd”.
if (!empty(date1_raw && !empty(date2_raw))) {
^ and here ^
Variable names all start with a $ sign - look at the code in my post.
$sql_datefilter = "SELECT * FROM costs WHERE costdate BETWEEN '$date1_raw' AND 'date2_raw' AND userid='".$_SESSION['id']."'";
^ here
This isn’t valid either, for the same reason, missing $ sign.
What code is on line 180? That’s what the 180 on the end of the error message refers to. However, that goes back to what we were talking about above - you need to either make the user type the date in the format the database expects it (a Bad Thing, let them use the format they are familiar with), or take their input, split it into the separate fields and re-assemble it to use in the query.
the ‘$’ i missed at the variables fixed everything!
Everything updates automatically now.
The only thing left to do now is order them by date…
Any idea on how to do that?
Thankyou very much, So you would recommend using the prepare function for the queries then?
Any example with the query i linked? Cuz i looked at the documentation but don’t quite understand it for a query this long .
In a year or two, when you’re used to SQL a bit more, you’ll look back and chuckle to think that yours was a long query. (Reading it back, that sounds really patronising and it’s really not meant to be. But really, the query is not long or complex in the scheme of things).
I don’t use mysqli myself, I use PDO. In PDO you would do something like:
$db->prepare("select * from costs where costdate between :start and :end and userid = :uid");
$db->bindParam(":start", $startdate);
$db->bindParam(":end", $enddate);
$db->bindParam(":uid", $userid);
$db->execute();
(I should add, that might not be exactly correct syntax, I’m not at a point where I can do this stuff off the top of my head yet).
So you specify “placeholders” for each of the variables you are passing into the query. While that doesn’t look like it achieves much, it does take out any need for you to care about quotes around the values, and quotes inside the values, and a lot of other stuff.
There are many other things, for example once you’ve “bound” a variable to a query, you can re-execute the query when the value of that variable has changed, simply by calling execute() again.
Definitely worth reading up on and practising with.
I can use both PDO and mysqli_*. I strongly jagged that you use prepared statements. You are prone to SQL Injection. Stuffing raw data into the query string won’t help. As @droopsnoot mention, PDO is preferred, but mysqli_* can also give you an advantage over PDO.
Do you have an idea on how to do the prepared statements on:
if (!empty($date1_raw && !empty($date2_raw))) {
//Query for selecting all costs of the date filter.
$sql_datefilter = "SELECT * FROM costs WHERE costdate BETWEEN '$date1_raw' AND '$date2_raw' AND userid='".$_SESSION['id']."'ORDER BY costdate";
$result_costs = mysqli_query($conn, $sql_datefilter);
} else {
// costslist define mysql
$sql_costs = "SELECT * FROM costs WHERE userid='".$_SESSION['id']."'";
$result_costs = mysqli_query($conn, $sql_costs);
}
…
.