Welcome to WebmasterWorld Guest from 54.166.178.177

Forum Moderators: open

Message Too Old, No Replies

A bit of help on a MySQL query

Syntax for 2 queries in one call to a MySQL db

     
1:13 am on Jan 13, 2010 (gmt 0)

WebmasterWorld Senior Member lorax is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



The select box and checkbox for the form

<h2>Choose a Town</h2>
<select name="Town">
<option value="px" selected> all towns </option>
<option value="px"> -------------- </option>
<?php
$farmq = "SELECT DISTINCT Town
FROM producers
WHERE ShowProd = TRUE
ORDER BY Town";
$farmr = mysql_query($farmq) or die("Select Query failed: ".mysql_error());
$num_farm_rows = mysql_num_rows($farmr);
for($i=1; $i<=$num_farm_rows; $i++) {
$all = mysql_fetch_array($farmr);
if($_POST['Town'] == $all['Town']) $sel = " selected"; else $sel = '';
echo "<option value=\"".$all['Town']."\" $sel>".$all['Town']."</option>";
}
?>
</select>
<p><input type="checkbox" value="1" name="cty" /> Search for all towns in the county this town is in?</p>

Here's the initial query


SELECT producers.pid,producers.FarmName,producers.LName1,producers.FName1
FROM producers
WHERE producers.ShowProd = TRUE AND producers.Town = '".$_POST['Town']."'
ORDER BY FarmName,LName1

I have a check box on the form that supplies the parameters to the handler (where this code comes from) that the user can use to indicate if they want all farms within the county the chosen town is within. Counties are a part of the farm record.

I'm pretty sure I can get the county the town is in and then find all of the other towns but I'm not sure how to write the query.

1:58 am on Jan 13, 2010 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Subqueries are probably your answer. This all seems kind of redundant as all towns is the first option and is the default selected option and yet the checkbox for county is there too. Or does that mean all towns no matter which county, meaning it may ignore county altogether? So that would mean you need to do some pre-filtering logic before we even get to this other query, right?

All right, no matter I must say the first thing to do is escape that user-supplied data -- meaning do not ever trust raw $_POST data. PHP has the MySQL API mysql_real_escape_string [php.net] to help you there.

$town = mysql_real_escape_string($_POST['Town']);

Next, the query you have will select just that town. Add the town column to your options list and add the county logic to your WHERE clause, if the checkbox is present. You could use DISTINCT or "LIMIT 1", either would suffice. Your result set can be ordered by town first if you would like. Then as you loop through you can separate by town (ORDER BY if necessary).


// rough idea, but should get you started
// (untested code, by the way ;)
// ... and this assumes a town was checked, not all towns
$town = mysql_real_escape_string($_POST['Town']);
if (isset($_POST['cty'])) {
$where =
"producers.countyFieldName =
(SELECT DISTINCT
producers.countyFieldName
FROM producers
WHERE
producers.ShowProd = TRUE AND
producers.Town = '{$town}')
"
;
} else {
$where = "WHERE producers.ShowProd = TRUE AND producers.Town = '{$town}'";
}
$sql=
"SELECT
producers.pid,
producers.FarmName,
producers.LName1,
producers.FName1,
producers.Town
FROM producers
{$where}
ORDER BY
FarmName,
LName1"
;

Like I said, this may contain errors but hopefully you can see where it is headed. Give it a shot and if you have troubles, come back ;)
2:37 am on Jan 13, 2010 (gmt 0)

WebmasterWorld Senior Member lorax is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



Ah! Subqueries - that's the term I was looking for.

All towns searches for all of the towns in the db that fit the criteria (2 other filters). The checkbox is only for towns within the same county.

>> escape that user-supplied data

You're right of course. Should I still apply the escape even if that data is from a drop down list? Or are you thinking the form is still open to exploit?

Thanks coop - I'll take what you posted and work with it.

5:08 pm on Jan 13, 2010 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Should I still apply the escape even if that data is from a drop down list?

Yes. One can easily manipulate a drop down list value and post the form to your server.

5:27 pm on Jan 13, 2010 (gmt 0)

WebmasterWorld Senior Member lorax is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



Via $_POST['var_name'] right?
5:30 pm on Jan 13, 2010 (gmt 0)

WebmasterWorld Senior Member lorax is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



Is there a way to loop through the $_POST variables and "wash" them using mysql_real_escape_string(), then stick them back into the $_POST array?
1:22 pm on Jan 22, 2010 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



You could do that, but you are best served analyzing form data for what you expect to see, discard everything else.

And yes to the $_POST question/confirmation. And it goes beyond that. A user can "File > Save" your html their desktop, modify the html and then post it to your server. Users can append data to the query string which modifies your $_GET superglobal array. Even $_SERVER vars can contain user-supplied data. $_SERVER['REQUEST_URI'] comes from the address bar in the browser.

Like I said, you are best served analyzing all input data, one by one.

 

Featured Threads

Hot Threads This Week

Hot Threads This Month