mysql query returns the value in the first column for all the other columns.

Question

Hi I made a query to a table below and when I tried to get the value in each column , it returns the same value from the first column for all the other columns. To elaborate
In my database table I have the following:

owner_id = 21
pet_id = 1
name = fluffy
color = green
type = dog
sub_type = boxer
location = LA

however whenever I try to access one column, say the name column, it returns 21 which is the value in the owner_id column corresponding to that pet_id. I am not sure why this is happening.

$query = sprintf("SELECT * FROM `petAttributes` where pet_id ='%d'",$p_id);
 $result = performQuery($query);

 $owner_id = stripslashes(mysql_result($result,"owner_id"));
 $pet_id = stripslashes(mysql_result($result,"pet_id"));
 $name = stripslashes(mysql_result($result,"name"));
 $color = stripslashes(mysql_result($result,"color"));
 $type = stripslashes(mysql_result($result,"type"));
 $sub_type = stripslashes(mysql_result($result,"sub_type"));
 $loc = stripslashes(mysql_result($result,"location"));

Information on my environment
PHP Version 5.2.14
MYSQL version 5.0.67

Answer 1

refering to http://php.net/manual/en/function.mysql-result.php mysql_result has it's parameters like this: mysql_result($result,$rownumber,$fieldname or $fieldnumber)

this should workd:

$query = sprintf("SELECT * FROM petAttributes where pet_id ='%d'",$p_id);
$result = performQuery($query);

$owner_id = stripslashes(mysql_result($result,0,"owner_id"));
$pet_id = stripslashes(mysql_result($result,0,"pet_id"));
$name = stripslashes(mysql_result($result,0,"name"));
$color = stripslashes(mysql_result($result,0,"color"));
$type = stripslashes(mysql_result($result,0,"type"));
$sub_type = stripslashes(mysql_result($result,0,"sub_type"));
$loc = stripslashes(mysql_result($result,0,"location"));

BTW mysql_result is getting very inefficient if you take more than one row. Then you should use mysql_fetch_row, mysql_fetch_array or mysql_fetch_assoc

Answer 2

I believe that if you use mysql_result you also have to specify the row index number (row 0 in your case?), before you specify the column.

$name = stripslashes(mysql_result($result, 0, "name"));

Answer 3

Also you can;

for only first row

$query = sprintf("SELECT * FROM petAttributes where pet_id ='%d'",$p_id);
$result = performQuery($query);
$row = mysql_fetch_array($result);

extract($row);

or all returned rows;

$query = sprintf("SELECT * FROM petAttributes where pet_id ='%d'",$p_id);
$result = performQuery($query);
while($row = mysql_fetch_array($result))
{
  foreach ($row as $value)  echo $value."<br>";
}