Have a problem to search between date and calculate some numbers

tedi · June 21, 2021, 6:15pm

1.when I started searching by date in the upper left corner I get absolutely all the displayed data? When I press the calculate button, all the information disappears?When I press the calculate button, only the tag remain? With the button SearchDate i Search date With the button “suma” i calculate the line.PLS HELP

require_once("connect.php");
if(isset($_POST['from_date']) && isset($_POST['to_date']))
{
        $from_date = $_POST['from_date'];
        $to_date = $_POST['to_date'];
        $sql="SELECT date, food_and_drinks, salary, insurance, gas, current, water, unexpected_expense FROM expense WHERE '$from_date' AND '$to_date'"; 
        $quer=mysqli_query($conn,$sql) or die ('error sql');
        while ($resul=mysqli_fetch_array($quer))
        {
            echo $resul['date'].$resul['food_and_drinks'].$resul['salary'].$resul['insurance'].$resul['gas'].$resul['current'].$resul['water'].$resul['unexpected_expense'];
        }
}
elseif(isset($_POST['sum']))
{
    $sql="SELECT  (expense.food_and_drinks+expense.salary+expense.insurance+expense.gas+expense.current+expense.water+expense.unexpected_expense) as Total 
    FROM expense "; 
    $quer2=mysqli_query($conn,$sql2)or die ('error sql2');
}
else
{   
    $sql="SELECT expense.date, food_and_drinks, salary, insurance, gas, current, water, unexpected_expense FROM expense";
    $quer=mysqli_query($conn,$sql)or die ('error sql');     
}
    
?>
<html>
    <head
    </head>
    <body>
        <center>
            <form action="<?php echo $_SERVER["PHP_SELF"]; ?>" method="POST" >
            от<input type="date" name="from_date"  value="<?php if(isset($_POST['from_date'])){ echo $_POST['from_date']; } ?>"> до
                <input type="date" name="to_date"  value="<?php if(isset($_POST['to_date'])){ echo $_POST['to_date']; } ?>">
                <input type="submit" name="" value="SearchDate">
                <table border="1" width ="40%">
                <tr>
                    <th>date</TH><th>food</TH><th>salary</TH><th>insurance</TH><th>gas</TH><th>current</TH><th>water</TH> <th>unexpected_expense</TH><th>Total Price</TH><th>Delete</TH>
                </tr>
<?php
while ($resul=mysqli_fetch_array($quer))
    {?>
        <tr>
        <td><input type="date" name="date"  placeholder="yyyy/mm/dd" value="<?php echo $resul['date'];?>"></td>
                <td><input type="text" name="food_and_drinks"  value="<?php echo $resul['food_and_drinks'];?>"></td>
                <td><input type="text" name="salary"  value="<?php echo $resul['salary'];?>"></td>
                <td><input type="text" name="insurance"  value="<?php echo $resul['insurance']; ?>"></td>
                <td><input type="text" name="gas"  value="<?php echo $resul['gas']; ?>"></td>
                <td><input type="text" name="current"  value="<?php echo $resul['current']; ?>"></td>
                <td><input type="text" name="water"  value="<?php echo $resul['water']; ?>"></td>
                <td><input type="text" name="unexpected_expense"  value="<?php echo $resul['unexpected_expense']; ?>" ></td>    
                <td><input type='submit' name='sum' value="suma"/>
                <?php
                    if(isset($_POST['sum'])) $resul2=mysqli_fetch_array($quer2);
                ?>
                <input class="totPro" type ="text" value="<?php if(isset($_POST['sum'])) echo $resul2['Total'];?>"  disabled=""/> </td>
                <td><a href="delete2.php?food_and_drinks=<?php echo $resul["food_and_drinks"]; ?>">Delete</a></td>
            </tr>   
    <?php
    }

    ?>

</table>

ErnieAlex · June 21, 2021, 7:40pm

Well, you did not say what formats your DB table is for this field, nor the format of the input fields.
Here is the general way you would do it. But, you may need to adjust this depending on formats used!

SELECT *
FROM expense
WHERE (date_field BETWEEN '2010-01-30 14:15:55' AND '2010-09-29 10:15:55')

Hope that helps…

tedi · June 21, 2021, 7:49pm

it give me “error sql” when i replace my select…
in my DB the column DATE is in ‘date’ format the rest of them is in 'int ’

ErnieAlex · June 21, 2021, 8:14pm

Well, that takes care of the DB table, what format is your data input in?
Is it a string where the user types something in? That never works as they can type in anything.
Is it a date-picker plugin? If so, you would need to pull out the time from it.
Is it a select drop-down where you lock in the dates?

More information please and we can solve it for you…

phdr · June 21, 2021, 11:03pm

You need error handling for the database statements that will actually help you by either displaying (when learning, developing, and debugging) or logging (when on a live server) the actual sql error information. Remove all the or die(…) logic and add the following line of code before the point where you make the mysqli connection -

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

This will cause database statement errors to use exceptions. If you then simply let php catch and handle these exceptions, php will automatically display or log the database statement errors the same as php errors. This requires that you have php’s error_reporting set to E_ALL (it should always be this value), and either set display_errors to ON or set log_errors to ON. These settings should be in the php.ini on your system so that you can set or change them at a single point.

tedi · June 22, 2021, 9:28am

Is it a select drop-down where you lock in the dates?
So when i select the first date from the left side then i select the second date and i pust the button “SearchDate” then it should show me the info between the date

ErnieAlex · June 22, 2021, 1:16pm

Then, just change the query to fit it.

$query = "SELECT *
FROM expense
WHERE (date_field_name BETWEEN '$from_date' AND 
$to_date' )";

Something like that should work. But, you are NOT using a select drop-down, you are using a date-picker plugin. Therefore, it depends on the format from the output of the plugin. You need to test it by displaying the values that are retrieved from the two plugin’s once the form is posted and see what data is being captured. Hope that makes sense to you!

phdr · June 22, 2021, 2:51pm

@ErnieAlex, type=“date” is a html5 thing. Modern browsers support it and implement a date-picker. The displayed values use the browser’s local/language setting. The submitted value is a standard YYYY-MM-DD format.

@tedi, the incorrect operation of your code is due to logic that doesn’t make sense and in the case of displaying all the data after you have picked dates, is due to what @ErnieAlex has shown. The WHERE term must reference your date column. The way to fix this logic is to first define, for each case, what input data you will have, what processing you are going to do based on that input data, and what result or output you are going to produce. Then design, write, test, and debug the code needed to implement the definition.

Also, before proceeding, you should be using $_GET inputs for things that determine what data will be displayed on a page. $_POST inputs are used for things that will cause an action on the server, such as inserting, updating, or deleting data. If someone searches for data and they want to return to the same search result later, they should be able to book-mark the URL of the current page and return to that page using that saved URL. Using $_GET inputs will allow this.

So, when a user first visits this page and there are no from/to date inputs, what result or output do you want? Do you want to display all the data? If someone selects just a from date, do you want to display the data starting with that date? If someone selects just a to date, do you want to display the data up to that date? If someone selects both a from and a to date, do you want to display the data between those dates?

Next, for the suma button what exactly are you trying to accomplish? You are currently outputting one form with fields repeated for each row of data (which actually won’t work because only the last form field for each field name will get submitted.) If you are just trying to display the total for each row, just calculate that and display it when you display the row.

ErnieAlex · June 22, 2021, 3:55pm

@phdr, missed the type=‘date’
So, to solve his question, my query should work for him.

tedi · June 22, 2021, 4:21pm

When i replace ‘$sql’ whit $query = “SELECT * FROM expense WHERE (date_field_name BETWEEN ‘$from_date’ AND $to_date’ )”; it give my ‘error sql’ yes i replace the name . and even if i replace _POST with _GET when i click the botton do nothing.
P.S. do you have Discord or somthing else where we can talk if i may ask.TY FOR YOUR TIME

ErnieAlex · June 22, 2021, 4:33pm

You are using POST, do not change that to GET.

Sorry, you should have it $sql = … My example was just to show how to do the WHERE part.
So, something like this should work.

$sql="SELECT date, food_and_drinks, salary, insurance, gas, current, water, unexpected_expense FROM expense WHERE date BETWEEN '$from_date' AND '$to_date'";

tedi · June 22, 2021, 4:46pm

It work at 75%
I have upload data at 11.06.21 and when i search between 12.06-22.06 it show rigth result.
but why in this format i dont know.

ErnieAlex · June 22, 2021, 5:07pm

As PHDR mentioned, the default is YYY-MM-DD, but, it appears you may have changed that.

You need to capture your inputs, display them and that way you can look in your database and compare.
Add a DIE to it to show you what the form is really posting back to the PHP code. Then, look in your database and see how you have been saving dates. It appears that 21 is NOT 2021. Therefore, you would need to alter the query to just use the 21. But, look at the data and let us know.

tedi · June 22, 2021, 5:15pm

I hope i put it in the right place
if(isset($_POST[‘from_date’]) && isset($_POST[‘to_date’]))
{
$from_date = $_POST[‘from_date’];
$to_date = $_POST[‘to_date’];
$sql=“SELECT date, food_and_drinks, salary, insurance, gas, current, water, unexpected_expense FROM expense WHERE date BETWEEN ‘$from_date’ AND ‘$to_date’”;
$quer=mysqli_query($conn,$sql) ;
die($_POST[‘from_date’]."-to-".$_POST[‘to_date’]);
while ($resul=mysqli_fetch_array($quer))
{
echo $resul[‘date’].$resul[‘food_and_drinks’].$resul[‘salary’].$resul[‘insurance’].$resul[‘gas’].$resul[‘current’].$resul[‘water’].$resul[‘unexpected_expense’];
}
}
P.S. can talk it will go more fast .TY FOR YOUR HELP

ErnieAlex · June 22, 2021, 5:32pm

So, you have verified that the input field is giving you results correctly. YYYY-MM-DD
One thing verified. Remove the DIE now.

The second part was to see how your database is storing dates in the “date” field. Go into your database console and review the data you have stored there. I did mention that in the previous post.

If your stored data is correctly formatted, the date’s should be in format YYYY-MM-DD.
My guess is that you did NOT store them that way. Let us know…

( I have too much going on and just keep checking back in for new posts. No time today to talk live! Sorry! My city is digging up my road and I have no water and very busy with other work. )

tedi · June 22, 2021, 5:43pm

THIS is the database where i store the data it is yyyy-mm-dd
when i remove die… again show me the resolt in the left corner
and the date is of type ‘date’

ErnieAlex · June 22, 2021, 6:04pm

So, your query is correct. It should work.

$sql="SELECT date, food_and_drinks, salary, insurance, gas, current, water, unexpected_expense FROM expense WHERE date BETWEEN '$from_date' AND '$to_date'";

I see nothing wrong with it. But, maybe your dates are not being placed into the query correctly.
To check that debug it with another DIE. Like this:

$sql="SELECT date, food_and_drinks, salary, insurance, gas, current, water, unexpected_expense FROM expense WHERE date BETWEEN '$from_date' AND '$to_date'";
die($sql);

And, this will show you want is being passed to MySQL. You can copy the output and run it in the database control panel under the SQL tab. Just copy the displayed query and paste it into the SQL tab and run it and see what is really returned in the database system.

Have to leave for awhile… Talk to you later on…

tedi · June 22, 2021, 6:11pm

when it searchdate

tedi · June 22, 2021, 6:11pm

and the result in SQL tab.

ErnieAlex · June 22, 2021, 7:11pm

So, it works. Now, just one further thing…

You are parsing the results as arrays, but, you are displaying them as ASSOCIATED data.
Change this:

while ($resul=mysqli_fetch_array($quer))
    {

To this:

while ($resul=mysqli_fetch_assoc($quer))
    {

Should work!