SQL查询返回错误结果 [英] SQL query returning wrong results

问题描述

在我的网站上，用户可以从32个玩家中选择一个玩家.起跑阵容中有15个位置，因此在32个球员阵容中，需要选择15个球员.

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个播放器可以扮演多个位置，如下图所示:

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:

如果您查看图片1，您会发现Badden Kerr可以同时打蝇头和后卫.

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

问题

在选择列表中，球员的次要位置未显示.作为示例，如果再次查看图像1，您会看到Badden Kerr可以同时发挥蝇头和后卫.但是在第二张图片中，后卫位置为空，Baden Kerrs的名字没有出现在他的次要位置(后卫)

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)

当我在Phpmyadmin中运行以下sql语句时:

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:

但是，当我尝试在PHP代码中运行相同的SQL语句时，会给我错误的/不希望的结果(如您在image2中所见)

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)

我的PHP代码如下:

// 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());

查询现在显示两个位置的球员，但显示不在选择团队中的其他球员?

任何帮助或建议将不胜感激

Any help or advice would be greatly appreciated

提前谢谢

推荐答案

您在PHP代码中的查询不会产生所需的结果，因为您已将Position和Secondary设置为相同的值.根据您提供的示例数据，该数据将不会返回任何内容.

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]')

这篇关于SQL查询返回错误结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！