0

I am in the process of producing a site search for my website (http://jpdesigns.me/studentnights) and have currently managed to implement searching across multiple tables using a keyword following the answers given in this question - PHP mysql search multiple tables using a keyword

I am currently searching over 3 tables.
- Clubs
- Events
- Music

And i have the following query:

$keystring = $mysqli->real_escape_string($_POST["s"]);
$keystring = strtoupper($keystring);
$key = "%".$keystring."%";

$search_stmt = $mysqli->prepare("SELECT id, name, type AS 'col3', city AS 'col4', 'Club' AS 'table' 
                                 FROM studentnights_clubs 
                                 WHERE UCASE(name) LIKE ? OR UCASE(city) LIKE ?
                                 UNION
                                 SELECT id, name, description AS 'col3', image AS 'col4', 'Event' AS 'table' 
                                 FROM studentnights_events 
                                 WHERE UCASE(name) LIKE ?
                                 UNION
                                 SELECT id, name, '' AS 'col3', '' AS 'col4', 'Genre' AS 'table' 
                                 FROM studentnights_music 
                                 WHERE UCASE(name) LIKE ?
                                 ORDER BY
                                    CASE
                                        WHEN name LIKE ? THEN 1
                                        WHEN name LIKE ? THEN 3
                                        ELSE 2
                                    END
                                LIMIT 10");
$search_stmt->bind_param('ssssss', $key, $key, $key, $key, $key, $key);
$search_stmt->execute();
$search_stmt->store_result();
$search_stmt_num = $search_stmt->num_rows;
$search_stmt->bind_result($id, $name, $col3, $col4, $table);


Note: Blank columns entered in the music table are required to make the query work, union queries require the same number of columns being selected in each table.

Onto the actual question. Whereas by above query search the multiple tables fine with the one keyword i want to expand on this so that multiple keywords can be taken into consideration.

This is easiest to explain by letting you see in real time.

Go to http://jpdesigns.me/studentnights/
Enter 'mint' into the search bar, dont press enter, let ajax do its work.
Notice 2 Clubs Are Displayed, Mint Club (Leeds), Mint Club (London).
Add 'leeds' into the search bar (so 'mint leeds').
Notice both clubs disappear.

What i want is, when 'mint leeds' is typed into the search bar it shows mint club in leeds,
because the 'name' of club is 'mint club' (thus contains the word 'mint')
and its in the 'city' called 'leeds'

Thank you for reading and know that any help would be massively appreiciated.


EDIT: Stament updated so that is parameterised.

Community
  • 1
  • 1
JParkinson1991
  • 1,256
  • 1
  • 7
  • 17
  • 1
    you can look at this https://code.google.com/p/anywhereindb/ – user3470953 May 02 '14 at 12:33
  • I am beginning to think to possibly split the search string up into the different keywords, run the query over each keyword, adding and appending each row to an array and then cycling through the array to display the data. – JParkinson1991 May 02 '14 at 12:53
  • Nice try, but you're (probably) still open to SQL Injection. You should be using [parameterized queries](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php), period. Especially because you appear to already be using mysqli. – Clockwork-Muse May 02 '14 at 14:34
  • I have been using parameters. I assume you mean - $mysqli->bind_parma('ss', $var1, $var2); I didnt think it would work because in that statement my '?' would be within quotes. Howver ive jsut thought i could set a varible to "%".$key."%". and sub that in. will try and let you know how it goes – JParkinson1991 May 02 '14 at 14:42
  • Done check, original question. – JParkinson1991 May 02 '14 at 14:47

0 Answers0