www.webdeveloper.com
Results 1 to 8 of 8

Thread: PHP Query Filter with multiple options - MySQL

  1. #1
    Join Date
    Jul 2011
    Posts
    5

    Smile PHP Query Filter with multiple options - MySQL

    Hi everyone,
    I'm trying to create a filter that requests data from a table and outputs it into xml.


    I've been introduced to the $query = "SELECT something FROM table WHERE some value"
    however I'm confused as to how I can do that when the request from the cient side is
    something like: BAR, in DOWNTON for date JULY 11, where entrance is LESS THAN 10.

    I have a simple code that I'm trying to get to work with an html page with no luck. I've
    searched google for two days straight but found nothing concretely for a begginer like me.
    If you have the time and/or some examples that you can point me to, I'd be thankful.

    Here's what I have:




    $query = "SELECT * FROM events WHERE Location='downtown'";
    $result = mysql_query($query);
    if (!$result) {
    die('Invalid query: ' . mysql_error());
    }


    Thanks a lot!

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,529
    Not sure if this answers your question, but you can have multiple criteria within a where clause, using the logical AND and OR operators:
    Code:
    SELECT * FROM events WHERE type='bar' AND location='downtown' AND entrance < 10
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Jul 2011
    Posts
    5
    Thanks, I tried doing that and this is what I have. I have some code below that retrieves all data from my table. Now, in the textbox, let's say that I input "bar". I want it to send a query to mysql to display all rows (in an xml format) which TYPE = bar. Here's some code below.
    Code:
    <html>
    <body>
    <?php
    $username="****";
    $password="*****";
    $database="****";
    
    mysql_connect(localhost,$username,$password);
    @mysql_select_db($database) or die( "Unable to select database");
    $query="SELECT * FROM markers";
    $result=mysql_query($query);
    
    $num=mysql_num_rows($result);
    
    mysql_close();
    ?>
    
    
    <form action="get_xml2.php" method="post">
    Name: <input type="text" name="Name"><br>
    Address: <input type="text" name="Address"><br>
    Type: <input type="text" name="Type"><br>
    <input type="Submit">
    </form>
    
    <table border="0" cellspacing="2" cellpadding="2">
    <tr>
    <th><font face="Arial, Helvetica, sans-serif">Name</font></th>
    <th><font face="Arial, Helvetica, sans-serif">Address</font></th>
    <th><font face="Arial, Helvetica, sans-serif">Type</font></th>
    </tr>
    
    <?php
    $i=0;
    while ($i < $num) {
    
    $f1=mysql_result($result,$i,"Name");
    $f2=mysql_result($result,$i,"Address");
    $f3=mysql_result($result,$i,"Type");
    ?>
    
    <tr>
    <td><font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font></td>
    <td><font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font></td>
    <td><font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font></td>
    </tr>
    
    <?php
    $i++;
    }
    ?>


    And here is get_xml2.php

    Code:
    <?php
    require("db_access.php");
    
    function parseToXML($htmlStr) 
    { 
    $xmlStr=str_replace('<','&lt;',$htmlStr); 
    $xmlStr=str_replace('>','&gt;',$xmlStr); 
    $xmlStr=str_replace('"','&quot;',$xmlStr); 
    $xmlStr=str_replace("'",''',$xmlStr); 
    $xmlStr=str_replace("&",'&amp;',$xmlStr); 
    return $xmlStr; 
    } 
    
    $Name=$_POST['Value1'];
    $Address=$_POST['Value2'];
    $Type=$_POST['Value3'];
    
    
    // Opens a connection to a MySQL server
    $connection=mysql_connect (localhost, $username, $password);
    if (!$connection) {
      die('Not connected : ' . mysql_error());
    }
    
    // Set the active MySQL database
    $db_selected = mysql_select_db($database, $connection);
    if (!$db_selected) {
      die ('Can\'t use db : ' . mysql_error());
    }
    
    
    
    
    // Select all the rows in the markers table
    $query = "SELECT Name,Address,Type FROM markers WHERE Name = 'Value1' AND Address = 'Value2' AND Type = 'Value3'";
    $result = mysql_query($query);
    if (!$result) {
      die('Invalid query: ' . mysql_error());
    }
    
    header("Content-type: text/xml");
    
    // Start XML file, echo parent node
    echo '<markers>';
    
    // Iterate through the rows, printing XML nodes for each
    while ($row = @mysql_fetch_assoc($result)){
      // ADD TO XML DOCUMENT NODE
      echo '<marker ';
      echo 'name="' . parseToXML($row['name']) . '" ';
      echo 'address="' . parseToXML($row['address']) . '" ';
      echo 'lat="' . $row['lat'] . '" ';
      echo 'lng="' . $row['lng'] . '" ';
      echo 'type="' . $row['type'] . '" ';
      echo '/>';
    }
    
    // End XML file
    echo '</markers>';
    
    ?>
    Here is a working example
    http://tinyurl.com/6a76aek

    As you can see, entering anything will return an empty xml file.

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,529
    Don't you want to use your form value variables ($Name, etc.) in the query where you currently have "Value1", etc.? On a side note, you'll want to apply mysql_real_escape_string() to those variables before using them in the query, in order to avoid SQL injection.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    Jul 2011
    Posts
    5
    Thanks, I changed the code now to this:
    Code:
    $query = "SELECT Name, Address, Type FROM markers WHERE Name = ' .$Name. ' AND Address = ' .$Address. ' AND type = ' .$Type. ' ";
    However, I still receive no results when I submit the form. (I put a link above to a working example)

    About the sql injection, could you explain further how I can implement that string?

  6. #6
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,529
    There is no need for concatenation:
    PHP Code:
    $query "SELECT Name, Address, Type FROM markers WHERE Name = '$Name' AND Address = '$Address' AND type = '$Type'"
    To add injection prevention (and get some debug info if it fails), you could do:
    PHP Code:
    $query sprintf(
       
    "SELECT Name, Address, Type FROM markers WHERE Name = '%s' AND Address = '%s' AND type = '%s'",
       
    mysql_real_escape_string($Name),
       
    mysql_real_escape_string($Address),
       
    mysql_real_escape_string($Type)
    );
    $result mysql_query($result);
    if(
    $result == false) {
       die(
    mysql_error() . "<br />\n$query");
    }
    if(
    mysql_num_rows($result) == 0) {
       
    user_error("No rows returned by:<br />\n$query");

    (Make sure display_errors is turned off in production version so that the DB query is not displayed to the user should a valid "no rows returned" situation occur.)

    You may also want to look at Undoing Magic Quotes to ensure that you do not end up "double escaping" your inputs.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  7. #7
    Join Date
    Jul 2011
    Posts
    5
    Hmmm, in that case I think there's a problem with the form communicating with the php file itself.
    (I created a row with the attributes name1,address1,type1)

    Query was empty
    SELECT Name, Address, Type FROM markers WHERE Name = 'name1' AND Address = 'address1' AND type = 'type1'

    Here's what the xml looks like now:
    Code:
    <?php
    require("db_access.php");
    
    function parseToXML($htmlStr) 
    { 
    $xmlStr=str_replace('<','&lt;',$htmlStr); 
    $xmlStr=str_replace('>','&gt;',$xmlStr); 
    $xmlStr=str_replace('"','&quot;',$xmlStr); 
    $xmlStr=str_replace("'",''',$xmlStr); 
    $xmlStr=str_replace("&",'&amp;',$xmlStr); 
    return $xmlStr; 
    } 
    
    $Name=$_POST['Name'];
    $Address=$_POST['Address'];
    $Type=$_POST['Type'];
    
    
    // Opens a connection to a MySQL server
    $connection=mysql_connect (localhost, $username, $password);
    if (!$connection) {
      die('Not connected : ' . mysql_error());
    }
    
    // Set the active MySQL database
    $db_selected = mysql_select_db($database, $connection);
    if (!$db_selected) {
      die ('Can\'t use db : ' . mysql_error());
    }
    
    
    
    
    // Select all the rows in the markers table
    $query = sprintf(
       "SELECT Name, Address, Type FROM markers WHERE Name = '&#37;s' AND Address = '%s' AND type = '%s'",
       mysql_real_escape_string($Name),
       mysql_real_escape_string($Address),
       mysql_real_escape_string($Type)
    );
    $result = mysql_query($result);
    if($result == false) {
       die(mysql_error() . "<br />\n$query");
    }
    if(mysql_num_rows($result) == 0) {
       user_error("No rows returned by:<br />\n$query");
    } 
    
    header("Content-type: text/xml");
    
    // Start XML file, echo parent node
    echo '<markers>';
    
    // Iterate through the rows, printing XML nodes for each
    while ($row = @mysql_fetch_assoc($result)){
      // ADD TO XML DOCUMENT NODE
      echo '<marker ';
      echo 'name="' . parseToXML($row['name']) . '" ';
      echo 'address="' . parseToXML($row['address']) . '" ';
      echo 'lat="' . $row['lat'] . '" ';
      echo 'lng="' . $row['lng'] . '" ';
      echo 'type="' . $row['type'] . '" ';
      echo '/>';
    }
    
    // End XML file
    echo '</markers>';
    
    ?>
    Last edited by pufAmuf; 07-12-2011 at 12:58 PM.

  8. #8
    Join Date
    Jul 2011
    Posts
    5
    FIXED


    $result = mysql_query($result);

    Should be

    $result = mysql_query($query);
    Thanks for all your help I probably wouldn't have done it without you )))

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles