SQL查询返回错误的结果

Question

On my site a user can select players out of a squad of 32 players. There are 15 positions in the starting lineup so out of the 32 player squad 15 players need to be selected.

1 Player can play more than one position as you can see in the image below:

Now when the user selects the starting line up out of the squad a certain players name that can play more than 1 position (as in the image above) needs to show up in both the positions, unfortunately this is not working as you can see in the image below:

If you look at image 1 you can see Badden Kerr can play both flyhalf and fullback.

The Problem

In the select list the secondary position of a player does not show up. As an example If you look at image 1 again you can see Badden Kerr can play both flyhalf and fullback. But in the second image the fullback position is empty and Baden Kerrs name does not appear in his secondary position (fullback)

---

When I run the following sql statment in Phpmyadmin:

SELECT *
FROM `allsquads`
WHERE `Team` = 'Blues'
AND `Position` = 'flyhalf'
AND `Secondary` = 'fullback'

I get the correct result returned as you can see in the image below:

However when I try to run the same SQL statment in my PHP code it gives me wrong / undesired results (As you can see in image2)

My PHP code follows:

// assign each position to array variable position
        $position = array(
            "prop",
            "hooker",
            "prop",
            "lock",
            "lock",
            "flank",
            "flank",
            "no8",
            "scrumhalf",
            "flyhalf",
            "center",
            "center",
            "wing",
            "wing",
            "fullback"
        );
        // for loop to loop through the number of positions in the team, which will be used to query DB
        $size     = sizeof($position);
        for ($i = 0; $i < $size; $i++) {
            // Query For positions
            $sqlprops = mysql_query("SELECT * FROM `allsquads` 
                                     WHERE `Team` = '$t1select'  
                                     AND `Position` = '$position[$i]' 
                                     OR `Secondary` = '$position[$i]'") or die(mysql_error());

New Problem When I do the following query

SELECT * FROM `allsquads` 
                               WHERE `Team` = '$t1select'  
                                AND `Position` = '$position[$i]' 
                                OR `Secondary` = '$position[$i]'") or die(mysql_error());

The query now show the players in both their positions but it displays other players who is not in the select team?

Any help or advice would be greatly appreciated

Thank you in advance

Answer 1

Your query in the PHP code won't produce the desired result because you have it setting Position and Secondary to the same value. Based on the sample data you provided that won't return anything.

If you're looking for a query to select eligible players for the dropdwon you could change the query to look like this:

SELECT * FROM `allsquads` 
WHERE `Team` = '$t1select'  
AND (`Position` = '$position[$i]' OR `Secondary` = '$position[$i]')