Running A Query From A Comma Separated String

[sintax63]

Good morning, everyone! I was hoping I could get a bit of help here. I'm setting up a short registration system for some workshops we have coming up here at work. Basically, for the part that matters for this issue, people can go in and choose from a list of upcoming classes they want to take. I am then storing them in my table ('classes' column) by ID's separated by a comma:

01, 14, 12, 07

So in that example above, the person registered for four workshops.

 

Now here is where I'm having trouble (and perhaps I'm going at this all wrong). I have a summary page for each employee where they can see what classes they signed up for, the title, time, description, etc. All this information is stored in an existing table. What I need to do is create a MySQL query that will go through each of those ID's above, run them against my other table, and spit out corresponding information.

 

Since I don't know how many workshops the person signed up for, I am not sure how to tell my query to keep looping through this comma delimited string until it's done. I know I probably need to make an array, which I imagine would look something like this:

$classArray = str_getcsv($classes, ", ", "'");

I hope I'm explaining this correctly. Any help out there would be greatly appreciated!

 

Thanks!

 

[ I'm running a fresh install of PHP and MySQL on my server ]

Edited November 5, 2013 by sintax63

[AbraCadaver]

First, don't do that!  Create another table for the selected workshops and store individual workshop ids along with the persons id:

person_id     workshop_id
5             1
5             14
5             12
5             7
13            1
13            11
13            9

Edited November 5, 2013 by AbraCadaver

[Psycho]

To add to AbraCadaver's response, once you properly normalize the tables you can get ALL the information you need for your output in a single query. So, let's say you have the following tables:

 

persons (the table to describe the individual people)

workshops (the table to describe the individual workshops)

person_workshop (the table to associate people to one or more workshops)

 

You could then run the following query to show all people and the workshops they are signed up for

 

SELECT *
FROM persons
LEFT JOIN person_workshop ON persons.person_id = person_workshop.persons_id
LEFT JOIN workshops ON person_workshop.workshop_id = workshops.workshop_id

 

The LEFT joins will ensure you get a record for every person - even if they don't have any workshops registered. If you don't need those, then remove the "LEFT" from the joins. Also, I just used '*' in the select portion of the query,but you should list out the fields you need.

[r3wt]

$result = @mysqlquery("SELECT * FROM `person_workshop` where person_ID =`person_id`->".$USER_ID.")

     

while ($row = mysql_fetch_assoc($result)){

 

       if ($result->$row !=null;) {

                 strip_tags_recursive($row, null);

                print_r($row,false);

}

}

function strip_tags_recursive( $str, $allowable_tags = NULL )
{
    if ( is_array( $str ) )
    {
        $str = array_map( 'strip_tags_recursive', $str, array_fill( 0, count( $str ), $allowable_tags ) );
    }
    else
    {
        $str = strip_tags( $str, $allowable_tags );
    }
    return $str;
} 

[sintax63]

Thanks for the help everyone!

 

I have normalized my database per AbraCadaver suggestion. I now have three like so:

 

'schedules' - contains two columns. One has the workshop code and the other the user ID

'workshops' - contains information about the workshop like name, description, dates, etc

'persons' - contains information about each person. (I don't think this is relevant for this query as I already have the users ID)

 

I have spent hours on this throughout the night trying different queries and reading forums and help docs online. I'm still having issues.

 

I believe that I only need to call two tables, not three, as I already have the ID of the person from an above call - shown below as $id.

 

(Please note that I'm stripping off the last 4 characters from the string in my workshops.name column so it will match the abbreviated version in my schedules.courseID column)

$query  = "SELECT s.courseID, s.userID, w.name, w.short_title FROM schedules s 
	LEFT JOIN workshops w ON s.courseID = substr(w.name, 0, -4) 
	WHERE s.userID = '$id'";

This is spitting out information from my 'schedules' table, but nothing from my 'workshops' table. I also tried the below which seemed like a much simpler solution, but also came up empty.

$query = "SELECT * FROM schedules, workshops WHERE schedules.courseID = substr(workshops.name, 0, -4) AND schedules.userID = '$id'";

What am I missing here? It's driving me crazy!