How To Join Two Tables?

[twilitegxa]

Hey guys. I'm getting back into PHP after about a year or two of a little hiatus and I need some help getting back into it. I am currently working on some old coding before I upgrade (will be learning that shortly), but I need some help joining these two tables so I can output the data I want to. Here's the basic premisis:

 

Table 1: (defects table)

 

id (#'s)

defect (text)

 

Table 2: (character defects table)

 

id (#'s)

identity (links the defects with the character)

defect (matches the id from the previous table)

desc (additional info about the defect for that particular character)

 

Here's the code I have so far:

//gather the defects
$get_defects = "select * from defects";

$get_defects_res = mysql_query($get_defects, $conn) or die(mysql_error());

while ($defects_info = mysql_fetch_array($get_defects_res)) {
$defects_id = $defects_info['id'];
$defects = $defects_info['defect'];

}

//gather the character defects
$get_character_defects = "select * from character_defects where identity = 'Sailor Moon'";
$get_character_defects_res = mysql_query($get_character_defects, $conn) or die(mysql_error());

$display_block .= "<table width=40% cellpadding=3 cellspacing=1 border=0>";

while ($character_defects_info = mysql_fetch_array($get_character_defects_res)) {
$character_defects = $character_defects_info['defect'];
$defect_desc = $character_defects_info['desc'];

$character_defect = 

}

So I have gathered all the defects and their respective id's, then gathered all the character defects where the identity is equal to a character (later this will be based on the logged in person and their selected character). So now I am stuck. I want to list all the defects this particular character has, plus the respective descriptions if they exist, but I don't know how to write the next part that connects them together. I'm thinking I need to JOIN them, but I'm kind of at a loss. I know, I know, I know...I should be using updated PHP. I haven't learned it yet and I'm waiting for a book to come in the mail for me to start learning, and then I will update it. My current server is running the old versions for now since this is what I know how to use and have examples to work with :-( Can anyone help me on this please? :-) It's very simple so far. I have the link if anyone needs it, although I doubt it will be as useful as the actual code I've listed above and the tables.

[twilitegxa]

Or...can I just select the data from one table and some data from the othe table based on a column field from the first table? I can't remember how to do this!!! :-(

[Ch0cu3r]

Yes you'll want to use a join. Something like

 

SELECT c.id,
       c.identity,     
       c.desc,
 
       d.defect,
       d.id as defect_id
FROM
    character_defects AS c
LEFT JOIN
    defects AS d USING(id)
WHERE
    c.identity = 'Sailor Moon'

[twilitegxa]

Okay now then another stupid question...how do I display the data? I tried this, but it didn't work :-(

$character_defect = "SELECT c.id,
       c.identity,     
       c.desc,
 
       d.defect,
       d.id as defect_id
FROM
    character_defects AS c
LEFT JOIN
    defects AS d USING(id)
WHERE
    c.identity = 'Sailor Moon'";

$display_block .= "
<tr>
<td>$character_defect</td>
</tr>";

}

[twilitegxa]

It is just displaying the code that many times now: http://zertinternetmarketing.com/rpg/profile.php

[twilitegxa]

Okay so I added some more code, but I'm lost. :-( Help?

$character_defect = "SELECT c.id,
       c.identity,     
       c.desc,
 
       d.defect,
       d.id as defect_id
FROM
    character_defects AS c
LEFT JOIN
    defects AS d USING(id)
WHERE
    c.identity = 'Sailor Moon'";
$character_defect_res = mysql_query($character_defect, $conn) or die(mysql_error());

while ($characters_defect_info = mysql_fetch_array($character_defect_res)) {



$display_block .= "
<tr>
<td>$character_defect</td>
</tr>";

}
}

?>

I know displaying the $character_defect variable is wrong, but how do I create the variables I want? I want to display the defect field from the defects table where the id from character_defects table matches the defect from the defects table. I hope that made sense.

[Ch0cu3r]

Umm.. What do expect? PHP isn't magic/psychic. You need to tell PHP to execute the query and then fetch the results from the query!

mysql_query - executes a query

mysql_fetch_assoc - fetches the next row from result set

 

Defining the query in variable does nothing.

 

Also the join query I gave is to replace the two queries you posted earlier.

[twilitegxa]

Which queries does yours replace? Sorry I feel like a complete idiot now :-(

[Barand]

 

defect (matches the id from the previous table)

therefore those are the columns you need to JOIN

SELECT c.id
    , c.desc
    , c.identity
    , d.defect
FROM character_defects c
    LEFT JOIN defects d ON c.defect = d.id
WHERE c.identity = 'Sailor Moon'