Advanced PHP/MySQL Question

[WendyLady]

Hi, everyone -- I hope that as I get more proficient with PHP I can be more of a help & not just asking --

I have a 2-part problem, and I have solved the 1st part:

In order to fully automate a site for a client, I have a set of customer categories that are changeable in an interface by the company owner. Today I *finally* [img src=\"style_emoticons/[#EMO_DIR#]/laugh.gif\" style=\"vertical-align:middle\" emoid=\":laugh:\" border=\"0\" alt=\"laugh.gif\" /] figured out how to populate a form (group of checkboxes) by calling those categories out of the database. Whew!

Now, I am setting up a search where a user would be able to search the database for entries matching those categories. I have set up a boolean search using MATCH . . . AGAINST, but I keep getting the same error:

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]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 'desc) AGAINST ('Array' IN BOOLEAN MODE) AS score FROM table_customer ' at line 2[/quote]

Below is my full code, starting with calling the categories to populate the form, and followed by the 2nd query.

I really appreciate any help you can offer! I've been pulling my hair out for two days. I'm not sure if I'm making a silly mistake somewhere that I can't catch because I'm so cross-eyed, or whether it doesn't like gathering the array to submit for the MATCH . . . AGAINST code.

Wendy

The searchForm function, because I'm using it several times throughout the site (the fact that it is a function is pulled from a tutorial on here because the switch statments were making me crazy) -- it returns the checkbox form like a charm:
[code]
    function searchForm2()
{
    $searchwords = (isset($_GET['cat']) ? htmlspecialchars(stripslashes($_REQUEST['cat'])) : '');

    echo '<form method="get" action="'.$_SERVER['PHP_SELF'].'">';
    echo '<input type="hidden" name="cmd" value="search1" />';

    include "includes/functions/mysql_connect.php";
            $category = $_GET['category'];
            $query = "SELECT category FROM table_customer";
            $result = mysql_query($query);
            while (list($category) = mysql_fetch_row($result))
            {
                echo "<input type=\"checkbox\" name=\"cat[]\" value=\"$category\" />$category
                      ";
            }
    echo '<input type="submit" name="submit1" value="Search Services" />';
    echo '</form>';
}
[/code]

The actual query where the error is appearing, minus the switch:
[code]
echo '<h2>Search Services: Results</h2><br />';
    searchForm2();
    $searchstring = mysql_escape_string($_GET['cat']);

    $sql = "SELECT first, last,
           MATCH(desc)
           AGAINST ('$searchstring' IN BOOLEAN MODE) AS score FROM table_customer
           WHERE MATCH(desc)
           AGAINST ('$searchstring' IN BOOLEAN MODE) ORDER BY score DESC";

  $result = mysql_query($sql) or die (mysql_error());

    while($row = mysql_fetch_object($result))
    {
      echo '<strong>First: '.stripslashes(htmlspecialchars($row->first)).'</strong><br />';
      echo 'Score:'. number_format($row->score, 1);
      echo '<hr size="1" />';
    }
[/code]

Thanks again!

[hvle]

SELECT first, last, MATCH(desc) AGAINST ('$searchstring' IN BOOLEAN MODE) AS score FROM table_customer [b]WHERE MATCH(desc) AGAINST ('$searchstring' IN BOOLEAN MODE)[/b] ORDER BY score DESC";

I am not an expert, but I think you need to delete the where clause, the bold part.
Good luck!

[jeremywesselman]

You can find more about searching with mysql [a href=\"http://techrepublic.com.com/5100-9592-6050484.html#\" target=\"_blank\"]here[/a].

You will need to add an addition (+) sign to the beginning of each word and then put them all in a string.

[code]<?php
$words; //your array of words

//add a plus sign to tell mysql to search for every word
for($i = 0; $i <= count($words); $i++)
{
     $words[$i] = "+" . $words[$i];
}

//join all the words to a single string
$searchstring = $words[0];
for($i = 1; $i <= count($words); $i++)
{
     $searchstring = $searchstring . " $words[$i]";
}
?>[/code]

And your query would look similar to this:
[code]<?php
$query = "SELECT first, last FROM table_customer WHERE MATCH (desc) AGAINST (\"$searchstring\" IN BOOLEAN MODE) ORDER BY MATCH (desc) AGAINST (\"$searchstring\" IN BOOLEAN MODE) DESC";
?>[/code]

I'm no expert either, but this is how it is set up in a book I have.

[!--coloro:#990000--][span style=\"color:#990000\"][!--/coloro--]Jeremy[!--colorc--][/span][!--/colorc--]

[448191]

[!--quoteo(post=373435:date=May 13 2006, 01:14 AM:name=jeremywesselman)--][div class=\'quotetop\']QUOTE(jeremywesselman @ May 13 2006, 01:14 AM) [snapback]373435[/snapback][/div][div class=\'quotemain\'][!--quotec--]
You can find more about searching with mysql [a href=\"http://techrepublic.com.com/5100-9592-6050484.html#\" target=\"_blank\"]here[/a].

You will need to add an addition (+) sign to the beginning of each word and then put them all in a string.

[code]<?php
$words; //your array of words

//add a plus sign to tell mysql to search for every word
for($i = 0; $i <= count($words); $i++)
{
     $words[$i] = "+" . $words[$i];
}

//join all the words to a single string
$searchstring = $words[0];
for($i = 1; $i <= count($words); $i++)
{
     $searchstring = $searchstring . " $words[$i]";
}
?>[/code][/quote]

This can be done a lot easier:
[code]
<?php
$searchstr = implode('+',$words);
?>
[/code]

[a href=\"http://www.devarticles.com/c/a/MySQL/Getting-Started-With-MySQLs-Full-Text-Search-Capabilities/\" target=\"_blank\"]Here's[/a] a good article that helped me understand and use MySQL's Fulltext searching...

[shoz]

[quote]
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]
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 'desc) AGAINST ('Array' IN BOOLEAN MODE) AS score FROM table_customer ' at line 2
[/quote]
[/quote]
The syntax error is most likely referring to the 'desc' in the query, as 'desc' is a [a href=\"http://dev.mysql.com/doc/refman/4.1/en/reserved-words.html\" target=\"_blank\"]reserved word[/a]. Change the column name or surround the name with backticks "``" (The character below the tilde ~. Not single quotes).

As for the other parts of the script. I don't think you want to use a checkbox the way you're using it here. Do a
[code]
vardump($_GET['cat']);
//print_r($_GET['cat']) may be easier to read.
[/code]
to see what a/the variable holds when the form is submitted.

If each entry in the table you're searching has a reference to a "category" then there should be no need for a fulltext search, but rather a simple lookup based on the category "key"
eg:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * [color=green]FROM[/color] [color=orange]table[/color] [color=green]WHERE[/color] cat_id [color=orange]=[/color] $user_chosen_cat_id
[!--sql2--][/div][!--sql3--]
I don't know how you've designed the database so that's only an example/guess of what you should do.

[WendyLady]

What awesome help! Thank you so much. I will go through these one by one & post results for future reference.

Thank you!

Wendy

[WendyLady]

IT IS WORKING!!! Woo-hooo!!!! I settled down long enough from doing a little jig to post results for posterity. Here are my responses to all of you who answered my plea for help, as well as the working code, since I have not seen this anywhere else on the web (populating a form & the submitting it).

jeremy & 448191: it occurred to me that if someone checked ALL the boxes, they would print out the entire database. NOT good for the purposes here. I changed it to a radio instead, which simplified having to implode or any of that. However, on the admin side, I am going to need to use that implode function for a userface to help insert categories using checkboxes, so I will be using it & appreciate you mentioning it (I haven't used it before).

shoz: I do actually need a full-text search, because (this is complicated) -- I have one table with my possible categories, and another table with customers, who can fall into more than one category. On the customer table, the description field contains the categories they belong to. This might not be the most efficient way to do it, but it seemed simplest for my meager programming skills. BUT-- the vardump/print_r functions were also new to me, and they are coming in handy! I used a variation on this for my search results, so that it prints out "Results for X" intead of just "Search Results". I also changed the 'desc' field & that helped.

Thanks so much, everyone! I am just so excited, because with these first two major problems figured out, doing the rest of the site will be a breeze. I was getting worried that it was way beyond me.

What worked in the code:
[code]
    function searchForm2()
{
    $searchwords = (isset($_GET['category']) ? htmlspecialchars(stripslashes($_REQUEST['category'])) : '');

    echo '<p><form method="GET" action="'.$_SERVER['PHP_SELF'].'">';
    echo '<input type="hidden" name="cmd" value="search2" />';

include ("includes/functions/mysql_connect.php");

            $category = $_GET['category'];
            $query = "SELECT category FROM mvci_category";
            $result = mysql_query($query);
            while (list($category) = mysql_fetch_row($result))
            {
                echo "<input type=\"radio\" name=\"category\" value=\"$category\" />$category
                      <br />";
            }
    echo '<input type="submit" value="Search Services" />';
    echo '</form></p>';

........

    echo '<h2>Search Services: Results for <i>'.($_GET['category']).'</i></h2><br />';
    searchForm2();

//   $searchstring = mysql_escape_string($_GET['$category']);

    $sql = "SELECT first, last,
           MATCH(description)
           AGAINST ('$category' IN BOOLEAN MODE) AS score FROM mvci_customer
           WHERE MATCH(description)
           AGAINST ('$category' IN BOOLEAN MODE) ORDER BY score DESC";

  $result = mysql_query($sql) or die (mysql_error());

    while($row = mysql_fetch_object($result))
    {
      echo '<strong>First: '.stripslashes(htmlspecialchars($row->first)).'</strong><br />';
      echo 'Score:'. number_format($row->score, 1);
      echo '<hr size="1" />';
    }
[/code]
What acutally made it work was ignoring the variable $searchstring & using straight $category in my query (second part). The first form was returning the categories, but it wasn't making it through the variable. If this opens me up to a security risk, please let me know. Thanks again so much!
Wendy

[shoz]

[quote]
What acutally made it work was ignoring the variable $searchstring & using straight $category in my query (second part). The first form was returning the categories, but it wasn't making it through the variable. If this opens me up to a security risk, please let me know
[/quote]
All user submitted data should be escaped before being used in your queries and if you know the format the data should take or exactly what is allowed, you can make the process more restrictive.

Currently you have the following
[code]

$category = $_GET['category'];
            $query = "SELECT category FROM mvci_category";
            $result = mysql_query($query);
            while (list($category) = mysql_fetch_row($result))
            {
                echo "<input type=\"radio\" name=\"category\" value=\"$category\" />$category
                      <br />";
            }
[/code]
$category is used in the second query and you haven't escaped it using a function such as [a href=\"http://www.php.net/mysql_real_escape_string\" target=\"_blank\"]mysql_real_escape_string()[/a]. You should also test for the existence of data in the first place with something such as [a href=\"http://www.php.net/isset\" target=\"_blank\"]isset()[/a].

As shown from the snippet above you retrieve the categories to create the form. You can use the list to test the user submitted category against.

eg:
[code]
$category = $_GET['category'];
$query = "SELECT category FROM mvci_category";
$result = mysql_query($query);
//$db_cats will hold all the categories present in the database.
$db_cats = array();
while (list($category) = mysql_fetch_row($result))
{
    //The array is populated here
    $db_cats[] = $category;
    echo "<input type=\"radio\" name=\"category\" value=\"$category\" />$category <br />";
}
if (!in_array($category, $db_cats))
{
    //If we reach this point we know the user
    //submitted data isn't valid, because the category
    //sent is not in the list of categories from the database
    //handle it how you choose

}
else
{
   //category is valid and can be used.
   //It would still be good practice to escape it.
}
[/code]
You can read the following guide as a starting point to learn about PHP security issues. [a href=\"http://phpsec.org/projects/guide/\" target=\"_blank\"]Php Security Guide[/a]

EDIT:
I just noticed that the variable $category which is given a value here
[code]
$category = $_GET['category'];
[/code]
is being overriden in the while loop
[code]
while (list($category) = mysql_fetch_row($result))
[/code]
You should change the variable name. Preferably to one that distguishes it as user input.