mysql search

[_user]

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"

[mlseim]

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"; 


[_user]

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.

[mlseim]

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"; 


[_user]

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.

[mlseim]

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).


.

[_user]

doesn't work.
now it doesn't work any search.

[mlseim]

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.

[_user]

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)

[_user]

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

[mlseim]

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"; 


[_user]

still dosn't work

[_user]

please help !!!

[_user]

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;

?>