使用mysql fetch行发出检索值

Question

I am having a problem returning a specific value from a mysql table i have. The code seems to be failing at the:

$role = $row[7];

line. I've tested this using echo.

My function looks like:

function fetchRole(){

$query = "SELECT * FROM membersTable WHERE username='$user'";
$result = queryMysql($query);

$row = mysql_fetch_row($result);

$role = $row[7];

switch ($role)
{
    case "HeadCoach":   $role = 'Head Coach'; break;
    case "Coach":       $role = 'Team Coach'; break;
    case "Science":     $role = 'Sport Scientist'; break;
    case "Strength":    $role = 'Strength & Conditioning'; break;
    case "Physio":      $role = 'Physiotherapist'; break;
    case "GK":          $role = 'Goalkeeper'; break;
    case "FB":          $role = 'Full-Back'; break;
    case "LB":          $role = 'Left-Back'; break;
    case "CB":          $role = 'Centre-Back'; break;
    case "RB":          $role = 'Right-Back'; break;
    case "LM":          $role = 'Left-Midfielder'; break;
    case "CM":          $role = 'Central-Midfielder'; break;
    case "RM":          $role = 'Right-Midfielder'; break;
    case "AML":         $role = 'Attacking-Midfielder (Left)'; break;
    case "AMC":         $role = 'Attacking-Midfielder (Central)'; break;
    case "AMR":         $role = 'Attacking-Midfielder (Right)'; break;
    case "WF":          $role = 'Wide Forward'; break;
    case "CF":          $role = 'Centre Forward'; break;                
    default:            $role = 'Unknown';
}   
return $role;
}

The membersTable schema looks like this:

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstName VARCHAR(16),
lastName VARCHAR(16),
dob DATE,
height CHAR(3),
weight CHAR(3),
type CHAR(1),
position VARCHAR(16),
teamName VARCHAR(32),
status VARCHAR(16),
username VARCHAR(16),
pass VARCHAR(16),
INDEX(firstName(6)),
INDEX(lastName(6))

I queryMysql is a function that i know works from other areas on my site.

Currently echoing $row prints out RESOURCE #19.

This previous function works:

function generateStafflist()

{

echo "<h3>Team Staff</h3>";

$positionArray = array('HeadCoach','Coach','Science','Strength','Physio');

$query = "SELECT * FROM membersTable WHERE type='S'";
$result = queryMysql($query);

$rows = mysql_num_rows($result);

$count = count($positionArray);

for($i = 0; $i < $count; ++$i)
{
    $result = queryMysql($query);

    for($j = 0; $j < $rows; ++$j)
    {
        $row = mysql_fetch_row($result);

        $position = $row[7];
        $currentPosition = $positionArray[$i];

        if($position == $currentPosition)
        {

            echo    "<div class='snapshot'>";

            if(file_exists($row[9] . ".jpg"))
            {
                echo "<img src='".$row[9].".jpg' height='210' width='180' />";
            }
            else
            {
                echo "<img src='imgs/profiles/noimage.png' height='210' width='180' />";
            }
            echo    $row[1] . " " . $row[2] . "<br />";

            switch($row[7])
            {
                case "HeadCoach":   $role = 'Head Coach'; break;
                case "Coach":       $role = 'Team Coach'; break;
                case "Science":     $role = 'Sport Scientist'; break;
                case "Strength":    $role = 'Strength & Conditioning'; break;
                case "Physio":      $role = 'Physiotherapist'; break;
                default:            $role = '';
            }

            echo    '[' . $role . ']<br />';
            echo    '</div>';
        }
    }
}

}

Answer 1

It looks like the reason your query is failing is because your are using the $user variabled without having initialized it to anything:

$query = "SELECT * FROM membersTable WHERE username='$user'";

The above line basically will be evaluated to this:

$query = "SELECT * FROM membersTable WHERE username=''";

That is most likely not what you intended and it's probably not matching any record in your table so naturally the $result is empty.

That said, your approach of querying the database directly opens you up to serious SQL injection vulnerabilities. You SHOULD use parameterized queries whenever possible and favor a data access abstraction layer if one is available for whatever framework you are using.

Also, using an all-inclusive query such as SELECT * FROM is pretty bad form. You are opening up your code to all sorts of potential bugs should your database schema change. All it takes is a reorder of the fields in the table to mess with your code. It would be far better to only query for the specific fields that you are interested in:

$query = "SELECT position FROM membersTable WHERE username='$user'";