Search Price

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
andy565
Forum Newbie
Posts: 7
Joined: Fri Oct 10, 2014 1:31 pm

Search Price

Post 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 ??
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Search Price

Post 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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Search Price

Post 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
andy565
Forum Newbie
Posts: 7
Joined: Fri Oct 10, 2014 1:31 pm

Re: Search Price

Post 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?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Search Price

Post 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.
andy565
Forum Newbie
Posts: 7
Joined: Fri Oct 10, 2014 1:31 pm

Re: Search Price

Post 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.
Last edited by andy565 on Tue Oct 14, 2014 3:24 am, edited 1 time in total.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Search Price

Post by Celauran »

andy565 wrote:

Code: Select all

AND $sf >= (s_sf) AND $sf <= (s_sf2);
What's going on here?
andy565
Forum Newbie
Posts: 7
Joined: Fri Oct 10, 2014 1:31 pm

Re: Search Price

Post 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
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Search Price

Post 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".
andy565
Forum Newbie
Posts: 7
Joined: Fri Oct 10, 2014 1:31 pm

Re: Search Price

Post 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?
Post Reply