Selecting tables based on multiple values in a cell

I have something that looks like this:

if ( $_SESSION['username'] ) 
        $owner = $_SESSION['username'];

    $q = "SELECT * FROM projects where owners='$owner' ORDER BY created DESC";

The problem is tha开发者_Python百科t the 'owners' column may have multiple entries separated by commas. I need a way to cycle through all the entries and select any table that has $owner in the owners columns. Whats the best way to go about this?

The proper design for a relational database is to store only one value in a given "cell" (i.e. in a given column on a given row -- but please do not use spreadsheet terminology with relational databases, you'll make Joe Celko cry. :-).

Here's an example:

CREATE TABLE Projects (
 project_id INT PRIMARY KEY,
 ...
);

CREATE TABLE Users (
 user_id INT PRIMARY KEY,
 ...
);

CREATE TABLE Project_Owners (
  project_id INT NOT NULL,
  user_id INT NOT NULL,
  PRIMARY KEY (project_id, user_id),
  FOREIGN KEY (project_id) REFERENCES Projects(project_id),
  FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

You insert one row to Project_Owners table for each owner of a given project. So you can easily have a project with multiple owners, and a user who works on multiple projects.

We say that Projects has a many-to-many relationship to Users.

So when you want to get projects associated with a given owner, you'd need to look up the owner in the users table, and then join that to projects via the many-to-many table:

<?php

$owner = mysql_real_escape_string($_SESSION["username"]);

$sql = "SELECT p.* FROM Projects
INNER JOIN Project_Owners o USING (project_id)
INNER JOIN Users u USING (user_id)
WHERE u.name = '$owner'";

Or if you want to simplify it, you can use integers in your session data. Then you can skip a join:

<?php

$owner = (int) $_SESSION["username"];

$sql = "SELECT p.* FROM Projects
INNER JOIN Project_Owners o USING (project_id)
WHERE o.user_id = $owner";

The concept of storing just one value in a cell is part of the basic definition of First Normal Form. Any book on SQL should cover this, even the Manga Guide to Databases.