Query/Subquery assistance (counting available rows)

[PodiumAssault]

Hi. I'm terribly novice when it involves MySQL queries so I've tried to figure out this problem but I just can't seem to work around it. I think I learn best when someone else shows me what I've done wrong, or presents a working version and I can then break it down to see where my errors were. So I apologize for the novice code that is about to follow in this post :)

http://beta.podiumassault.com/setupgarage.php

- On that page, the users are prompted to select a vehicle.
- Once they select a vehicle (inserted into URL via GET variable) they are presented with a list of tracks to select.
- Once they select a track (inserted into URL via GET, along with vehicle) they are presented with a list of setups available for that combination.

(use Riley Daytona Prototype and then Daytona International Speedway - Road, to see it working fine)

That works just fine and dandy, but it is a bit tedious and uninformative. I figured the best way was to show how many setups are available for each vehicle from the start.

So I queried the database for the vehicle names, and used a WHILE loop to display the names and then query the database again inside the WHILE loop to count the setups available. This gives me an error when the second mysql_fetch_array comes around, saying it isn't a valid resource. Perhaps it is my novice coding biting me in the bottom.

I've tried and tried to get it to work while it cycles through the vehicles, but I just can't figure it out. Below I've included the database schema as well as the horrible PHP code I've come up with to get the page to work.

CARS table
- carID
- carnameshort
- carnamelong
- carstatus (active/inactive)

TRACKS table
- trackID
- tracknameshort
- tracknamefull
- trackstatus (active/inactive)

SETUPS table
- setupID
- creatorID
- carnameshort
- tracknameshort
- filename
- availability (public/private)
(there are more fields, but I dont think they are relevant)

Here is the PHP coding with the inlaid MySQL queries where I pull the information. I'll love to see any suggestions on protecting the data from injection, and/or just general code tips.

I use a file for functions, and I connect to the database with one (dbconnect) and then select a database with another (selectdb). Here is the code, laugh away. ;)

(coding questions at the bottom)

<?php
// Display the setups if both variables are set.
if (isset($_GET['vehicle']) && isset($_GET['track'])) {
// Assign the GET variables to something simpler
$vehiclesearch = $_GET['vehicle'];
$tracksearch = $_GET['track'];
$viewability = "Public";

// Connect to the database and start pulling information
dbconnect();
selectdb();

$setupQuery = "SELECT * FROM setups WHERE carnameShort='$vehiclesearch' AND tracknameShort='$tracksearch' AND availability='$viewability' ORDER BY setupID";
$setupResult = mysql_query($setupQuery);

// check to see if there are any setups or not
if (mysql_num_rows($setupResult) == 0) {
echo "There are no setups available at this car and track combination, please check back later!";
echo "<meta http-equiv='refresh' content='3;url=setupgarage.php' />";
}
else {
// Display the list of setups
while ($setupData = mysql_fetch_array($setupResult)) {
echo $setupData['setupFilename'] . "<br />";
}
}


}

// Display the tracks if the vehicle variable is the only one set
elseif (isset($_GET['vehicle'])) {
// Connect to the database and select the table
dbconnect();
selectdb();

$getVehicle = $_GET['vehicle'];

// Query the database for the track information
$trackQuery = "SELECT tracknameFull, tracknameShort, trackStatus FROM tracks WHERE trackStatus='active' ORDER BY tracknameFull";
$trackResult = mysql_query($trackQuery) or die(mysql_error());

while($trackData = mysql_fetch_array($trackResult)) {
echo "<a href=\"" . $_SERVER['PHP_SELF'] ."?vehicle=".$getVehicle."&track=".$trackData['tracknameShort']."\">".$trackData['tracknameFull']."</a><br />";
}
}
// Display the vehicles if no variables are set
else {
// Connect to the database and select the table
dbconnect();
selectdb();

// Query the database for the car information
// RETURNS AT LEAST A RESULT ==> $carQuery = "SELECT c.carnameFull, c.carnameShort, COUNT(*) as count FROM cars c, setups s WHERE c.carStatus='active' AND s.availability='Public' GROUP BY c.carID";
$carQuery = "SELECT carnameFull, carnameShort FROM cars WHERE carStatus='active' ORDER BY carID";
$carResult = mysql_query($carQuery) or die(mysql_error());

while($carData = mysql_fetch_array($carResult)) {
echo "<a href=\"" . $_SERVER['PHP_SELF'] ."?vehicle=".$carData['carnameShort']."\">".$carData['carnameFull']."</a> (". $carData['setupCount']." setups available)<br />";
}
}

?>
There are two things I would really LOVE to learn how to do (or at least figure them out)

1) Display the total setups available next to each car upon the initial page view. (tried this and failed)

2) Once a vehicle is selected, show the tracks available that ONLY have at least one setup available for them, rather than a list of 50 tracks. (no idea how to do this :( )

I do apologize for the length of this post, and if the request is too much. I'm just lost with this.

Thanks,
Ryan

[Old Pedant]

...and this stupid forum *ATE* it!

AAARRRGGGHHH!!!

Okay, I'm going to post this in THREE PARTS, in case it was the size of the reply that triggered the problem.

First:

In your current DB design, the fields
- carID
- carnameshort
and
- trackID
- tracknameshort
are redundant.

In the SETUPS table, you use carnameshort and tracknameshort as foreign keys to the Cars and Tracks tables. That's okay, but usually you could use CarID and TrackID.

If the only reason for existence for carnameshort and tracknameshort are to provide those foreign keys, then dump them. Use the ID's only.

If you really need the "short"s for other purposes, then dump the ID's. So long as you have both, you risk confusion and possible DB errors.

For my next two answers, I will ASSUME that you are dumping the "shorts" and going with the IDs. But, again, it's okay to stick with the shorts if you dump the IDs. Just alter my queries accordingly.

[Old Pedant]

Okay, I am *NOT* a PHP person. ASP, JSP, ASP.NET? Okay. PHP? Two weeks in 1999. So I'll give you SQL and you can translate to PHP usage.

1) Display the total setups available next to each car upon the initial page view.

SELECT C.CarID, C.CarNameLong, COUNT(S.CarID) AS AvailableSetups
FROM Cars AS C LEFT JOIN Setups AS S ON C.CarID = S.CarID
WHERE C.carstatus = 'active'
GROUP BY C.CarID, C.CarNameLong
ORDER BY C.CarNameLong

That will show you *all* 'active' cars, even those with ZERO setups available.

If you only want cars with at least one available setup, just change the LEFT JOIN to an INNER JOIN. No other change should be needed.

[Old Pedant]

Once a vehicle is selected, show the tracks available that ONLY have at least one setup available for them

SELECT T.TrackID, T.TrackNameFull, COUNT(*) AS AvailableSetups
FROM Tracks AS T INNER JOIN Setups AS S ON T.TrackID = S.TrackID
WHERE S.CarID = $CARID
AND T.trackstatus = 'active'
GROUP BY T.TrackID, T.TrackNameFull
ORDER BY T.TrackNameFull

That will give you the number of setups on each track (no zeroes, because of the INNER JOIN) which you can ignore if you don't need it.

**************

Notice that I at least know enough about PHP to realize you'll dump the selected carid into the query via $CARID or similar.

**************

Untested, off the top of my head in this stupid little text box. Nevertheless warranted for 30 picoseconds or 30 picometers, whichever comes first, against all bedbugs, fleas, and bugs with 12 or more legs.

[PodiumAssault]

Wow, thank you very much! I've taken your queries and dropped them into the code and it works like a charm.

I'll have to do a bit of research on the structure of the queries to figure out what occured and where/why/how, but thank you a ton.

I'll look into the database redundancy issue as well, thanks for the heads up on that. My mindset when creating the table for the tracks was to have an ID for simple logging purposes, and the tracknameShort for the URL $GET variable so it would be easy to query the database with it, while keeping the user of the website at ease with what car/track combination they were looking at.

setupgarage.php?vehicle=RileyDP&track=Daytona-Road
versus
setupgarage.php?vehicle=1&track=34

I see what you mean about the redundancy, though. I could basically just eliminate the trackID/carID because in reality they hold no real value if I continue to use the Short variables in the query and nothing else.

Thank you thank you thank you, though. The forum ate my initial post as well, hence the choppy and probably glossed over version posted. :)

-- Ryan

[Old Pedant]

I'll change over to your short names in this version, in place of the IDs.


SELECT C.CarNameSHort, C.CarNameLong, COUNT(S.CarNameShort) AS AvailableSetups
FROM Cars AS C LEFT JOIN Setups AS S ON C.CarNameShort = S.CarNameShort
WHERE C.carstatus = 'active'
GROUP BY C.CarNameShort, C.CarNameLong
ORDER BY C.CarNameLong

First, I hope you understand the concept of "aliasing" table names. CARS AS C, for example. That just gives me the ability to refer to the CARS table through the shorthand abbreviation "C" instead of having to spell it out every time. YOU CAN SPELL IT OUT! (But you can mix aliasing with spelling out...one or the other, only.)

Now, we need to consider the meaning of a LEFT JOIN (there's also an analogous RIGHT JOIN...my brain thinks better left-to-right so I only use RIGHT JOIN when desparate).

When you do Cars LEFT JOIN Setups, you are saying that you want *ALL* records in the Cars table NO MATTER WHAT [except see below] and then you ALSO want all records in the Setups table that match those Cars records. EVEN IF THERE ARE NO MATCHES!!!

That is, you want all the LEFT records (hence "LEFT join") and any right records that happen to match.

So if your tables had, say:

Cars:
audi, audi owned by Audrey
bmw, beemer owned by Bob

Setups:
audi, track34
audi, track91
audi, track99

Then the LEFT JOIN would produce pseudo-records that consisted of

C.audi, C.audi owned by Audrey, S.audi, S.track34
C.audi, C.audi owned by Audrey, S.audi, S.track91
C.audi, C.audi owned by Audrey, S.audi, S.track99
C.bmw, C.beemer owned by Bob, NULL, NULL

If you like, you could consider that a "pseudo-table".

SO now your query becomes, effectively,

SELECT C.CarNameSHort, C.CarNameLong, COUNT(S.CarNameShort) AS AvailableSetups
FROM pseudo-table
GROUP BY C.CarNameShort, C.CarNameLong
ORDER BY C.CarNameLong

(I'm ignoring the Cars.carstatus field for the moment.)

Now, do you understand AGGREGATE functions in SQL??? MAX(), MIN(), COUNT(), AVG(), etc.?? Those are functions that work on a *collection* of records and produce the desired property from that collection.

COUNT() is an easy one: It just counts how many records there are.

But you *MUST* use a GROUP BY clause to tell SQL which GROUPS of records are to be counted together.

Hopefully, in this case it is obvious that for the "audi" record, there are THREE records, and so the COUNT() is 3.

But what about the "bmw" record??? There *IS* one record there! So why doesn't COUNT( ) give us a count of 1?? Ahhh...because I purposely counted one of the fields from the RIGHT-SIDE table! Count(S.CarNameShort) And guess what: COUNT() does *NOT* count NULL values!!! So because that one and only record for "bmw" has a NULL for S.CarNameShort, the count is....zero!

I ignored the Cars.CarStatus up until now. You may *ALSO* have a WHERE clause that limits the records from the LEFT table (only! it breaks down if you use right table fields in the WHERE...it's an exotic reason). That WHERE takes place *before* the LEFT JOIN, so effectively it's a simple limitation on the left side table. [If you need other conditions on the right side table, you put them into the ON instead of into the WHERE.]

****************

I mentioned in my original post that you could get only cars with 1 or more Setups by just changing the LEFT JOIN to an INNER JOIN. That's because an INNER JOIN *does* insist on only matching *existing* records. None of this "left only" bit. And, of course, that's why I used the INNER JOIN in the second query. Where, indeed, you wanted to elminate the zero cases. [It's another exotic fact, but... With an INNER JOIN, you *can* use the right side table in the WHERE clause. When you understand why that works but it doesn't with LEFT JOIN, you can say you have "arrived" at understanding SQL. It's weird.]