I have a set of names of institutes stored in each row in my schools table. the same table has four different category columns which store the names of the subjects they offer. i want to enable a a functionality where the user conducts a search on the table to find out all the names of the institutes which teach a particular subject (for eg: physics). The name of the particular subject could be stored in any of the four category columns. Please suggest a mysqli_/mysql_ query to conduct such a search or if you may so please, suggest a PHP script that would achieve the above functionality.
EDIT: I am mentioning the table structure below:
Table showing column values
Table structure
Adding all the different queries i have tried with no result:
$query = mysql_query("SELECT name FROM institutes WHERE MATCH(category1, category2, category3, category4) AGAINST('$searchq' IN BOOLEAN MODE)") or die(" No records found.");
$query = mysql_query("SELECT name FROM institutes WHERE $searchq IN(category1, category2, category3, category4)") or die(" No records found.");
$query = mysql_query("SELECT name FROM institutes WHERE category1 LIKE '%$searchq%' OR category2 LIKE '%$searchq%' OR category3 LIKE '%$searchq%' OR category4 LIKE '%$searchq%' OR category5 LIKE '%$searchq%'") or die(" No records found.");
The search form:
<div class="row col-md-6 col-md-offset-2">
<div class="col-lg-6">
<div class="input-group">
<form action="search_results.php" method = "post">
<input type="text" name="search" class="form-control" placeholder="Search by subjects...">
<span class="input-group-btn">
<button class="btn btn-default" type="button submit">Go!</button>
</span>
</form>
</div><!-- /input-group -->
</div>
</div>
The entire PHP code:
<?php
mysql_connect("localhost", "root", "secretpassword") or die("could not connect.");
mysql_select_db("edhoc") or die("could not find database.");
$result = "";
//collect info from database
if(isset($_POST['search'])) {
$searchq = $_POST['search'];
$searchq = preg_replace("#[^0-9a-z]#i", "", $searchq);
//echo $searchq;
//SQL query
$query = mysql_query("SELECT name FROM institutes WHERE category1 = '$searchq' OR category2 = '$searchq' OR category3 = '$searchq' OR category4 = '$searchq' OR category5 = '$searchq'") or die(" No records found.");
$count = mysql_num_rows($query);
if($count == 0) {
$output = "There's no search result";
} else {
while($array = mysql_fetch_assoc($query)) {
?>
<li><?php echo $array['name'];?></li>
<?php
}
}
}
?>
None of them produce the desired output. When physics is the search string the current output is:
physics No records found.
When physics is the search string, the desired output is:
You have the following command:
$query = mysql_query("SELECT ....") or die("No records found.");
However, when the die part is executed (as seems to be the case because "No records found" is output), then this does not mean that no record was found. It means that there was an error executing the sql statement. So either there is an error in the sql statement or there is a problem with the database connection. Modify the line like this to get a specific error message:
$query = mysql_query("SELECT ....") or die(mysql_error());
Since the sql statement fails and yet you still get the same five institute names listed (even before the error message) this means that the list of institute names you see does not come from the php code in your question but from somewhere else.
We don't likely answer because you don't give a table structure that is easily readable , so I'm going to assume one .
Select school from schooltable where subject1=? or subject2=? or subject3=?
? Is the search query , if you weren't able to implement it in php reply here and I'll write it for you . If I didn't got you , it's your problem you didn't explain enough.