simple php mysql search engine with checkboxes

Question

I would like to do a search engine for my webpage. I have several tables in my mysql database and i would like them combined when user

Table users
id  name        age country     vip profile_image
1   nick        23  sweden      1   yes
2   michael     20  germany     0   no
3   laura       19  usa         1   yes
4   gary        33  china       1   yes

Table online
id  user_id     online
1   1             1
2   2             1 
3   4             1

user_id is connected to id in users table

Now i have checkboxes
[ ] Those which are online
[ ] Those which are vip
[ ] Those with profile image

Im coding my page in PHP and im trying to figure how to include certain searches in a sql query if certain checkbox is checked. I can have tons of options here. Example if no checkbox is checked, iff on you want to search for those which are online, how do i go in to the second table?

I hope you get my point here. I really hope someone could help me and give me an working example of the php & sql query.

Cheerz!

you should normalize your database to the third form devshed.com/c/a/MySQL/An-Introduction-to-Database-Normalization/… then simply write "Where online = 1" etc. in your query depending on the checkboxes. — Manuel Schweigert
– Manuel Schweigert, Commented Dec 9, 2012 at 16:09

TCFDS · Accepted Answer · 2012-12-09 16:16:12Z

1

First you have to check which checkboxes have been checked. Then you must write MySQL-query with PHP based on that information. You must think in every checkbox, what information it needs to check. If your database is well written, there is seldom a problem that two options affect each other. If information is in users-table, you need just write line to where-clause. If you need to join table to users-table to get information you need to do that too. Here is an example

$query = "";
$query .= "SELECT users.* FROM users";
if ($include_online == 1) {
  $query .= " LEFT JOIN online ON online.user_id = users.id";
}
$query .= " WHERE";
if ($include_vip == 1) {
 $query .= " users.vip = 1 AND";
}
if ($include_image == 1) {
  $query .= " users.profile_image = 'yes' AND";
}
if ($include_online == 1) {
  $query .= " online.online = 1 AND";
}
$query .= " users.name LIKE '%".$search_string."%'";

answered Dec 9, 2012 at 16:16

TCFDS

6226 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mensur Over a year ago

Is there any way to contact you? I would like you to help me with a query and i can pay you if you are interested? I have looked everywhere on stackoverflow to find a contact information but i cant find any, and i dont know if it's okey to write something here but i'll do it anyway, if it's not correct please delete this post.

TCFDS Over a year ago

Don't know about correct, but I have gmail as tuomo.m.makela. But I'm not going to do anything big.

Expedito · Accepted Answer · 2012-12-09 17:16:27Z

0

You need to form a query something like the following:

SELECT users.name, users.age, users.country
FROM users
LEFT JOIN online ON user.id = online.user_id
WHERE ((user.vip = 1) && (online.online = 1))

edited Dec 9, 2012 at 17:16

answered Dec 9, 2012 at 16:15

Expedito

7,8095 gold badges32 silver badges45 bronze badges

Collectives™ on Stack Overflow

simple php mysql search engine with checkboxes

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related