Page 1 of 1

SELECT expression - multiple queries via a single script

Posted: Mon Jun 05, 2006 5:13 pm
by fissssssi
This is my first day working with php and mySQL so apologies if I'm missing something obvious. I used to think I could Google, but how do you write a query about writing queries?! There is so much information that not knowing what I'm looking for is the hardest part! Please feel free to direct me at the right guidance - I'm happy to RTFM if I only knew where to begin.

I have set up a table which is basically a list of members with various attributes - some yes/no type fields, a catA,catB,catC field, a URL field and the member name.

I've managed to use php to display a list of members, linked to their websites where they exist and can filter by a single field (memCat) by putting

Code: Select all

$query="SELECT * FROM Members WHERE memCat='$memCat' ORDER BY memName ASC";
Ideally, I'd like to complete this php script and use the different fields in the URL, e.g. members.php?memCat=I&memD=D

However, I've come unstuck on two different questions.

1) How do I make it show all records? leaving memCat= out of the URL doesn't work and I've looked at wildcards but can't figure them out - I've tried _ and % but must be missing something

2) How do I make it only filter by those fields listed in the URL (e.g. if the URL is members.php?memD=D how do I make it show all records that have memD=D no matter what the value of memCat is?

Thanks for any help you can give me!


Adam

Re: SELECT expression - multiple queries via a single script

Posted: Mon Jun 05, 2006 6:21 pm
by RobertGonzalez
fissssssi wrote:1) How do I make it show all records? leaving memCat= out of the URL doesn't work and I've looked at wildcards but can't figure them out - I've tried _ and % but must be missing something

2) How do I make it only filter by those fields listed in the URL (e.g. if the URL is members.php?memD=D how do I make it show all records that have memD=D no matter what the value of memCat is?
1.

Code: Select all

<?php
$query="SELECT * FROM Members ORDER BY memName ASC";
?>
2.

Code: Select all

<?php
if (isset($_GET['memD']))
{
    $memD = $_GET['memD'];
    $query="SELECT * FROM Members WHERE memD='$memD' ORDER BY memName ASC";
}
?>

Re: SELECT expression - multiple queries via a single script

Posted: Tue Jun 06, 2006 7:38 am
by shiznatix
Everah wrote:

Code: Select all

<?php
if (isset($_GET['memD']))
{
    $memD = $_GET['memD'];
    $query="SELECT * FROM Members WHERE memD='$memD' ORDER BY memName ASC";
}
?>
please please please sanitize your user input before putting it into a query

Code: Select all

$memD = mysql_real_escape_string($_GET['memD']);
//or if you are only going to get a int then make sure it is one
$memD = (int)$_GET['memD'];

Posted: Tue Jun 06, 2006 12:20 pm
by fissssssi
Thank you both for your speedy responses.

I take it the santizing is to remove any nasty type code that could try and do other things than what's intended and possibly be harmful?

What I'm trying to do is have a single php page that lists the members which can then be filtered by entering one *or more* fields in the URL. From shiznatix's response, building on Everah's, I see the

Code: Select all

if (isset ($GET['memD'])){
section. Does that mean the way to do it is to combine the fields I want to filter by so that I end up with if, else, else, else, else for the various combinations of fields that could be included or not included in the URL to end up with the $query line I need?

Thanks again,


Adam