my first mysql driven website! help with search button...

[i8igmac]

"movie torrent download site" name does not exist yet...

1099 uniq movie titles exist (blue ray, dvd rip, hdtv rip)

i wont share a link, as this kind of site im sure violates the forum user agreements...(pm)

i have just completed my first mysql project(beta testing)... i started a thread maybe a few years ago, and lost interest... started from scratch, many times over... someone's negative input on my theory had sparked motivation to complete the site...

i understand the copyright laws and dont need a lecturing... still not sure if ill try and go big as this is a scary subject... so, im looking for help with a search button, mysql is still a new subject... my current working search bar runs this sql query.

$search_html="alien"

SELECT * FROM `movies` WHERE `name` REGEXP '$search_html' or `actors` REGEXP '$search_html' or `genre` REGEXP '$search_html' or `description` REGEXP '$search_html' or `download_append` REGEXP '$search_html'

name="some movie(2015)"

actors="jim smith, joe jhonson, mike anderson, tim smootch" genre="action drama animation adventure sci-fi"

description="this movie might be about some people doing some odd things with alien life forms"

download_append="acid_burn_uploaded_this_file_has_size_of_2_gig_blueray.torrent, and_more_dvd_rip.torrents"

so, currently my search bar works well WHEN only submitting a single string... we can find all movies that include the string alien... im looking for more examples on search a mysql database...

$search_html="cia 2015 denzel"

so, my brain thinks like this... i need to split the search query into blocks,

if query(db=>query"cia") ==true?
   if query(db=>query"2015") ==true?
      if query(db=>query"denzel") ==true?
return resaults that include all 3 strings...

Edited August 6, 2015 by i8igmac

[vailixi]

You should check out PHP/MySQL tutorials on thenewboston YouTube channel. There are 200 video tutorials total.

There is a search engine tutorial that goes into exploding and splitting strings. Stuff I really don't know how to do in PHP. I hate PHP with a passion.

Also there is the MySQL manual

http://dev.mysql.com/doc/

Also there is a pirate bay clone on github.

https://github.com/isohuntto/openbay

There might be some code there you can use. If I remember right there is a SQL dump of TPB available and that's a lot of torrents.

I'm going to give my two cents on torrents. Torrents are a great way share files. Since I do almost everything with software I can build from source on Linux I don't really need to download anything illegally. But when I'm downloading a Linux image torrents seem to be a lot faster than a http or ftp download.

What I think would be great is a site full of open source software of creative commons documentary and educational type movies. I would like to see a lot of YouTubers make their videos available for download.

[digip]

lookup like and in coommands

[i8igmac]

thanks for the links. I know what I need to do... I have to many columns that I'm searching...

For a much simpler version, ill add a column called 'searchable'... ill put all the information in this column...

$search_html='cia denzel 2015'
Explode($search_html)

Select * from moves where 'searchable' REGEXP 'cia' and 'searchable' REGEXP 'denzel' and 'searchable' REGEXP '2015'

this will allow my pagination to continue working with a simple single sql query

Edited August 7, 2015 by i8igmac

[Jason Cooper]

Have you considered using MySQL's full-text search functions [https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html]?

[i8igmac]

Have you considered using MySQL's full-text search functions [https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html]?

all this looks like the answer, I gave it a quick test this morning and was getting MySQL errors. I changed the MySQL engine as explained from online sources. thanks for this suggestion

[i8igmac]

Im strugling a bit with this full text search, trying to produce proper results...

.php?search=2015+comedy

i already know how many movies in the database exist with the date of 2015 and comedy...

SELECT * FROM movies WHERE MATCH(name, actors, description, download_append, genre) AGAINST ('comedy' IN BOOLEAN MODE) AND MATCH(name, actors, description, download_append, genre) AGAINST ('2015' IN BOOLEAN MODE)

above query will find exactly how many movies include comedy and 2015... i still run a single string, twice on the query... would require some explode in php and a bunch of ugly code to correct my pagination...

SELECT * FROM movies WHERE MATCH(name, actors, description, download_append, genre) AGAINST ('comedy 2015' IN BOOLEAN MODE)

above will return like 350 movie results, witch is not yet correct... almost like comedy OR 2015

SELECT * FROM movies WHERE MATCH(name, actors, description, download_append, genre) AGAINST ('"comedy 2015"' IN BOOLEAN MODE)

above ' " exact match of the string how it was written " ' will return zero results... "comedy 2015" exact string does not exist my my database...

SELECT * FROM movies WHERE IFNULL(MATCH(name, actors, description, download_append, genre) AGAINST ('"2015 comedy"' IN BOOLEAN MODE), MATCH(name, actors, description, download_append, genre) AGAINST ('2015 comedy' IN BOOLEAN MODE))

above, i tried this fancy little query... but return zero results... i figured if exact string not found, then try a more basic query

Edited August 8, 2015 by i8igmac

[cooper]

Well, about that last one, IFNULL would imply that you want non-matching records, but my guess is that each returns something at some point rather than null so nothing matches.

To be honest I think your first select is the only one that even looks like it should produce the expected result.

[i8igmac]

I figured it out... '+comedy +2014 +seth +rogen'

+ string must exist...

The ifnull, I was trying to submit a query, if the first returns null then attempt a second more lenient query

Now I'm looking at php, trying to modify the $search_html . . . Such a ugly loop to accomplish such a simple string replacement

[cooper]

Like this?

Edited August 8, 2015 by Cooper

[i8igmac]

So, ill share, my solution... only had to make a few small changes to my index.php..

My new full text search query...

SELECT * FROM movies WHERE MATCH(name, actors, description, download_append, genre) AGAINST ('+comedy +seth +rogen' IN BOOLEAN MODE)

then i modified the form request the client will supply... insert + before every string

<?php
$str="comedy seth rogen";
$counter=count(explode(" ", $str));
$sql_query="";
for ($i=0; $i<$counter; $i++) {
	$sql_query .= " +".explode(" ", $str)[$i];
	echo "<br>";
}
echo $sql_query;

?>

$sql_query => "+comedy +seth +rogen"

[cooper]

<?php
$str="comedy seth rogen";
$sql_query="";
$sql_query .= " +".str_replace(" "," +",$str)
echo $sql_query;
?>

Am I missing something?

[i8igmac]

<?php
$str="comedy seth rogen";
$sql_query="";
$sql_query .= " +".str_replace(" "," +",$str)
echo $sql_query;
?>

Am I missing something?

lol, i tried that on my first but... duh... lol omg...

" +".str_replace(" "," +",$str)

ga damn it

Edited August 9, 2015 by i8igmac

[i8igmac]

So. Now I'm looking to add a predictable/completion. search query

Some friends suggest solr... if any one has experience implementing this pleas share...

[cooper]

Solr is basically that search query you want but put inside a distributable framework with its own storage and everything.

Basically, you've just created a makeshift raft and are looking to expand on your concept of 'something that floats' by looking at a nuclear-powered Battle Cruiser complete with support fleet.

What you want to do is 2 things:

1. Make a lookup table that has an entry for each word in the various columns you want to search for paired with how often that work appears.

2. When someone types more than X characters in the input field, do a search in your lookup table for words with those X characters, ordered by their appearance count, and present the first Y hits of that in a div below the entry field, allowing you to select it resulting in populating the entry field with that selection.

An alternative to sort based on how often the word appears in your data set you could have it sort based on words that you/others searched for before (and how often, obviously), but that means it takes a while to train the search index. On the plus side, you never have to regenerate the index which, if you go for the first option, you should do every so often.

Edited August 10, 2015 by Cooper