joint table arrays

billiondevil

kk i have used this code

$sql="SELECT * FROM useritems, items WHERE useritems.itemid = items.id AND useritems.userid='1' AND items.type ='1'";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){

echo $result['useritems.quantity']."x ".$result['items.name']."<br />";

}

to get information from my database, the sql is fine cos itryed it in phpmyadmin ant it worked fine and brung up what it should do, but when i try to display to results nothing shows up, i am not sure if i have done the arrays for the result right or somthing. could anyone tell me where i have been going wrong?

dagon

the array you want to be echoing is $row not $result

billiondevil

kk i tryed this

$sql="SELECT * FROM useritems, items WHERE useritems.itemid = items.id AND useritems.userid='".$info['id']."' AND items.type='1'";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){

echo $row['useritems.quantity']."x ".$row['items.name']."<br />";

}

and it still doesnt show the arrays in the echo but it does show the ."x ". in the middle so it is getting the amount or rows right its just not displaying the info

dagon

put

print_r($row);

in the while loop and you will see the problem

Bjom

did you check the array's keys?

My bet is there is no 'useritem.quantity'

try this to find out:

$sql="SELECT * FROM useritems, items WHERE useritems.itemid = items.id AND useritems.userid='".$info['id']."' AND items.type='1'"; 
$result=mysql_query($sql); 
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){ 
    foreach($row as $key=>$value) {
        echo $key . '=>' . $value . '<br />';
    }
}

billiondevil

kk i found the problem, when php makes the array from the query it only uses the field name for the key so the working code would look like this

$sql="SELECT * FROM useritems, items WHERE useritems.itemid = items.id AND useritems.userid='".$info['id']."' AND items.type='1'";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){

echo $row['quantity']."x ".$row['name']."<br />";

}

but then if you did use the same field name in 2 tables you might have a probelm getting the value you want

thx for the help guys

Bjom

No you won't because you will never use a query with the asterisk (*) but will rather list the fields and then you can give them alias names like so:

SELECT `myField` AS `myField1`, `myField` AS `myField2`, `myOtherField` FROM `myTable`;

Bjom

billiondevil

didnt know you could do that, that makes everything sooo much easyer!!!