User Preference Page

[kney]

Hi,

 

I'm trying to build a pages for user preferences for digital tv.

Each user has to login so we know which user to assign the preferences to.

On the page i have a question about which genre of movies the user likes (so I have 1 table "Genre" with 10+ genres in it)

So the first thing I do is display checkboxes with the genres that are in the DB.

Now here are some things I don't really know:

- I have a table userGenre that combines the users and the genres they've picked, so if userid 1 chooses 3 genres, it comes like this in the userGenre table (

userID: 1, genreID: 1

userID: 1, genreID: 3

userID: 1, genreID: .. I don't know if this is the best way though.

- How can I display the genres they picked starting from the values in the DB? I have something now: "SELECT * FROM userGenre WHERE userID = " . $userID

  or something like that.. I don't know what to do next

[trq]

This is pretty basic database handling:

 

$sql = "SELECT genreID FROM userGenre WHERE userID = " . $userID;
if ($result = mysql_query($sql)) {
  if (mysql_num_rows($result)) {
    while ($row = mysql_fetch_object($result)) {
      echo $row->genreID . "<br />";
    }
  }
}

[kney]

Yeah I know that sorry, but my explanation wasn't correct

I need to display the 10+ genres with checkboxes (Genre table)

and my db has data that userID 1 has these values in DB (userGenre table):

 

userID: 1, genreID: 1

userID: 1, genreID: 3

userID: 1, genreID: 4

 

So it should show like this

 

x Action

  Adventure

x War

x Thriller

  Drama

  Science Fiction

  Horror

  ...

[kney]

I have this:

 

X Action
   Adventure
   Comedy
X Horror
X Thriller
   Thriller
   War
   Drama

 

But should get this:

 

X Action
   Adventure
   Comedy
X Horror
X Thriller
   War
   Drama

 

I get the Thriller category twice.

This is my code now

 

<?php
$sql = "SELECT genreID FROM userGenre WHERE userID = " . $userID;
$sql2 = "SELECT * FROM genre";

$result = mysql_query($sql);
$result2 = mysql_query($sql2);
$i = 0;

while ($row = mysql_fetch_object($result)) {
while($row2 = mysql_fetch_object($result2)){
	if($row2[1] == $row[1]){
		$i++;
		if($i < 4){
?>
			<tr>
				<td><input type="checkbox" name="type" checked="checked" value="<?php echo $row2[1];>" /><?php echo $row2[2]; ?></td>
			</tr>
<?php
		}
		if($i < 3){
			break;
		}
	}
?>
<tr>
	<td><input type="checkbox" name="type" value="<?php echo $row2[1];>" /><?php echo $row2[2]; ?></td>
</tr>
<?php
}
}
?>

[ignace]

<?php // untested

$sql = "SELECT * FROM genre LEFT JOIN userGenre USING genreID WHERE userID = " . intval($userID);
$res = mysql_query($sql);

while ($row = mysql_fetch_object($res)): ?>
<tr>
    <td><input type="checkbox" name="type"<?php print !empty($row->userID) ? ' checked="checked"' : ''; ?> value="<?php echo $row->genre;>" /><?php echo $row->genre; ?></td>
</tr>
<?php endwhile; ?>

 

Learn SQL. Exotic solutions like yours won't always help you solve certain problems.

[kney]

The SQL statement returns "Error code -1, SQL state 42X01: Syntax error: Encountered "genreID" at line 1, column 56.

 

Is this statement also correct?

 

"SELECT * FROM genre LEFT JOIN userGenre ON genre.genreID = userGenre.genreID WHERE userGenre.userID =" . intval($userID);

[kney]

Nevermind, I changed the SQL statement to:

 

"SELECT * FROM genre LEFT JOIN usersGenre USING (genreID) WHERE userID = " . intval($userID);

 

But now I get this result.

 

X Adventure
X Horror
X Thriller

 

And I should get

 

  Action
X Adventure
  Comedy
X Horror
X Thriller
  War
  Drama

[ignace]

I'm here to help, not to do the job for you. Analyse, edit, run! Repeat. I'm not going to be there to hold your hand when you are on someone's payroll.

 

"Is this statement also correct?"

 

Have you tried it? I already know the answer: Yes, that is also correct. If you want to pursue a career in programming you should start doing to.

 

A possible correct answer is:

 

SELECT genre_id IN(SELECT genre_id FROM user_genre WHERE user_id = 1) user_has_genre, genre_name FROM genre;

 

Yet you made 0.0 attempt at trying to solve it yourself.