SitePoint Sponsor

User Tag List

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

    How do you echo a select statement ("before it hits MySQL")?

    This script is driving me up the wall. When I don't have a parse error on this line...

    PHP Code:
    ORDER BY ' . {$_POST['order']} . ',' . {$_POST['direction']}; 
    ...I get this error message:

    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

    (Right now I'm getting the parse error.)

    Someone told me I could find out what the problem is if I echo the select statement "before it hits MySQL."

    How do I do that?

    Thanks.

    PHP Code:
    <head>[DATABASE CONNECTION]</head>
    <body>
              <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>

  2. #2
    SitePoint Wizard Lats's Avatar
    Join Date
    Jun 2003
    Location
    Melbourne, AU
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You're basically echoed out the statement in your die

    Try this...
    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']."'"
    Lats...

  3. #3
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, that's an improvement; it fixed my parse error. But now I'm back to this error message:

    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 '','' - 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 '\'eur\') ORDER BY '',''' at line 10

    I'm not sure if there's something esoteric involved here or if I'm just making a very basic mistake(s). Thanks.

    PHP Code:
    <head>[DATABASE CONNECTION]</head>
    <body>
              <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>

  4. #4
    SitePoint Wizard Lats's Avatar
    Join Date
    Jun 2003
    Location
    Melbourne, AU
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The obvious error there is (F.IDArea = \'eur\'), so change that to (F.IDArea = 'eur').

    It's also not picking up any values for the sort order.
    Lats...

  5. #5
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Lats
    The obvious error there is (F.IDArea = \'eur\'), so change that to (F.IDArea = 'eur').

    It's also not picking up any values for the sort order.
    OK, the pieces are beginning to fall in place now. Thanks.

  6. #6
    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)
    items used by ORDER BY where not (single) quoted since they reflect fieldnames !
    You've to change that too.
    See my last query correction in your other thread (didn't know you opened this one, sorry).
    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
  •