Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How to read the value from URL and compare with index that i have created in MYSQL table. I have a form where provide list of selection using drop down list and i pass the selected value to the next page URL. Now i want to compare this value with my index. how to do this? please help me. i have something like this at the first form.

 <script language="javascript">
    function showMe(str)
    {
    document.getElementById('myDiv').innerHTML = 'You have selected :'+str;
    }   
    </script>
    <select id="ccrseid" name="selectvalue" onchange="showMe(this.value);">
  <option value="">Select</option>
  <option value="1">Computer Science</option>
  <option value="2">Engineering</option>
  <option value="3">Business</option>
  <option value="4">Education and Training</option>
  </select>
  <input type="submit" value="Submit">
<div id="myDiv"></div>

then im trying to get the selected value and check with index that i have created in another form

$id=(int)$_GET['selectvalue'];;

$ret1="SELECT * FROM $db_tb_name WHERE MATCH(Id_index=$id) AGAINST('%$str%' IN BOOLEAN MODE)";
$result1 = MySQL_query($ret1);

but when i run i get this error

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 '==2) AGAINST('%%' IN BOOLEAN MODE)' at line 1

how to solve this?

share|improve this question
    
Hi, can you please give some more detail on how you are trying to compare. What is the actual use case? –  Raf Oct 22 '12 at 22:04
    
hi Raf. Thanks for the feedback. Actually i want to try to perform searching with index column. So what i have is get the category from the one form by selecting the option value and pass this to url. Then i'm trying to get this value and look into my MYSQL table which have 4 categories where all the fields are index. For a example if i select option 2 then i should refer to column number 2 to look for stored keyword. –  vaani Oct 23 '12 at 0:18
add comment

closed as off topic by Martin Smith, Paul White, Derek Downey, RolandoMySQLDBA, Leigh Riffel Oct 25 '12 at 18:56

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

I think you need to revise the design of your db tables. The way you have described where using fixed categories as fields of a table is not so wise, what if you needed to add more categories?

So assuming your problem is to search for keywords of those items belonging to a selected category, here is the way to do it.

We can use two tables in the Database.

Table 1. Categories

==============================
|  ID  | Category            |
------------------------------
|  1   | Computer Science    |
|  2   | Engineering         |

Table 2. Items

=============================================
 ID  |  Category_ID  | Keywords             |
=============================================
  1  |      1        | Keyword A            |
---------------------------------------------
  2  |      2        | Keyword B            |
---------------------------------------------
  3  |      1        | Keyword C            |
---------------------------------------------

Now you can do try the following query in the page that gets the selected id as url parameter.

// you can use $id = $_GET['selectvalue'];
// but do remember to escape the parameter value before passing to the query to avoid any security issues
// therefore use the following

$id = mysql_real_escape_string($_GET['selectvalue']);
$query = "SELECT * FROM ITEMS WHERE category_id = $id";

//Do note that use of mysql_query() is discouraged. Instead, the MySQLi or PDO_MySQL extension should be used. 
//However to give you a simple answer

$result_query = mysql_query($query); 
$result_data = mysql_fetch_assoc($result_query);

If this is not what you are trying to do and if you really want to search for a particular field with keyword values, than here is another option

$selectColumn = $_GET['selectvalue'];
$keywords = "2,3,4";

$query = "SELECT * FROM items WHERE $selectColumn IN ($keywords)";
$result_query = mysql_query($query);

UPDATED

If you want to do a fulltext search as what you are trying to do according to the details in your question, I want you to check the following.

Make sure the index of type is FULLTEXT. Full-text indexes can be used only with MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns.

Inside the MATCH() you need to put the field names comma separated and AGAINST() can only have a string and optional modifier indicating what type of search you want to do. SO in your case, it should be something like this:

SELECT * FROM TableNameHere WHERE MATCH (fieldA, fieldB) AGAINST ('"$str"' IN BOOLEAN MODE)

try doing a test by using a full query with actual values instead of the $variables in the query.

For more information, have a look at these two links

http://dev.mysql.com/doc/refman/5.5/en//fulltext-search.html

http://dev.mysql.com/doc/refman/5.5/en//fulltext-boolean.html

share|improve this answer
    
Hi Raf thanks for your suggestion and ideas. But im still getting the same syntax " version for the right syntax to use near '==2) AGAINST('%%' IN BOOLEAN MODE)' at line 1" –  vaani Oct 24 '12 at 11:26
    
@vaani thats because you are not using the query correctly. Did you try the method I have described above? –  Raf Oct 24 '12 at 12:55
    
see my updated answer with some more things tobe noted –  Raf Oct 24 '12 at 13:16
    
Hi Raf. I got where is my mistake. $ret1="SELECT * FROM $db_tb_name WHERE MATCH ('Seq_in_index=$selectvalueid') AND AGAINST ('%$str%' IN BOOLEAN MODE)"; this is completely not working because i try to to put 'Seq_in_index=$selectvalueid' instead of column name after the match. But how to write if i want to compare the select id and categryoid( from the table)/ seq_in_index (which is not the column in table) but the details of table when i execute show index and check against the string that i give as a input through text box. –  vaani Oct 25 '12 at 10:54
    
Are you saying you want to put the selectvalueid as the field to be used inside the MATCH? If that is what you are trying to do, put only the variable string. MATCH($selectedvalueid). But I would actually put a switch case for each type of column and define the SQL query relevant to the case passed by the URL. this way, it will be secure. –  Raf Oct 25 '12 at 11:41
show 2 more comments

Not the answer you're looking for? Browse other questions tagged or ask your own question.