#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    4
    Rep Power
    0

    Dynamically alter a query


    I' trying to display a database query. That worked OK, until I decided I need to use WHERE clause.

    So, I want to be able to use a Select OPTION to choose between showing cats, dogs or horses - but I'd the user to be able to do that, rather than hard code.

    The code I have at the moment is:
    PHP Code:
    <html>
    <head>
    <title>Pets Report</title>
    </head>
    <body>
    <table>
      <tr>
        <td align="center">Pets Data</td>
      </tr>
      <tr>
        <td>
          <table border="1">
          <tr>
          <td>ID</td>
            <td>NAME</td>
            <td>DESCRIPTION</td>
            <td>SIZE</td>
            <td>TYPEID</td>
            <td>OWNERID</td>
          </tr>
    <form method="POST"  action="<?php echo $_SERVER['REQUEST_URI']; ?>">      
    <?php
    require ("dbcnx.php");
    $get=mysql_query("SELECT typeid,types FROM types");
      
    /* create form containing selection list */
      
            
    echo"<select name='typeoption'>\n";

      while (
    $row mysql_fetch_array($get))
      {
         
    extract($row);
         echo 
    "<option value='$typeid'>$typeid $types\n";
      }
      echo 
    "</select>\n";
      
       
    ?>      
    </form>      
    <?php 
    require ("dbcnx.php");

    $order "SELECT * FROM pets ORDER BY petid
    WHERE petid = 
    {$_POST['typeoption']}"

    //order to search data - declare in the order variable
                    
    $result mysql_query($order);    
    //order executes the result is saved - in the variable of $result

                
    while($data mysql_fetch_row($result)){
    echo(
    "<tr><td>$data[0]</td><td>$data[1]</td><td>$data[2]</td><td>$data[3]</td><td>$data[4]</td><td>$data[5]</td></tr>");
    }

    ?>
    </table>

    </body>
    </html>
    The types table looks like:
    typeid
    type

    That displays OK too, in the Options box.

    The error is:
    Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/a2049476/public_html/pets/pets.php on line 50
    That's the line where I echo the data.

    Appreciate any advice to resolve the error.
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    your code is vulnerable to pretty much every attack known to man. Before you do anything, you first need to repair that. Read up on The 6 worst sins of security and then rewrite your code.

    This includes replacing the ancient mysql_* functions with one of the two contemporary database libraries (as explained in the link). After you've done that, you'll get a detailed error message telling you why the query above is syntactically invalid. So you kill two birds with one stone.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    In addition to what Jacques1 noted already, that particular error message means that your SQL query is invalid.

    In general, when you get stuck on an invalid query it often helps to echo out the query in the browser and debug it independently of PHP by running the query directly in MySQL.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    4
    Rep Power
    0
    Thanks for the advice re:security. I guess in the back of my mind, I knew this needed to be done. I had put _real_escape_string in all my forms where I required user input.

    I know I'll get flamed, but these idiots who exploit PHP/MYSQL have taken what was once a something you could try to teach kids at school (so they had an appreciation of what's going on behind the scenes), to something that is now fundamentally more complicated. Sorry about the frustration - not shooting the messenger. I do appreciate the advice.

    I think I implemented some fixes from 1 and 3 on the list of 6. The others didn't seem to apply in this particular instance.

    I tired to test it in a separate php file:
    PHP Code:
    <?php
    require_once dirname(__FILE__) . '/database.inc.php';   
    $specy=mysql_query("SELECT typeid,types FROM types");
    echo 
    $specy;
    ?>
    The errors are:
    Warning: mysql_query() [function.mysql-query]: Access denied for user 'a2049476'@'localhost' (using password: NO) in /home/a2049476/public_html/pets/test2.php on line 3
    Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in /home/a2049476/public_html/pets/test2.php on line 3
    The database.inc.php looks like:
    PHP Code:
    <?php
    $db_options 
    = array( 
        
    PDO::ATTR_EMULATE_PREPARES => false                     // important! use actual prepared statements (default: emulate prepared statements) 
        
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION           // throw exceptions on errors (default: stay silent) 
        
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC      // fetch associative arrays (default: mixed arrays) 
    ); 
    $database = new PDO('mysql:host=mysql4.xxxxx.com;dbname=xxxxx_pets;charset=utf8''xxxx_basil60''xxxxx'$db_options);    // important! specify the character encoding in the DSN string, don't use SET NAMES  
    ?>
    Any advice most welcome.
  8. #5
  9. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    You can't mix PDO and mysql_*, but it's better to use PDO. To run a prepared query using PDO you do:
    PHP Code:
    $st $database->prepare("SELECT typeid,types FROM types");
    $st->execute();
    $rows $st->fetchAll();

    // var_dump is not a part of running the query, it's just an example of outputting the results
    var_dump($rows); 
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around

IMN logo majestic logo threadwatch logo seochat tools logo