Welcome Guest, Not a member yet? Register   Sign In
Help creating a search model for my new website
#11

[eluser]unmillon[/eluser]
Hello John,

Thanks for replying, i went to the site and i think it will definitely work on my site.

Let me know what you need from me to make this work for my site.

Right now i have two fields that needs to be search (artistName and title (title of the song) )

I will appreciate you helping with this.
#12

[eluser]unmillon[/eluser]
John, thanks for helping me creating the search model for my site. I love what you did because it's very accurate with the result, but the only problem is that it's super slow.

I'm going to transfer the site from a share hosting to a dedicated server and see if that improve the speed on the query.

I will include the model created by John for me, can someone take a look and tell me if i can do anything else to improve the speed. This model is really accurate as i said before.

Code:
<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class Search_model extends Model {

    function Search_model()
    {
        parent::Model();
    }
    
    function m_filter($tmp_filter, $num, $offset, $andor='AND')

          {

            // following values are returned

            $result['agood'  ]  = array();

            $result['unable']   = array();

            $result['found' ]   = array();

            

            if (empty($tmp_filter))

            {

              $awords = array('Please_try_again: and__enter_words_ separated___by__spaces');

            }else{

              $awords = explode(" ", trim($tmp_filter)); // TRIM IS ESSENTIAL !!!

            }


            // search ALL words AND if agood word add to agood ELSE add to bad

            $cnt = count($awords);

            for ($i2=0; $i2<$cnt; $i2++)

            {

              $row = $awords[$i2];

              $sql = "SELECT * FROM songs WHERE title LIKE '%" .$row ."%' OR artistName LIKE '%" .$row ."%'";

              $query = $this->db->query($sql);

              

              // Results found from a single query - IF FOUND then add to agood to use in final sql statement

              if ($query->num_rows() > 0)

              {

                $result['agood'][]  = $awords[$i2];

              }else{

                $result['unable'][] = $awords[$i2];

              } // endif

            }// for (...) ============================


            // the number of successful words found

            $cnt = count($result['agood']);

            // if we find more than one then dab word found onto the end

            $sql_agood = ''; // initally empty but words added when found

            for ($i2=0; $i2<$cnt; $i2++)

            {

              $thisword = $result['agood'][$i2];

              

              // I DON'T THINK THE OR WILL EVER BE USED BUT IT IS HERE JUST IN CASE

              if ($andor == 'OR')

              {

                $andor = ($i2>0) ? ' OR ' : '';

              }else{

                $andor = ($i2>0) ? ' AND ' : '';

              }  

              $andor .= " ( (title LIKE '%" .$thisword ."%' OR artistName LIKE '%" .$thisword ."%'))";

              

              // test num_rows()

              $query = $this->db->query("SELECT * FROM songs WHERE " .$sql_agood .$andor);


              // now test for combined words

              if (($query->num_rows() > 0) AND (!empty($row)))

              {

                $result['found'][] = $thisword;

                $sql = "SELECT * FROM songs WHERE " .$sql_agood .$andor . "LIMIT $offset, $num";

                $sql_agood .= $andor; // initally empty but words added when found

              }else{

                $result['unable'][] = $thisword;

              }

            }// end for/next for each word found

              

            // just in case the last found was FALSE

            $query          = $this->db->query($sql);

            $cnt            = $query->num_rows();

            $result  =  $query->result();  //$sql;


            return $result;

          }//endfunc m_filter


} //End Class

?&gt;

Thanks.
#13

[eluser]John_Betong[/eluser]
Hi Eddie,

The search I borrowed from my jokes site was instant on our server with your supplied access of 24 records.

As I mentioned in my email I could easily optimise the search if I had access to the site or if you supplied the complete table so that I can try it on our server.

I am back to work tomorrow and will send you a slimmer and optimised version.

Cheers from sunny Pattaya,

John_Betong
#14

[eluser]unmillon[/eluser]
Hello John,

The search is now working better on my dedicated server. The problem seems to be the share hosting account i was using.

You can test it out now at www.browseformusic.com. It works so much faster than before and accurate.

I'm having a small issue now with the pagination. For some reason it's not displaying it, can you help me with this?

Thanks.
#15

[eluser]John_Betong[/eluser]
Hi Eddie,

I have updated my test-site and you can find it here:

http://johns-jokes.com/browse4music/

As you may notice the pagination is working and I have also included a FULLTEXT search option and played about with the different parameters.


It is tremendously difficult to optimise with so little data. Is it possible to:

1. make a copy of your table to "songs_test"
2. update songs_test SET url='http://example.com';
3. export the table in a ZIP format
4. upload the ZIP file to your server and give me the link

If you do this then I will be able to search your data and your original data will still be secure.

Many thanks for your prompt PayPal payment.

Regards,

John_Betong
&nbsp;
&nbsp;
&nbsp;




Theme © iAndrew 2016 - Forum software by © MyBB