Joining Issues

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
CoolAsCarlito
Forum Contributor
Posts: 192
Joined: Sat May 31, 2008 3:27 pm
Contact:

Joining Issues

Post by CoolAsCarlito »

I've been working on this join for about 3 hours now. And I'd really like to seek out some help on figuring out what my issue is now. I'm trying to get the correct values for my style, alignment, and status fields for the list of characters I have. I don' t know what the issue is whether it be my join code or the table code itself calling the table values.

Here is my DB scheme:

table efed_bio
id
charactername
username
charactershortname
posername
style_id
fed_id
status_id
alignment_id
sortorder

table efed_list_style
id
name

table efed_list_status
id
name

table efed_list_alignment
id
name

Code: Select all

 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
 
<head>
    <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
    <meta name="author" content="" />
 
    <title>Untitled 1</title>
</head>
 
<body>
 
<?php {
    print '<h1 class=backstage>Character Management</h1><br />';
    print "<h2 class=backstage>Characters :: <a href=\"#\" onclick=\"ajaxpage('addcharacter', 'content'); return false;\">Add New</a></h2><br />";
      $query = "SELECT 
            bio.charactername,
            bio.style_id,
            style.id,
            style.name
        FROM
            efed_bio as bio
        INNER JOIN
            efed_list_styles as style
        ON
            (
                bio.style_id = style.id                
            )
        WHERE
            $row ['style_id'] = 'style.name'"; 
            
     $result = mysql_query ( $query ); // Run The Query
     $rows = mysql_num_rows($result);
     if ($rows > 0)
     {
    
    print '<table width="100%" class="table1">';
    print '<tr class="rowheading">';
    print '<td>&nbsp;</td>';
    print '<td>&nbsp;</td>';
    print '<td>Character Name</td>';
    print '<td align=center width=100>Poser Name</td>';
    print '<td align=center width=60>Style</td>';
    print '<td align=center width=60>Alignment</td>';
    print '<td align=center width=60>Status</td>';
    print '</tr>';
        // Fetch and print all records.
        $i = 0;
        $current_row = 0;
        while ( $row = mysql_fetch_array ( $result, MYSQL_ASSOC ) ) {
            $sClass = 'row2';
            if ($i ++ & 1) {
                $sClass = 'row1';
            }
            printf ( "<tr class=\"%s\">", $sClass );
            print "<td valign=\"top\" align=center width=35><a href=\"#\" onclick=\"ajaxpage('editcharacter', 'content'); return false;\">Edit</a></td>";
            print "<td valign=\"top\" align=center width=25><a href=\"#\" onclick=\"ajaxpage('bio', 'content'); return false;\">Bio</a></td>";
            printf ( "<td valign=\"top\">%s</td>", $row ['charactername'] );
            printf ( "<td align=\"center\" valign=\"top\">%s</td>", $row ['posername'] );
            printf ( "<td align=\"center\" valign=\"top\">%s</td>", $row ['style_id'] );
            printf ( "<td align=\"center\" valign=\"top\">%s</td>", $row ['alignment_id'] );
            printf ( "<td align=\"center\" valign=\"top\">%s</td>", $row ['status_id'] );
            print '</tr>';
        }
         print '</table>';
     }
     else
     {
     print '<span>There are characters.</span><br />';
     }
     print '<br />';
returnmain();
}
?>
 
</body>
</html>
So for example if I have the following for one row for a character just as example lol:

Darth Vader- Charactername
darth.jpg- posername
1- style_id
1-alignment_id
1-status_id

So basically it's going to take Darth's style_id which is one and then go to the efed_list_style table and match 1 against the id in the table and then grab the value of what's in name field in this case it could be "bad guy" and then return that value to the actual table list of characters with the word "bad guy" in that place for the style for Darth Vader.I have a 1 inside the style_id field in the bio table so it shoudl take that and search for the id on the styles table and then select the corresponding name with it and then actually put that name into the html table.
Post Reply