how to use several criteria to retrieve the specific email address in database

hi

i am doing mailinglist currently. the code in my first page is like this :

Expand|Select|Wrap|Line Numbers

 :

<html>

    <head>

    <link rel="stylesheet" type="text/css" href="gallery.css" />

<script language="JavaScript">
 
<!--
 
function validate_form ( )

{

    valid = true;

 if ( document.mailinglist.years.value == "" )

    {

        valid = true;

    }  
 
else if((document.mailinglist.years.value != "") &&((/^((\d{1})|(\d{2}))$/).test(mailinglist.years.value) ==false)){

alert ( "your input is incorrect,please enter numerical value");

        mailinglist.years.focus();

        valid = false;

    }
 
        return valid;

        }

        //-->

</script>
 
</head>
 
    <body>

    <div id="header">Mailing List

</div>

<div id="leftcol">    

</div>

<div id="maincol">

    <form method = 'POST' name= 'mailinglist' action = 'mailinglist.php' onSubmit = 'return validate_form ();'>

    Select any one of below options to check Mailing list<br> 

        Age: 

         <select name="age">

                            <option value="">---</option>

                            <option value="15-25">15-25years</option>

                            <option value="26-35">26-35years</option>

                            <option value="36-45">36-45years</option>

                            <option value="46-60">46-60years</option>

                            <option value="60+">60+years</option>

                            </select>

        </select>
 
        <br><br>

        Gender: 

        <select name = 'gender'value='3'>

            <option value = ''>--</option>

            <option value = 'Female'>Female</option>

            <option value = 'Male'>Male</option>
 
        </select>

        <br><br>
 
        Country: 

        <select name="country" tabindex="11">

                            <option value="">---</option>

                            <option value="Indonesia" >Indonesia  </option>

                            <option value="Malaysia" >Malaysia  </option>

                            <option value="Philippines" >Philippines   </option>

                            <option value="Singapore" > Singapore </option>

                            <option value="Thailand" > Thailand</option>

                            <option value="Vietnam " > Vietnam  </option>

                            <option value="Laos" >Laos </option>

                            <option value="Myanmar" > Myanmar </option>

                            <option value="Cambodia" > Cambodia </option>

                            <option value="Brunei" > Brunei </option>

        </select>

        <br><br>
 
   <td><font size="4"color="red">*</font>write down your number of years played golf: </td> <td><input type = "text" name="years"></td>
 
    <tr><td><i>(Please enter in numerical figures)</i></td></tr>    

                    <br><br>

                    <input type = 'submit' value = 'Show Mailinglist'>

        </form>

        </div>

</body>

</html>

i would like to let user retrieve email address of user by choosing gender alone or gender with age, gender with country or gender with years . similarly, they can also choose three criteria, for example, gender with country with age. or similarly, they can combine four criterias in order to select the specific email address. for example gender with country with age with years( this is no of years people played golf)

the coding of my next page mailinglist.php is in the following:

Expand|Select|Wrap|Line Numbers

 :

<?

//session_start() does not create new session, but identifies same session from do_login.php

session_start();

require("db.php");
 
mysql_connect(MACHINE, USER, '');

mysql_select_db(DBNAME);
 
$gender=$_POST['gender'];

$country=$_POST['country'];

$age=$_POST['age'];

$years=$_POST['years'];
 
    if ($age){

        $selEmail= "SELECT * from users where age ='".$age."'";

}
 
    elseif($gender){

        //$selEmail = "SELECT * from mailinglist";

                $selEmail= "SELECT * from users where gender ='".$gender."'";

    }

    if(($gender)&&($age)){

        //$selEmail = "SELECT * from mailinglist";

                $selEmail= "SELECT * from users where gender ='".$gender."'"."and age ='".$age."'";

                echo $selEmail;

    }
 
    elseif($country){

        //$selEmail = "SELECT * from mailinglist";

                $selEmail= "SELECT * from users where country='".$country."'";

                }

    if(($country)&&($age)){
 
                $selEmail= "SELECT * from users where country ='".$country."'"."and age ='".$age."'";

                echo $selEmail;

                }

    if(($country)&&($gender)){
 
                $selEmail= "SELECT * from users where country ='".$country."'"."and gender='".$gender."'";

                echo $selEmail;

                }

    if(($country)&&($years)){
 
                $selEmail= "SELECT * from users where country ='".$country."'"."and years ='".$years."'";

                echo $selEmail;

    }
 
    elseif($years){

        //$selEmail = "SELECT * from mailinglist";

                $selEmail= "SELECT * from users where years='".$years."'";

    }

    if (($years)&&($age)){

               $selEmail= "SELECT * from users where years='".$years."'"."and age ='".$age."'";

      }

     if(($years)&&($gender)){
 
                $selEmail= "SELECT * from users where country ='".$country."'"."and gender ='".$gender."'";

                echo $selEmail;

                }
 
        $a = mysql_query($selEmail);
 
    echo "<table border = '1'>";

    //normal header row
 
    //advance header row for Q17

    echo "<tr>";

    echo "<td><b>Name</b></td>";

    echo "<td><b>Email</b></td>";

    echo "<td><b>Age</b></td>";

    echo "<td><b>Gender</b></td>";

    echo "<td><b>Country</b></td>";

    echo "<td><b>Years Played Golf</b></td>";

    //echo "<td><b>Delete?</b></td>";

    //echo "<td><b>Update?</b></td>";

    echo "</tr>";

    //////////////////////////////////////////////////////
 
    while($thisEmail = mysql_fetch_array($a)){

        $name=$thisEmail['name'];

        $age = $thisEmail['age'];

        $gender = $thisEmail['gender'];

        $country = $thisEmail['country'];

        $years=$thisEmail['years'];

        $email=$thisEmail['email'];

        echo "<tr>";

        echo "<td>".$name."</td>";

        echo "<td>".$email."</td>";

        echo "<td>".$age."</td>";

        echo "<td>".$gender."</td>";

        echo "<td>".$country."</td>";

        echo "<td>".$years."</td>";

        //echo "<td><a href=\"do_delete.php?id=".$id."\">Delete</a></td>";

        //echo "<td><a href=\"updatecontact.php?id=".$id."\">Update</a></td>";

        echo "</tr>";        

    }

    echo "</table>";

    echo"<a href=mailinglist.html>go back</a>";

    echo"<br>";

    echo"<a href=index.php>go back to home page</a>";

?>

i think my code until now can only let user use one criteria or two criteria to retrieve emailing address from database. how can i let them use three criteria and four criteria to retrieve emailing address from database so that they can get the specific email address they want.

anyone can help me with this, thanks in advance. :)

Jul 7 '07 #1

Subscribe Post Reply

2031

pbmods

5,821

Expert 4TB

Heya, Kang.

Instead of creating a separate query for each case, try something like this:

Expand|Select|Wrap|Line Numbers

 
$sql = 'SELECT * FROM `users` ';

$search = '';
 
foreach($_POST as $field => $val)

    if(! empty($val))

        $search .= ($search ? ' AND' : 'WHERE') . " (`{$field}` = '{$val}')";
 
if(empty($search)) {

    // No search criteria.  Generate an error.

    .

    .

    .

} else {

    // Execute the search and process the results.

    $result = mysql_query($sql . $search);

    .

    .

    .

}

This will run through ALL values in $_POST and append them to the search query.

Jul 9 '07 #2

kang jia

Heya, Kang.

Instead of creating a separate query for each case, try something like this:

Expand|Select|Wrap|Line Numbers

$sql = 'SELECT * FROM `users` ';

$search = '';

foreach($_POST as $field => $val)

    if(! empty($val))

        $search .= ($search ? ' AND' : 'WHERE') . " (`{$field}` = '{$val}')";

if(empty($search)) {

    // No search criteria.  Generate an error.

    .

    .

    .

} else {

    // Execute the search and process the results.

    $result = mysql_query($sql . $search);

    .

    .

    .

}

This will run through ALL values in $_POST and append them to the search query.

hi
thanks your kind reply, now my code is

Expand|Select|Wrap|Line Numbers

 
<?

session_start();

require("db.php");
 
mysql_connect(MACHINE, USER, '');

mysql_select_db(DBNAME);
 
$gender=$_POST['gender'];

$country=$_POST['country'];

$age=$_POST['age'];

$years=$_POST['years'];

$sql="SELECT * from users";

$search='';

foreach($_POST as $field=>$val)

if (!empty ($val))

$search.=($search ? 'AND':'WHERE')."('{$field}'='{$val}')";

if (empty($search)){

//no search criteria, Generate an error.

echo "<script>alert('you did not select any criteria to view email list ')</script>";

    }

else{

//execute the search and process the results

$result=mysql_query($sql.$search);

}

    echo "<table border = '1'>";

    //normal header row
 
    //advance header row for Q17

    echo "<tr>";

    echo "<td><b>Name</b></td>";

    echo "<td><b>Email</b></td>";

    echo "<td><b>Age</b></td>";

    echo "<td><b>Gender</b></td>";

    echo "<td><b>Country</b></td>";

    echo "<td><b>Years Played Golf</b></td>";

    //echo "<td><b>Delete?</b></td>";

    //echo "<td><b>Update?</b></td>";

    echo "</tr>";

    //////////////////////////////////////////////////////
 
    while($thisEmail = mysql_fetch_array($result)){

        $name=$thisEmail['name'];

        $age = $thisEmail['age'];

        $gender = $thisEmail['gender'];

        $country = $thisEmail['country'];

        $years=$thisEmail['years'];

        $email=$thisEmail['email'];

        echo "<tr>";

        echo "<td>".$name."</td>";

        echo "<td>".$email."</td>";

        echo "<td>".$age."</td>";

        echo "<td>".$gender."</td>";

        echo "<td>".$country."</td>";

        echo "<td>".$years."</td>";

        //echo "<td><a href=\"do_delete.php?id=".$id."\">Delete</a></td>";

        //echo "<td><a href=\"updatecontact.php?id=".$id."\">Update</a></td>";

        echo "</tr>";        

    }

    echo "</table>";

    echo"<a href=mailinglist.html>go back</a>";

    echo"<br>";

    echo"<a href=index.php>go back to home page</a>";

?>

However when i execute the code, the page always complain that there is errror: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource. i don;t know where is wrong with this while loop.

PS. if you don;t mind, can explain to me of the code:

Expand|Select|Wrap|Line Numbers

 
      $search .= ($search ? ' AND' : 'WHERE') . " (`{$field}` = '{$val}')";

from my understand i know you are doing sql select query, but where does "?" ":" and {$field} means? in fact, this is the first time i see this kind of selection. As i have to do my presentation to the judge, so i also have to explain to them of the meaing. thank you very much :) if possible, can reply me ASAP, as the project's deadline is very near. a bit urgent of the time now.

Jul 10 '07 #3

pbmods

5,821

Expert 4TB

Heya, Kang.

However when i execute the code, the page always complain that there is errror: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource. i don;t know where is wrong with this while loop.

There's something wrong with the query. Change line 23:

Expand|Select|Wrap|Line Numbers

 
if(! ($result = mysql_query($sql . $search))

    exit($sql . $search . "\n" . mysql_error());

This will set $result, and if it is invalid will tell you what is wrong.

PS. if you don;t mind, can explain to me of the code:

Expand|Select|Wrap|Line Numbers

$search .= ($search ? ' AND' : 'WHERE') . " (`{$field}` = '{$val}')";

from my understand i know you are doing sql select query, but where does "?" ":" and {$field} means? in fact, this is the first time i see this kind of selection. As i have to do my presentation to the judge, so i also have to explain to them of the meaing. thank you very much :)

Sure. The '? :' is part of PHP's ternary operator. What we're doing in that statement is prepending 'WHERE' if we're adding the first search term. Otherwise, we'll prepend 'AND' instead.

As far as putting '{}' around variables in strings, it's not strictly necessary, but I like to do it to make my code easier to read (for me anyway). It's known as complex syntax.

Jul 10 '07 #4

kang jia

Heya, Kang.

There's something wrong with the query. Change line 23:

Expand|Select|Wrap|Line Numbers

if(! ($result = mysql_query($sql . $search))

exit($sql . $search . "\n" . mysql_error());

This will set $result, and if it is invalid will tell you what is wrong.

Sure. The '? :' is part of PHP's ternary operator. What we're doing in that statement is prepending 'WHERE' if we're adding the first search term. Otherwise, we'll prepend 'AND' instead.

As far as putting '{}' around variables in strings, it's not strictly necessary, but I like to do it to make my code easier to read (for me anyway). It's known as complex syntax.

thank you very much, now it works, if we go into a bit deep, how can i alter users if database don;t have their selected criteria for the email. thanks

Jul 11 '07 #5

by: Sami | last post by:

I hope someone will tell me how to do this without having to do any VB as I know nothing in that area. I am a rank beginner in using Access. I have created a database consisting of student...

Microsoft Access / VBA

UI Metaphors for complex criteria selection

by: Steve Jorgensen | last post by:

Mailing List management is a good example of a case where my conundrum arises. Say there is a m-m relationship between parties and groups - anyone can be a member of any combintation of groups. ...

Microsoft Access / VBA

Web Application needs to detect and retrieve emails

by: Richard Thornley | last post by:

Hello, I was just been given a project and I have some questions on how to accomplish the first part of the task. If a user sends an email to a specific email address I need to detect...

ASP.NET

Why do two queries on same table with same criteria return different dynasets?

by: MLH | last post by:

Why does this one produce 9 records... SELECT DISTINCT tblVehicleJobs.VehicleJobID, tblVehicleJobs.Reclaimed, tblVehicleJobs.VSaleID, tblVehicleJobs.ENF262Written FROM tblVehicleJobs WHERE...

Microsoft Access / VBA

Query Grid with two Criteria

by: starke1120 | last post by:

I need to write a query that has a criteria of two separate fields (one compared to another, then a seperate one) but I can't figure out how to do it. Let me first explain the table id...

Microsoft Access / VBA

Access 2003 form criteria for inline list box query

by: rhobson2 | last post by:

Hello, I wrote a database applicaiton using Access XP (2002) and everything has been working good for the client until they purchased a couple of new computers with Access 2003. The meetings...

Microsoft Access / VBA

Proper Way To Store, Retrieve Text To/From PostgreSQL

by: Alfred | last post by:

I want to post text field data from these HTML TEXTAREA tags to a PostgreSQL database and have it reappear back on another page exactly as I had typed it. Over the years I have done this but only...

PHP

Problem to retrieve information from a mysql database

by: zek2005 | last post by:

Hi! I have a value loaded in a database as following: test test 2questions<img alt=""...

PHP

how to retrieve and add data to a database

by: gavy7210 | last post by:

hello friends i am new to struts. i want to add the user information in the database and then display it back to him. i am using struts 1.2,Eclipse Platform Version: 3.4.2,mySql 5.0.1,jdk...

Java

Access Europe meeting on Wed 4 Oct - The future of Access

by: isladogs | last post by:

The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...

Microsoft Access / VBA

How to find best mobile app development company in usa

by: Aliciasmith | last post by:

In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...

Mobile Development

Can faster file sharing be achieved by connecting computers simultaneously via WiFi and LAN cables

by: tracyyun | last post by:

Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...

Networking - Hardware / Configuration

VBA Class Basics

by: NeoPa | last post by:

Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...

Microsoft Access / VBA

Finding a record, and then saving a new record after the record has been amended.

by: nia12 | last post by:

Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...

Microsoft Access / VBA

VBA Class Basics (Report Class)

by: NeoPa | last post by:

Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...

Microsoft Access / VBA

Access Europe Meeting - Wed 6 Dec - Streamline Your Import/Export Spec Workflow with VBA Classes

by: isladogs | last post by:

The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

Microsoft Access / VBA

Take a Database to the Next Level

by: GKJR | last post by:

Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

Microsoft Access / VBA

Applying ComboBox filters Sequentially

by: SueHopson | last post by:

Hi All, I'm trying to create a single code (run off a button that calls the Private Sub) for our parts list report that will allow the user to filter by either/both PartVendor and PartType. On...

Microsoft Access / VBA

how to use several criteria to retrieve the specific email address in database

Similar topics