SELECT multiple variable columns

Question

I'm making a restaurant reservation module using PHP.

The problem is that I would like to make the user choose which time they would like to come and which time the would like to go.

Between these two times the table needs to be occupied, so there needs to be a name in that cell between those times.

I have no idea how to get this done.

Here is my existing code:

$day="0".$_POST['day'];
$month="0".$_POST['month'];
$year=$_POST['year'];
$date=$year ."-". $month ."-". $day;
$table=$_POST['table'];
$name=$_POST['name'];
$fromtime=$_POST['fromtime'];
$untiltime=$_POST['untiltime'];
$tablenumber = $table;

$host="localhost";
$user="root";
$password="root";
$database="restaurant";
$connection = mysql_connect ($host, $user, $password)
    or die ("could not connect with server");
$db = mysql_select_db ($database, $connection)
    or die ("could not connect with db");

$query = mysql_query("SELECT * FROM tables WHERE table='$tablenumber' AND date = '$date'");
?>

What's the structure of your tables table? By the way, if possible, stop using mysql_ php.net/manual/en/intro.mysql.php — cheesemacfly
– cheesemacfly, Commented Dec 20, 2012 at 16:51
Also, you have made no call to update the value in the table - only selecting it. — Jon Peterson
– Jon Peterson, Commented Dec 20, 2012 at 16:54
Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial. — Kermit
– Kermit, Commented Dec 20, 2012 at 17:11

Marc · Accepted Answer · 2012-12-20 17:24:02Z

1

I guess you have a one to many relationship with a table for the reservations (if they can only reserve 1 table), so you need an insert statement to add a reservation

Here some pseudo code

INSERT INTO reservation (tableid, startTime,endtime,reservationName) VALUES (tableid, yourFronttime, yourEndTime, reservationName)

but before inserting you have to check if the table is free like this

SELECT tableId
FROM reservation 
WHERE (startTime BETWEEN yourFrontTime AND untilTime) OR (endtime BETWEEN yourFrontTime AND untilTime) 
AND TableId = yourId

If you got results with that select they got to choose another time or another table.

edited Dec 20, 2012 at 17:24

answered Dec 20, 2012 at 17:05

Marc

16.6k20 gold badges80 silver badges121 bronze badges

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

2 Comments

Kermit Over a year ago

Why on Earth must you SELECT * to see if records exist? A simple SELECT COUNT(*) would suffice.

Marc Over a year ago

it's only an example... I don't think they will copy/paste this speudoCode... You don't even need a Count(*).. you could select any column and it's only for guidance, I didn't even concat or use a prepared statement for the parameters. But I will edit my answer just in case. Thank you.

Collectives™ on Stack Overflow

SELECT multiple variable columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related