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

88 New Member

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 Reply

2081

pbmods

5,821

Recognized Expert Expert

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

New Member

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_arr ay(): 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

Recognized Expert Expert

Heya, Kang.

However when i execute the code, the page always complain that there is errror: Warning: mysql_fetch_arr ay(): 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

New Member

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

Similar topics

4739

Selecting only one page out of a several-page report for printing - how to route in Access?

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 athletes. I have now learned how to join two different tables in a query so that I might generate a report. Specifically, student athletes at a community college are required to graduate with an AA or AS degree. Consequently, various steps are in...

Microsoft Access / VBA

2961

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. Now, let's say the user wants to be able to send mailings to people who have various combinations of membership and non-membership in those groups. Here's a medium-complex example: (Knitting Group or Macrame Group) and Active Contact and Mailing...

Microsoft Access / VBA

1827

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 that

ASP.NET

1513

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 (((tblVehicleJobs.Reclaimed)=No) AND ((tblVehicleJobs.VSaleID) Is Null) AND ((tblVehicleJobs.ENF262Written)=True)); And this one produces NONE... SELECT DISTINCT tblVehicleJobs.Reclaimed, tblVehicleJobs.VSaleID,

Microsoft Access / VBA

1832

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 (primary ID) FirstName (text) LastName (text) BadgeIn (text) BadgeOut (text) Damaged (yes/no)

Microsoft Access / VBA

3512

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 form has an unbound combo box listing all the meetings in the database allowing the user to navigate between meeting records. The meetings form also has a list box that displays a list of members associated with a meeting.

Microsoft Access / VBA

5058

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 did it with simple text. This time around, I want to handle much more complex text. I need to preserve some kinds of features. (Yes, I have turned off magic quotes.) - Need to strip diacritics. I learned I could use htmlentities to catch...

PHP

2074

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="" src="http://www.mysite.com/fckeditor/editor/images/smiley/msn/ regular_smile.gif" />

PHP

5559

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 1.5.. i have created a form bean to get the data from the user. then in my action class i m trying to get a database connection and adding the values of the user. and i also wanna show them back to him. its a dummy project for my learning(to get...

Java

8776

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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

9310

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

9236

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

9182

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

8186

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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

4809

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

3261

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

2724

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

2180

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General