PHP inside of a SQL query seems to be failing

[gsr4565]

I found a script from http://www.roscripts.com/PHP_search_engine-119.html, I have succesfully set this up almost completely. I have a few final issues that I cannot seem to fix. I think I know what they are but I can't figure out how to resolve them. The error I recieve is "mysql_fetch_array(): supplied argument is not a valid MySQL result resource". The code is as follows below:

$sql = "SELECT DISTINCT COUNT(*) As occurrences, title, description, fulldescr FROM video_search WHERE (";

while(list([COLOR="red"]$key[/COLOR],[COLOR="red"]$val[/COLOR])=each($split_stemmed)){   
          if([COLOR="red"]$val[/COLOR]<>" " and strlen([COLOR="red"]$val[/COLOR]) > 0){   
          $sql .= "(title LIKE '%'.[COLOR="red"]$val[/COLOR].'%' OR subtitle LIKE '%'.[COLOR="red"]$val[/COLOR].'%' OR content LIKE '%'.[COLOR="red"]$val[/COLOR].'%') OR";   
          }   
}   
          $sql=substr($sql,0,(strLen($sql)-3));//this will eat the last OR   
          $sql .= "[COLOR="Red"])[/COLOR] GROUP BY id ORDER BY occurrences DESC";  

Now, I see three issues:

1. The ( and ) in red I believe are misplaced but I have tried deleting and moving them outside of the quote and it doesn't work. IF I move it outside of the quote PHP matches them up however I then get a PHP error.

2. THe $key in red is only found right there and no where else in the code that I got. It seems that it would have to be defined somewhere, however I cannot figure out what it should be defined as.

3. The $val in red is also only found in this section of code and one would think it would need to be defined. I believed it should have been replaced with what is the variable for the form used to perform the search or in my case $find. However replacing it with that doesn't help. I still recieve the exact same error.

Please, any help would be wonderful or point me to a better search engine code that can be used to accomplish seperating and searching multiple words, and dropping the plurals from words and so on.

Thank you in advance,
Ryan

[NogDog]

$sql=substr($sql,0,(strLen($sql)-3));//this will eat the last OR

Because you use "=" instead of ".=", the above will replace the current contents of $sql with the result of the substr(), which I suspect is not what you want?

[gsr4565]

That definitly changed the query I am now getting a diffirent error. I will star researching it now. I am also posting it here.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT DISTINCT * FROM video_search WHE ORDER BY title DESC' at line 1

Thanks for responding so fast.

[stijnm]

'SELECT DISTINCT * FROM video_search WHE ORDER BY title DESC'

Don't think you can use 'distinct' near ''. Try 'select distinct some_field' or 'select '.

[gsr4565]

Wow you respond really fast I appreciate the fast response alot. I removed the distinct although I was sure you could have that.

However, I still get the same error as before. No change what so ever

[NogDog]

Try the following pattern to help find out what the problem is when you actually axecute the query:

$result = mysql_query($sql);
if($result == false)
{
   user_error('Query failed [$sql] ' . mysql_error());
}

This way you will see the query exactly as it was sent to MySQL, hopefully giving you a clue as to where the problem is.

[gsr4565]

Where should I place that? I entered it in directly below the query and it doesn't give me anything more than what we see without it in the error.

You know at this rate I am going to owe you some beer.

As you can tell I am not a newbie but far from being proficient.

Would it help if I posted the entire php script?

[NogDog]

Wherever you actually execute the query is where you want to check the return value, similar to what I did in my previous post. If the result is false, then you can output some debug info as I did in the IF block. I was assuming that you are using MySQL for your DBMS and the mysql_query() function to execute the query. If any of those assumptions are incorrect, then we'll need more info and perhaps some code samples.

[bpat1434]

Notice that WHE is not a MySQL keyword (or SQL keyword for that matter). You probably wanted "WHERE"; and even then you're missing the WHERE clause....

Try this:

<?php
$sql = "SELECT DISTINCT `title`, COUNT(*) AS `occurances`, `title`, `description`, `fulldescr`
FROM `video_search`
WHERE ";

// foreach is a nicer loop sometimes ;)
foreach($split_stemmed as $key=>$val)
{
    if(!empty($val) && strlen($val) > 0)
    {
         $sql .= "
    (`title` LIKE '%".$val."%' OR `subtitle` LIKE '%".$val."%' OR `content` LIKE '%".$val."%') OR";
    }
}

// Remove the last " OR"..
$sql = substr($sql, 0, strlen($sql)-3);

$sql .= "
GROUP BY `id`
ORDER BY `occurances` DESC";

echo 'Query to be Run: ' . $sql;

Show us the full query that is to be run.... a quick test shows the proper formatting... although I think the DISTINCT may limit the COUNT() to be 1 for everything....