Databases
StackOverflow2013
Postsdbo
POTrying to sort results from a multi-column fulltext mysql search
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTrying to sort results from a multi-column fulltext mysql search
    primarykey
    data
    text
    <p>I'm working with a table that has 10 columns. The first is a broad category, and the next 9 are concepts that become more specific as you go up. All the rows have a category and at least number 1 attribute, but might be blank from there down. </p> <p>I am trying to write a query that orders keyword fulltext search results first grouped by number of results within a category and next by the highest attributes possible. So a cat with 30 instances would come before one with 10. But within that category all results with a 9th attribute would be at the top before a result whose 9th column is empty.</p> <p>Below are the two primary pieces of code to show where I am at with this:</p> <pre> $sql = "SELECT *, MATCH (SubCat09Name) AGAINST ('".$keyword."') AS res9, MATCH (SubCat08Name) AGAINST ('".$keyword."') AS res8, MATCH (SubCat07Name) AGAINST ('".$keyword."') AS res7, MATCH (SubCat06Name) AGAINST ('".$keyword."') AS res6, MATCH (SubCat05Name) AGAINST ('".$keyword."') AS res5, MATCH (SubCat04Name) AGAINST ('".$keyword."') AS res4, MATCH (SubCat03Name) AGAINST ('".$keyword."') AS res3, MATCH (SubCat02Name) AGAINST ('".$keyword."') AS res2, MATCH (SubCat01Name) AGAINST ('".$keyword."') AS res1, MATCH (Category) AGAINST ('".$keyword."') AS res15 FROM stufftaxonomy WHERE MATCH (SubCat09Name, SubCat08Name, SubCat07Name, SubCat06Name, SubCat05Name, SubCat04Name, SubCat03Name, SubCat02Name, SubCat01Name, Category) AGAINST ('".$keyword." IN BOOLEAN MODE') ORDER BY res15 DESC, res9 DESC, res8 DESC, res7 DESC, res6 DESC, res5 DESC, res4 DESC, id DESC LIMIT 25000"; </pre> <p>And also:</p> <pre> foreach($mvres as $res){ if(strlen($res->Category) > 1 || strlen($res->SubCat01Name) > 1 || strlen($res->SubCat02Name) > 1 || strlen($res->SubCat03Name) > 1 || strlen($res->SubCat04Name) > 1 || strlen($res->SubCat05Name) > 1 || strlen($res->SubCat06Name) > 1 || strlen($res->SubCat07Name) > 1 || strlen($res->SubCat08Name) > 1 || strlen($res->SubCat09Name) > 1){ if($catHead != $res->Category){$response .= '';} if($catHead != $res->Category){$response .= 'id.'" class="categoryHead">'.$res->Category.' ';} $response .= ''; $response .= 'id.'" class="taxID">id: id.'">'.$res->id.''; if(strlen($res->SubCat01Name) > 0){$response .= ''.$res->Category.'';} if(strlen($res->SubCat09Name) > 0){$response .= ' 09:'.$res->SubCat09Name.'';} if(strlen($res->SubCat08Name) > 0){$response .= ' 08:'.$res->SubCat08Name.'';} if(strlen($res->SubCat07Name) > 0){$response .= ' 07:'.$res->SubCat07Name.'';} if(strlen($res->SubCat06Name) > 0){$response .= ' 06:'.$res->SubCat06Name.'';} if(strlen($res->SubCat05Name) > 0){$response .= ' 05:'.$res->SubCat05Name.'';} if(strlen($res->SubCat04Name) > 0){$response .= ' 04:'.$res->SubCat04Name.'';} if(strlen($res->SubCat03Name) > 0){$response .= ' 03:'.$res->SubCat03Name.'';} if(strlen($res->SubCat02Name) > 0){$response .= ' 02:'.$res->SubCat02Name.'';} if(strlen($res->SubCat01Name) > 0){$response .= ' 01:'.$res->SubCat01Name.'';} $response .= ''; $catHead = $res->Category; if($catHead != $mvres[$i+1]->Category){$response .= '';} $i++; } } </pre> <p>I know this database design is too rudimentary. So that's also part of my question; is it worth it to break off a category table, and index the original? It's 84,000 rows and this is how I got it.</p> <p>Please any expert insight is greatly appreciated!</p> <p>Additionally, I have found that adding <code>+</code> or <code>-</code> to my <code>$keyword</code> is not having the desired boolean effect. I'd love for that to work too...</p> <hr> <p>Update:</p> <p>Well, I'm feeling pretty satisfied with the following:</p> <pre> SELECT * FROM stufftaxonomy WHERE MATCH (Category) AGAINST ('".$keyword."' IN BOOLEAN MODE) || MATCH (SubCat01Name) AGAINST ('".$keyword."' IN BOOLEAN MODE) || MATCH (SubCat02Name) AGAINST ('".$keyword."' IN BOOLEAN MODE) || MATCH (SubCat03Name) AGAINST ('".$keyword."' IN BOOLEAN MODE) || MATCH (SubCat04Name) AGAINST ('".$keyword."' IN BOOLEAN MODE) || MATCH (SubCat05Name) AGAINST ('".$keyword."' IN BOOLEAN MODE) || MATCH (SubCat06Name) AGAINST ('".$keyword."' IN BOOLEAN MODE) || MATCH (SubCat07Name) AGAINST ('".$keyword."' IN BOOLEAN MODE) || MATCH (SubCat08Name) AGAINST ('".$keyword."' IN BOOLEAN MODE) || MATCH (SubCat09Name) AGAINST ('".$keyword."' IN BOOLEAN MODE) ORDER BY Category ASC, length(SubCat09Name) + length(SubCat08Name) + length(SubCat07Name) + length(SubCat06Name) + length(SubCat05Name) + length(SubCat04Name) + length(SubCat03Name) + length(SubCat02Name) + length(SubCat01Name) DESC, SubCat09Name ASC, SubCat08Name ASC, SubCat07Name ASC, SubCat06Name ASC, SubCat05Name ASC, SubCat04Name ASC, SubCat03Name ASC, SubCat02Name ASC, SubCat01Name ASC LIMIT 25000 </pre> <p>I'd be even happier if the single level attributes were alphabetized.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USGaffi
    1. UShzane
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POTrying to sort results from a multi-column fulltext mysql search
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COI also messed around with ORDER BY length(res9) DESC... and that kind of worked, but was still not quite right.
      singulars
      1. POTrying to sort results from a multi-column fulltext mysql search
      1. UShzane
    2. COI would try to come up with a score system and create a live view in MySQL. You could then easily sort by that score which should be in the view.
      singulars
      1. POTrying to sort results from a multi-column fulltext mysql search
      1. USPaul
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload