Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 14 of 14

Thread: mysql search

  1. #1
    New Coder
    Join Date
    Nov 2011
    Posts
    77
    Thanks
    4
    Thanked 0 Times in 0 Posts

    mysql search

    I made I search form for my mysql db.
    The search will be made using criterions from field1, field2, field3 and field4.
    if field1 ="" and/or field2 ="" and/or field3 ="" and/or field4 ="" make the search using only the field that value is not "" (so could be "a", "b", "c", etc)
    if all fields are = "" than give error message no criterion was selected.

    I did this but doesn't work...

    $field1 = $_GET['field1'];
    $field2 = $_GET['field2'];
    $field3 = $_GET['field3'];
    $field4 = $_GET['field4'];


    $query = "SELECT * FROM table WHERE field1='$field1' ORDER BY id DESC";
    if ( $field2 != "" ) $query .= "AND field2='$field2'";
    if ( $field3 != "" ) $query .= "AND field2='$field3'";
    if ( $field4 != "" ) $query .= "AND field2='$field4'";



    "" could be no value or could be value="blank"

  • #2
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,531
    Thanks
    8
    Thanked 1,091 Times in 1,082 Posts
    PHP Code:

    $string
    ="WHERE ";
    if(
    $_GET['field1']){
    $string .= "field1='".$_GET['field1']."' OR ";
    }
    if(
    $_GET['field2']){
    $string .= "field2='".$_GET['field2']."' OR ";
    }
    if(
    $_GET['field3']){
    $string .= "field3='".$_GET['field3']."' OR ";
    }
    if(
    $_GET['field4']){
    $string .= "field4='".$_GET['field4']."' OR ";
    }
    $string substr($string,0,-2);
    $string .="ORDER BY id DESC";

    $query "SELECT * FROM table $string"
    Last edited by mlseim; 01-18-2012 at 01:31 PM.

  • #3
    New Coder
    Join Date
    Nov 2011
    Posts
    77
    Thanks
    4
    Thanked 0 Times in 0 Posts
    thx, but still don't work
    I made a search using field 1 (the rest of field value was ="")
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OORDER BY cont ASC' at line 1


    I have 4 dropdown menus to select criterions for the search. (DDM)
    DDM1
    <option value="">Select"</option>
    <option value="1">1</option>
    <option value="2">2</option>

    DDM2
    <option value="">Select"</option>
    <option value="A">A</option>
    <option value="B">B</option>

    DDM3
    <option value="">Select"</option>
    <option value="aA">aA</option>
    <option value="bB">bB</option>

    DDM4
    <option value="">Select"</option>
    <option value="1A">1A</option>
    <option value="1B">1B</option>


    and I wanna search in db using this criterions. one criterion selected or all of them.
    Last edited by _user; 01-18-2012 at 02:00 PM.

  • #4
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,531
    Thanks
    8
    Thanked 1,091 Times in 1,082 Posts
    I see the error ...
    You will have to decide what to do if no fields are entered.
    Do the query and get no results, or don't do the query.

    PHP Code:
    $string="WHERE ";
    $flag=0;
    if(
    $_GET['field1']){
    $string .= "field1='".$_GET['field1']."' OR ";
    $flag=1;
    }
    if(
    $_GET['field2']){
    $string .= "field2='".$_GET['field2']."' OR ";
    $flag=1;
    }
    if(
    $_GET['field3']){
    $string .= "field3='".$_GET['field3']."' OR ";
    $flag=1;
    }
    if(
    $_GET['field4']){
    $string .= "field4='".$_GET['field4']."' OR ";
    $flag=1;
    }
    if(
    $flag==1){
    $string substr($string,0,-3);
    $string .="ORDER BY id DESC";
    }

    $query "SELECT * FROM table $string"

  • #5
    New Coder
    Join Date
    Nov 2011
    Posts
    77
    Thanks
    4
    Thanked 0 Times in 0 Posts
    no errors but still not working.
    (

    and the problem seems to be "OR".
    I need "AND" (but if I use AND i get error)

    If all 4 fields are selected, I need to do the search using all selections.
    And if are selected 1 or 2... I need to do the search using those selection.
    Last edited by _user; 01-18-2012 at 02:19 PM.

  • #6
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,531
    Thanks
    8
    Thanked 1,091 Times in 1,082 Posts
    Change all of the "OR" to "AND"

    and change this line:
    $string = substr($string,0,-3);

    to this:
    $string = substr($string,0,-4);

    The purpose of substr is to remove the last "AND " from the string,
    because we don't know which fields will be used. The "AND " is
    the last 4 character of the string (-4).


    .
    Last edited by mlseim; 01-18-2012 at 03:33 PM.

  • #7
    New Coder
    Join Date
    Nov 2011
    Posts
    77
    Thanks
    4
    Thanked 0 Times in 0 Posts
    doesn't work.
    now it doesn't work any search.

  • #8
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,531
    Thanks
    8
    Thanked 1,091 Times in 1,082 Posts
    Just for the heck of it ...

    Do some examples and let's see what some various queries look like.

    Instead of this line:
    $query = "SELECT * FROM table $string";

    Do this:
    echo $string;
    exit;
    $query = "SELECT * FROM table $string";

    Do a variety of trials, picking 1 field, 2 fields, mix it up.
    Copy the echo results of the $string variable, and list them for us.
    I want to see what some of the queries look like.

  • #9
    New Coder
    Join Date
    Nov 2011
    Posts
    77
    Thanks
    4
    Thanked 0 Times in 0 Posts
    WHERE field1='2' AND field2='A' AND field3='< Select >' AND field4='< Select >' ORDER BY cont ASC
    WHERE field2='1' AND field2='B' AND field3='< Select >' AND field4='< Select>' ORDER BY cont ASC

    it seems he dosnt ignore the field3 and field4 (wich are not selected)

  • #10
    New Coder
    Join Date
    Nov 2011
    Posts
    77
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I solved the problem.
    I used "AND"
    and
    if($locuri != "blank" ){

    it works... but still have some issues....

    If I select
    DDM1 "2" - LIST OK
    If I select
    DDM1 "2" and DDM2 "B" - LIST OK
    if I select
    DDM4 "1B" - LIST OK
    but If I select DDM1 "2" and DDM4 "1B" - 0 results

  • #11
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,531
    Thanks
    8
    Thanked 1,091 Times in 1,082 Posts
    In my example, the one's that didn't have anything
    selected would not even be part of the query string.

    My mistake there is the fact that even when they don't
    select anything, there is still a value there: "<Select>"

    So take my example and try it like this ...
    PHP Code:
    $string="WHERE ";
    $flag=0;
    if(
    $_GET['field1'] != "<Select>"){
    $string .= "field1='".$_GET['field1']."' OR ";
    $flag=1;
    }
    if(
    $_GET['field2'] != "<Select>"){
    $string .= "field2='".$_GET['field2']."' OR ";
    $flag=1;
    }
    if(
    $_GET['field3'] != "<Select>"){
    $string .= "field3='".$_GET['field3']."' OR ";
    $flag=1;
    }
    if(
    $_GET['field4'] != "<Select>"){
    $string .= "field4='".$_GET['field4']."' OR ";
    $flag=1;
    }
    if(
    $flag==1){
    $string substr($string,0,-3);
    $string .="ORDER BY id DESC";
    }

    $query "SELECT * FROM table $string"

  • Users who have thanked mlseim for this post:

    _user (01-20-2012)

  • #12
    New Coder
    Join Date
    Nov 2011
    Posts
    77
    Thanks
    4
    Thanked 0 Times in 0 Posts
    still dosn't work

  • #13
    New Coder
    Join Date
    Nov 2011
    Posts
    77
    Thanks
    4
    Thanked 0 Times in 0 Posts
    please help !!!

  • #14
    New Coder
    Join Date
    Nov 2011
    Posts
    77
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I found a search script for a dating website... but I can't see how can I make mine works the same....


    <?php
    if ( !defined( 'SMARTY_DIR' ) ) {
    include_once( 'init.php' );
    }

    $psize = getPageSize();

    $t->assign ( 'psize', $psize );

    $with_photo = isset($_REQUEST['with_photo'])?$_REQUEST['with_photo']:false;

    $country = isset($_REQUEST['lookcountry'])?$_REQUEST['lookcountry']:'AA';

    $cpage = isset($_REQUEST['page'])?$_REQUEST['page']:'1';

    $zip = isset($_REQUEST['srchzip'])?$_REQUEST['srchzip']:'';


    if( $cpage == '' ) {
    $cpage = 1;
    }

    $lookgender_search="";

    /* Bypass cross matching in search if set in global settings */
    if ($config['bypass_search_lookgender'] == 'N' or $config['bypass_search_lookgender'] == '0' ) {
    $lookgender_search = " AND usr.lookgender in ('A' ";
    if ($_REQUEST['txtgender'] == 'M' || $_REQUEST['txtgender'] == 'F') {
    $lookgender_search .= ",'B'";
    }
    $lookgender_search .= ",'".$_REQUEST['txtgender']."') ";
    }

    $gender_search = " AND usr.gender in ( ";

    if (isset($_REQUEST['txtlookgender']) && $_REQUEST['txtlookgender'] == 'A') {
    $gender_search .= "'M','F','C'";
    } elseif ( isset($_REQUEST['txtlookgender']) && $_REQUEST['txtlookgender'] == 'B') {
    $gender_search .= "'M','F'";
    } else {
    $gender_search .= "'".(isset($_REQUEST['txtlookgender'])?$_REQUEST['txtlookgender']:'A')."'";
    }
    $gender_search .= ") ";

    $zipcodes_in = '';
    if ($zip != '' && $country != 'AA') {
    /* Zip code proximity search */
    if ($country == 'GB') {
    $ukzip = explode(' ',$zip);
    $srchzip = $ukzip[0];
    } else {
    $srchzip = $zip;
    }
    $row = $osDB->getRow('select latitude, longitude from ! where code=? and countrycode=? limit 1',array(ZIPCODES_TABLE, ltrim(rtrim($srchzip)), $country ) );
    $lat = isset($row['latitude'])?$row['latitude']:'';
    $lng = isset($row['longitude'])?$row['longitude']:'';
    if ($lng!='' && $lat!='') {
    $radius = $config['iplocation_radius'];
    if (substr_count($radius,'K') > 0) {
    $radiustype = 'kms';
    } else {
    $radiustype='miles';
    }
    $radius = str_replace(array('K','M'),'',$radius);
    if ($radiustype == 'kms') {
    /* Kilometers calculation */
    $zipcodes_in = " and ( sqrt(power(69.1*(usr.zip_latitude - $lat),2)+power(69.1*(usr.zip_longitude-$lng)*cos(usr.zip_latitude/57.3),2)) < " . $radius ." ) ";
    } else {
    /* Miles */
    $zipcodes_in = " and ( (3958* 3.1415926 * sqrt((usr.zip_latitude - $lat) * (usr.zip_latitude- $lat) + cos(usr.zip_latitude / 57.29578) * cos($lat/57.29578)*(usr.zip_longitude - $lng) * (usr.zip_longitude - $lng))/180) < " . $radius ." ) ";
    }
    }
    }

    if (!isset($_REQUEST['sort_by']) ) {
    $sort_by=$config['search_sort_by'];
    } else {
    $sort_by=$_REQUEST['sort_by'];
    }

    if (!isset($_REQUEST['sort_order'] )) {
    $sort_order='asc';
    } else {
    $sort_order=$_REQUEST['sort_order'];
    }

    $t->assign('sort_by', $sort_by);

    $sortme = " order by ";

    if ($sort_by == 'username') {

    $sortme .= 'usr.username ';

    } elseif ( $sort_by == 'age' ) {

    $sortme .= ' age ';

    } elseif ( $sort_by == 'level' ) {

    $sortme .= ' usr.level ';

    } elseif ( $sort_by == 'logintime' ) {

    $sortme .= 'usr.lastvisit ';
    if (!isset($_REQUEST['sort_order']) || $_REQUEST['sort_order'] == '') {
    $sort_order=' desc ';
    } else {
    $sort_order=$_REQUEST['sort_order'];
    }

    } elseif ( $sort_by == 'online' ) {

    $sortme .= ' onl.is_online desc, usr.username ';
    }

    $t->assign('sort_order', $sort_order);

    $sortme .= $sort_order." ";

    $bannedlist = '';
    if (isset($_SESSION['UserId'])) {
    /* Make a banned users list */
    $bannedusers = $osDB->getAll('select bdy.ref_userid from ! as bdy where bdy.act=? and bdy.userid = ? union select bdy1.userid as ref_userid from ! as bdy1 where bdy1.act=? and bdy1.ref_userid = ?', array(BUDDY_BAN_TABLE, 'B', $_SESSION['UserId'], BUDDY_BAN_TABLE, 'B', $_SESSION['UserId'] ) );
    if (count($bannedusers) > 0) {
    $bannedlist=' and usr.id not in (';
    $bdylst = '';
    foreach ($bannedusers as $busr) {
    if ($bdylst != '') $bdylst .= ',';
    $bdylst .= "'".$busr['ref_userid']."'";
    }
    $bannedlist .=$bdylst.') ';
    }
    unset($bannedusers);
    }
    /*
    $yearstart = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d")+1, date("Y") - $_REQUEST['txtlookageend'])-1);
    $yearend = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d"), date("Y") - $_REQUEST['txtlookagestart']));
    */
    $yearend = $osDB->getOne('select date_sub(curdate(),interval '.(isset($_REQUEST['txtlookagestart'])?$_REQUEST['txtlookagestart']:$config['default_end_agerange']) .' year)');

    $yearstart = $osDB->getOne('select date_sub(curdate(),interval '.(isset($_REQUEST['txtlookageend'] )?($_REQUEST['txtlookageend'] + 1)$config['default_start_agerange']+1)) .' year)');

    $countryselect='';

    if ($country != 'AA') {
    $countryselect = " and usr.country ='".$country."' ";
    }

    $_SESSION['lookcountry'] = $country;

    $start = ( $cpage - 1 ) * $psize;

    $t->assign ( 'start', $start );

    $photoqry='';
    if ($with_photo == 1) {
    $photoqry = ' and usr.id = ANY (select snp.userid from '.USER_SNAP_TABLE. ' as snp where snp.userid = usr.id ) ';
    }

    $sql = 'SELECT SQL_CALC_FOUND_ROWS distinct usr.*, floor((to_days(curdate())-to_days(birth_date))/365.25) as age FROM '.MEMBERSHIP_TABLE.' as mem, '.USER_TABLE." as usr WHERE mem.roleid=usr.level and mem.includeinsearch=1 AND usr.id > 0 and lower(usr.status) in (lower('".get_lang('status_enum','active')."'),'active') AND usr.active=1 AND usr.birth_date BETWEEN '".$yearstart."' AND '".$yearend."' ". $countryselect. $bannedlist.$gender_search. $lookgender_search.$photoqry.$zipcodes_in.$sortme ." limit ".$start.",".$psize;

    $rs = $osDB->getAll( $sql);

    $rcount = $osDB->getOne('select FOUND_ROWS()');

    if( isset($rs) && !empty($rs) && $rcount > 0 ) {

    $t->assign( 'totalrecs', $rcount );

    $pages = ceil( $rcount / $psize );

    if( $pages > 1 ) {

    if ( $cpage > 1 ) {

    $prev = $cpage - 1;

    $t->assign( 'prev', $prev );

    }
    $t->assign ( 'cpage', $cpage );

    $t->assign ( 'pages', $pages );

    if ( $cpage < $pages ) {

    $next = $cpage + 1;

    $t->assign ( 'next', $next );
    }

    }

    }

    setcookie($config['cookie_prefix']."osdate_info[search_ages]", (isset($_REQUEST['txtlookagestart'])?$_REQUEST['txtlookagestart']:$config['default_end_agerange']).':'.(isset($_REQUEST['txtlookageend'] )?($_REQUEST['txtlookageend'] + 1)$config['default_start_agerange']+1)), strtotime("+30day"), "/" );

    $_SESSION['simplesearch']['txtgender'] = isset($_REQUEST['txtgender'])?$_REQUEST['txtgender']:'A';
    $_SESSION['simplesearch']['txtlookgender']= isset($_REQUEST['txtlookgender'])?$_REQUEST['txtlookgender']:'A';
    $_SESSION['simplesearch']['lookageend'] = (isset($_REQUEST['txtlookageend'] )?($_REQUEST['txtlookageend'] + 1)$config['default_start_agerange']+1));
    $_SESSION['simplesearch']['lookagestart'] = (isset($_REQUEST['txtlookagestart'])?$_REQUEST['txtlookagestart']:$config['default_end_agerange']);
    $_SESSION['simplesearch']['with_photo'] = isset($_REQUEST['with_photo'])?$_REQUEST['with_photo']:false;
    $_SESSION['simplesearch']['lookcountry'] = $country;
    $_SESSION['simplesearch']['srchzip'] = $zip;
    $querystring = array(
    'txtgender' => $_SESSION['simplesearch']['txtgender'],
    'txtlookgender' => $_SESSION['simplesearch']['txtlookgender'],
    'txtlookagestart' => $_SESSION['simplesearch']['lookagestart'],
    'txtlookageend' => $_SESSION['simplesearch']['lookageend'],
    'with_photo' => $_SESSION['simplesearch']['with_photo'],
    'lookcountry' => $_SESSION['simplesearch']['lookcountry'],
    'srchzip' => $_SESSION['simplesearch']['srchzip']
    ) ;



    if ( !isset($rs) || empty($rs) || $rcount <= 0 ) {

    $t->assign ( 'error', "1" );

    $t->assign('querystring', $querystring);

    $t->assign ( 'backlink', 'searchprofile.php' );

    } else {

    if ( isset($_REQUEST['savesearch']) && $_REQUEST['savesearch'] == 'on' && isset( $_SESSION['UserId'] ) ) {

    $osDB->query( 'INSERT INTO ! ( userid, query) VALUES(? , ?)', array(USER_SEARCH_TABLE, $_SESSION['UserId'], $sql ) );
    }

    $data = array();
    if (isset($rs) && !empty($rs) ) {
    foreach( $rs as $row ) {

    $row['countryname'] = getCountryName( $row['country'] );

    $row['statename'] = getStateName( $row['country'], $row['state_province']);

    $data[] = $row;
    }
    }
    hasRight('');

    $lang['sort_types'] = get_lang_values('sort_types');

    $t->assign ( 'querystring', $querystring) ;

    $t->assign ( 'data', $data );

    unset($data, $rs, $querystring);
    }
    $t->assign ( 'lang', $lang );

    $t->assign('simplesearch', $_SESSION['simplesearch']);

    $t->assign('rendered_page', $t->fetch('showsimpsh.tpl') );

    $t->display ( 'index.tpl' );

    exit;

    ?>


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •