creating site search - help

[davieboy]

im creating a site search with the following options:

aircraft
airline
location
keywords

my search query is:
[code=php:0]
SELECT * FROM photos WHERE airline LIKE '%$airline_search%' AND location LIKE '%$airport_search%' AND aircraft LIKE '%$aircraft_search%' AND comments LIKE '%$keyword_search%' AND status = 'accepted' ORDER by ID
[/code]

the way i have written it (it is working), it must have information in all of the fields.
How would i put it that only the airline is selected and it would still bring up resutls?

David

[Jessica]

Change AND to OR

[davieboy]

all of them?

[HuggieBear]

Jesi, that won't yield the same results.  He'll need to constrcut the string as he goes.

Something like this

[code]
<?php
$sql = "SELECT * FROM photos WHERE 1=1 ";
if (!empty($airline_search)){
  $sql .= "AND airline LIKE '%$airline_search%' ";
}
if (!empty($airport_search)){
  $sql .= "AND location LIKE '%$airport_search%' ";
}
?>
[/code]

Regards
Huggie

[Jessica]

Maybe I misunderstood the question. Thanks Huggie

[davieboy]

hey thanks for the help

i have tried creating a string, but i get the error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/****/public_html/search_results.php on line 174

[code=php:0]
// due to!
$query  = "SELECT * FROM photos WHERE status = 'accepted' ORDER by ID LIMIT $limitvalue,$limit";
if (!empty($airline_search)){
  $query .= "AND airline LIKE '%$airline_search%' ";
}
if (!empty($airport_search)){
  $query .= "AND location LIKE '%$airport_search%' ";
}       
if (!empty($aircraft_search)){
$query .= "AND aircraft LIKE '%aircraft_search' ";
}
if (!empty($keyword_search)) {
$query .= "AND comments LIKE '%keyword_search'";
}
[/code]

this line:
while ($r=mysql_fetch_array($result))
{   

i think its 'due to' above, which is limiting the numbers for pagination of the search results

David

[HuggieBear]

It's not relating to the pagination constraints.  It's because you're not submitting a valid query.

The SELECT syntax has to follow a specific order, ORDER BY must come after WHERE, try it this way:

[code]
<?php
$query  = "SELECT * FROM photos WHERE status = 'accepted'";
if (!empty($airline_search)){
  $query .= " AND airline LIKE '%$airline_search%'";
}
if (!empty($airport_search)){
  $query .= " AND location LIKE '%$airport_search%'";
}       
if (!empty($aircraft_search)){
  $query .= " AND aircraft LIKE '%aircraft_search%'";
}
if (!empty($keyword_search)) {
  $query .= " AND comments LIKE '%keyword_search%'";
}
$query .= " ORDER by ID LIMIT $limitvalue, $limit";
?>
[/code]

Regards
Huggie

[davieboy]

cheers mate.
only problem is its not passing the search string onto other pages of the search resuklts

David

[HuggieBear]

They need to be passed in the URL ideally.

[code]<a href="pagename.php?page=2&airline_search=$airline_search">Page 2</a>[/code]

Regards
Huggie

[davieboy]

hi huggie thats for that, my only question is im going to have to use the
if (!$airline_search)
coding, but how do i get this into the searhc pages?

[HuggieBear]

You set a default.  So the top of your page looks something like this:

[code]<?php
$airline_search = !empty($_GET['airline_search']) ? $_GET['airline_search'] : 'ALL';
$airport_search = !empty($_GET['airport_search']) ? $_GET['airport_search'] : 'ALL';
$aircraft_search = !empty($_GET['aircraft_search']) ? $_GET['aircraft_search'] : 'ALL';
$keyword_search = !empty($_GET['keyword_search']) ? $_GET['keyword_search'] : 'ALL';
?>[/code]

Regards
Huggie

[davieboy]

ok, im a little confused. sorry.
do i then jsut do my search page links like

searh.php?page=2&airline=$airline
will that not still have the '&airline' in it even if 'airline_search' is not set?

David

[boo_lolly]

maybe this will help.
[code]
<?php
      /*
        *checks to see which input fields had data
        *then depending on the input field
        *enter in the correct sql query for that input field
        *into a new index in the array $query_array
        */
                        $query_array = array();
                        if(!empty($lname)){
                                $query_array[] = "brideLname LIKE '%". $lname ."%' OR groomLname LIKE '%". $lname ."%'";
                        }
                        if(!empty($fname)){
                                $query_array[] = "brideFname LIKE '%". $fname ."%' OR groomFname LIKE '%". $fname ."%'";
                        }
                        if(!empty($event_day)){
                                $query_array[] = "event_day LIKE '%". $event_day ."%'";
                        }
                        if(!empty($event_month)){
                                $query_array[] = "event_month LIKE '%". $event_month ."%'";
                        }
                        if(!empty($event_year)){
                                $query_array[] = "event_year LIKE '%". $event_year ."%'";
                        }

      /*
        *$query string becomes all the sql queries
        *with 'AND' in between them
        */
                        $query_string = implode(" AND ", $query_array);

                        #echo $query_string ."<br />\n"; /*check sql query*/

                        $result = mysql_query("SELECT * FROM my_search_table WHERE ". $query_string ."") OR die(mysql_error());
?>
[/code]
this way, you can add AND to your search query IF the user has entered data in that specific field. of course, this is my own search tool, you'll have to tweak it in order to impliment it with your own, but that should get you started.

[HuggieBear]

[quote author=davieboy link=topic=123520.msg511347#msg511347 date=1169564328]
ok, im a little confused. sorry.
do i then jsut do my search page links like

searh.php?page=2&airline=$airline
will that not still have the '&airline' in it even if 'airline_search' is not set?
[/quote]

Yes, that's correct, hence always having a default value.

Regards
Huggie

[davieboy]

[quote author=HuggieBear link=topic=123520.msg511387#msg511387 date=1169565995]
[quote author=davieboy link=topic=123520.msg511347#msg511347 date=1169564328]
ok, im a little confused. sorry.
do i then jsut do my search page links like

searh.php?page=2&airline=$airline
will that not still have the '&airline' in it even if 'airline_search' is not set?
[/quote]

Yes, that's correct, hence always having a default value.

Regards
Huggie
[/quote]

but then would this be correct?

$PHP_SELF?page=$pageprev&airline=$airline_search&aircraft=$aircraft_search&location=$location_search&keyword=$keyword_search

[HuggieBear]

Not quite, this would...

$PHP_SELF?page=$pageprev&airline_search=$airline_search&aircraft_search=$aircraft_search&location_search=$location_search&keyword_search=$keyword_search

Regards
Huggie

[HuggieBear]

I'll code you a full example.  Give me half an hour.

Regards
Huggie

[davieboy]

ok huggie thank you.
im just a bit confused over it thats all.

David

[HuggieBear]

Sorry, haven't had a chance to post it yet, really busy at work, but I'll try to get on it now.

Regards
Huggie

[HuggieBear]

This is a bit scrappy, as I've cobbled it together in about 10 mins, but it should give you an idea as to what's going on.

[code]<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="GET">
<input type="text" name="country"> Country<br>
<input type="submit" name="search" value="Search"><br /><br />

<?php
include_once('*****');

// If page number is set then use it, if not, set one!
$page = !isset($_GET['page']) ? '1' : $_GET['page'];

// Define the number of results per page
$max_results = 5;

// Figure out the limit for the query based on the current page number.
$from = (($page * $max_results) - $max_results);

// Specify the default SQL query
$sql = "SELECT countries_name FROM countries WHERE 1=1";

// Amend the serach parameters if there are any
if (isset($_GET['country'])){
  $sql .= " AND countries_name LIKE '{$_GET['country']}%'";
}

// Run the query to get the total number of results.
$result = mysql_query($sql) or die(mysql_error());
$total_results = mysql_num_rows($result);

// Amend the limit and order by parameters and re-query
$sql .= " ORDER BY countries_name LIMIT $from, $max_results";

// Re-run the query with additional constraints
$result = mysql_query($sql) or die(mysql_error());
if ($total_results < 1){
  echo "Sorry, your search produced no results";
}
else {
  echo "Your search produced $total_results results<br><br>\n";
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
  echo "{$row['countries_name']}<br>\n";
}

// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);

// Construct the base link...
$href = $_SERVER['PHP_SELF']."?";
foreach ($_GET as $k => $v){
  if ($k != "page"){
      $href .= "$k=$v&";
  }
}

//Previous and next links
$plink = "&lt;&lt; Previous";
$nlink = "Next &gt;&gt;";

echo "<center>";
// Build Previous Link
if($page > 1){
  $prev = ($page - 1);
  echo "<a href=\"{$href}page={$prev}\">{$plink}</a> ";
}

for($i = 1; $i <= $total_pages; $i++){
  if(($page) == $i){
      echo "$i ";
  }
  else {
      echo "<a href=\"{$href}page={$i}\">{$i}</a> ";
  }
}

// Build Next Link
if($page < $total_pages){
  $next = ($page + 1);
  echo "<a href=\"{$href}page={$next}\">{$nlink}</a> ";
}
echo "</center>";
}
?>[/code]

You can see the code in action here... http://dizzie.co.uk/php/daviboy.php try it with no search data, an empty field, a full coutry, e.g sweden, and just the first letter e.g. S

Regards
Huggie

[davieboy]

sorry for bringing this up again

on my first page of reuslts my sql query is:
SELECT * FROM photos WHERE status = 'accepted' AND location LIKE '%eghh%' ORDER by ID DESC LIMIT 0, 15

on the second it changes to
SELECT * FROM photos WHERE status = 'accepted' ORDER by ID DESC LIMIT 15, 15

with the link:
search_results.php?page=2&airline_search=&location_search=&aircraft_search=&keyword_search=

so effectivly not passing the search query from page to page

any help?
david