My SQL query will not output the field result correctly

Question

I am trying to run a query which will provide a user on the website with:

The competition name
The image title
The result (1st place, 2nd place or 3rd)

So far I have managed to pull out the below result:

<div class="grid-2">
    <h3>Competition Entries</h3>
        <form action = "" method = "POST">
        <select name="competitionID">
        <option value="">Select Competition</option>
        <option value="1">Winter Warmer</option>
        <option value="2">Fresh New Year</option>
        <option value="3">Month of Love</option>
        <option value="4">Seaside Scenery</option>
        </select>
        </fieldset>
     </form>
<?php

    $query = "SELECT `fldCompName`, `fldName`, `fldResult` FROM `tblMembEntComp` JOIN `tblCompetition` ON `tblMembEntComp`.`fldCompID`=`tblCompetition`.`fldCompID` JOIN `tblImage` ON `tblMembEntComp`.`fldMemberID`=`tblImage`.`fldMemberID` ORDER BY `fldResult` DESC LIMIT 3";

$result = $conn -> query($query);

while($row = $result -> fetch_assoc()) 
    {
        echo $row['fldCompName']." ".$row['fldName']." ".$row['fldResult']."<br>";
    } 
    ?>
    <button>View Competition Winners</button>
</div>

However, the results are all showing up as 17 for all of those top three entries when in the tbmMembEntComp fldResult I have 19, 17 and 11. Can someone just highlight where I've gone wrong and give guidance on what the query should be.

DinoCoderSaurus · Accepted Answer · 2019-03-10T19:34:15.190

2

You are not limiting the results to one specific competition, therefore it gives all the joined rows between member table and competition table.

Something like this (example only)

SELECT `fldCompName`, `fldName`, `fldResult`  
FROM `tblMembEntComp`  
JOIN `tblCompetition` ON `tblMembEntComp`.`fldCompID`=`tblCompetition`.`fldCompID`
-- EXAMPLE
AND `tblCompetition`.`fldCompID` = {someX}
------ 
JOIN `tblImage` ON tblMembEntComp`.`fldMemberID`=`tblImage`.`fldMemberID` 
ORDER BY `fldResult` DESC LIMIT 3";

edited Mar 10 '19 at 19:34

answered Mar 09 '19 at 11:48

DinoCoderSaurus

6,110
2
10
15

Hi @DinoCoderSaurus, do you know where in the query I should just state which competition please? – Sunny0101 Mar 09 '19 at 17:30
Modifying the sql is simple: in the ON clause of the tblCompetion join use something like `and compID = someX`. If your intention is that `someX` is based on user selection in the drop down, you would need instead an ajax call (to the server). The php runs one time when the page loads, and not again. – DinoCoderSaurus Mar 09 '19 at 18:23
I've just given it a try pasting it in that ON clause for tblComp and it doesn't seem to like where I pasted it, after which part of the code do you mean (sorry to ask specifics) :) – Sunny0101 Mar 10 '19 at 16:33
added an example to the answer. – DinoCoderSaurus Mar 10 '19 at 18:10
Technically a cartesian join returns all possibilities between two tables (i.e., all members paired with all competitions) but a `JOIN` is used between tables. A `JOIN` without `ON` (or `CROSS JOIN`) would be a cartesian join. OP is just not filtering to selected competition in form. Here, *{somex}* should be [parameterized](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). – Parfait Mar 10 '19 at 19:03
Corrected the language in the answer to avoid further confusion. – DinoCoderSaurus Mar 10 '19 at 19:35
Thank you, It's still coming up with 17 as the 3 results even with the compID set as 2 for e.g, as apposed to 19, 17 and 11. It must be the join I have done, I'll have a look into this again tonight – Sunny0101 Mar 11 '19 at 16:17
Assume you are testing the SELECT in phyMyAdmin before abstracting it to the php. Maybe a `WHERE` would be better than the change to the `JOIN`. Good luck! – DinoCoderSaurus Mar 11 '19 at 16:48

My SQL query will not output the field result correctly

1 Answers1