php and mysql Order By help

[cjcj01]

Hi.

I'm writing a component for joomla which searches inside files stored on the server and I'm returning a list of filenames from a search engine indexing catalog (implode from an array) into a query that looks like this:

SELECT * FROM #_document_table WHERE filename in ('filename1', 'filename2', 'filename3')

However the order of the the list of filenames is really important as it's returned from a ranked search query. i.e. filename1 must come before filename2 in the results.

I'm then using a PHP for loop to display the resulting table from MySQL. But... the problem is that MySQL is returning the data in a different order.

i.e. when MySQL returns the above statement, the result is ordered by a column called datepublised in the above table rather than by the order of the filenames in the query...
'filename1', 'filename2', 'filename3'

Can anyone give me a hint as to how to get MySQL to return results in the order of the request?

Thanks

[bascherz]

Simple:

Code: Select all

SELECT * FROM #_document_table WHERE filename in ('filename1', 'filename2', 'filename3') ORDER BY filename;

Default ordering is ascending. If you want descending order, append DESC after filename in the query.

SELECT * FROM #_document_table WHERE filename in ('filename1', 'filename2', 'filename3')
. . .
i.e. when MySQL returns the above statement, the result is ordered by a column called datepublised in the above table rather than by the order of the filenames in the query...
'filename1', 'filename2', 'filename3'

Can anyone give me a hint as to how to get MySQL to return results in the order of the request?

Thanks

[CirTap]

Hi,
@bascherz: I don't think that's what cjcj01 is intending to do.
The filenames must not be in asc/desc order but by the order provided in the IN() clause.

the order of the the list of filenames is really important as it's returned from a ranked search query

I assume it could also be "filename132, filename3, filename97" and the result must return
a) filename132
b) filename3
c) filename97
and not (ascending)
a) filename3
b) filename97
c) filename132

If no ORDER clause is provided MySQL will sort by the available indexes. I presume "datepublished" is one of those or even part of the PRIMARY index.
I don't think there's anything you can do about it but to re-order the resultset in PHP according to the "ranking" provided by the other script that gave you "filename132, filename3, filename97" in the first place. mySQL just doesn't have the information (for order) you want.
You could of course create a tempoprary "in-memory" table from the ranked data using their order as the "primary key" and the filenames, then join that with the other table you have to provide a ordering hint.

http://dev.mysql.com/doc/refman/5.0/en/ ... ngine.html

Have fun,
CirTap


(edit: added mysql doc link)

[cjcj01]

The filenames must not be in asc/desc order but by the order provided in the IN() clause

CirTap - Thanks so much for your reply.  Yes - you are right I need to do the above.

You are also right in saying that MySQL is returning the data by order of the primary key - not datepublished - they just both happen to coincide.

I think you are suggesting two methods to solve the problem

re-order the resultset in PHP according to the "ranking" provided by the other script

or a

tempoprary "in-memory" table

do you mean something like this?:
http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html

I like the sound of the second option as I'd also like to pass some other variables in i.e. search ranking, file size, abstract of search results... does this sound sensible?

Any other links / resources / suggestions on joining an array to an in-memory table?!

My heads spinning a bit trying to work out how to pass other variables from a record set into an in-memory table and then join the two tables and return the results...

I guess I need to extract not just $file = $fileRS->fields['filename']->value;  but $file->filename = $fileRS->fields['filename']->value;  and $file->filesize = $fileRS->fields['filesize']->value; and then instead of imploding the results in to an IN()clause create an in-memory table and join it with #__docman?

Chris

Code: Select all

    function getFileList()
    {
       // Get query results as a RecordSet
      $fileRS = $this->getRecordSet(); 
      
      if($fileRS == "")
        {
           $this->_filelist = "";
        }
        
        else
        {
           // Return number of records in RecordSet
           $count = $fileRS->RecordCount;
           
           if(!$count == 0) 
           {
              $fileRS->MoveFirst();
              
              
              for($i = 0; $i < $count; $i++)
            {

               
               // Create a container to hold additional object parameters
               $file =& $filelist[$i];
            
               // Get record filename from RecordSet
               $file = $fileRS->fields['filename']->value;
               
                   //Move to next record in RecordSet
                   $fileRS->MoveNext();               
                  
            }   
            
            $this->_filelist = "'" . implode("','", $filelist) . "'";
            
           }
            
      }
      
      return $this->_filelist;
      
    }
    
   // Build's query to return * from #__docman where filenames match existing records
   // i.e. are return in search query AND are not orphans
   function _buildQuery()
    {     
         $query = 'SELECT * FROM #__docman'
            . ' WHERE dmfilename in (' . $this->_filelist . ')';
      var_dump($query);
      return $query;
    }
    
    // Gets array of data from #__docman
   function getData()
   {
      if (empty($this->data))
      {
         $query = $this->_buildQuery();
         $this->_data = $this->_getList($query);
      }
      
      return $this->_data;
   
   }

[CirTap]

Hi,

yes, I was refering to the memory-head tables and no: I don't have any other links at hand

Reading the code snippet it seems like this ranking data is already available in the database.
If you have MySQL 4 runnin you could instead do a subquery and JOIN that with the docman tables. The filelist appears to be already order by ranking since you create the name list "in order of appearance". If you use that query as the subquery for the docman query in its WHER clause you should get the related documents in the very same order w/o any memory tables being involved.
see http://dev.mysql.com/doc/refman/4.1/en/subqueries.html

Maybe give this a try first before bothering with a heap table.

Have fun,
CirTap

[cjcj01]

Have fun

:D thanks!

PS the ranking data is only available in the array rather than the database, thus I think I need to create the in-memory table to then JOIN it with docman to then use the subquery...

The problem though is that when I use e.g.

Code: Select all

$db =& JFactory::getDBO();
$table = "#__docsearch_results;";
$query = 'CREATE TABLE ' . $table . ' (
        id int(11) NOT NULL auto_increment,
        rank int NOT NULL, 
        filename varchar(150) NOT NULL,
        filesize varchar(10) NOT NULL,
        PRIMARY KEY (id)
        ) ENGINE=MEMORY;';
              
$db->SetQuery($query); 

The table doesn't get created. In fact, the only way
$db =& JFactory::getDBO();
$db->SetQuery($query);
seem to work is if I use a SELECT statement.

Can I not use this method for CREATE TABLE or INSERT INTO?

e.g. this

Code: Select all

foreach ($filelist as $file) 
{
$query = "INSERT INTO " . $table . " (rank, filename, size)
   VALUES ('" . $file->rank . "', '" 
   . $file->filename . "', '" 
   . $file->size . "');";
               
   echo "<br /><br />";
   var_dump($query);
               
       $db->SetQuery($query);
}

returns a valid var_dump but the query isn't impacting the in-memory table. Or indeed if I change it to a standard #__ database table rather than an in-memory one. 

[CirTap]

Hey,

the doc for setQuery() states: "Sets the SQL query string for later execution.". You need to call $db->query() to execute it. And it's setQuery() not SetQuery(); you'll get in trouble if this runs in PHP5.

I'd actually just use $db->replacePrefix() instead of setQuery(). All you want at this point is to get rid of the prefix #__ -- but: you don't actually need it for this table; just scratch that part :)

Check with $db->getErrorMsg() to see if there was, well: an error.
Make a dump of the result query() gives you: this should be some mysql resource #id
  $res = $db->query($create_sql);
  var_dump($res);
enable debugging to see when things go wrong:
  $db->debug( 1 );

oh, and: which version of Joomla! are we talking about anyway?

CirTap

[cjcj01]

You need to call $db->query() to execute it.

:-[ whoops!  Thanks!

---- Delay while testing ------

;D ;D ;D ;D  It's working!!! CirTap Thank you!!  woohoo!  I now have a working set of results joining indexing server query results with #__docman enabling sorting by ranking etc...!!

And it's setQuery() not SetQuery(); you'll get in trouble if this runs in PHP5.

thanks - am on PHP5 and J!1.5 stable

Check with $db->getErrorMsg() to see if there was, well: an error.
Make a dump of the result query() gives you: this should be some mysql resource #id
  $res = $db->query($create_sql);
  var_dump($res);
enable debugging to see when things go wrong:
  $db->debug( 1 );

Thanks - I hadn't worked out how to do that yet...

phew.. it felt like I was almost there but couldn't work out why the table wasn't getting created! 

Really appreciate your help - Chris

[cjcj01]

Last thing...

If I'm doing this:

Code: Select all

$db =& JFactory::getDBO();

$query = 'CREATE TABLE ' . $table . ' (
                 rank int(10) NOT NULL, 
                 filename varchar(150) NOT NULL,
                 filesize varchar(10) NOT NULL,
                 INDEX USING BTREE (rank)
                 ) ENGINE=MEMORY;';

$db->setQuery($query);          
$db->query(); 

Then aren't I inserting the table into my J! database instead of in-memory due to: $db =& JFactory::getDBO();??

I am creating and then dropping the table in the same page (Model->docsearch.php) but I notice that if I don't drop it, it is sitting there in my J!database. 

Do I need to use something other than $db =& JFactory::getDBO(); to get an in-memory table?  I imagine I'm slowing the page load down writing it to disk rather than just using memory?

If I'm doing the above I guess I woudl be better to createthe table on install of the component in J! and then just drop all rows after a search rather than creating and dropping a whole table? would this make things quicker?

[CirTap]

Hi,

getDBO() is fine and holds the internal mysql resource handle which is needed, but: the SQL runs thru replacePrefix() (via setQuery()) which might potentionally remove "ENGINE=MEMORY;" or anything behind the closing ")" to prevent sql injection ??- I'm not sure.
Look at the final sql to see if ENGINE got lost. That'd result in the default engine being used == physical storage.
var_dump($db->_sql);

You may of course use your own table for this. There's nothing bad in having a component withits own table
Don't worry too much about performance, MySQL is wicked fast if your indices are correct, and J! does much more database and file I/O tasks all by itself than your component could possible exceed
So yes, if it's an option, then go for a "real" table (I thought you wanted to avoid this hence my suggestion to use the MEMORY table.)

You don't necessarily need to purge (truncate) the ranking-table on every result - I believe that'd be indeed some ugly overhead; just give each resultset a unique and random "request token", save that in the user session and make it part of the JOIN for docman. Add a timestamp to the ranking table and do your garbage collection on occasion (delete entries older than xx hours/days on random page requests).
Even if the ranking table contains some thousand records, it wouldn't slow things down; provided your indices are set correctly :-) ... and you might be able to "archive" or "bookmark" (or "cache") search results.

Have fun,

CirTap

[cjcj01]

CirTap - I just can't say how useful your advice has been - Thank you for all your time and advice. 

Once I've added the admin interface I'll let you know if you're interested to see it working...  the component provides full text indexing of docs in the docman folder (pdf's and office docs).  Unfortunately it only works with Microsoft indexing server at the moment so will only work on XP or Server 2003+ but I guess it wouldn't take too much to adapt it to work with a search engine on unix... maybe that's the next challenge!

I'll also take in your advice from the above into the next version - you've got some great ideas...

again. thanks for your help - am off to the next MVC tutorial on dev.joomla to learn how to do the admin interface!