Post office web site - Notice the use of aliases for the tables.
Notice the use of aliases for the tables. The character table is c, the power link table is pk, and the power table is p. This allows you to refer to the appropriate columns with a shorter syntax (for example pk.char_id instead of char_power_link.char_id). It is not necessary to use table.column syntax if the column name is unique across all tables. However, it is a good practice to keep so that you are always aware of which data you are accessing. It is required, of course, for column names that are duplicated across multiple tables (such as id). Some might recommend that you always use unique names for all of your fields, but we prefer the practice of naming all primary keys id and using proper table.column syntax in SQL queries. Next, you create a multidimensional array. That s fancy talk for an array with more than one index. This one is two-dimensional. Think of a two-dimensional array as being like a spreadsheet, and it isn t difficult to understand. if (mysql_num_rows($result) > 0) { while ($row = mysql_fetch_array($result)) { $p[$row[ id ]][] = $row[ power ]; } The trick here is that you have multiple powers for the same id. By adding [] to the $p array, a new array item is created for each row that has the same id. The end result is that you have a $p array of x characters, each element of which contains a $p[x] array of y powers. That is a multidimensional array. Now you go back through the temporary array $p, and pull out each array that it holds. The $key variable contains the character id, and $value contains the array of that character s powers. You then implode the powers into a comma-separated list of powers, and store that in the $powers array, using the character ID ($key) as the array index. You end up with an array that contains a list of powers for each character. foreach ($p as $key => $value) { $powers[$key] = implode( , , $value); } Oh boy, another JOIN. This one is similar to the previous M:N query, with a couple of exceptions. First of all, you are linking the character table twice. You can see that you are creating two instances of that table, one called c for character and one called n for nemesis. This distinction is very important. $sql = SELECT c.id, n.alias . FROM char_main c . JOIN char_good_bad_link gb . JOIN char_main n . ON (c.id = gb.good_id AND n.id = gb.bad_id) . OR (n.id = gb.good_id AND c.id = gb.bad_id) ; The other exception is the ON statement. You have characters that you are attempting to link to other characters as enemies. Call them opponents, or nemeses, or whatever. Typically, you expect good versus evil and vice versa. However, you are allowing any character to be the enemy of any other character. That makes linking more interesting because you are using a table with a bad_id and a good_id. If you have two evil characters that are enemies, which one gets stored in the good_id column? The answer is that it doesn t matter. What you want to do is to make sure that you not only don t have any duplicates in the char_good_bad_link table, but also that you don t have what we call reverse 317 Building Databases
We recommend high quality webhost to host and run your jsp application: christian web host services.