homepage Welcome to WebmasterWorld Guest from 54.205.193.39
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
A bit of help on a MySQL query
Syntax for 2 queries in one call to a MySQL db
lorax




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

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.

 

coopster




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

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

lorax




msg:4059577
 2:37 am on Jan 13, 2010 (gmt 0)

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.

coopster




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

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.

lorax




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

Via $_POST['var_name'] right?

lorax




msg:4060024
 5:30 pm on Jan 13, 2010 (gmt 0)

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?

coopster




msg:4065652
 1:22 pm on Jan 22, 2010 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved