SELECT expression - multiple queries via a single script

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
fissssssi
Forum Newbie
Posts: 9
Joined: Mon Jun 05, 2006 5:04 pm

SELECT expression - multiple queries via a single script

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: SELECT expression - multiple queries via a single script

Post 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";
}
?>
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: SELECT expression - multiple queries via a single script

Post 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'];
fissssssi
Forum Newbie
Posts: 9
Joined: Mon Jun 05, 2006 5:04 pm

Post 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
Post Reply