Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Feb 2009
    Location
    Delaware
    Posts
    11
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Arrow Query/Subquery assistance (counting available rows)

    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 Code:
                    <?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

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts

    I gave a nice long reply...

    ...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.

  • Users who have thanked Old Pedant for this post:

    PodiumAssault (02-23-2009)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    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.
    Code:
    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.

  • Users who have thanked Old Pedant for this post:

    PodiumAssault (02-23-2009)

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Once a vehicle is selected, show the tracks available that ONLY have at least one setup available for them
    Code:
    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.

  • Users who have thanked Old Pedant for this post:

    PodiumAssault (02-23-2009)

  • #5
    New Coder
    Join Date
    Feb 2009
    Location
    Delaware
    Posts
    11
    Thanks
    7
    Thanked 0 Times in 0 Posts
    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

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts

    Let me try to explain a bit...

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

    Code:
    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:
    Code:
    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
    Code:
       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,
    Code:
    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.]


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •