...

View Full Version : mysql search



_user
01-18-2012, 12:51 PM
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
01-18-2012, 01:28 PM
$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
01-18-2012, 01:47 PM
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
01-18-2012, 01:55 PM
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.



$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
01-18-2012, 02:10 PM
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
01-18-2012, 03:30 PM
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
01-18-2012, 04:10 PM
doesn't work. :(
now it doesn't work any search.

mlseim
01-18-2012, 04:26 PM
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
01-18-2012, 04:41 PM
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
01-18-2012, 06:22 PM
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
01-18-2012, 07:33 PM
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 ...


$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
01-18-2012, 08:01 PM
still dosn't work :(

_user
01-19-2012, 12:09 AM
please help !!!

_user
01-19-2012, 11:29 AM
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;

?>



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum