-1

Im building a very simple trip-booking system. Im used to Filemaker and love the calculation fields it have. Im new at sql and I dont have the brains to figure out this following 2 things:

I have two tables "Trips" and "Bookings"

Trips: ID,MaxSpots,CurrentSpots

Bookings: ID,FK_ID,Spots

I want my customer to be able to click a row in the "Trips" table and be sent to a page that have a form to enter "Bookings" entries. There will be a hidden field "FK_ID that I want the "ID" from "Trips" to be filled in.

How do I check if entered "Bookings.Spots" is bigger than "Trips.CurrentSpots" and then have it calculate and update Trips.CurrentSpots ?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Jacob
  • 29
  • 4
  • Please add some code that shows what you have already tried, and also read https://stackoverflow.com/help/how-to-ask – EFrank Aug 24 '17 at 13:15
  • I have this on the php page that show a table with available trips: $query = "SELECT Spots FROM SO_Bookings, SO_Trips WHERE Spots < CurrentSpots and '$ID' = SO_Trips.ID"; $result = mysqli_query($dbConnected, $query); $dbconnected is the one im using to connect in the top of my page. Down in my table I've entered "$value = mysqli_fetch_assoc($result)" in my html table – Jacob Aug 24 '17 at 13:22
  • Would I be able to do something like this then: $sql = "SELECT SUM(Spots) FROM SO_Bookings WHERE FK_ID = ID FROM SO_Trips"; $bookedSpots = mysql_query($sql, MYSQL_ASSOC); $newCurrentSpots = SO_Trips.CurrentSpot - $bookedSpots And the just use $newCurrentSpots in my cell ?
     – Jacob Aug 25 '17 at 06:45

1 Answers1

0

You can just make a simple querie to compare both of them, just get the ID you need first:

//$conn should be your connection
$query = "SELECT Spots FROM Bookings, Trips WHERE Spots < CurrentSpots and '$ID' = Trips.ID";
$result = mysqli_query($conn, $query);

The result can be read like this if its only one

$value = mysqli_fetch_assoc($result)

Then just get the value from Booking.Spots and update your Trips table however you need with an UPDATE query.

JuanjoC
  • 185
  • 1
  • 14
  • Im not sure how to implement that – Jacob Aug 24 '17 at 12:08
  • @Jacob Where exactly are you having troubles? Do you already have the connection to the DB? – JuanjoC Aug 24 '17 at 12:11
  • Yeah I have a working php page with a submitting form to Bookings. but I dont know how to get the id from the clickable link on the Trips page. so they get linked. Then I dont know where to put your solution on the pages. – Jacob Aug 24 '17 at 12:15
  • @Jacob I updated the answer a little bit so you can see how to continue by yourself. – JuanjoC Aug 24 '17 at 12:24
  • Thanks but I still haven't got it to work. ill keep trying. btw what do you mean "if it only one" ? – Jacob Aug 24 '17 at 12:56