Select db rows between a date range

Question

Im trying to add a form so I can select a date range then resubmit the form so I can have rows only from a specific date range listed in the table, but I cant get it to work very well. Whats wrong in the code ? (php newbie alert) (UPDATED)

<html>
<head>
<meta name="viewport" content="width=device-width,initial-scale=1,maximum-scale=1,user-scalable=no">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<meta name="HandheldFriendly" content="true">
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" /> 
<title>EBS Service Skjema</title>
<link href="style.css" rel="stylesheet" type="text/css" />
<link href="print.css" rel="stylesheet" media="print" type="text/css" />
</head>

<body>
<?php
include 'connection.php';
// Check if session is not registered, redirect back to main page. 
// Put this code in first line of web page. 
session_start();
if (!isset($_COOKIE["user"])) {
header("location:login.php");
}
echo "<div class=\"blackbar\">Sjåfør:" .$_COOKIE["user"]."</div>"; 

$dbx = $_POST["databases"];
$con=mysqli_connect("$host", "$username", "$password","$db_name")or die("cannot connect"); 
?>
<div class="bluebox"><form action="data.php" method="post">
<h2 align="center">Utskrift av logger</h2>
<label>Velg type logg du vil skrive ut:</label><br />
<select name="databases">
  <option value="trallevask">Trallevask</option>
  <option value="bilvask">Bilvask</option>
  <option value="maintenance">Vedlikehold</option>
  <option value="diesel">Diesel Stats</option>
  <option value="workhours">Arbeidstid</option>
</select>
<input type="date" name="df" /><input type="date" name="dt" />
<input type="submit" value="Submit"><input type="button" value="Tilbake" onClick="parent.location='index.php'" />
<input type="button" value="Skriv ut" onclick="window.print();return false;" />

</form></div>

<?php
//Row Colors setting
$color1 = "#E1EEf4";
$color2 = "#FFFFFF";
$row_count = 0;

echo "<div class=\"datagrid\"><table width=\"100%\" cellspacing=\"0\" cellpadding=\"5\" border=\"0\">";
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

if ($dbx == "trallevask")   {
    //$result = mysqli_query($con,"SELECT trallevask.regnr, trallevask.date, trallevask.type, equipment.eqname AS vaske_type FROM trallevask JOIN equipment ON equipment.eqid = trallevask.type WHERE userid = ". $_COOKIE['userid']);
    $result = mysqli_query($con, "SELECT trallevask.regnr, trallevask.date, 
trallevask.type, equipment.eqname AS vaske_type 
FROM trallevask JOIN equipment ON equipment.eqid = trallevask.type 
WHERE userid = ". $_COOKIE['userid'] . " AND trallevask.date 
BETWEEN " . $_POST['df'] . " AND " . $_POST['dt']);
    echo "<thead><tr><th>Dato</th><th>Tralle nr.</th><th>Vaskemiddel</th><th>Utført av</th></tr></thead><tbody>";

        while($row = mysqli_fetch_array($result))
        {
            $row_color = ($row_count % 2) ? $color1 : $color2;
            echo "<tr bgcolor=\"$row_color\"><td>" . $row['date'] . "</td><td>" . $row['regnr'] ."</td><td>" . $row['vaske_type'] . "</td><td>".$_COOKIE['user']."</td></tr>";
            $row_count++;
        }
}

elseif ($dbx == "bilvask")  {
    //$result = mysqli_query($con,"SELECT * FROM bilvask, equipment");
    $result = mysqli_query($con,"SELECT bilvask.date, bilvask.type, equipment.eqname AS vaske_type FROM bilvask JOIN equipment ON equipment.eqid = bilvask.type WHERE userid = ". $_COOKIE['userid']);
    echo "<thead><tr><th>Dato</th><th>Beskrivelse</th></tr></thead><tbody>";

        while($row = mysqli_fetch_array($result))
        {
            $row_color = ($row_count % 2) ? $color1 : $color2;
            echo "<tr bgcolor=\"$row_color\"><td>" . $row['date'] . "</td><td>" . $row['vaske_type'] ."</td></tr>";
            $row_count++;
        }

}

elseif ($dbx == "workhours")    {
    $result = mysqli_query($con,"SELECT * FROM workhours WHERE userid = ". $_COOKIE['userid']);
    echo "<thead><tr><th>Dato</th><th>Status</th><th>Klokka</th></tr></thead><tbody>";

        while($row = mysqli_fetch_array($result))
        {
            $row_color = ($row_count % 2) ? $color1 : $color2;      
            echo "<tr bgcolor=\"$row_color\"><td>" . $row['date'] . "</td><td>" . $row['status'] ."</td><td>" . $row['time'] . "</td></tr>";
            $row_count++;       
        }

}

elseif ($dbx == "diesel")   {
    $result = mysqli_query($con,"SELECT * FROM diesel WHERE userid = ". $_COOKIE['userid']);
    echo "<thead><tr><th>Dato</th><th>Km.stand</th><th>Liter</th><th>KR/L</th><th>Stasjon</th><th>Sted</th></tr></thead><tbody>";

        while($row = mysqli_fetch_array($result))
        {
            $row_color = ($row_count % 2) ? $color1 : $color2;      
            echo "<tr bgcolor=\"$row_color\"><td>" . $row['dato'] . "</td><td>" . $row['km'] . "</td><td>" . $row['liter'] . "</td><td>" . $row['krl'] . "</td><td>" . $row['stasjon'] . "</td><td>" . $row['sted'] . "</td></tr>";
            $row_count++;       
        }

}  

elseif ($dbx == "maintenance")  {
    $result = mysqli_query($con,"SELECT * FROM maintenance WHERE userid = ". $_COOKIE['userid']);
    echo "<thead><tr><th>Dato</th><th>Km.stand</th><th>Reg.nummer</th><th>Beskrivelse</th></tr></thead><tbody>";

        while($row = mysqli_fetch_array($result))
        {
            $row_color = ($row_count % 2) ? $color1 : $color2;      
            echo "<tr bgcolor=\"$row_color\"><td>" . $row['date'] . "</td><td>" . $row['mileage'] ."</td><td>" . $row['registration'] ."</td><td>" . $row['info'] ."</td></tr>";
            $row_count++;       
        }

}  

else { 
        echo "Velg en rapport fra menyen over!";
    }

echo "</tbody></table></div>";

mysqli_close($con);
?>
</body>
</html>

Have you got any error? And explain I cant get it to work very well. — Hüseyin BABAL
– Hüseyin BABAL, Commented Apr 12, 2014 at 16:27

Jayesh Chandrapal · Accepted Answer · 2014-04-12 16:34:50Z

1

May be this will help. You haven't used df and dt parameters in SQL query:

$result = mysqli_query($con, "SELECT trallevask.regnr, trallevask.date, 
trallevask.type, equipment.eqname AS vaske_type 
FROM trallevask JOIN equipment ON equipment.eqid = trallevask.type 
WHERE userid = ". $_COOKIE['userid'] . " AND trallevask.date 
BETWEEN " . $_POST['df'] . " AND " . $_POST['dt']);

answered Apr 12, 2014 at 16:34

Jayesh Chandrapal

6847 silver badges11 bronze badges

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

8 Comments

Roger Andersen Over a year ago

thanks, still working on this. I have added the whole form into the same page. I will update the post with the new code, but I still can't make it work though.

Jayesh Chandrapal Over a year ago

You can check php server logs to see what exactly is causing it to not work. Post your logs if you are getting any errors.

Roger Andersen Over a year ago

I tried to view the apache error log, but I cant see any errors for this project. :( Is there any other logs I should check ?

Roger Andersen Over a year ago

In my database the date field is varchar and formatted like this 2014-04-03

Jayesh Chandrapal Over a year ago

For varchar date field, you can use WHERE str_to_date(fromdate,'%d/%m/%Y') BETWEEN '29/09/2012' AND '07/10/2012'. fromdate is your post parameter. I hope you get idea.

|

LukasMac · Accepted Answer · 2014-04-12 16:40:02Z

0

First thing I see that HTML is formatted incorrectly:

Change line:

echo "<div class=\"datagrid\"><table width=\"100%\" cellspacing=\"0\" cellpadding=\"5\"     border=\"0\"><input type=\"submit\" /></form>";

to

echo "<input type=\"submit\" /></form><div class=\"datagrid\"><table width=\"100%\" cellspacing=\"0\" cellpadding=\"5\" border=\"0\">";

Also in the very last line before ?> add </table></div> because you need to close DIV and TABLE tags.

I also can't see where $_POST["databases"]; is coming from, that might cause code to work incorrectly.

edited Apr 12, 2014 at 16:40

answered Apr 12, 2014 at 16:33

LukasMac

8981 gold badge8 silver badges20 bronze badges

Comments

user3317346 · Accepted Answer · 2014-04-12 16:51:17Z

0

select * from table where date >= [start date] and date <= [end date] or (i'm not 100% sure if this works in mysql but it does in postgres!)

select * from table where date between [start date] and [end date]

answered Apr 12, 2014 at 16:51

user3317346

33 bronze badges

Collectives™ on Stack Overflow

Select db rows between a date range

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related