Not sure I totally understand the question, but I think the answer is no
because your count isn't part of the database, nor is it part of the query.
You're asking MySQL to sort a field that doesn't exist, in other words.

The only way around that is to have MySQL calculate the number of occurances
as part of the actualy SQL statement, and off the top of my head I can't
think of a way to do that. There is not a function (I know of) that works
like substr_count() in SQL.

Maybe someone else has a better answer for you, but that's the best I can
do. sorry!


"Kristjan Kanarik" <[EMAIL PROTECTED]> wrote in message
> Hi,
> I've come across a problem and I'm affraid that I'm lacking the skills of
> writing proper SQL queries in order to solve this. I'm building a little
> search engine for my site (yes, from scratch! It doesn't have to be the
> best, but I think that for my personal use it will be enough) and the bit
> of code I have right now is as follows:
> <snip>
> /* I'm using MySQL 3.23.37 together with 4.0.6 on a FreeBSD box */
> $article_query = mysql_query("select article_name, article_text from
articles where article_name LIKE '%$q%'") or die (mysql_error());
> /* where $q is the search query with a space added in front */
> while ($results = mysql_fetch_array($article_query)) {
>  $art_name = $results["article_name"];
>  $article_text = $results["article_text"];
>  $q_count = substr_count($article_text, "$q");
>  echo "<b>$art_name</b><p>$article_text<p>";
> }
> </snap>
> But what I'd like to achieve is that the results would be displayed in a
> way ORDER BY $q_count DESC - the entry with most $q_count would be
> displayed first, next one second etc. I'm sure that there is a way to sort
> the array with PHP, but is it also possible to let the MySQL do the job?
> It would save some CPU power I think... if not, what would be the easiest
> way to do it with PHP?
> TIA,
> Kristjan
> P.S. Please CC me as well, I am only on the digest.

PHP Database Mailing List (
To unsubscribe, visit:

Reply via email to