0

Im trying to get a sum of Bookings.Spots if the Bookings.FK_ID is equal to Trips.ID - Whats wrong with my code? :

I have 2 tables. Table 1: "Trips" with the fields "ID" and "MaxSpots". Table 2: "Bookings" with the fields "FK_ID" and "Spots" 

$sumSpots = "SELECT SUM(Spots) FROM Bookings INNER JOIN 
Trips ON Bookings.FK_ID = Trips.ID"; 
$bookedSpots = mysql_query($sumSpots, MYSQL_ASSOC);

FILE: https://wetransfer.com/downloads/7ec0ee4ecf91718f26804f46f84bf00b20170829113434/0682b9

Im having problems pasting a lot of code to look good :)

Jacob
  • 29
  • 4
  • 2
    You need to join the trips table. This is very, very basic. Take a SQL tutorial. – juergen d Aug 28 '17 at 20:03
  • I am actually but I have to start somewhere :) – Jacob Aug 29 '17 at 06:35
  • **The `mysql` PHP extension is dead** -- Stop using the [`mysql` PHP extension](http://php.net/manual/en/function.mysql-connect.php). It is old, deprecated since PHP 5.5 and completely removed in PHP 7.0. Use [`mysqli`](http://php.net/manual/en/book.mysqli.php) or [`PDO_mysql`](http://php.net/manual/en/ref.pdo-mysql.php) instead. Read the answers to [this question](https://stackoverflow.com/q/12859942/4265352) to learn more about why and how. – axiac Aug 29 '17 at 08:39

1 Answers1

1

Your wrong thing is that you are not adding Trips table in FROM or JOIN. Your query is wrong and you haven't showed us the table structures, so based on my understanding you might need to fix your query is follows:

$sql = "SELECT SUM(Spots) AS SpotsSum FROM Bookings
INNER JOIN Trips ON Bookings.FK_ID = Trips.ID";
$result = mysql_query($sql);
$row = $result->fetch_assoc();
print $row['SpotsSum'];

I believe it didn't work for you because you are returning an associative array without specifying the field name, you need to give it an alias.

Lamar
  • 1,761
  • 4
  • 24
  • 50
  • I have 2 tables. Table 1: "Trips" with the fields "ID" and "MaxSpots" Table 2: "Bookings" with the fields "FK_ID" and "Spots" – Jacob Aug 29 '17 at 06:56
  • Ive tried the following, but its not working when i echo the $bookedSpots: $sumSpots = "SELECT SUM(Spots) FROM Bookings INNER JOIN Trips ON Bookings.FK_ID = Trips.ID"; $bookedSpots = mysql_query($sumSpots, MYSQL_ASSOC); – Jacob Aug 29 '17 at 07:17
  • Please update your question above. add details about the structure of the tables and the code you have tried. This way other can help you. – Lamar Aug 29 '17 at 08:49
  • Thanks, check my updated answer. Please mark it as solution if it works for you. – Lamar Aug 29 '17 at 09:06
  • Still having problems. Got it to return back with some text like "array #6" etc but not any closer. Does it matter that im putting this inside a that have a "while row array" to list the other fields not mentioned?
     – Jacob Aug 29 '17 at 10:03
  • seems like it's returning an array, check my last update then :) – Lamar Aug 29 '17 at 11:11
  • Im changing the names $sql, $row etc to $sql1, $row1 etc to not interfere with the other code. doesn't render page now... im supposed to use the "$row1['SpotsSum']" in my correct? btw theres lacking a ";" in your forst line ?
     – Jacob Aug 29 '17 at 11:25
  • Yes by all means, add the semi colon, and don't forget the print in the last line, I've just added it. – Lamar Aug 29 '17 at 11:27
  • I can send you a file if you want to se it in context ? – Jacob Aug 29 '17 at 11:27
  • Post it's content in the question, no harm in that. – Lamar Aug 29 '17 at 11:28