Search box No Results

[morrism35]

I'm creating a search box for my website but not getting any results. My database is up and working fine and i'm able to select it from my code. I want to be able to pull up different information on a artist by searching his first or last name.

 

<form action='inc_artist_search_box.php' method='get'>

<p>Search for your favorite artist, song, or label</p>

<input type='text' name='userSearch' value='<?php echo $_GET['userSearch']; ?>' />

<input type='submit' value='Search' />

 

</form>

<hr />

 

<?php

 

$k=$_GET['userSearch'];//get user search term

$terms=explode(" ", $k);//user search term into array

$query="Select first_name, last_name, groups, era

FROM artist_table WHERE ";//query string appended to querry string after foreach loop

 

$i=0;//counter variable to catch first search term 

 

//loop through search term and find terms that are like query request

foreach($terms as $each){

$i++;

if($i==1)

$query.="first_name LIKE '%$each%'";//ending append from $query statement above if first search term 

else 

$query.="OR first_name LIKE '%$each%'";//ending append from $query statement above if second or later search term

 

 

 

 

}

 

$servername = '127.0.0.1';

$username = 'root';

$password = 'Conquest1';

 

// Create connection

$conn = new mysqli($servername, $username, $password);

 

// Check connection

if ($conn->connect_error) {

    die("Connection failed: " . $conn->connect_error);

} 

echo "Connected successfully";

//select database

if(mysqli_select_db($conn,"Artist"))

echo "connection successful";

else

echo "connection failed";

 

 

$query=mysqli_query($conn,$query);//perform the query

 

$numrows=mysqli_num_rows($query);//number of rows in query results found

 

//loop throw rows and extra data

if($numrows<0){

while($row=mysqli_fetch_assoc($query)){

$first_name=$row['first_name'];

$last_name=$row['last_name'];

$group=$row['group'];

$era=$row['era'];

 

echo $first_name;

echo $last_name;

echo $group;

echo $era;

}

}

else

echo"No search results found for \"<b>$k</b>\"";

[morrism35]

I actually got it to work. My if($numrows>0) was incorrect, now i'm getting results based on the first name column. Now I add functionality so I can search based on the artist last_name, group, and era. Not really sure how to do that, any ideas or hints.

[QuickOldCar]

A few ways can go about it

 

Do additional LIKE for each additional column want to look within

Make the column name a dynamic variable

Add a form and use if/else or a switch to perform different queries

Can use fulltext search in boolean mode and simplify the queries a lot

[morrism35]

Tried it like below but kept getting error messages. Only in my first two months of learning php and mysql so i'm limited so really trying to keep it simple as possible. I'm somewhat familiar how to write this in strictly mysql in a command line or gui but not with php.

 

else if

$query.="OR first_name__name LIKE '%$each%'";//ending append from $query statement above if second or later search term

 

else if

$query.="OR last_name LIKE '%$each%'";//ending append from $query statement above if second or later search term

 

else if

$query.="OR groups LIKE '%$each%'";//ending append from $query statement above if second or later search term

[mac_gyver]

as you are testing the inputs to determine what terms should be part of the sql statement, you need to add each OR'ed term to an array, then just implode/join the array elements with the ' OR ' condition between them. this will simplify your code (you won't have to test if any term is the first one.) you should also use a prepared query, rather than to put your $each search term directly into the sql statement. see the following thread - http://forums.phpfreaks.com/topic/299482-php-pdo-how-to-bind-values-to-varibale-contains-concatenated-string/

[mac_gyver]

if you are going to be doing this in the sql statement, here's another tip that will simplify the sql and may improve the performance  of the query (your LIKE term, with a leading % isn't using an index anyway.)

 

you can concatenate the database columns you are searching, and use one LIKE comparison for each of the search keywords (the following would be what gets imploded/joined with the ' OR ' between them.)

 

the sql would look like - CONCAT_WS(' ',first_name, last_name, groups) LIKE '%$each%'

 

in this case, you would also dynamically build the list of column names to put into the CONCATE_WS(' ',...) syntax based on whatever inputs you are testing.

[morrism35]

Now I'm really confused lol, I'm really trying to keeps this real simple for now. but thanks you guys and girls are great.

[morrism35]

Found another bug if I add one extra space in my search kjkj kjkj  space here it will display the whole table. Getting frustrated this portion of my website it due in 10 days.

[mac_gyver]

if I add one extra space in my search kjkj kjkj  space here 

 

 

that's because you are asking the query to search for either anything that contains a space - %space here% or anything %%. you should probably trim() the $each value and only use it if it is not an empty string, which is different than using empty(), which would treat a search for a 0 (zero) as empty.

 

the coding suggestions that have been made will actually reduce and simply the amount of code it takes to build the sql statement.

Edited December 3, 2015 by mac_gyver

[morrism35]

Ok the trim function did work. Would like to just add functionality to search with last name and groups. I thought it would be as simple as adding an extra like statements underneath the $query.="first_name LIKE '%each' statement but that's not working and the other suggestions are going way over my head. The implode with the OR like "McGuyver" suggested seems to be more understandable but I need to research that a little more.

[mac_gyver]

see the three different 'version A, B, C' examples in this code - 

<form action='inc_artist_search_box.php' method='get'>
<p>Search for your favorite artist, song, or label</p>
<input type='text' name='userSearch' value='<?php echo $_GET['userSearch']; ?>' />
<input type='submit' value='Search' />
</form>
<hr />
<?php
// make the database connection first. a database connection is required for the mysqli_real_escape_string() function that's being used when the sql search term is being built
$servername = '127.0.0.1';
$username = 'root';
$password = 'Conquest1';
// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
//select database
if(mysqli_select_db($conn,"Artist"))
    echo "connection successful";
else
    echo "connection failed";


$k= isset($_GET['userSearch']) ? trim($_GET['userSearch']) : ''; //get user search term

// your code implies that a search term is required (an empty WHERE in the sql statement would result in a query error)
// if you instead want to match all rows when there isn't a search term, the WHERE keyword in the sql statement would need to be added only if there is a where term or you would need to supply a true value after the empty WHERE keyword
if($k == ''){
    echo 'A search term is required';
} else {
    $terms = array();
    $search=explode(" ", $k);//user search term into array
    //loop through search term and find terms that are like query request
    
    // if the columns to include in the search is selectable (checkboxes?) see the 'version C example code' below
    // you would build the $search_columns variable at this point in the code with whatever columns you want
    // if you are just asking to always search a list of columns, you can either use the 'version B example code' below
    // or you could build the $search_columns variable at this point with the columns hard-coded in it and use the 'version C example code' below
    
    foreach($search as $each){
        $each = trim($each);
        if($each != ''){
            $each = $conn->real_escape_string($each); // if not using a prepared query, escape the string data


            // **** version A example code - duplicates what you posted at the start of the thread
            $terms[] = "first_name LIKE '%$each%'";
            
            // **** version B example code -
            // to unconditionally search multiple columns
            // replace the above line of code with this -
            $terms[] = "CONCAT_WS(' ',first_name, last_name, groups) LIKE '%$each%'";
            
            // **** version C example code -
            // if the columns to be included in the search are picked via some section process (checkboxes?),
            // you would build the list of column names in a php variable before the start of this loop,
            // then just use that variable in this code.
            // note: do NOT put raw external user data into a query. if the column names are coming directly from user supplied data, you MUST validate that they are exactly and only permitted column names. no amount of escaping the values will protect against sql injection because these are not string data values.
            // replace the above line of code with this -
            $terms[] = "CONCAT_WS(' ',$search_columns) LIKE '%$each%'";
            
        }
    }
    $query = "Select first_name, last_name, groups, era
        FROM artist_table
        WHERE " . implode(' OR ', $terms);


    $query=$conn->query($query);//perform the query
    $numrows=mysqli_num_rows($query);//number of rows in query results found
    //loop throw rows and extra data
    if($numrows > 0){
        while($row=mysqli_fetch_assoc($query)){
            $first_name=$row['first_name'];
            $last_name=$row['last_name'];
            $group=$row['group'];
            $era=$row['era'];
            echo $first_name;
            echo $last_name;
            echo $group;
            echo $era;
        }
    }
    else
        echo"No search results found for \"<b>$k</b>\"";
}

Edited December 3, 2015 by mac_gyver

[morrism35]

thank you mc-gyver so much. I used version b. Version b is very close to what I had in mind, but I was trying to use it like you would make a choice with a typical if/else or switch clause. I understand putting each search term into an array, then it gets a little murky. This was much harder than I thought in  my reading or the youtube video that I watched. My next goal is to try and include my song table and find songs based on searching for the song. My easy answer without testing would be to create a join  with my song table and artist table, then I just include songs with my query statement in the script.