SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Posting search results

    Hi Guys,

    i am a bit stuck on this part of coding, when people search ALL categories i leave the option value as: <option value="0">

    so if 0 is passed to the GET all categories should be searched instead of just category 1,2 or 3 etc

    here is what i have so far:

    PHP Code:
    <?php
         
    ## variables #######################################################################
         
    $keywords urldecode($_GET['keywords']);
         
         
    ## new search ######################################################################
         
    echo '<br /><h4>Search Results</h4><br />';
         
         
    ## if no search was done ###########################################################
         
    if(empty($keywords)) {
         
         echo 
    '<b><font color="red">Error: </font>Sorry You Never Entered Anything To Search For!</b><br /><br />';
         include(
    "includes/footer.php");
         exit;
         
         }
         
         
    ## put the gets in variables #######################################################
         
    $category_id $_GET['cat_id'];
         
            
    ## Pagination start #############################################################
            
    echo "<center>";
            
    // If current page number, use it 
            // if not, set one! 
            
            
    if(!isset($_GET['page'])){ 
                
    $page 1
            } else { 
                
    $page $_GET['page']; 
            } 
            
            
    // Define the number of results per page 
            
    $max_results 50;         
            
            
    // Figure out the limit for the query based 
            // on the current page number. 
            
    $from = (($page $max_results) - $max_results);  
            
            
    ## Pagination start #############################################################
         
         ## create the query using our keywords #############################################
          
    $search_query "SELECT * ";
         
    $search_query .= "FROM `uploaded_files` ";
         
    $search_query .= "WHERE (`file_name` LIKE '%$keywords%' OR `file_name` LIKE '%$keywords%')";
         
         
    // search all cats...////////////////////////////////////////////////////////////
            
            
    $quick_results $_GET['results'];
            
            if(
    $_GET['results'] == 'Ascending') {
            
            
    $sort_order 'ASC';
            
            } else {
            
            
    $sort_order 'DESC';
            
            }
            
         
    $search_query .= "  ORDER BY `date_added` $sort_order LIMIT $from$max_results";
         
         echo 
    $search_query;
         
         
    ## the resultset ###################################################################
         
    $query_result mysql_query($search_query) or die (mysql_error());
         
         
    $how_many mysql_num_rows($query_result);
         
         
    ## if no results back dont make the table ##########################################
         
    if($how_many == 0) {
         
         echo 
    '<b><font color="red">Error: </font>Sorry We Have Found No Results With That Search Query!</b><br /><br />';
         include(
    "includes/footer.php");
         exit;     
         
         }
         
         
    ## a table for the results #########################################################
         
    echo '<table width="750" border="1" bordercolor="#000000" cellpadding="0" cellspacing="0" />
               <tr>
               <th bgcolor="#004E98" /><font color="#ffffff">Category</font></th><th bgcolor="#004E98" /><font color="#ffffff">NZB Name</font></th><th bgcolor="#004E98" /><font color="#ffffff">Added</font></th>
               </tr>'
    ;
         
    ## while loop results ##############################################################
         ## while loop results ##############################################################
         
    while ($row mysql_fetch_array($query_result)) {
         
         
    ## the variables ###################################################################
         
    $id $row['id'];
         
    $file_name $row['file_name'];
         
    $date_added $row['date_added'];
         
    $cat_id $row['cat_id'];
         
         
    // link the 2 tables.../////////////////////////////////////////////////////////////
         
    $query_1 "SELECT * FROM `uploaded_files` WHERE `cat_id`='$cat_id'";
         
    $result_1 mysql_query($query_1) or die (mysql_error());        
         
    $cat_id_2 mysql_fetch_array($result_1) or die (mysql_error());           
         
    $gif $cat_id_2['cat_id'];
         
         
    // get the .gif...//////////////////////////////////////////////////////////
         
    $query_2 "SELECT * FROM `categories` WHERE `id`='$gif'";
         
    $result_2 mysql_query($query_2) or die (mysql_error());             
         
    $rows mysql_fetch_array($result_2) or die (mysql_error());
         
    $display_gif $rows['image'];
         
         echo 
    '<td width="5%" bgcolor="#004E98"><img src="category_pics/'.$display_gif.'" /></a></td><td align="left">&nbsp;<a href="file_details.php?id='.$id.'" />'.$file_name.'</a></td><td>'.$date_added.'</td></tr>'
         
         }
         
    ## while loop results ##############################################################
         ## while loop results ##############################################################  
             
         ## end the table ###################################################################
         
    echo '</table><br />';
         
             
    ## Pagination end ###############################################################
            
            // Figure out the total number of results in DB: 
            
    $total_results mysql_result(mysql_query("SELECT COUNT(*) as Num FROM `uploaded_files` WHERE `cat_id`='$cat_id'"),0); 
            
            
    // Figure out the total number of pages. Always round up using ceil() 
            
    $total_pages ceil($total_results $max_results); 
            
            
    // Build Previous Link 
            
    if($page 1){ 
                
    $prev = ($page 1); 
                echo 
    " <a href=\"".$_SERVER['PHP_SELF']."?keywords=$keywords&amp;page=$prev\"><<<&nbsp;</a> "
            } 
            
            for(
    $i 1$i <= $total_pages$i++){ 
                if((
    $page) == $i){ 
                    echo 
    "[<b>$i</b>] "
                    } else { 
                        echo 
    "<a href=\"".$_SERVER['PHP_SELF']."?keywords=$keywords&amp;page=$i\">$i</a> "
                } 
            } 
            
            
    // Build Next Link 
            
    if($page $total_pages){ 
                
    $next = ($page 1); 
                echo 
    "<a href=\"".$_SERVER['PHP_SELF']."?keywords=$keywords&amp;page=$next\"> >>></a>"
            } 
            echo 
    "<br /><br />"
            
            
    ## Pagination end ###############################################################
    ?>
    This is what i have so far the pagination results are wrong, they always seem to give back the wrong results, and help on this would be great pagination always = headaches for me lol

    thanks guys

    Graham

  2. #2
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Join the tables with JOIN clause, drop the idea of running queries within a loop:

    Code php:
    $search_query = "SELECT * ";
    $search_query .= "FROM `uploaded_files` 
    LEFT JOIN categories ON uploaded_files.cat_id=categories.id";
    $search_query .= "WHERE (`file_name` LIKE '%$keywords%' OR `file_name` LIKE '%$keywords%')";

    To answer your categories question, add the the cat_id to the WHERE clause only if it's not 0:
    Code php:
    if($category_id>0) $search_query .= " AND cat_id=$category_id";

    Also, do the input validation:
    Code php:
    $category_id=(int)$_GET['cat_id'];
    Saul

  3. #3
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi PHP-Daemon,

    i have implemented the changes you said, when echoing out my query i'm getting this error:

    Code:
    SELECT * FROM `uploaded_files` LEFT JOIN categories ON uploaded_files.cat_id=categories.idWHERE (`file_name` LIKE '&#37;test%' OR `file_name` LIKE '%test%') AND cat_id=1SELECT * FROM `uploaded_files` LEFT JOIN categories ON uploaded_files.cat_id=categories.idWHERE (`file_name` LIKE '%test%' OR `file_name` LIKE '%test%') AND cat_id=1 ORDER BY `date_added` ASC LIMIT 0, 50FUNCTION categories.idWHERE does not exist
    i'm not sure what exactly doesn't exhists?

    cheers mate

    Graham

  4. #4
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Scrub the above post it was because i never left a space between the word WHERE.

    this works great the only problem i am facing is the pagination links at the bottom when i do a search it displays:

    <<< 1 2 >>>

    if i goto the next page it shows:

    <<< 1 2 3 >>>

    they just don't seem to be responding correctly.

    thanks mate

    Graham

  5. #5
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Add the cat_id to the page links too.
    Saul


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •