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

2058

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

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

Career Advice

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

Microsoft Access / VBA

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...

C# / C Sharp

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

Similar topics