View Full Version : search in database with restrictions

08-25-2009, 09:30 AM
I have this big problem. I have a contact database with 1 table in mysql, 2 groups of people can login and add contacts to the database, each group should not be able to see the contact ifo from the other group unless a share checkbox is clicked.

Well the problem is as followed, in the database I have column called, group_id (1 or 2), when a group logs in and they want to search in the database, display the records in the fields (input text, checkboxes, memofields), I have create this query to do so, but when I click on search I don't get the result I want, it shows the result of the other group, (I double checked the group_id is set correctly), don't where its going wrong.

Here is the query I use:

//connect to the database
$db=mysql_connect ("localhost", "user", "password") or die ('I cannot connect to the database because: ' . mysql_error());
//-select the database to use
//query the database table
$sql="SELECT group_id, contact_name FROM contacts WHERE group_id='1' AND contact_name LIKE \"%$search%\"";
// run the query against the mysql query function
$result=mysql_query($sql) or die("Couldn't execute query");
if ($result)
// create while loop and loop through result set
while($row = mysql_fetch_array ($result)){
$contact_name =$row["contact_name"];
$group_id =$row["group_id"];

echo "$contact_name";
echo "$group_id";
else {
echo "<p>Geef een zoek woord op</p>";

Here the form search code:

<form id="searchcontact" method="post" action="search.php" />
<input type="text" class="search2" name="search"/>&nbsp;<input type="submit" name="submit" class="buttons" value="Zoek" />

Please need help!

08-25-2009, 09:33 AM
$sql="SELECT group_id, contact_name FROM contacts WHERE group_id='1' AND contact_name LIKE \"%$search%\"";
Don't wrap the values by quotes, if the field type is integer. Have a try by removing that.

08-25-2009, 09:37 AM
Still same results.

08-25-2009, 01:06 PM
The problem is not clear. From your query, you are searching for records that belong to group 1 only.
When you say "it shows the result of the other group", you mean the results show records with group_id 2 ?
could you list some sample data from the table?

08-25-2009, 02:15 PM
In the table, I have 2 records at the moment. Both with different group_id.
The query I have listed above should display the name of the contact in that record with group_id 1 and that is Henk, but in my case it shows stephan from group 1 (I double check and I have a group_id 2 for the name stephan in de database).

08-26-2009, 07:02 AM
try this code..

$sql="SELECT group_id, contact_name FROM contacts WHERE group_id='1' AND contact_name LIKE '%$search%' ";
dont use " in sql statements.. use single quotes.