Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Apr 2019
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy Cannot get search site to process 2 variables and output results accordingly

    Php Code problem where I cannot figure out how to get the code right to be able to select more than two cities for it to search and display two cities results. It does one at a time or everything in the database but not a selected number.

    I will include some Select side code first below and right after that some of the processing page code. There is a third page of code as well which is the output code I think. If anyone needs to see that I can post that later. I'm not sure the solution needs to address that page but I might be wrong there.

    I've put this up for solutions on other forums and nobody has the answer that fixes it yet. It could be that I'm missing something but that's the latest code changes... Need Help bad.


    PHP Code:
    <form id="main-search-form" method="GET" action="<?= $baseurl?>/_searchresults6.php" role="search">


            <select id="query-input" name="city_id" style="width:28.8%" style="height:69px" required>

                <option value="">Select Area</option>

                <option value="3;1">Ches & VB</option>
                <option value="3">Chesapeake , VA</option>
                <option value="9">Hampton , VA</option> 
                <option value="10">Newport News , VA</option>           
                <option value="2">Norfolk , VA</option>         
                <option value="12">Poquoson , VA</option>   
                <option value="4">Portsmouth , VA</option>              
                <option value="5">Suffolk , VA</option>             
                <option value="1">Virginia Beach , VA</option>  
                <option value="11">Williamsburg , VA</option>
                <option value="0">All Active US Cities</option>



                </select>
                                </form>

    //This next code is from the processing page//

    <?php
    require_once(__DIR__ '/inc/config.php');
    ?>
    <?php
    $total_rows 
    0;
    $response = array();

    $query_city_id = (!empty($_GET['city_id'])) ? $_GET['city_id'] : 0;
    $query_query   = (!empty($_GET['query']))   ? $_GET['query']   : '';  
    $page          = (!empty($_GET['page']))    ? $_GET['page']    : 1;

    // check vars
    if(!is_numeric($query_city_id)) {

    }
    $query_city_id = (int)$query_city_id;

    // city details
    $query_city_name  '';
    $query_state_abbr '';



    if (isset(
    $_GET['city_id'])) {
        if (
    $_GET['city_id'] != 0) {
            
    $ids explode(';'$_GET['city_id']) ;        // split multiple ids
            
    $k count($ids);
            
    $placeholders array_fill(0$k'?');
            
    $placestr join(','$placeholders);
            
    $stmt $conn->prepare("SELECT city_name, state FROM cities WHERE city_id IN ($placestr)");
            
    $stmt->execute($ids); 
        }
        else {
            
    // if you want all cities then a where clause is not needed
            
    $stmt $conn->query("SELECT city_name, state FROM cities");        
        }
        
    // process the stmt results here
        
    while ($row $stmt->fetch(PDO::FETCH_ASSOC)) {
        
    $query_city_name $row['city_name'];
        
    $query_state_abbr $row['state'];
        echo 
    "$query_city_name$query_state_abbr<br>";
    }
    }


    // paging vars
    $limit $items_per_page;
    if(
    $page 1) {
        
    $offset = ($page-1) * $limit 1;
    }
    else {
        
    $offset 1;
    }

    // get page
    if($page == 1) {
        
    $pag '';
    }
    else {
        
    $pag "- $txt_page $page";
    }

    // count total rows
    if(!empty($query_city_id) && !empty($query_query)) {
        
    $query "SELECT COUNT(*) AS total_rows
            FROM places
            WHERE city_id = :city_id AND status != 'trashed' AND paid = 1
                AND MATCH(place_name, description) AGAINST(:query) "
    ;

        
    $stmt $conn->prepare($query);
        
    $stmt->bindValue(':city_id'$query_city_id);
        
    $stmt->bindValue(':query'$query_query);
        
    $stmt->execute();
        
    $row $stmt->fetch(PDO::FETCH_ASSOC);
        
    $total_rows $row['total_rows'];
    }
    else if (empty(
    $query_city_id) && !empty($query_query)) {
        
    $query "SELECT COUNT(*) AS total_rows
            FROM places
            WHERE status != 'trashed' AND paid = 1
            AND MATCH(place_name, description) AGAINST(:query)"
    ;

        
    $stmt $conn->prepare($query);
        
    $stmt->bindValue(':query'$query_query);
        
    $stmt->execute();
        
    $row $stmt->fetch(PDO::FETCH_ASSOC);
        
    $total_rows $row['total_rows'];
    }
    else if (!empty(
    $query_city_id) && empty($query_query)) {
        
    $query "SELECT COUNT(*) AS total_rows
            FROM places
            WHERE status != 'trashed' AND paid = 1 AND city_id = :city_id"
    ;

        
    $stmt $conn->prepare($query);
        
    $stmt->bindValue(':city_id'$query_city_id);
        
    $stmt->execute();
        
    $row $stmt->fetch(PDO::FETCH_ASSOC);
        
    $total_rows $row['total_rows'];
    }
    else {
        
    $total_rows 0;
    }

    $pager = new DirectoryApp\PageIterator($limit$total_rows$page);
    $start $pager->getStartRow();

    // initialize empty city and query check
    $empty_city_and_query false;

    /*--------------------------------------------------
    LIST ITEMS LOGIC
    --------------------------------------------------*/
    if(!empty($query_city_id) && !empty($query_query)) {
        
    $query "SELECT p.place_id, p.place_name, p.address, p.cross_street,
                    p.postal_code, p.phone, p.area_code, p.lat, p.lng, p.state_id, p.description,
                    c.city_name, c.slug, c.state, ph.filename, ph.dir,
                    rev_table.avg_rating
                    FROM places p
                    LEFT JOIN cities c ON p.city_id = c.city_id
                    LEFT JOIN photos ph ON p.place_id = ph.place_id
                    LEFT JOIN (
                        SELECT *,
                            AVG(rev.rating) AS avg_rating
                            FROM reviews rev

                        ) rev_table ON p.place_id = rev_table.place_id
                    WHERE p.city_id = :city_id AND p.status != 'trashed' AND paid = 1
                        AND MATCH(place_name, description) AGAINST(:query)

                    LIMIT :start, :limit"
    ;
        
    $stmt $conn->prepare($query);
        
    $stmt->bindValue(':city_id'$query_city_id);
        
    $stmt->bindValue(':query'$query_query);
        
    $stmt->bindValue(':start'$start);
        
    $stmt->bindValue(':limit'$limit);
    }

    else if(empty(
    $query_city_id) && !empty($query_query)) {
        
    $query "SELECT p.place_id, p.place_name, p.address, p.cross_street,
                    p.postal_code, p.phone, p.area_code, p.lat, p.lng, p.state_id, p.description,
                    c.city_name, c.slug, c.state, ph.filename, ph.dir,
                    rev_table.avg_rating
                    FROM places p
                    LEFT JOIN cities c ON p.city_id = c.city_id
                    LEFT JOIN photos ph ON p.place_id = ph.place_id
                    LEFT JOIN (
                        SELECT *,
                            AVG(rev.rating) AS avg_rating
                            FROM reviews rev
                        ) rev_table ON p.place_id = rev_table.place_id
                    WHERE p.status != 'trashed' AND paid = 1
                        AND MATCH(place_name, description) AGAINST(:query)

                    LIMIT :start, :limit"
    ;
        
    $stmt $conn->prepare($query);
        
    $stmt->bindValue(':query'$query_query);
        
    $stmt->bindValue(':start'$start);
        
    $stmt->bindValue(':limit'$limit);
    }

    else if(!empty(
    $query_city_id) && empty($query_query)) {
        
    $query "SELECT p.place_id, p.place_name, p.address, p.cross_street,
                    p.postal_code, p.phone, p.area_code, p.lat, p.lng, p.state_id, p.description,
                    c.city_name, c.slug, c.state, ph.filename, ph.dir,
                    rev_table.avg_rating
                    FROM places p
                    LEFT JOIN cities c ON p.city_id = c.city_id
                    LEFT JOIN photos ph ON p.place_id = ph.place_id
                    LEFT JOIN (
                        SELECT *,
                            AVG(rev.rating) AS avg_rating
                            FROM reviews rev

                        ) rev_table ON p.place_id = rev_table.place_id
                    WHERE p.city_id = :city_id AND p.status != 'trashed' AND paid = 1

                    LIMIT :start, :limit"
    ;
        
    $stmt $conn->prepare($query);
        
    $stmt->bindValue(':city_id'$query_city_id);
        
    $stmt->bindValue(':start'$start);
        
    $stmt->bindValue(':limit'$limit);
    }

    else{ 
    // both $query_loc and $query_query empty
        
    $empty_city_and_query true;
    }

    // now execute query
    $stmt->execute();
    Last edited by FishMonger; Apr 16th, 2019 at 04:39 PM.

  2. #2
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    3,365
    Thanks
    2
    Thanked 234 Times in 227 Posts
    Sorry I didn't followup in your duplicate question in the mysql section.

    As I showed in that other post, you need to make 2 adjustments to the select statement.
    1) You need to add the 'multiple' attribute to allow the selection of multiple values.
    2) You need to add [] brackets to the 'name' value which tells php to accept multiple values.

    So, change:
    PHP Code:
    <select id="query-input" name="city_id" style="width:28.8%" style="height:69px" required
    To:
    PHP Code:
    <select id="query-input" name="city_id[]" multiple="multiple" style="width:28.8%" style="height:69px" required
    I have not looked at the rest of the code, but start with that change and dump out the $query_city_id var and inspect its contents to verify you received all selected options.


 

Tags for this Thread

Posting Permissions

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