--- Ligaya Turmelle <[EMAIL PROTECTED]> wrote: > Think I found it. I made the changes with > explanations of what I did. > If you have any further questions feel free to ask. > Oh and this should > be on the list for others to see and maybe learn > from
Wow, thanks so much for going to all that trouble. Several other people sent me tips, too. I feel bad to tell you that it still doesn't work. I got an immediate parse error. Also, I don't know if I should continue this on the list since it may be turning into more of a PHP problem. But it is a cool script that others might like to learn about. You can see a working example on my website at http://www.geoworld.org/reference/people/ (A good column to sort is Population; you'll see China at the top of the column if you choose DESCENDING.) But this page only sorts data from ONE database table. I'm now trying to make one that will sort fields from multiple tables. The problem is that there are too many elements, none of which I really understand. So if I fix a parse error, the data doesn't display, and if I fix it so the data displays, the PHP sorting switch doesn't work. I have learned a few things: 1. For some reason, I can't limit the display with a regular WHERE query. It displays ALL the rows (all the world's nations), even if I ask it to display rows only WHERE F.IDParent = 'eur' (Eurasia). To make it work, I have to use an "official" join, like this: FROM cia_people C LEFT JOIN famarea2 F ON C.IDArea = F.IDArea WHERE F.IDParent = 'eur' * * * * * * * * * * 2. I had the wrong field for the 'eur' values; it should be F.IDParent, not IDArea. * * * * * * * * * * 3. This is the most critical code: ORDER BY '" . $_POST['order'] . "','" . $_POST['direction']."'"; It's usually the first to flake out, either causing a parse error or simply not functioning. Every time I modify another key function, I have to modify this line, and it's too complex for me to re-engineer. * * * * * * * * * * 4. I've received a variety of opinions on the quotes, on functions throughout the source code. I'm not sure sure if I should be using single quotes, double quotes or no quotes at all in certain instances. * * * * * * * * * * 5. There may also be a conflict with globals and $_Post. Again, I don't understand this stuff. If I understand correctly, I should either turn globals on or off (or not have them in the first place), and use $_Post in one instance but not the other? * * * * * * * * * * I'm amazed there isn't more information about this script readily avaiable. It seems like such a useful function, I thought it would be rather common. Below is my current source code. It displays the data correctly, without errors, but the sort function doesn't work. Once again, it draws from two tables, named cia_people and famarea2, joined by the field they share in common, IDArea. Every field cited as an "option value" is from table cia_people except IDParentReg, which is the field from table famarea2 I want to sort by. Actually, both tables share a field named "Name," but I think I identified cia_people.Name in the query. Don't feel obligated to pursue this; I've already spent two days on it! :) Thanks. * * * * * * * * * * <head>[DATABASE CONNECTION]</head> <body> <div class="formdiv"> <form action="remote.php" method="GET"> <select name="order"> <option value="Name">Country, etc.</option> <option value="Pop">Population</option> <option value="Nationality">Nationality</option> <option value="NationalityPlural">Nationality: Plural</option> <option value="NationalityAdjective">Nationality: Adjective</option> <option value="IDParentReg">Geographic Region</option> </select> <input type="radio" name="direction" value="0">+ <input type="radio" name="direction" value="1">- <input type="submit" name="submit" value="Submit"> </form> </div> <?php $colors = array( '#eee', '', '#ff9', '', '#cff', '', '#cfc', '' ); $n=0; $size=count($colors); $result = mysql_query('select count(*) FROM cia_people C, famarea2 F WHERE C.IDArea = F.IDArea AND F.IDParent = "eur" AND C.Nationality is not null'); if (($result) && (mysql_result ($result , 0) > 0)) { // continue here with the code that starts //$res = mysql_query ("SELECT * FROM type..... } else { die('Invalid query: ' . mysql_error()); } switch($order) { case 1: $order = 'Name'; break; case 2: $order = 'Pop'; break; case 3: $order = 'Nationality'; break; case 4: $order = 'NationalityPlural'; break; case 5: $order = 'NationalityAdjective'; break; case 6: $order = 'IDParentReg'; break; default: $order = 'Name'; break; } switch($direction) { case 0: $direction = 'ASC'; break; case 1: $direction = 'DESC'; break; default: $direction = 'ASC'; break; } $sql = "SELECT F.IDArea, C.IDArea, C.Name, C.Pop, C.Nationality, C.NationalityPlural, C.NationalityAdjective FROM cia_people C LEFT JOIN famarea2 F ON C.IDArea = F.IDArea WHERE F.IDParent = 'eur' ORDER BY '" . $_POST['order'] . "','" . $_POST['direction']."'"; $res = mysql_query($sql) or die('Failed to run ' . $sql . ' - ' . mysql_error()); echo '<table class="sortphp" id="tab_cia_people_peo"> <thead> <tr><th>Country</th><th>X</th></tr> </thead> <tbody>'; //<!-- BeginDynamicTable --> $rowcounter=0; while ($row = mysql_fetch_array ($res)) { $c=$colors[$rowcounter++%$size]; echo "<tr style=\"background-color:$c\" class='". $row['Name'] ."'><". $_SERVER['PHP_SELF'] .'?id='. $row['IDArea'] ."> <td class='tdname' '". $row['Name'] ."'>". $row['Name'] ."</td> <td> </td></tr>\n"; } ?> </tr> </tbody> </table> </body> </html> __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
