Including $variable in MySQL

Question

Here are my failed attempts to include a PHP variable in a MySQL expression. Replacing the variable with a 1 results in the results being printed. Any help will be appreciated.

 $query = "
 SELECT name FROM teams
 WHERE id = '$shooterID'";

$shooters = mysql_query($query)
 or die(mysql_error());

$i = 0;
while($shooter = mysql_fetch_array( $shooters )) {
 echo $shooter[$i];
 $i++;
}

---

$shooters = mysql_query("
 SELECT name FROM teams
 WHERE id = '$shooterID'")
 or die(mysql_error());

$i = 0;
while($shooter = mysql_fetch_array( $shooters )) {
 echo $shooter[$i];
 $i++;
}

Thanks

---

Attempting to utilize the methods here have not fully solved the problem (though thanks again). Here are my revised efforts along with further context (I don't need to sanitize the data as it is coming directly from another query.

$shooters = mysql_query("
 SELECT * FROM events JOIN teams
 on events.shooter = teams.id
 ") or die(mysql_error());

$i = 0;
while($results = mysql_fetch_array( $shooters )) {
    $shooterIDs[$i] = $results[0];
    $i++;
}

//var_dump($shooterIDs); == array(1) { [0]=>  string(1) "1" } 

$query = "
 SELECT name FROM teams
 WHERE id = '".$shooterID[0]."'";

$shooters = mysql_query($query)
 or die(mysql_error());

while($shooter = mysql_fetch_array( $shooters )) {
 echo $shooter[0];
}

---

Turns out my last attempt was missing a 's' in the variable namee $shooterIDs[0]. Stupid error. There were probably others as well that have been already solved with all of your help. Thanks!

Answer 1

dont put the single quotes around $shooterID inside the query.

you'll probably also want something like:

while($shooter = mysql_fetch_array( $shooters )) {
 echo $shooter[0];
 $i++;
}

to print out the results.

Answer 2

Have you tried:

 $query = "SELECT name FROM teams WHERE id = '" . $shooterID . "'";

Also, I don't see you defining $shooterID anywhere make sure you define it.
I.E.

$shooterID = 0;

Also,

$i = 0;  
while($shooter = mysql_fetch_array( $shooters )) {
     echo $shooter[$i];
     $i++;
}

should be

while($shooter = mysql_fetch_array( $shooters )) {
     echo $shooter[0];
}

or

while($shooter = mysql_fetch_array( $shooters )) {
     echo $shooter['name'];
}

or

while($shooter = mysql_fetch_object( $shooters )) {
     echo $shooter->name;
}

Answer 3

The query is not your problem, the output is:

This is wrong:

$i = 0;
while($shooter = mysql_fetch_array( $shooters )) {
 echo $shooter[$i];
 $i++;
}

This is correct:

while($shooter = mysql_fetch_array( $shooters )) {
 echo $shooter[0];
}

Also

Just make sure you are properly sanitizing your input if you want to include the variable like that. For instance:

$shooterID = (int)$_GET['shooter_id'];

That forces the number to either be a 0 if it is not a number or a 1 if they pass in shooter_id[]=somthing, but it can never be a SQL injection string.

Answer 4

Try something like this (comments added for clarity):

// Create the query, assuming $shooterID is an integer
$query = "SELECT name FROM teams WHERE id = '{$shooterID}'";

// Execute query
$shooters = mysql_query($query);

// Check result
if (!$shooters) { die(mysql_error()); }

// Iterate through rows
while ($shooter = mysql_fetch_array($shooters)) {
  // To display the entire $shooter array
  print_r($shooter);

  // To select the first item in $shooter array (no matter what it is)
  echo $shooter[0];

  // To specifically select the name field in $shooter array
  echo $shooter['name'];

  // To iterate over the $shooter array and display all fields
  // This will only be the name, unless you change the query to SELECT * FROM,
  // in which case this will return all fields in the table
  foreach ($shooter as $field) {
    echo $field;
  }
}

Answer 5

Also, you probably want some separation in your output:

while ($shooter = mysql_fetch_array( $shooters )) 
{
   echo $shooter[0], "\n";   //  or '<br>' if outputting to html
}