I have a couple of forms:
In the first form I enter: Reservation date, customer information, and the table i am assigning them for that date.
I the second form I enter: Data to take an order, I enter in the date and the table and I want to know which reservation ID is linked to that.
Now i cannot figure out how to do this since the reservation date, table_number and reservation id are not getting pulled out of the reservervation table using the following query:
SELECT o.OrderID
, o.MenuItemID
, o.ReceiptID
, r.Res_Datum
, r.Tafel_Id
, r.Reservering_Id
FROM Orders o
JOIN reserveringen r
ON o.Res_ID = r.Reservering_Id
GROUP
BY o.Res_Datum
, o.Res_ID
, o.Tafel_Id
This query does work though:
$sql = "
SELECT O.Res_Datum
, O.Res_ID
, O.Tafel_Id
, O.ReceiptID
, SUM(MI.ItemPrice) TotalReceiptPrice
FROM Orders O
JOIN MenuItem MI
ON O.MenuItemID = MI.MenuItemID
GROUP
BY O.Res_Datum
, O.Res_ID
, O.Tafel_Id
";
Problem with this is that i have to enter everything manually even the reservation_id and then i can culculate what the total order price is of that table for that date.
I want the reservation_id from the reservation that is bound to the reserved table that day so i know who made what order. So I dont have to type it in manually.
I have looked up everything from JOINS, Vieuws, and multiple select statements but i cannot figure it out. Also tried foreign keys but this makes things even more confusing.
What I am trying to accomplish: 1.To make and vieuw reservervations and assign a table ( this works ) 2.To take an order, and see the totalorderprice of the table -> with reservation_id ( and date) ( this works but only when i enter the reservation_id manually into sql. )
Below is what I already have created. You see that I am almost done. Just need this last query to work, and would be very happy to know what I am doing wrong.
My database:
MenuItem:
MenuItemID int(11)
ItemName varchar(255)
ItemPrice double
orders:
OrderID int(11)
MenuItemID int(11)
ReceiptID int(11)
Res_Datum date
Tafel_Id int(11)
Res_ID int(11)
receipt:
ReceiptID int(11)
ReceiptPrice double
reserveringen:
Reservering_Id int(11)
Tafel_Id int(11)
VoorNaam varchar(255)
AchterNaam varchar(255)
TelefoonNummer varchar(255)
Email varchar(255)
Res_Datum date
Below I am going to add two of my php files for creating an order If you copy paste the code from the 2 forms and use the same name you should be able to recreate.
Bestelling.php:
<form action="/restaurant/maak_bestelling.php" method="POST">
<h2>Enter Order</h2>
Table Number:<br>
<input type="text" name="tafelnummer" value=""><br><br>
Receipt Id:<br>
<input type="text" name="receiptid" value=""><br><br>
Menu_Item:<br>
<input type="text" name="menu_item" value=""><br><br>
Date: <br>
<input type="date" name="date" value=""><br><br>
<input type="submit" value="Submit">
</form>
<h2>Pending Orders:</h2>
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "restaurant";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
//MY Query i am trying to run
// $sql = "SELECT O.Res_Datum, R.Reservering_Id, O.Tafel_Id,O.ReceiptID, SUM(MI.ItemPrice) AS TotalReceiptPrice
// FROM Orders, Reserveringen AS O INNER JOIN MenuItem AS MI ON O.MenuItemID = MI.MenuItemID
// AS O LEFT JOIN Reservering_Id AS R on O.Reservering_Id = R.reservering_Id
// GROUP BY O.Res_Datum, O.Res_ID, O.Tafel_Id ";
//The statement I get with an Empty reservation_ID
$sql = "SELECT O.Res_Datum, O.Res_ID, O.Tafel_Id,O.ReceiptID, SUM(MI.ItemPrice) AS TotalReceiptPrice
FROM Orders AS O INNER JOIN MenuItem AS MI ON O.MenuItemID = MI.MenuItemID
GROUP BY O.Res_Datum, O.Res_ID, O.Tafel_Id ";
$result = $conn->query($sql);
if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo "Res_datum: ". $row["Res_Datum"]. " ReservationID : " . $row["Res_ID"]. " - Table_Number: " . $row["Tafel_Id"]. " Total Order Price: " . $row["TotalReceiptPrice"]." ". "<br>";
}
} else {
echo "0 results";
}
mysqli_close($conn);
?>
</div>
maak_bestelling.php :
<?php
$con = mysqli_connect('localhost','root','');
if(!$con) {
echo 'Not connected with server';
}
if(!mysqli_select_db ($con,'restaurant')) {
echo 'Database Not selected';
}
$tablenumber = $_POST['tafelnummer'];
$receiptid = $_POST['receiptid'];
$menu_item = $_POST['menu_item'];
$date = $_POST['date'];
$sql = "INSERT INTO Orders (orders.Tafel_Id, orders.ReceiptID, orders.MenuItemID, orders.Res_Datum )
VALUES ('$tablenumber', '$receiptid', '$menu_item', '$date')";
if(!mysqli_query($con,$sql)){
echo 'insert did not work';
}else {
echo 'Order created successfully';
}
header("refresh:1; url=bestelling.php");
?>
I have two more files for creating the reservation, The form looks like this may you want to have an impression:
<form action="/restaurant/maak_reservering.php" method="POST">
Voornaam:<br>
<input type="text" name="voornaam" value=""><br><br>
Achternaam:<br>
<input type="text" name="achternaam" value=""><br><br>
Email:<br>
<input type="text" name="email" value=""><br><br>
Telefoonnummer:<br>
<input type="text" name="telefoonnummer" value=""><br><br>
Tafel:<br>
<input type="text" name="tafel" value=""><br><br>
Reserverings Datum:<br>
<input type="date" name="datum" value="dd//mm//yy"><br><br>
<input type="submit" value="Submit">
</form>
<div id="Tafels">
<h3>Gereserveerde Tafels</h3>
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "restaurant";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// $sql = "SELECT reserveringen.Tafel_Id, reserveringen.Res_Datum
// FROM reserveringen
// INNER JOIN tafels ON reserveringen.Tafel_Id=tafels.Tafel_Id";
// $result = $conn->query($sql);
//andere query welke ik heb geprobeerd met reservering nummer
//tafels.tafel_Nummer,reserveringen.Res_Datum,reserveringen.Reservering_Id FROM tafels INNER JOIN reserveringen ON reserveringen.Res_Datum = reserveringen.Res_Datum AND reserveringen.Reservering_Id = reserveringen.Reservering_Id
//$sql = "SELECT tafels.tafel_Nummer,reserveringen.Res_Datum,reserveringen.Reservering_Id FROM tafels LEFT JOIN reserveringen ON reserveringen.Res_Datum = reserveringen.Res_Datum AND reserveringen.Reservering_Id = reserveringen.Reservering_Id";
$sql = "SELECT Reservering_Id, Tafel_Id, Res_Datum, VoorNaam, AchterNaam FROM reserveringen ORDER BY Res_Datum DESC";
$result = $conn->query($sql);
if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo "reserveringID: ". $row["Reservering_Id"]. " tafelnummer: " . $row["Tafel_Id"]. " - Reservering_datum: " . $row["Res_Datum"]. " " . $row["VoorNaam"]." ".$row["AchterNaam"]. " ". "<br>";
}
} else {
echo "0 results";
}
mysqli_close($conn);
