Page 1 of 1
PHP Query Filter with multiple options - MySQL
Posted: Mon Jul 11, 2011 6:43 am
by pufAmuf
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:
Code: Select all
$query = "SELECT * FROM events WHERE Location='downtown'";
$result = mysql_query($query);
if (!$result) {
die('Invalid query: ' . mysql_error());
}
Thanks a lot!
Re: PHP Query Filter with multiple options - MySQL
Posted: Mon Jul 11, 2011 10:41 am
by social_experiment
Code: Select all
<?php
$qry = "SELECT field FROM table WHERE field = 'value' AND field2 = 'value2' AND field3 = 'value3'";
?>
Have you tried something like this?
Re: PHP Query Filter with multiple options - MySQL
Posted: Mon Jul 11, 2011 2:37 pm
by pufAmuf
Alright, here's what I tried. 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: Select all
<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: Select all
<?php
require("db_access.php");
function parseToXML($htmlStr)
{
$xmlStr=str_replace('<','<',$htmlStr);
$xmlStr=str_replace('>','>',$xmlStr);
$xmlStr=str_replace('"','"',$xmlStr);
$xmlStr=str_replace("'",''',$xmlStr);
$xmlStr=str_replace("&",'&',$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.
Re: PHP Query Filter with multiple options - MySQL
Posted: Tue Jul 12, 2011 1:39 am
by social_experiment
Code: Select all
<?php
$query = "SELECT Name,Address,Type FROM markers WHERE Name = 'Value1' AND Address = 'Value2' AND Type = 'Value3'";
?>
Value1, Value2 and Value3 shouldn't actually be used verbatim. They are your name, address and type value, in your case
Code: Select all
<?php
$Name=$_POST['Value1'];
$Address=$_POST['Value2'];
$Type=$_POST['Value3'];
//
$query = "SELECT Name, Address, Type FROM markers WHERE Name = '" . $Name . "' AND Address = "' .
$Address . "' AND type = '" . $Type . "' ";
?>
Re: PHP Query Filter with multiple options - MySQL
Posted: Tue Jul 12, 2011 5:47 am
by pufAmuf
Thanks so much for your help! Unfortunately I received an error:
Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in /home/nightl7/public_html/mysqlmap/get_xml2.php on line 35
So what I did was change the code like this:
Code: Select all
$query = "SELECT Name, Address, Type FROM markers WHERE Name = ' . $Name . ' AND Address = ' . $Address . ' AND type = ' . $Type . ' ";
Now I do not get an error but when I fill out the form, I get an empty xml.
Re: PHP Query Filter with multiple options - MySQL
Posted: Tue Jul 12, 2011 6:16 am
by social_experiment
Code: Select all
<?php
Name: <input type="text" name="Name"><br>
Address: <input type="text" name="Address"><br>
Type: <input type="text" name="Type"><br>
// name attribute values are used to retrieve corresponding
// values
$Name = $_POST['Name'];
$Address = $_POST['Address'];
$Type = $_POST['Type'];
?>
The values you place in each of the name attributes' are the values to use when retrieving their values from the post array.
Re: PHP Query Filter with multiple options - MySQL
Posted: Tue Jul 12, 2011 6:41 am
by pufAmuf
I can't believe I didn't notice that. However, I still get an empty xml file. Could the script be errorneous in the way that spaces and commas could be giving the database difficulties? Or how about if I search for type=restaurant, but leave others blank - also be the problem? Can the problem be that these fields are set to VARCHAR in MySQL?
Re: PHP Query Filter with multiple options - MySQL
Posted: Tue Jul 12, 2011 9:12 am
by social_experiment
If you echo the following to the browser, what is displayed?
Code: Select all
<?php
while ($row = @mysql_fetch_assoc($result)){
echo $row['name'] . '<br />';
echo $row['address'] . '<br />';
}
?>
Re: PHP Query Filter with multiple options - MySQL
Posted: Tue Jul 12, 2011 9:27 am
by pufAmuf
Piroshky Piroshky
1908 Pike pl, Seattle, WA
Wingdome
1416 E Olive Way, Seattle, WA
Mama's Mexican Kitchen
2234 2nd Ave, Seattle, WA
Crab Pot
1301 Alaskan Way, Seattle, WA
Sake House
2230 1st Ave, Seattle, WA
Ipanema Grill
1225 1st Ave, Seattle, WA
The Melting Pot
14 Mercer St, Seattle, WA
Buddha Thai & Bar
2222 2nd Ave, Seattle, WA
Pan Africa Market
address
Re: PHP Query Filter with multiple options - MySQL
Posted: Tue Jul 12, 2011 11:09 am
by social_experiment
So the query is working 100%. Only thing i can think of is a problem with the xml structure, your xml document doesn't contain this line just before you start creating the document
Code: Select all
<?xml version="1.0" encoding="ISO-8859-1"?>
Re: PHP Query Filter with multiple options - MySQL
Posted: Tue Jul 12, 2011 11:38 am
by pufAmuf
Hmm, I used it for getting complete data with the * and it retrieved everything. Can't there be a problem with the way the file.php communicates with get_xml2.php? Like using GET instead of POST or something?
For example, here's a modified xml.
http://tinyurl.com/6jcwbvq
Code: Select all
<?php
require("db_access.php");
function parseToXML($htmlStr)
{
$xmlStr=str_replace('<','<',$htmlStr);
$xmlStr=str_replace('>','>',$xmlStr);
$xmlStr=str_replace('"','"',$xmlStr);
$xmlStr=str_replace("'",''',$xmlStr);
$xmlStr=str_replace("&",'&',$xmlStr);
return $xmlStr;
}
// 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 * FROM markers WHERE 1";
$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>';
?>
Re: PHP Query Filter with multiple options - MySQL
Posted: Tue Jul 12, 2011 12:51 pm
by social_experiment
pufAmuf wrote:Like using GET instead of POST or something?
If $_POST wasn't working there wouldn't be any data retrieved from the database, i suspect using $_GET will have the same effect i.e retrieving the data but not printing the xml documents
Re: PHP Query Filter with multiple options - MySQL
Posted: Tue Jul 12, 2011 1:07 pm
by pufAmuf
But there isn't any data retrieved from the database. The code you gave me to echo some tables was placed in file.php, where the form happens to be. After inputting data to form.php, you get sent to get_xml2.php, where the data entered on the previous page can't be retrieved for some reason.
Works now:
$result = mysql_query($result);
Should be
$result = mysql_query($query);
Thanks so much for your help

)))))
Re: PHP Query Filter with multiple options - MySQL
Posted: Tue Jul 12, 2011 1:46 pm
by social_experiment
pufAmuf wrote:Piroshky Piroshky
1908 Pike pl, Seattle, WA
Wingdome
1416 E Olive Way, Seattle, WA
Mama's Mexican Kitchen
2234 2nd Ave, Seattle, WA
Crab Pot
1301 Alaskan Way, Seattle, WA
Sake House
2230 1st Ave, Seattle, WA
Ipanema Grill
1225 1st Ave, Seattle, WA
The Melting Pot
14 Mercer St, Seattle, WA
Buddha Thai & Bar
2222 2nd Ave, Seattle, WA
Pan Africa Market
address
This is not data?
Code: Select all
<?php
$query = "SELECT * FROM markers WHERE Name = '" . $Name . "' AND Address = '" . $Address . "' AND Type = '" . $Type . "'";
?>
Change your query to the format above. At the moment you are only selecting Name, Address and Type from the database but you want to display lng and lat as well.
Re: PHP Query Filter with multiple options - MySQL
Posted: Tue Jul 12, 2011 3:15 pm
by pufAmuf
Thanks a lot
