using like and order by

slimjim

Can anyone tell me what I am doing wrong, What I want to do is have the query show results for a word, not just in the front, or in back, but any where in this field, Example :
query food, now if i have words like food club, boxed food, or even more food clubs...
I want it to show all results that have the word food in it.
But I also want to order it by something as well.
Here is what I have:

	$query="SELECT keyword, stotal from searchword WHERE keyword LIKE '$keys%' ORDER BY stotal DESC LIMIT 10";

Not working the way i need it too....

bretticus

just surround the $key variable with %% to get front, back & anywhere in between:

$query="SELECT keyword, stotal from searchword WHERE keyword LIKE '%$keys%' ORDER BY stotal DESC LIMIT 10";

...I also want to order it by something as well...

Not working the way i need it too....

Umm...could you be more specific here?

slimjim

well i am keeping tracked of how many times that word is searched so the number adds up by 1 each time..

so i want it so that highest number in filed stotal is first and lowest on bottom, and only show 10

slimjim

I was not thinking right, i want the query to work so that even if a word like " tools " would show up if I searched for " tool "

So even if a word is part of a word i need it to show up

Of course the order by and limit as well

bretticus

okay, well, if you are incrementing stotal then the order by desc should be doing the trick just fine. As for the tool->tools example, the %% notation will still work. You won't be able to do tools->tool of course.

tekky

So long as you leave no space between the %'s and the variable you're good to go, otherwise it will do what you originally thought you said which is only match the whole words only

bretticus

I wasn't very clear on that LIKE '%[phrase]%' will only match that phrase. This is not what you are trying to do from earlier examples you stated, so I'll write some sample code here and I'll use it in my own script (I just happen to be working on something similiar.)

$search = "This is a phrase";
//Remove improper characters.
$searchString = preg_replace("/[^a-zA-Z0-9\s]/", "", trim($search));
//Break search phrase into array.
$searchArray = explode(" ", $searchString);
$query="SELECT keyword, stotal from searchword WHERE (keyword LIKE '%";
$query.= implode("%'  OR keyword LIKE '%", $searchArray);
$query.="%') ORDER BY stotal DESC LIMIT 10";
echo $query;

slimjim

I think that is what I am trying to do, but your code is printing the select statment on page instead of the results of the query...

Drakla

Sigh...

slimjim

what the heck does that mean :
Sigh...

Geeze when a person is learning and goes thru just about everything, and then a person submits a comment like Sigh....

That's wrong...

Drakla

Well I presume Bretticus was echoing the query for demonstration purposes, assuming that after 300 posts you know how to use mysql_query by now.

slimjim

Well you may think so, but it seems so much to learn and everyone codes so differently.. I do no have a problem with echo a query for most ways, but for some reason this is not working the way I had hoped, I was able to get it to echo the search word, but not get other results.

It's not like I am not trying..

slimjim

Ok this example only shows the word i used for the search and not all words with the keys in them :

$search = "$keys"; 
//Remove improper characters. 
$searchString = preg_replace("/[^a-zA-Z0-9s]/", "", trim($search)); 
//Break search phrase into array. 
$searchArray = explode(" ", $searchString); 
$query="SELECT keyword, stotal from searchword WHERE (keyword LIKE '"; 
$query.= implode("'  OR keyword LIKE '", $searchArray); 
$query.="') ORDER BY stotal DESC LIMIT 10"; 
$result = mysql_query($query) or die("Query failed");	
while ($myrow = mysql_fetch_array($result)) {
		$stotal=$myrow["stotal"];
		$keyword=$myrow["keyword"];
}
echo $keyword;

I did a search for tool, and I added many keywords in database that had tool in the word, but only result i got was the word i used, which in this case was " tool "

SO any idea what I am doing wrong...

bretticus

Ah, I wish somebody at phpbuilder could make sure that backslashes in PHP code did not get wiped out.

I'm suspecting that in the line...

$searchString = preg_replace("/[^a-zA-Z0-9s]/", "", trim($search));

You just need to escape that "s". The pattern is meant to say, "everything that is NOT 'a' thru 'z', 0 thru 9, or a space." Then I replace that with an empty string to filter out characters that don't belong. You may find that you need to include more chars to exclude as you delve in deeper.

Cheers!

bretticus

Do this instead by the way:

while ($myrow = mysql_fetch_array($result)) {
        echo $myrow["stotal"];
        echo $myrow["keyword"];
}

You were rewriting $keyword with every row iteration.

bretticus

Oh and one more...

Well I presume Bretticus was echoing the query for demonstration purposes

I am just giving you code to test. The echo was meant for you to print out the query and check to see it's in the proper format. I expect you to be able to take that query and run it through mysql_query() which is what you did. Just for future reference, my code is untested and often for debugging only.

tekky

Originally posted by bretticus
Do this instead by the way:
while ($myrow = mysql_fetch_array($result)) {
        echo $myrow["stotal"];
        echo $myrow["keyword"];
}
You were rewriting $keyword with every row iteration. [/B]

if you are going to be using the column names you may as well use [man]mysql_fetch_assoc/man

slimjim

Well I changed it, but, I still only get the one result.
I get the same word I used for search and the stotal for that one word.

I Need it to find all keywords that have the search word in it...
So, if i use " tool " for a search word...
It should return many words like tools, subtools, tool box, including stool...

Is this impossible ?

When I do this :

$query="SELECT keyword, stotal from searchword WHERE keyword LIKE '%$keys%' ORDER BY stotal DESC LIMIT 10";

This brings up all the words that have the search word in a string

But like I said I need it to find the search word inside a single word also, like tools, the s at end of tools isn't being included cause of it is like a different word, but still has tool in it..

Anyone got some ideas...

This looks like it should work, but doesn't :

$search = "$keys"; 
//Remove improper characters. 
$searchString = preg_replace("/[^a-zA-Z0-9s]/", "", trim($search)); 
//Break search phrase into array. 
$searchArray = explode(" ", $searchString); 
$query="SELECT keyword, stotal from searchword WHERE (keyword LIKE '"; 
$query.= implode("'  OR keyword LIKE '", $searchArray); 
$query.="') ORDER BY stotal DESC LIMIT 10"; 
$result = mysql_query($query) or die("Query failed");	
while ($myrow = mysql_fetch_assoc($result)) { 
        echo $myrow["stotal"]; 
        echo $myrow["keyword"]; 
}

bretticus

Wow, you were fast on my code before I got a chance to edit it.

Notice that the last version of the code you had (sorry, I didn't notice it and I thought you'd probably see the problem since we chatted about it for so long) there are no % monikers.

Here is a corrected version (again escape that "s" in case you haven't already. I cannot tell because this forum strips off the backslashes:

$search = "$keys"; 
//Remove improper characters.
$searchString = preg_replace("/[^a-zA-Z0-9s]/", "", trim($search)); 
//Break search phrase into array.
$searchArray = explode(" ", $searchString); 
$query="SELECT keyword, stotal from searchword WHERE (keyword LIKE '%"; 
$query.= implode("%'  OR keyword LIKE '%", $searchArray); 
$query.="%') ORDER BY stotal DESC LIMIT 10"; 
$result = mysql_query($query) or die("Query failed");    

while ($myrow = mysql_fetch_assoc($result)) { 
        echo $myrow["stotal"]; 
        echo $myrow["keyword"]; 
}

If you still have trouble, please echo out and post the result of $query and shown me some data from your table while you're at it.

slimjim

Not actually sure on how to escape the S

Sorry..

Now will try that code you just pasted...