SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need Help with PHP Switch (2 Database Tables)

    I have a PHP switch that lets visistors sort table columns. It queries two database tables, though just one table is referenced in the switch function.

    I want to modify it so that I can sort by fields from a second table. Here's where I run into trouble:

    PHP Code:
    {
       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;

    /* I just added Case 6, which references a field (IDParentReg) from the SECOND table, famarea2. */

       
    case 6:
           
    $order 'IDParentReg';
           break;

       case 
    7:
       default:
           
    $order 'Name';
           break;

    Now look at the error message I'm getting:

    Unknown column 'C.IDParentReg' in 'order clause'

    Of course, there IS no C.IDParentReg; it should be F.IDParentReg. So what do I have to do to fix this?

    I also see another bug. I don't know if it's part of this problem, but I assume I'll have to modify it before I can sort by fields from a second table:

    ORDER BY C." . $order . $direction);

    How do I change that so it sorts by tables C (cia_people) AND F (famarea2), not just C?

    I pasted the entire source code below. Thanks for any tips!

    PHP Code:
    <head>[DATABASE CONNECTION]</head>
              <div class="formdiv">
                <form action="remote.php" method="GET">
                  <select name="order">
                    <option value="1">Country, etc.</option>
                    <option value="2">Population</option>
                    <option value="3">Nationality</option>
                    <option value="4">Nationality: Plural</option>
                    <option value="5">Nationality: Adjective</option>
                    <option value="6">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());
    }

    {

    $order = isset($_REQUEST['order']) ? intval($_REQUEST['order']) : 0;
    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;
       case 
    7:
       default:
           
    $order 'Name';
           break;
    }
    if (isset(
    $_REQUEST['direction']) && intval($_REQUEST['direction'])) {
    // if (isset($_REQUEST['direction']) && intval($_REQUEST['direction'])) {
    $direction ' DESC';
    } else {
        
    $direction '';
    }

    $res mysql_query("SELECT F.IDArea, C.IDArea, IDParent, F.IDParentReg, C.Name, Pop,
      Nationality, NationalityPlural, NationalityAdjective
      FROM cia_people C, famarea2 F
      WHERE C.Nationality is not null AND F.IDArea = C.IDArea AND IDParent = 'eur'
      ORDER BY C." 
    $order $direction);
    if (!
    $res) {
      die(
    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>&nbsp;</td></tr>\n"
    ;
    }
    }
    ?>
          </tr>
          </tbody>      
        </table>
    </body>
    </html>

  2. #2
    SitePoint Addict
    Join Date
    Sep 2004
    Location
    Kelowna, BC
    Posts
    202
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try setting a variable for the table in the switch statement and outputting that.

    case x:
    $order = 'value';
    $table = 'C';
    break;

    then in your order clause:

    ORDER BY " . $table . "." . $order . $direction);

    (my eyes are kinda cross-eyed right now - sorry if the syntax isn't correct!)

  3. #3
    SitePoint Wizard
    Join Date
    Mar 2004
    Posts
    1,647
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ORDER BY C." . $order . $direction);
    u miss here F...
    couse it orders just on C and not on F
    cheers

  4. #4
    SitePoint Addict
    Join Date
    Sep 2004
    Location
    Kelowna, BC
    Posts
    202
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    use the correct table name with each field in your switch statement based on whatever field name you're catching...

    $table = 'C';
    OR
    $table = 'F';

    are you looking for the exact code line by line that you need to make this work? or just an idea on how to get it working?

  5. #5
    SitePoint Wizard
    Join Date
    Mar 2004
    Posts
    1,647
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by fumbler
    try setting a variable for the table in the switch statement and outputting that.

    case x:
    $order = 'value';
    $table = 'C';
    break;

    then in your order clause:

    ORDER BY " . $table . "." . $order . $direction);
    good point!!!

  6. #6
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by fumbler
    use the correct table name with each field in your switch statement based on whatever field name you're catching...

    $table = 'C';
    OR
    $table = 'F';

    are you looking for the exact code line by line that you need to make this work? or just an idea on how to get it working?
    The exact code line would be great; this is pretty confusing for me.

    I tried this earlier:

    PHP Code:
    $res mysql_query("SELECT F.IDArea, C.IDArea, C.Name, Pop, Nationality, NationalityPlural, NationalityAdjective
      FROM cia_people C, famarea2 F
      WHERE C.Nationality is not null AND F.IDArea = 'usa'
      ORDER BY C. OR F." 
    $order $direction);
    if (!
    $res) {
      die(
    mysql_error());

    but it didn't work.

    I also tried this without success:

    PHP Code:
       case 6:
           
    $order 'famarea.IDParentReg';
           break; 
    I suspected I would have to change $order = 'IDParentReg'; to change $order = famarea.IDParentReg'; AND change this line:

    ORDER BY C." . $order . $direction);

    It looks like you want me to change that last line to this:

    ORDER BY " . $table . "." . $order . $direction);

    but where do I put this?

    PHP Code:
    case x:
    $order 'value';
    $table 'C';
    break; 
    Also, I should change it to this, right?

    PHP Code:
    [PHP]
    case 
    x:
    $order 'value';
    $table 'C';
    OR 
    $table 'F';
    break; 
    [/PHP]

    Thanks.

  7. #7
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Where you are selecting the sort by order, replace your code with this code

    PHP Code:
    switch($order)
    {
       case 
    1:
           
    $order 'cia_people.Name';
           break;
       case 
    2:
           
    $order 'cia_people.Pop';
           break;
       case 
    3:
           
    $order 'cia_people.Nationality';
           break;
       case 
    4:
           
    $order 'cia_people.NationalityPlural';
           break;
       case 
    5:
           
    $order 'cia_people.NationalityAdjective';
           break;
       case 
    6:
           
    $order 'famarea2.IDParentReg';
           break;
       default:
           
    $order 'cia_people.Name';
           break;

    Where you are selecting the sort direction, put this code

    PHP Code:
    switch($direction)
    {
      case 
    0:
        
    $direction 'ASC';
        break;

      case 
    1:
        
    $direction 'DESC';
        break;

      default:
        
    $direction 'ASC';
        break;

    Where you run the query, replace your code with this code
    PHP Code:
    $sql 'SELECT 
              F.IDArea,
              C.IDArea, C.Name, C.Pop, C.Nationality, C.NationalityPlural, C.NationalityAdjective
            FROM 
              cia_people C, famarea2 F
            WHERE 
              (C.Nationality is not null)
            AND 
              (F.IDArea = \'usa\')
            ORDER BY 
            ' 
    $order ' ' $direction;
    $res mysql_query($sql) or die('Failed to run ' . $ sql ' - ' mysql_error()); 

    Note, if you were to change the values specified in the form, you could eliminate the switch statements

    Your Code
    PHP Code:
    ...
      <
    option value="4">NationalityPlural</option>
      <
    option value="5">NationalityAdjective</option>
      <
    option value="6">Geographic Region</option>
    ... 
    My version
    PHP Code:
    ...
      <
    option value="cia_people.NationalityPlural'">NationalityPlural</option>
      <
    option value="cia_people.NationalityAdjective'">NationalityAdjective</option>
      <
    option value="famarea2.IDParentReg">Geographic Region</option>
    ... 
    Similarly for the direction value.

    Then, when building the $sql string, replace
    PHP Code:
            $order ' ' $direction
    with
    PHP Code:
            $_POST['order'] . ' ' $_POST['direction']; 

    Hope his helps

  8. #8
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow, thanks for the makeover!

    It isn't working quite yet, though. The immediate problem is...

    **** error: parse error, unexpected T_STRING in C:\sites\geoworld\about\guide\world\eur\remote.php on line 118

    Below are lines 117-119:

    ORDER BY
    $_POST['order'] . ' ' . $_POST['direction'];
    $res = mysql_query($sql) or die('Failed to run ' . $ sql . ' - ' . mysql_error());

    I was also confused by the statement "Similarly for the direction value." Or were you just leading into the next sentence, "Then, when building the $sql string, replace . . ."?

    Below is my current source code:

    PHP Code:
    <head>[DATABASE CONNECTION]</head>
             <div class="formdiv">
                <form action="remote.php" method="GET">
                  <select name="order">
              <option value="cia_people.Name'">Country, etc.</option>
              <option value="cia_people.Pop'">Population</option>
              <option value="cia_people.Nationality'">Nationality</option>
              <option value="cia_people.NationalityPlural'">Nationality: Plural</option>
              <option value="cia_people.NationalityAdjective'">Nationality: Adjective</option>
              <option value="famarea2.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 'cia_people.Name';
           break;
       case 
    2:
           
    $order 'cia_people.Pop';
           break;
       case 
    3:
           
    $order 'cia_people.Nationality';
           break;
       case 
    4:
           
    $order 'cia_people.NationalityPlural';
           break;
       case 
    5:
           
    $order 'cia_people.NationalityAdjective';
           break;
       case 
    6:
           
    $order 'famarea2.IDParentReg';
           break;
       default:
           
    $order 'cia_people.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, famarea2 F
            WHERE
              (C.Nationality is not null)
            AND
              (F.IDArea = \'usa\')
            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>&nbsp;</td></tr>\n";
    }
    }
    ?>
          </tr>
          </tbody>      
        </table>
    </body>
    </html>

  9. #9
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    //-- [...]
     
    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());
    }
     
    //-- This curly brace is too many: 
    //{
     
    //-- [...]
    $sql 'SELECT
    F.IDArea,
    C.IDArea, C.Name, C.Pop, C.Nationality, C.NationalityPlural, C.NationalityAdjective
    FROM
    cia_people C, famarea2 F
    WHERE
    (C.Nationality is not null)
    AND
    (F.IDArea = \'usa\')
    ORDER BY
     
    //-- This has to be changed:
    //$_POST['
    order'] . ' ' . $_POST['direction'];
    $_POST['
    order'], $_POST['direction'];
    //-- or 
    {$_POST['
    order']}, {$_POST['direction']};
     
    $res = mysql_query($sql) or die('
    Failed to run ' . $ sql . ' ' . mysql_error());
     
    //-- [...] 
     
    //-- This curly brace is too many: 
    //}
    ?>
    </tr>
    </tbody> 
    </table>
    </body>
    </html> 
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  10. #10
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. I made your changes, except that I only found one curly brace to replace. I now get this error message:

    Parse error: parse error, unexpected T_STRING in C:\sites\geoworld\about\guide\world\eur\remote.php on line 119

    Below are lines 118-120:

    PHP Code:
    ORDER BY
    $_POST
    ['order'], $_POST['direction'];
    $res mysql_query($sql) or die('Failed to run ' . $ sql ' - ' mysql_error()); 
    Also, what does this mean?

    //-- [...]

    PHP Code:
    <head>[DATABASE CONNECTION]</head>
              <div class="formdiv">
                <form action="remote.php" method="GET">
                  <select name="order">
              <option value="cia_people.Name'">Country, etc.</option>
              <option value="cia_people.Pop'">Population</option>
              <option value="cia_people.Nationality'">Nationality</option>
              <option value="cia_people.NationalityPlural'">Nationality: Plural</option>
              <option value="cia_people.NationalityAdjective'">Nationality: Adjective</option>
              <option value="famarea2.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 'cia_people.Name';
           break;
       case 
    2:
           
    $order 'cia_people.Pop';
           break;
       case 
    3:
           
    $order 'cia_people.Nationality';
           break;
       case 
    4:
           
    $order 'cia_people.NationalityPlural';
           break;
       case 
    5:
           
    $order 'cia_people.NationalityAdjective';
           break;
       case 
    6:
           
    $order 'famarea2.IDParentReg';
           break;
       default:
           
    $order 'cia_people.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, famarea2 F
    WHERE
    (C.Nationality is not null)
    AND
    (F.IDArea = \'usa\')
    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>&nbsp;</td></tr>\n";
    }
    }
    ?>
          </tr>
          </tbody>      
        </table>
    </body>
    </html>

  11. #11
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    then try, as suggested as well:
    PHP Code:
    {$_POST['order']}, {$_POST['direction']}; 
    The //-- [...] just means i'm leaving out some code
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  12. #12
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by frezno
    then try, as suggested as well:
    PHP Code:
    {$_POST['order']}, {$_POST['direction']}; 
    Hmmmm... I changed it, but I still get an error on the same line.

  13. #13
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmpf, the quotes have to escaped as well

    PHP Code:
    $sql 'SELECT 
    F.IDArea, 
    C.IDArea, C.Name, C.Pop, C.Nationality, C.NationalityPlural, C.NationalityAdjective 
    FROM 
    cia_people C, famarea2 F 
    WHERE 
    (C.Nationality is not null) 
    AND 
    (F.IDArea = \'usa\') 
    ORDER BY 
    $_POST[\'order\'], $_POST[\'direction\']'

    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  14. #14
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    BTW, to avoid an subsequent error, there's a space to much between $ and sql:

    Quote Originally Posted by geosite
    PHP Code:
    $res mysql_query($sql) or die('Failed to run ' . $ sql ' - ' mysql_error()); 
    PHP Code:
    $res mysql_query($sql) or die('Failed to run ' $sql ' - ' mysql_error()); 
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  15. #15
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now I get this error:

    Parse error: parse error, unexpected T_STRING, expecting T_VARIABLE or '$' in C:\sites\geoworld\about\guide\world\eur\remote.php on line 126

    PHP Code:
    $_POST[\'order\'], $_POST[\'direction\']';
    $res mysql_query($sql) or die('Failed to run ' . $ sql ' - ' mysql_error()); 
    I thought '$' usually meant you need to add or remove a curly brace, so I added one, but it didn't help. Thanks.

  16. #16
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    LOL - This is like a wild goose chase!

    I just now saw your post and fixed $sql. I then got another error, but I was able to fix it. So now I'm getting THIS error:

    Failed to run SELECT F.IDArea, C.IDArea, C.Name, C.Pop, C.Nationality, C.NationalityPlural, C.NationalityAdjective FROM cia_people C, famarea2 F WHERE (C.Nationality is not null) AND (F.IDArea = 'eur') ORDER BY $_POST['order'], $_POST['direction'] - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '['order'], $_POST['direction']' at line 11

    PHP Code:
    <head>[DATABASE CONNECTION]</head>
              <div class="formdiv">
                <form action="remote.php" method="GET">
                  <select name="order">
              <option value="cia_people.Name'">Country, etc.</option>
              <option value="cia_people.Pop'">Population</option>
              <option value="cia_people.Nationality'">Nationality</option>
              <option value="cia_people.NationalityPlural'">Nationality: Plural</option>
              <option value="cia_people.NationalityAdjective'">Nationality: Adjective</option>
              <option value="famarea2.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 'cia_people.Name';
           break;
       case 
    2:
           
    $order 'cia_people.Pop';
           break;
       case 
    3:
           
    $order 'cia_people.Nationality';
           break;
       case 
    4:
           
    $order 'cia_people.NationalityPlural';
           break;
       case 
    5:
           
    $order 'cia_people.NationalityAdjective';
           break;
       case 
    6:
           
    $order 'famarea2.IDParentReg';
           break;
       default:
           
    $order 'cia_people.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, famarea2 F
    WHERE
    (C.Nationality is not null)
    AND
    (F.IDArea = \'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>&nbsp;</td></tr>\n"
    ;
    }
    ?>
          </tr>
          </tbody>      
        </table>
    </body>
    </html>




    Quote Originally Posted by frezno
    BTW, to avoid an subsequent error, there's a space to much between $ and sql:

    PHP Code:
    $res mysql_query($sql) or die('Failed to run ' $sql ' - ' mysql_error()); 

  17. #17
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    New day new luck...

    Since i hate this escaping thingie let's get the query modified first:
    PHP Code:
    $sql "
    SELECT   F.IDArea, 
             C.IDArea, 
             C.Name, 
             C.Pop, 
             C.Nationality, 
             C.NationalityPlural, 
             C.NationalityAdjective 
    FROM     cia_people C,
             famarea2 F 
    WHERE    C.Nationality is not null
    AND      F.IDArea = 'eur'
    ORDER BY "
    $_POST['order'] .", "$_POST['direction']

    When using switch you're using just the variables $order and $direction. Could be you don't need the Superglobal POST in your query?
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •