Jump to content

search query done... i think?


rootKID

Recommended Posts

Hello W3S - me again...

So i have this little peace of code:

    if( isset($_POST['FORMNAME_submit_search']) ) {
        
        $qur = "";
        
        @$SearchKeywords = mysqli_real_escape_string($dblink, $_POST['FORMNAME_SogProfilBar_Keywords']); // Search Bar Profil Keywords
        @$MinAlder = mysqli_real_escape_string($dblink, $_POST['FORMNAME_MinAlder']); // Minimum Alder
        @$MaxAlder = mysqli_real_escape_string($dblink, $_POST['FORMNAME_MaxAlder']); // Maximum Alder
        @$Landsdel = mysqli_real_escape_string($dblink, $_POST['FORMNAME_Landsdel']); // Landsdel
        @$Kon = mysqli_real_escape_string($dblink, $_POST['FORMNAME_Kon']); // Køn
        @$MinHojde = mysqli_real_escape_string($dblink, $_POST['FORMNAME_MinHojde']); // Minimum Højde
        @$MaxHojde = mysqli_real_escape_string($dblink, $_POST['FORMNAME_MaxHojde']); // Maximum Højde
        @$Etnicitet = mysqli_real_escape_string($dblink, $_POST['FORMNAME_Etnicitet']); // Etnicitet
        @$Sprog = mysqli_real_escape_string($dblink, $_POST['FORMNAME_Sprog']); // Sprog
        @$MinVaegt = mysqli_real_escape_string($dblink, $_POST['FORMNAME_MinVaegt']); // Minimum Vægt
        @$MaxVaegt = mysqli_real_escape_string($dblink, $_POST['FORMNAME_MaxVaegt']); // Maximum Vægt
        @$Tatoveringer = mysqli_real_escape_string($dblink, $_POST['FORMNAME_Tatoveringer']); // Tatoveringer
        @$Kategorier = mysqli_real_escape_string($dblink, $_POST['FORMNAME_Kategorier']); // Kategorier
        
        // Søgning af ALT starter her.
        
        $query = "
        SELECT
            users.users_id,
            
            any_value(users.users_fornavn),
            any_value(users.users_mellemnavn),
            any_value(users.users_efternavn),
            any_value(users.users_email),
            any_value(users.users_city),
            any_value(users.users_zipcode),
            any_value(users.users_birthday_date),
            any_value(users.users_address),
            any_value(users.users_tlf_telefon),
            any_value(users.users_tlf_mobil),
            
            users_info.usersinfo_FK_users_id,
            
            any_value(users_info.usersinfo_id),
            any_value(users_info.usersinfo_profilesearch_viewable),
            any_value(users_info.usersinfo_hojde_cm),
            any_value(users_info.usersinfo_vaegt_kg),
            any_value(users_info.usersinfo_ErfaringKvalifikationer),
            any_value(users_info.usersinfo_beskaeftigelse),
            any_value(users_info.usersinfo_FK_usersinfoHairColor_id),
            any_value(users_info.usersinfo_FK_usersinfoEyeColor_id),
            any_value(users_info.usersinfo_FK_usersinfoBukseBredde_id),
            any_value(users_info.usersinfo_FK_usersinfoBukseLaengde_id),
            any_value(users_info.usersinfo_FK_usersinfoBluseStorrelse_id),
            any_value(users_info.usersinfo_FK_usersinfoSkoStorrelse_id),
            any_value(users_info.usersinfo_FK_usersinfoLandsdel_id),
            any_value(users_info.usersinfo_FK_usersinfoEtnicitet_id),
            any_value(users_info.usersinfo_FK_usersinfoTatoveringer_id),
            any_value(users_info.usersinfo_FK_usersinfoKategorier_id),
            any_value(users_info.usersinfo_FK_usersinfoSprog_id)
        FROM
            users
        INNER JOIN users_info ON users.users_id = users_info.usersinfo_FK_users_id
        ";
        
        // Search patterns - needs editing! from old-original to my version so it fits!
        include_once("index.search_patterns_setup.php");
        
        // fortsætter herunder
        $query .= " GROUP BY users_id, usersinfo_FK_users_id";
        $result = mysqli_query($dblink, $query) or die(mysqli_error($dblink));
        
        if( mysqli_num_rows($result) > 0 ) {
            
            while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
                //echo '<p>her er jeg!</p>'; // indsæt søge resultater her! (DET ING VIIIIRKER!)
                
                $output = '<p>'.$query.'</p>';
                echo $output;
                
                echo '<p>'.$row['users_fornavn'].'</p>';
            }
            
        } else {
            
            $row = '<p>Der var desværre ingen resultater på din søgning. Prøv igen.</p>';
            echo $row;
            $output = '<p>'.$query.'</p>';
            echo $output;
            die();
            
        }
    } else {
        
        include_once("index.no_search_show_cards_profiles.php");
        
    }

The small code above is the main file. This code here is where i check for patterns and what has been checked in the form for searching etc...

/////////////////////////// ALDER ///////////////////////////

if( $MinAlder > 0 ) {
    echo clauseCHECK_AND();
    //echo "<p>".clauseCHECK_AND()."</p>";
    
    // dob = Date Of Birth
    $time = new DateTime('now');
    $newtime = $time->modify('-'.$MinAlder.' year')->format('Y-m-d');
    $query .= $clause." users_birthday_date <= '$newtime' ";
}
if( $MaxAlder > 0 ) {
    //echo clauseCHECK_AND();
    //echo "<p>".clauseCHECK_AND()."</p>";
    
    // dob = Date Of Birth
    $time = new DateTime('now');
    $newtime = $time->modify('-'.$MaxAlder.' year')->format('Y-m-d');
    $query .= $clause." users_birthday_date >= '$newtime' ";
}

/////////////////////////// KØN ///////////////////////////

if( !empty($Kon) ) {
    echo clauseCHECK_AND();
    //echo "<p>".clauseCHECK_AND()."</p>";
    
    $query .= $clause." usersGender_id = '$Kon' ";
}

/////////////////////////// LANDSDEL ///////////////////////////

if( !empty($Landsdel) ) {
    echo clauseCHECK_AND();
    //echo "<p>".clauseCHECK_AND()."</p>";
    
    $query .= $clause." usersinfoLandsdel_id = '$Landsdel' ";
}

/////////////////////////// ETNICITET ///////////////////////////

if( !empty($Etnicitet) ) {
    echo clauseCHECK_AND();
    //echo "<p>".clauseCHECK_AND()."</p>";
    
    $query .= $clause." usersinfoEtnicitet_id = '$Etnicitet' ";
}

/////////////////////////// TATOVERINGER ///////////////////////////

if( !empty($Tatoveringer) ) {
    // når jeg skifter til checkboxes, bruger jeg clauseCHECK_OR()! Som den bruges i de originale! Tjek for eksempler!
    
    echo clauseCHECK_AND();
    //echo "<p>".clauseCHECK_AND()."</p>";
    
    $query .= $clause." usersinfoTatoveringer_id = '$Tatoveringer' ";
}

/////////////////////////// SPROG ///////////////////////////

if( !empty($Sprog) ) {
    // når jeg skifter til checkboxes, bruger jeg clauseCHECK_OR()! Som den bruges i de originale! Tjek for eksempler!
    
    echo clauseCHECK_AND();
    //echo "<p>".clauseCHECK_AND()."</p>";
    
    $query .= $clause." usersinfoSprog_id = '$Sprog' ";
}

/////////////////////////// KATEGORIER ///////////////////////////

if( !empty($Kategorier) ) {
    // når jeg skifter til checkboxes, bruger jeg clauseCHECK_OR()! Som den bruges i de originale! Tjek for eksempler!
    
    echo clauseCHECK_AND();
    //echo "<p>".clauseCHECK_AND()."</p>";
    
    $query .= $clause." usersinfoKategorier_id = '$Kategorier' ";
}

/////////////////////////// VÆGT ///////////////////////////

if( $MinVaegt>0 && $MaxVaegt<1 ) {
    $reg_vaegt_max="999";
}
if( $MaxVaegt>0 && $MinVaegt<1 ) {
    $reg_vaegt_min="1";
}
if( $MinVaegt>0 || $MinVaegt>0 ) {
    // når jeg skifter til checkboxes, bruger jeg clauseCHECK_OR()! Som den bruges i de originale! Tjek for eksempler!
    
    echo clauseCHECK_AND();
    //echo "<p>".clauseCHECK_AND()."</p>";
    
    $query .= $clause."userinfo_vaegt_kg BETWEEN $MinVaegt AND $MaxVaegt  ";
    $clause = " OR ";
}

/////////////////////////// HØJDE ///////////////////////////

if( $MinHojde>0 && $MaxHojde<1 ) {
    $reg_hoejde_max="999";
}
if( $MaxHojde>0 && $MinHojde<1 ) {
    $reg_hoejde_min="1";
}
if( $MinHojde>0 || $MinHojde>0 ) {
    // når jeg skifter til checkboxes, bruger jeg clauseCHECK_OR()! Som den bruges i de originale! Tjek for eksempler!
    
    echo clauseCHECK_AND();
    //echo "<p>".clauseCHECK_AND()."</p>";
    
    $query .= $clause."usersinfo_hojde_cm BETWEEN $MinHojde AND $MaxHojde ";
    $clause = " OR ";
}

/////////////////////////// KEYWORDS ///////////////////////////

//if keyword set, goes here
if( !$SearchKeywords=="" ) {
    echo clauseCHECK_AND(); // Initial clause
    //echo "<p>".clauseCHECK_AND()."</p>"; // Initial clause
    
    // search query
    $c = "
    users_id LIKE '%$SearchKeywords%'
    OR
    users_fornavn LIKE '%$SearchKeywords%'
    OR
    users_mellemnavn LIKE '%$SearchKeywords%'
    OR
    users_efternavn LIKE '%$SearchKeywords%'
    ";
    
    $query .= $clause." $c";
}

Now, i have a little trouble with the latest version of MySQL installed, i always keep getting the error:

Expression #12 of SELECT list is not in GROUP BY clause and contains nonaggregated column (...) which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

So i had to change my main SQL a little, which is why i added "any_value()" to my main sql as a temporary solution, and seems to work, only whenever i get an output, i cannot actually row out any output Oo? Whenever i try to output say "users_fornavn", which is Danish for first name... i get index undefined error... any ideas? I can only assume it it the sql that has the error?

Hope you guys can help out a little.

PS: Note that the "any_value()" part, was only supposed to be a temporary solution for a customer that want's the website up and running somewhere by next week, so i thought to add the any_value() sql function for now and at a later point be more specific about the code. I just need the rough ways for now so i am able to change that at a later point.

Thanks in advance! Really hope you peepz can help me out! ;):)

 

Link to comment
Share on other sites

Whenever i try to output say "users_fornavn", which is Danish for first name... i get index undefined error... any ideas?

The column name is not users_fornavn, it is "any_value(users.users_fornavn)".  You can use print_r to print out the entire array to see what's in it.  You can use an alias to change the column name in the result.

Wouldn't it make more sense to disable the SQL mode causing that error?

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...