php explode() a variable from MySQL database - proper syntax?

Question

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!

Answer 1

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

Answer 2

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.