Linker Tables problems. Some help please?

[shantred]

Hello,
I'm not new to php, but i've never had to learn to link tables from MYSQL together to execute a query. and now that i've tried i'm having a few problems here, and was wondering if anyone could help.

Here's the code:

[code]
function getEquip($item_type) {
if($item_type == "all") {
//Add that function later 
//return getAllEquips();
}
$sql = "SELECT * FROM equip";
$result = mysql_query($sql);
$var = mysql_fetch_assoc($result);
$id = 0;
if ($item_type == "head") {
$id = $var['head'];
} elseif ($item_type == "weapon") {
$id = $var['weapon'];
} elseif ($item_type == "body") {
$id = $var['body'];
} elseif ($item_type == "leg") {
$id = $var['leg'];
} elseif ($item_type == "shield") {
$id = $var['shield'];
}
$sql = "SELECT * FROM DATA_ITEMS WHERE id = '$id";
$result = mysql_query($sql);
return mysql_fetch_assoc($result);
}
[/code]

this is in the middle of a function i have called functions.php, so you're not going to see EVERYTHING. so dont assume i'm stupid because i dont connect or use tags. thats earlier in the file :p.
that is supposed to link two tables in my database together
one is named equip, the other DATA_ITEMS. I have fields in equip labeled "weapon" "head" "body" "leg" "shield".  its supposed to take the value placed in one of those fields, and search 'DATA_ITEMS' for the ID that matches the value in that field.

so say in "weapon" i have the integer "1".
so i would make the code $wep = getEquip(weapon);
then it would take the "1" in weapon field, to search for the ID "1" in DATA_ITEMS.

but whenever i execute it. I get THIS error:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /usr/home/shantred/public_html/delirium/inc/functions.php on line 59

line 59 in my file would be:

"return mysql_fetch_assoc($result);"

can anyone help me figure out whats wrong here? thanks for any of those who take the time to read this and try and figure it out for me.

Thanks,
Shantred

[fert]

try making your database connection resource a global like this:
[code]
global $cn;

$cn=@mysql_connect("host","username","password") or die(mysql_error());
[/code]
and change
[code]
$result = mysql_query($sql);
[/code]

to
[code]
$result=@mysql_query($sql,$cn) or die(mysql_error());
[/code]

[marcus]

change

[code]
$sql = "SELECT * FROM DATA_ITEMS WHERE id = '$id";
[/code]

to:

[code]
$sql = "SELECT * FROM DATA_ITEMS WHERE id = $id";
[/code]

[marcus]

also, you already have $id defined as 0, select 0 from your db won't do anything

[sanfly]

Actually, you should always enclose your variables like that in single quotes, you were just missing the closing quote

[quote]$sql = "SELECT * FROM DATA_ITEMS WHERE id = '$id[color=red]'[/color]";[/quote]

Handy Hints:

Always use codes to debug your script.  eg: after a query, have it print an error (this would have picked up the missing ')

eg: [code=php:0]$result = mysql_query($sql) or die(mysql_error());[/code]

This will kill the script if it causes an error, and tell you what the error is

Next handy hint

Try using a [url=http://nz2.php.net/manual/en/control-structures.switch.php]switch[/url] rather than that long else/if statement

eg [code=php:0]switch($item_type){
case "head" : $id = $var['head']; break;
case "weapon" : $id = $var['weapon']; break;

etc.....
}[/code]