By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 1,646 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

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

P: 88
hi

i am doing mailinglist currently. the code in my first page is like this :
Expand|Select|Wrap|Line Numbers
  1. :
  2. <html>
  3.     <head>
  4.     <link rel="stylesheet" type="text/css" href="gallery.css" />
  5. <script language="JavaScript">
  6.  
  7. <!--
  8.  
  9. function validate_form ( )
  10. {
  11.     valid = true;
  12.  if ( document.mailinglist.years.value == "" )
  13.     {
  14.         valid = true;
  15.     }  
  16.  
  17. else if((document.mailinglist.years.value != "") &&((/^((\d{1})|(\d{2}))$/).test(mailinglist.years.value) ==false)){
  18. alert ( "your input is incorrect,please enter numerical value");
  19.         mailinglist.years.focus();
  20.         valid = false;
  21.     }
  22.  
  23.         return valid;
  24.         }
  25.         //-->
  26. </script>
  27.  
  28. </head>
  29.  
  30.     <body>
  31.     <div id="header">Mailing List
  32. </div>
  33. <div id="leftcol">    
  34. </div>
  35. <div id="maincol">
  36.     <form method = 'POST' name= 'mailinglist' action = 'mailinglist.php' onSubmit = 'return validate_form ();'>
  37.     Select any one of below options to check Mailing list<br> 
  38.         Age: 
  39.          <select name="age">
  40.                             <option value="">---</option>
  41.                             <option value="15-25">15-25years</option>
  42.                             <option value="26-35">26-35years</option>
  43.                             <option value="36-45">36-45years</option>
  44.                             <option value="46-60">46-60years</option>
  45.                             <option value="60+">60+years</option>
  46.                             </select>
  47.         </select>
  48.  
  49.         <br><br>
  50.         Gender: 
  51.         <select name = 'gender'value='3'>
  52.             <option value = ''>--</option>
  53.             <option value = 'Female'>Female</option>
  54.             <option value = 'Male'>Male</option>
  55.  
  56.         </select>
  57.         <br><br>
  58.  
  59.  
  60.         Country: 
  61.         <select name="country" tabindex="11">
  62.                             <option value="">---</option>
  63.                             <option value="Indonesia" >Indonesia  </option>
  64.                             <option value="Malaysia" >Malaysia  </option>
  65.                             <option value="Philippines" >Philippines   </option>
  66.                             <option value="Singapore" > Singapore </option>
  67.                             <option value="Thailand" > Thailand</option>
  68.                             <option value="Vietnam " > Vietnam  </option>
  69.                             <option value="Laos" >Laos </option>
  70.                             <option value="Myanmar" > Myanmar </option>
  71.                             <option value="Cambodia" > Cambodia </option>
  72.                             <option value="Brunei" > Brunei </option>
  73.         </select>
  74.         <br><br>
  75.  
  76.    <td><font size="4"color="red">*</font>write down your number of years played golf: </td> <td><input type = "text" name="years"></td>
  77.  
  78.     <tr><td><i>(Please enter in numerical figures)</i></td></tr>    
  79.                     <br><br>
  80.                     <input type = 'submit' value = 'Show Mailinglist'>
  81.         </form>
  82.         </div>
  83. </body>
  84. </html>
  85.  
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
  1. :
  2. <?
  3. //session_start() does not create new session, but identifies same session from do_login.php
  4. session_start();
  5. require("db.php");
  6.  
  7. mysql_connect(MACHINE, USER, '');
  8. mysql_select_db(DBNAME);
  9.  
  10. $gender=$_POST['gender'];
  11. $country=$_POST['country'];
  12. $age=$_POST['age'];
  13. $years=$_POST['years'];
  14.  
  15.  
  16.     if ($age){
  17.         $selEmail= "SELECT * from users where age ='".$age."'";
  18. }
  19.  
  20.     elseif($gender){
  21.         //$selEmail = "SELECT * from mailinglist";
  22.                 $selEmail= "SELECT * from users where gender ='".$gender."'";
  23.     }
  24.     if(($gender)&&($age)){
  25.         //$selEmail = "SELECT * from mailinglist";
  26.                 $selEmail= "SELECT * from users where gender ='".$gender."'"."and age ='".$age."'";
  27.                 echo $selEmail;
  28.     }
  29.  
  30.     elseif($country){
  31.         //$selEmail = "SELECT * from mailinglist";
  32.                 $selEmail= "SELECT * from users where country='".$country."'";
  33.                 }
  34.     if(($country)&&($age)){
  35.  
  36.                 $selEmail= "SELECT * from users where country ='".$country."'"."and age ='".$age."'";
  37.                 echo $selEmail;
  38.                 }
  39.     if(($country)&&($gender)){
  40.  
  41.                 $selEmail= "SELECT * from users where country ='".$country."'"."and gender='".$gender."'";
  42.                 echo $selEmail;
  43.                 }
  44.     if(($country)&&($years)){
  45.  
  46.                 $selEmail= "SELECT * from users where country ='".$country."'"."and years ='".$years."'";
  47.                 echo $selEmail;
  48.     }
  49.  
  50.     elseif($years){
  51.         //$selEmail = "SELECT * from mailinglist";
  52.                 $selEmail= "SELECT * from users where years='".$years."'";
  53.     }
  54.     if (($years)&&($age)){
  55.                $selEmail= "SELECT * from users where years='".$years."'"."and age ='".$age."'";
  56.       }
  57.      if(($years)&&($gender)){
  58.  
  59.                 $selEmail= "SELECT * from users where country ='".$country."'"."and gender ='".$gender."'";
  60.                 echo $selEmail;
  61.                 }
  62.  
  63.         $a = mysql_query($selEmail);
  64.  
  65.  
  66.     echo "<table border = '1'>";
  67.     //normal header row
  68.  
  69.     //advance header row for Q17
  70.     echo "<tr>";
  71.     echo "<td><b>Name</b></td>";
  72.     echo "<td><b>Email</b></td>";
  73.     echo "<td><b>Age</b></td>";
  74.     echo "<td><b>Gender</b></td>";
  75.     echo "<td><b>Country</b></td>";
  76.     echo "<td><b>Years Played Golf</b></td>";
  77.     //echo "<td><b>Delete?</b></td>";
  78.     //echo "<td><b>Update?</b></td>";
  79.     echo "</tr>";
  80.     //////////////////////////////////////////////////////
  81.  
  82.     while($thisEmail = mysql_fetch_array($a)){
  83.         $name=$thisEmail['name'];
  84.         $age = $thisEmail['age'];
  85.         $gender = $thisEmail['gender'];
  86.         $country = $thisEmail['country'];
  87.         $years=$thisEmail['years'];
  88.         $email=$thisEmail['email'];
  89.         echo "<tr>";
  90.         echo "<td>".$name."</td>";
  91.         echo "<td>".$email."</td>";
  92.         echo "<td>".$age."</td>";
  93.         echo "<td>".$gender."</td>";
  94.         echo "<td>".$country."</td>";
  95.         echo "<td>".$years."</td>";
  96.         //echo "<td><a href=\"do_delete.php?id=".$id."\">Delete</a></td>";
  97.         //echo "<td><a href=\"updatecontact.php?id=".$id."\">Update</a></td>";
  98.         echo "</tr>";        
  99.     }
  100.     echo "</table>";
  101.     echo"<a href=mailinglist.html>go back</a>";
  102.     echo"<br>";
  103.     echo"<a href=index.php>go back to home page</a>";
  104. ?>    
  105.  
  106.  
  107.  
  108.  
  109.  
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
Share this Question
Share on Google+
4 Replies


pbmods
Expert 5K+
P: 5,821
Heya, Kang.

Instead of creating a separate query for each case, try something like this:
Expand|Select|Wrap|Line Numbers
  1. $sql = 'SELECT * FROM `users` ';
  2. $search = '';
  3.  
  4. foreach($_POST as $field => $val)
  5.     if(! empty($val))
  6.         $search .= ($search ? ' AND' : 'WHERE') . " (`{$field}` = '{$val}')";
  7.  
  8. if(empty($search)) {
  9.     // No search criteria.  Generate an error.
  10.     .
  11.     .
  12.     .
  13. } else {
  14.     // Execute the search and process the results.
  15.     $result = mysql_query($sql . $search);
  16.     .
  17.     .
  18.     .
  19. }
  20.  
This will run through ALL values in $_POST and append them to the search query.
Jul 9 '07 #2

P: 88
Heya, Kang.

Instead of creating a separate query for each case, try something like this:
Expand|Select|Wrap|Line Numbers
  1. $sql = 'SELECT * FROM `users` ';
  2. $search = '';
  3.  
  4. foreach($_POST as $field => $val)
  5.     if(! empty($val))
  6.         $search .= ($search ? ' AND' : 'WHERE') . " (`{$field}` = '{$val}')";
  7.  
  8. if(empty($search)) {
  9.     // No search criteria.  Generate an error.
  10.     .
  11.     .
  12.     .
  13. } else {
  14.     // Execute the search and process the results.
  15.     $result = mysql_query($sql . $search);
  16.     .
  17.     .
  18.     .
  19. }
  20.  
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
  1. <?
  2. session_start();
  3. require("db.php");
  4.  
  5. mysql_connect(MACHINE, USER, '');
  6. mysql_select_db(DBNAME);
  7.  
  8. $gender=$_POST['gender'];
  9. $country=$_POST['country'];
  10. $age=$_POST['age'];
  11. $years=$_POST['years'];
  12. $sql="SELECT * from users";
  13. $search='';
  14. foreach($_POST as $field=>$val)
  15. if (!empty ($val))
  16. $search.=($search ? 'AND':'WHERE')."('{$field}'='{$val}')";
  17. if (empty($search)){
  18. //no search criteria, Generate an error.
  19. echo "<script>alert('you did not select any criteria to view email list ')</script>";
  20.     }
  21. else{
  22. //execute the search and process the results
  23. $result=mysql_query($sql.$search);
  24. }
  25.     echo "<table border = '1'>";
  26.     //normal header row
  27.  
  28.     //advance header row for Q17
  29.     echo "<tr>";
  30.     echo "<td><b>Name</b></td>";
  31.     echo "<td><b>Email</b></td>";
  32.     echo "<td><b>Age</b></td>";
  33.     echo "<td><b>Gender</b></td>";
  34.     echo "<td><b>Country</b></td>";
  35.     echo "<td><b>Years Played Golf</b></td>";
  36.     //echo "<td><b>Delete?</b></td>";
  37.     //echo "<td><b>Update?</b></td>";
  38.     echo "</tr>";
  39.     //////////////////////////////////////////////////////
  40.  
  41.     while($thisEmail = mysql_fetch_array($result)){
  42.         $name=$thisEmail['name'];
  43.         $age = $thisEmail['age'];
  44.         $gender = $thisEmail['gender'];
  45.         $country = $thisEmail['country'];
  46.         $years=$thisEmail['years'];
  47.         $email=$thisEmail['email'];
  48.         echo "<tr>";
  49.         echo "<td>".$name."</td>";
  50.         echo "<td>".$email."</td>";
  51.         echo "<td>".$age."</td>";
  52.         echo "<td>".$gender."</td>";
  53.         echo "<td>".$country."</td>";
  54.         echo "<td>".$years."</td>";
  55.         //echo "<td><a href=\"do_delete.php?id=".$id."\">Delete</a></td>";
  56.         //echo "<td><a href=\"updatecontact.php?id=".$id."\">Update</a></td>";
  57.         echo "</tr>";        
  58.     }
  59.     echo "</table>";
  60.     echo"<a href=mailinglist.html>go back</a>";
  61.     echo"<br>";
  62.     echo"<a href=index.php>go back to home page</a>";
  63. ?>    
  64.  
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
  1.       $search .= ($search ? ' AND' : 'WHERE') . " (`{$field}` = '{$val}')";
  2.  
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
Expert 5K+
P: 5,821
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
  1. if(! ($result = mysql_query($sql . $search))
  2.     exit($sql . $search . "\n" . mysql_error());
  3.  
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
  1.       $search .= ($search ? ' AND' : 'WHERE') . " (`{$field}` = '{$val}')";
  2.  
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

P: 88
Heya, Kang.



There's something wrong with the query. Change line 23:
Expand|Select|Wrap|Line Numbers
  1. if(! ($result = mysql_query($sql . $search))
  2.     exit($sql . $search . "\n" . mysql_error());
  3.  
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

Post your reply

Sign in to post your reply or Sign up for a free account.