How to search an array value, using GET method, to retrieve Data from database?
In other word, I have (prof_ids) Stored in database like this: 1, 15, 52, All those three values are stored in one field. I have a link containing this (?prof_id=1) for example and I want a query to search if the (prof_id) value exists on (prof_ids) field or not. and I have no Idea how to do it.
Is it possible to this?
$sql = $mysqli->query("SELECT * FROM profs WHERE prof_ids LIKE '%".$_GET[prof_id'']."%' ");
I sure my query will not get me the result I'm looking for, if any one please have any other solution can you please help me with this..
The problem with doing it using LIKE (aside from the SQL injection) is that you will get all sorts of records you might not want. Consider if we pass 1 in. That would match 1, 10, 14, 100, 21, etc
This is why you need a one-to-many table that contains the data in individual records, so you can match the records exactly. It takes more space but it is the only way to ensure you get the records you want every time.
SELECT table1.column
FROM table1
INNER JOIN table2 ON table1.id = table2.id
WHERE table2.record = "1"
You can try RLIKE instead of LIKE So your condition will look something like
"SELECT * FROM profs WHERE prof_ids REGEXP '[[:<:]]".$_GET['prof_id']."[[:>:]]' "
Because you put 3 ids in a column, it is hard to do the plain matching.
But if the pattern is always like (int,int,int), you can consider using REGEXP like:
$param = $_GET['prof_id'];
//getting the records where the id in first position
$sql = $mysqli->query("SELECT * FROM profs WHERE prof_ids REGEXP param .',[0-9]*,[0-9]*'");
//getting the records where the id in second position
$sql2 = $mysqli->query("SELECT * FROM profs WHERE prof_ids REGEXP '[0-9]*,'.param .',[0-9]*'");
//getting the records where the id in third position
$sql3 = $mysqli->query("SELECT * FROM profs WHERE prof_ids REGEXP ',[0-9]*,[0-9]*'.param");
I think this could have been done in 1 query, but that is all i can think of.