PHP Query Filter with multiple options - MySQL

**pufAmuf** · 07-11-2011 06:39 AM

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!

**NogDog** · 07-11-2011 12:28 PM

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

**pufAmuf** · 07-11-2011 03:39 PM

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.

**NogDog** · 07-11-2011 08:30 PM

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.

**pufAmuf** · 07-12-2011 05:51 AM

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?

**NogDog** · 07-12-2011 11:43 AM

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.

**pufAmuf** · 07-12-2011 11:56 AM

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>';

?>

**pufAmuf** · 07-12-2011 01:46 PM

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: PHP Query Filter with multiple options - MySQL

Thread Tools

Display

PHP Query Filter with multiple options - MySQL

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions