Page 1 of 1

Search Price

Posted: Fri Oct 10, 2014 1:37 pm
by andy565
ok so i have a mysql database with some php code that does a search

the search brings up title, image, description and price.

everything works fine up to here.

now on a quick search i want to be able to specify the minimum and maximum price and get results what is the best way to go about this do you know any tutorials on this, any referencing ??

Re: Search Price

Posted: Fri Oct 10, 2014 1:48 pm
by requinix
Not that I know anything about your application but I'm pretty sure the answer is just adding

Code: Select all

... AND price >= (min) AND price <= (max)
to the WHERE in your SQL query.

Re: Search Price

Posted: Fri Oct 10, 2014 1:49 pm
by Celauran
Simplest would probably be to include min/max price fields in your search form and use those as constraints in your query.

Code: Select all

SELECT title, image, description, price
FROM some_table_name
WHERE price >= :min_price
AND price <= :max_price

Re: Search Price

Posted: Mon Oct 13, 2014 7:19 am
by andy565
good answers but the search i am using search's by link for example:
if i search for price then it will appear in the link like so:
http://www.example.com/search.html?sear ... on=Y&S_P=1
S_P=1 = finds the number associated with id 1
in this case it is £100

now i have this
foreach ($sf as $sf_item)
$sql_group[] = " tblJOBS_JOBRENTLINK.job_level_val = $sf_item ";

$sql_where[] = " EXISTS ( SELECT vacancy_id AS vc FROM tblJOBS_JOBRENTLINK " .
"WHERE vacancy_id = tblJOBS_VACANCIES.vacancy_id AND $s_sf >= (min) AND $s_sf2 <= (max);
AND " .
"( " . implode(" OR ", $sql_group) . " ) ) ";

and i tried inputting the min and max here but does not seem to work any ideas?

Re: Search Price

Posted: Mon Oct 13, 2014 10:49 am
by Celauran
Did you use (min) and (max) literally? You're comparing against price, right? So price would need to be in the query for sure. min and max are placeholders which you'd replace by query-specific values.

Re: Search Price

Posted: Mon Oct 13, 2014 10:55 am
by andy565
lol no

Code: Select all

// rent
 $s_sf = (array_key_exists('s_sf', $args)) ? $args['s_sf'] : null;
 $s_sf2 = (array_key_exists('s_sf2', $args)) ? $args['s_sf2'] : null;
 $sf = (array_key_exists('sf', $args)) ? $args['sf'] : null;
 if ($sf !== null)
 {
  if (!is_array($sf))
   $sf = array($sf);
  
  if (count($sf) > 0)
  {
   //$sql_join[]= " LEFT JOIN tblJOBS_JOBDISCIPLINELINK ON tblJOBS_VACANCIES.vacancy_id = tblJOBS_JOBDISCIPLINELINK.vacancy_id ";
   $sql_group = array();
   foreach ($sf as $sf_item)
    $sql_group[] = " tblJOBS_JOBRENTLINK.job_level_val = $sf_item ";
    
   $sql_where[] = " EXISTS ( SELECT vacancy_id AS vc FROM tblJOBS_JOBRENTLINK " . 
    "WHERE vacancy_id = tblJOBS_VACANCIES.vacancy_id AND $sf >= ($s_sf) AND $sf <= ($s_sf2);
     AND " . 
    "( " . implode(" OR ", $sql_group) . " ) ) ";
  }
 }
This is the current code.

Re: Search Price

Posted: Mon Oct 13, 2014 10:57 am
by Celauran
andy565 wrote:

Code: Select all

AND $sf >= (s_sf) AND $sf <= (s_sf2);
What's going on here?

Re: Search Price

Posted: Mon Oct 13, 2014 1:32 pm
by andy565
s_sf is the first search im passing over (the lowest possible price)
s_sf2 is the second search im passing over (the highest possible price)
$sf is what im searching in (price)

i may be a little confused on this works as its been a long day

Re: Search Price

Posted: Mon Oct 13, 2014 2:49 pm
by Celauran
If $sf is a variable containing 'price', then that should be OK. Maybe you want that flexibility to query against different columns. s_sf and s_sf2 are just string literals in the query, though. Pretty sure that "WHERE price >= s_sf" isn't going to yield the results you want. At the very least, it should be ">= $s_sf".

Re: Search Price

Posted: Tue Oct 14, 2014 3:26 am
by andy565
Celauran wrote:If $sf is a variable containing 'price', then that should be OK. Maybe you want that flexibility to query against different columns. s_sf and s_sf2 are just string literals in the query, though. Pretty sure that "WHERE price >= s_sf" isn't going to yield the results you want. At the very least, it should be ">= $s_sf".


hi yes thanks for pointing that out i have now edited the code to exactly how you said, however this still does not work at this time.
Any suggustions?