Kund = customer We know that we lack in security in our code but so far that is not the problem and this code will not go on to an public website.
This code sorts out values from an database and puts them in a table. We need to sort the customer S001 and everything between to S176 in a checkbox but we cant get BETWEEN to work with Varchar.
<!doctype html>
<html>
<head>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
<link rel="stylesheet" href="css.css">
<link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/morris.js/0.5.1/morris.css">
</head>
<body>
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "garp";
$conn = new mysqli ($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
/* $query = $_GET['query']; */
$orderkund = $_GET['Orderkundinput'];
$artikel = $_GET['Artikelinput'];
$startDate =$_GET['startDate'];
$endDate =$_GET['endDate'] ;
$kund =$_GET['kund'];
$kund2 =$_GET['kund2'];
$kunds;
$min_length = 0;
$artikelQuery="";
$orderkundQuery="";
$kundQuery="";
$kund2Query="";
$kundsQuery;
if(strlen($artikel) >= $min_length){
$artikel = htmlspecialchars($artikel);
$artikel = mysqli_real_escape_string($conn, $artikel);
$artikelQuery = " AND Artikelnr = '$artikel'";
}
if (strlen($orderkund) >= $min_length){
$orderkund = htmlspecialchars($orderkund);
$orderkund = mysqli_real_escape_string($conn, $orderkund);
$orderkundQuery = " AND OrderKund = '$orderkund'";
}
if(strlen($kund) >= $min_length){
$kund = htmlspecialchars($kund);
$kund = mysqli_real_escape_string($conn, $kund);
$kundQuery = " OR OrderKund = '$kund'";
}
if(strlen($kund2) >= $min_length){
$kund2 = htmlspecialchars($kund2);
$kund2 = mysqli_real_escape_string($conn, $kund2);
$kund2Query = " OR OrderKund = '$kund2'";
}
if(strlen($kunds) >= $min_length){
$kunds = htmlspecialchars($kunds);
$kunds = mysqli_real_escape_string($conn, $kunds);
$kundsQuery = " between 'S176'";
}
if (strlen($startDate) >= $min_length){
$startDate = htmlspecialchars($startDate);
$startDate = mysqli_real_escape_string($conn, $startDate);
$startDateQuery = " AND Orderdatum between '$startDate' ";
}
if (strlen($endDate) >= $min_length){
$endDate = htmlspecialchars($endDate);
$endDate = mysqli_real_escape_string($conn, $endDate);
$endDateQuery = "AND '$endDate'";
}
$query = $artikelQuery.$orderkundQuery.$kundQuery.$kund2Query.$kundsQuery.$startDateQuery.$endDateQuery;
if(strlen($query) >= $min_length){
$sql = "SELECT OrderHuvud.Ordernummer ,OrderHuvud.OrderserieIK ,OrderKund ,Fakturakund ,Orderdatum ,Erreferens ,Levereratvarde ,Radnummer ,Artikelnr ,Benamning ,Leveranstid ,Ursprungligtantal ,Levereratantal ,Forspris ,Bruttopris ,Varukostnad FROM garp.OrderHuvud left join garp.OrderRad on OrderHuvud.Ordernummer = OrderRad.Ordernummer where OrderHuvud.OrderserieIK = 'K'" .$query ;
echo $sql;
$raw_results = $conn->query ($sql);
$row_cnt = false === $raw_results ? 0 : $raw_results->num_rows;
echo " <p class='rows'> Numbers of rows loaded: $row_cnt </p>";
if($row_cnt > 0){
while($raw_result = mysqli_fetch_array($raw_results)){
echo "<table class='table'><thead class='thead-light'><tr><th class='col'>".'Ordernummer'."</th><th class='col'>".'OrderserieIK'."</th><th class='col'>".'Orderkund'."</th><th class='col'>".'fakturakund'."</th><th class='col'>".'orderdatum'."</th><th class='col'>".'erreferens'."</th><th class='col'>".'leveratvarde'."</th><th class='col'>".'radnummer'."</th><th class='col'>".'artikelnr'."</th><th class='col'>".'benamning'."</th><th class='col'>".'leveranstid'."</th><th class='col'>".'Ursprungligtantal'."</th><th class='col'>".'Levereratantal'."</th><th class='col'>".'forspris'."</th><th class='col'>".'bruttopris'."</th><th class='col'>".'varukostnad'."</th></tr></thead>";
echo "<tbody><tr><td>".$raw_result['Ordernummer']."</td><td>".$raw_result['OrderserieIK']."</td><td>".$raw_result['OrderKund']."</td><td>".$raw_result['Fakturakund']."</td><td>".$raw_result['Orderdatum']."</td><td>".$raw_result['Erreferens']."</td><td>".$raw_result['Levereratvarde']."</td><td>".$raw_result['Radnummer']."</td><td>".$raw_result['Artikelnr']."</td><td>".$raw_result['Benamning']."</td><td>".$raw_result['Leveranstid']."</td><td>".$raw_result['Ursprungligtantal']."</td><td>".$raw_result['Levereratantal']."</td><td>".$raw_result['Forspris']."</td><td>".$raw_result['Bruttopris']."</td><td>".$raw_result['Varukostnad']."</td></tr></tbody></table>";
}
}
else{
echo "No return";
}
}
else{
echo "Minimum length is ".$min_length;
}
$query = substr($query, 4);
$sql2 = "SELECT OrderKund,Artikelnr, SUM(Forspris) as Forspris,SUM(Levereratantal) as Levereratantal FROM orderhuvud
left JOIN orderrad on orderhuvud.Ordernummer = orderrad.Ordernummer
where ". $query ." GROUP BY OrderKund,Artikelnr";
$result = mysqli_query($conn, $sql2) or die(mysqli_error($conn));
while($row = mysqli_fetch_array($result)){
echo "<div class='continer'><table class='table'><thead class='thead-light'><tr><th class='col'>".'OrderKund'."</th><th class='col'>".'Artikelnr'."</th><th class='col'>".'Forspris'."</th><th class='col'>".'levereratantal'."</th></tr></thead></div>";
echo "<div class='continer'><tbody><tr><td>".$row['OrderKund']."</td><td>".$row['Artikelnr']."</td><td>".$row['Forspris']."</td><td>".$row['Levereratantal']."</td></tr></tbody></table></div>";
}
?>
</body>
</html>