MYSQLi select from database results that are like / contain

Question

Since I am new to Mysqli in general I am not sure what to look for so I am posting here.

TABLE = id | firstname | lastname | attribute1 | attribute 2

Example string to search for is "test" The script should search for any row where the firstname or lastname contain "test" or "tést" or "tèst" or a variation of this.

Then is should return all results in JSON format.

How would I go about doing this?

just fetch mysql query result in array and use PHP json_encode function to convert array to json — Girish, Jan 10 '14 at 19:45
What part of this are you stuck on? Do you know how to write SQL queries? Do you know how to run them in PHP? Do you know how to output JSON? — gen_Eric, Jan 10 '14 at 19:46
Use `WHERE` and `LIKE` <= Google that. Or `FULLTEXT` search. — Funk Forty Niner, Jan 10 '14 at 19:47
If you want to use a search function, your database has to be in myIsam. — loveNoHate, Jan 10 '14 at 19:47

score 4 · Answer 1 · answered Jan 10 '14 at 19:46

Well, MySQL to JSON is a big stretch.

Let's focus on the MySQL part here.

Query to filter on your keyword: SELECT * FROM table WHERE firstname LIKE '%test%' OR lastname LIKE '%test%'
Then using mysqli you can retrieve the results.

You have to do research on your own. Post specific questions or problems here. We can't just do it for you...

score 1 · Answer 2 · answered Jan 10 '14 at 19:53

1

SELECCT * FROM <TABLE NAME> WHERE firstname LIKE '%test%' OR lastname LIKE '%test%';

then use fetch array function and apply json_encode() function on that array to get the json string.

answered Jan 10 '14 at 19:53

Muhammad Saud

356
3
13

score 0 · Answer 3 · answered Jan 10 '14 at 19:50

What you need to do to search for a string is this

SQL: SELECT * FROM TABLE WHERE firstname LIKE '%test%' OR lastname LIKE '%test%' OR attribute1 LIKE '%test%' OR attribute2 LIKE '%test%'

You can of course put a post variable in the like statement if you want so like '%".$_POST['value']."%'

To put it in json use the php function json_encode.

score 0 · Answer 4 · answered Jan 10 '14 at 19:59

0

Thanks for the help guys!

I was mainly looking for the SQL query

Using

$results = mysqli_query($mysqli, "SELECT * FROM players WHERE firstname LIKE '%test%' OR lastname LIKE '%test%'");


$myarray = array();
while ($record = mysqli_fetch_assoc($results))
    {
        //print_r($record);
        $temparray = $record;
        array_push($myarray, $temparray);
    }
        echo json_encode($myarray);

answered Jan 10 '14 at 19:59

user2001411

51
1
2
7

That does not cater for the variations of test. You could do %t_st%. – loveNoHate Jan 10 '14 at 20:01

score 0 · Answer 5 · answered Jan 13 '16 at 16:54

I found it by myself. I hope this can help you as alternate coding.

$temp_findcategory = $_POST['findcategory']; 
$temp_findkeyword = "%".$_POST['findkeyword']."%";
$search = mysqli_query($conn,"SELECT * FROM table WHERE field1 like '$temp_findkeyword' order by '$temp_findcategory' asc"); 
$row_search = mysqli_fetch_assoc($search);

MYSQLi select from database results that are like / contain

5 Answers5