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

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • kang jia
    New Member
    • Jun 2007
    • 88
    #1

    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 :
    [code=PHP]:
    <html>
    <head>
    <link rel="stylesheet " type="text/css" href="gallery.c ss" />
    <script language="JavaS cript">

    <!--

    function validate_form ( )
    {
    valid = true;
    if ( document.mailin glist.years.val ue == "" )
    {
    valid = true;
    }

    else if((document.ma ilinglist.years .value != "") &&((/^((\d{1})|(\d{2 }))$/).test(mailingl ist.years.value ) ==false)){
    alert ( "your input is incorrect,pleas e enter numerical value");
    mailinglist.yea rs.focus();
    valid = false;
    }

    return valid;
    }
    //-->
    </script>

    </head>

    <body>
    <div id="header">Mai ling List
    </div>
    <div id="leftcol">
    </div>
    <div id="maincol">
    <form method = 'POST' name= 'mailinglist' action = 'mailinglist.ph p' 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="Indonesi a" >Indonesia </option>
    <option value="Malaysia " >Malaysia </option>
    <option value="Philippi nes" >Philippines </option>
    <option value="Singapor e" > 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>(Ple ase enter in numerical figures)</i></td></tr>
    <br><br>
    <input type = 'submit' value = 'Show Mailinglist'>
    </form>
    </div>
    </body>
    </html>
    [/code]

    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:

    [code=php]:
    <?
    //session_start() does not create new session, but identifies same session from do_login.php
    session_start() ;
    require("db.php ");

    mysql_connect(M ACHINE, 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='".$cou ntry."'";
    }
    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='".$gend er."'";
    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)&&($ag e)){
    $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($se lEmail);


    echo "<table border = '1'>";
    //normal header row

    //advance header row for Q17
    echo "<tr>";
    echo "<td><b>Nam e</b></td>";
    echo "<td><b>Ema il</b></td>";
    echo "<td><b>Age </b></td>";
    echo "<td><b>Gen der</b></td>";
    echo "<td><b>Country </b></td>";
    echo "<td><b>Yea rs Played Golf</b></td>";
    //echo "<td><b>Delete? </b></td>";
    //echo "<td><b>Update? </b></td>";
    echo "</tr>";
    //////////////////////////////////////////////////////

    while($thisEmai l = mysql_fetch_arr ay($a)){
    $name=$thisEmai l['name'];
    $age = $thisEmail['age'];
    $gender = $thisEmail['gender'];
    $country = $thisEmail['country'];
    $years=$thisEma il['years'];
    $email=$thisEma il['email'];
    echo "<tr>";
    echo "<td>".$nam e."</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_delet e.php?id=".$id. "\">Delete</a></td>";
    //echo "<td><a href=\"updateco ntact.php?id=". $id."\">Update</a></td>";
    echo "</tr>";
    }
    echo "</table>";
    echo"<a href=mailinglis t.html>go back</a>";
    echo"<br>";
    echo"<a href=index.php> go back to home page</a>";
    ?>




    [/code]

    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. :)
    Tags: None
    • pbmods
      Recognized Expert Expert
      • Apr 2007
      • 5821
      #2
      Heya, Kang.

      Instead of creating a separate query for each case, try something like this:
      [code=php]
      $sql = 'SELECT * FROM `users` ';
      $search = '';

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

      if(empty($searc h)) {
      // No search criteria. Generate an error.
      .
      .
      .
      } else {
      // Execute the search and process the results.
      $result = mysql_query($sq l . $search);
      .
      .
      .
      }
      [/code]

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

        Comment

        • kang jia
          New Member
          • Jun 2007
          • 88
          #3
          Originally posted by pbmods
          Heya, Kang.

          Instead of creating a separate query for each case, try something like this:
          [code=php]
          $sql = 'SELECT * FROM `users` ';
          $search = '';

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

          if(empty($searc h)) {
          // No search criteria. Generate an error.
          .
          .
          .
          } else {
          // Execute the search and process the results.
          $result = mysql_query($sq l . $search);
          .
          .
          .
          }
          [/code]

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


          hi
          thanks your kind reply, now my code is
          [code=PHP]
          <?
          session_start() ;
          require("db.php ");

          mysql_connect(M ACHINE, 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.=($sear ch ? '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_q uery($sql.$sear ch);
          }
          echo "<table border = '1'>";
          //normal header row

          //advance header row for Q17
          echo "<tr>";
          echo "<td><b>Nam e</b></td>";
          echo "<td><b>Ema il</b></td>";
          echo "<td><b>Age </b></td>";
          echo "<td><b>Gen der</b></td>";
          echo "<td><b>Country </b></td>";
          echo "<td><b>Yea rs Played Golf</b></td>";
          //echo "<td><b>Delete? </b></td>";
          //echo "<td><b>Update? </b></td>";
          echo "</tr>";
          //////////////////////////////////////////////////////

          while($thisEmai l = mysql_fetch_arr ay($result)){
          $name=$thisEmai l['name'];
          $age = $thisEmail['age'];
          $gender = $thisEmail['gender'];
          $country = $thisEmail['country'];
          $years=$thisEma il['years'];
          $email=$thisEma il['email'];
          echo "<tr>";
          echo "<td>".$nam e."</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_delet e.php?id=".$id. "\">Delete</a></td>";
          //echo "<td><a href=\"updateco ntact.php?id=". $id."\">Update</a></td>";
          echo "</tr>";
          }
          echo "</table>";
          echo"<a href=mailinglis t.html>go back</a>";
          echo"<br>";
          echo"<a href=index.php> go back to home page</a>";
          ?>
          [/code]
          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:
          [code=PHP]
          $search .= ($search ? ' AND' : 'WHERE') . " (`{$field}` = '{$val}')";
          [/code]

          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.

            Comment

            • pbmods
              Recognized Expert Expert
              • Apr 2007
              • 5821
              #4
              Heya, Kang.

              Originally posted by kang jia
              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:
              [code=php]
              if(! ($result = mysql_query($sq l . $search))
              exit($sql . $search . "\n" . mysql_error());
              [/code]

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

              Originally posted by kang jia
              PS. if you don;t mind, can explain to me of the code:
              [code=PHP]
              $search .= ($search ? ' AND' : 'WHERE') . " (`{$field}` = '{$val}')";
              [/code]

              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.

                Comment

                • kang jia
                  New Member
                  • Jun 2007
                  • 88
                  #5
                  Originally posted by pbmods
                  Heya, Kang.



                  There's something wrong with the query. Change line 23:
                  [code=php]
                  if(! ($result = mysql_query($sq l . $search))
                  exit($sql . $search . "\n" . mysql_error());
                  [/code]

                  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

                    Comment

                    Previous template Next
                    Working...