current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a form where users choose from a checklist of jobs (Instructor, Translator, Interpreter). The results are stored as a variable ($employment) and sent to a MySQL database. They are separated by a comma-space delimiter.

Administration can login through a backend page and is sent to a directory where the different jobs are listed. When the Instructors link is clicked, they are taken to a page where all the users who clicked 'Instructors' are listed.

For this, I have used the code: $sql = "select * from GDI_teacher WHERE $employment='Instructor'";

But if the user has selected more than one job (say, Instructor and Translator), this user does not show up as having clicked Instructor using the code above.

I believe I have to use the explode() function, but am unsure of the syntax. I have been guessing at the code (from other forum posts) but have been unsuccessful.

I hope this is clear. Thanks in advance!

share|improve this question
2  
fix the db structure, it will solve this and many other problems latter –  Dagon Feb 7 '12 at 4:02
    
Can you please expand on that? How should I fix it? What needs to be changed? –  Coleen Feb 7 '12 at 4:08

2 Answers 2

up vote 1 down vote accepted

explode() will only help you after you've run the query and have the results in PHP. You SHOULD create a relational table to hold this data, but if you want to get all the correct results in mysql you should create a fulltext index and run your MySQL search against that. Alternately, you could run the much less efficient query:

$sql = "select * from `GDI_teacher` WHERE `employment` LIKE '%Instructor%'";

Placing wildcards at either side will let the string match:

'Instructor'
'[something],Instructor'
'Instructor,[something]'
'[something],Instructor,[something]'

However! Be very careful... if there is any chance that there will be textual overlap in categories (for instance, 'Instructor' and 'Instructor of Science') you need to create a more complicated query (if you're not going to (a) Just create a relational table (BEST SOLUTION) or (b) a Fulltext-Index (OK SOLUTION)) your last-resort query would look like:

$sql = "select * from `GDI_teacher` WHERE 
            `employment` LIKE 'Instructor'
            OR `employment` LIKE 'Instructor,%'
            OR `employment` LIKE '%,Instructor'
            OR `employment` LIKE '%,Instructor,%'"

If you want to give the relational table a go, you'll need to set up a second MySQL table, so your structure would look like:

Table 1

`UserID   |   UserName   |    UserEmail    |   etc`
..........................................................
1         |   John Doe   |   [email protected]  | [More]
2         |   Jane Doe   |   [email protected]  | [More]
3         |   Jake Doe   |   [email protected]  | [More]

Table 2

UserID   |   Employment 
.........................
1         |   Instructor
1         |   Translator  
2         |   Instructor  
2         |   Translator  
2         |   Interpreter
3         |   Instructor

Thus each user can will have multiple possible employment listings, identified by the user's ID. Now, if you want to see who is an instructor you can join the tables or run a sub-query.

Just make sure that UserID is an auto-incremented primary key on Table 1, and that it is indexed (but not unique or primary) on Table 2. Depending on how large table 2 becomes, you may want to index both UserID and Employment

share|improve this answer
    
can you explain 'relational table'? If I should do it this way, then that's what I want to do. –  Coleen Feb 7 '12 at 3:51
    
I've added an explanation in the answer. It will take a little bit more work setting it up, but it will dramatically increase the number of things you can do and the speed that it will work as the table get's better. –  Ben D Feb 7 '12 at 4:08
    
Wow! Thank you so much! For the time being, I will use $sql = "SELECT * from GDI_teacher WHERE employment LIKE '%Instructor%'"; like you suggested. But I will ABSOLUTELY be attempting to restructure my MySQL as per your lovely example. I'm oh, so new, to all of this. Great answer. :) Cheers –  Coleen Feb 7 '12 at 4:30
up vote 1 down vote

From PHP manual:

<?php
// Example 1
$pizza  = "piece1 piece2 piece3 piece4 piece5 piece6";
$pieces = explode(" ", $pizza);
echo $pieces[0]; // piece1
echo $pieces[1]; // piece2

// Example 2
$data = "foo:*:1023:1000::/home/foo:/bin/sh";
list($user, $pass, $uid, $gid, $gecos, $home, $shell) = explode(":", $data);
echo $user; // foo
echo $pass; // *

?>

I hope it helps you.

share|improve this answer
    
not quite. I need to explode a variable from a MySQL database $employment, not words written directly into my code like the PHP manual shows. –  Coleen Feb 7 '12 at 3:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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