Help with comments on a website

[NateC16]

Hey guys not sure if this is the right spot for this 100% but I hope it is.

 

I have a website that I'm making for a class project and I would like it to be a Movie Review type site. What I want to happen is that a person searches for the movie, and once if they find the movie it goes to another page saying if it was found or not. And if it was found information of the movie would come up. I made a comment section for people to be able to post what they thought about the movie. (It doesn't work yet but it should be up soon)

 

But anyway how would I make it so like say a person searches for "avatar" and the movie pops up with the information, that the review is for avatar and only avatar. Then next they search for Lord of the rings, and only lord of the rings reviews pop up?

 

And just to let ya know I'm not a super coder with this stuff and I am using dreamweaver cs5 to do a lot of the coding. Any and all help would be appreciated.

[Fadion]

I get the idea that people can see and add reviews, but you didn't tell where the movies will be fetched. Are you going to use an external movie database (like IMDB), or are movies going to be in your own database? The first option would be the most obvious choice imo, while reviews are of your own site.

 

The choice of the database reflects the programming options. If you choose to use IMDB, you'll have their API at your disposal which you can use to make searches and present information. If you make your own database, you'll have to code a simple search engine. MySQL has pretty neat feature called FULLTEXT, which basically indexes columns as "searchable" and has also a few functions to make the search. That would make searching a lot easier.

 

A search may be exact (generates one result) or partial (many results). If it is partial, you can present a page with the matched movie titles that link to the info and reviews page. Otherwise [if it's exact], you just redirect the user to the movie's reviews page. The process is quite simple and straightforward, even for a beginner.

[NateC16]

oh yeah sorry about that, we have a database for class and I have 2 tables, the main movie table where the movie information such as the movie id, description, title, genre and rating is, then I have another table where the comments go. It has the rating id, name, comment and Movie id as a foreign key (trying to get the movie id to have the comment id)

 

I did make a search thing, but you have to exactly type in what you want in order for the stuff to show up. But as I have it right now, comments/reviews for movies are posted from the database, BUT, they are on every single page I do. I would kind of like it like IMDB where you search for a movie, then there are reviews for THAT movie on THAT page. I mean if it's a ton of work and will take a long time to do, I may have to change my approach on this. But it would be fun to have it work =p

[Fadion]

We are touching it a bit superficially, but I'll do my best to guess what you mean

 

As for the search, you probably are using "WHERE movie_title LIKE '%$search%'" or even "WHERE movie_title='$search'". That's why I suggested FULLTEXT, as it will improve a lot your search queries. The first result on google is this article from Zend (back in 2002, but things haven't changed much) that has some good examples on FULLTEXT searching with PHP and MySQL. The boolean mode and order by "score" are things that will give your search results some weight.

 

As for the logic! I guess you have tables like this:

 

table "movies"

-----------------

id | title | description | genre

 

table "comments" (aka reviews, right?)

----------------------

id | movie_id | name | comment

 

When a movie is searched and found, you redirect the visitor to that movie's page, right? Something like movie.php?id=10. As you have the movie id (10 in that case), appart from the movie's information, you can also show comments made for that movie. So, basically:

 

<?php
$id = (int) $_GET['id'];
$results = mysql_query("SELECT title, description, genre FROM movies WHERE id=$id");
$values = mysql_fetch_assoc($results);

echo '<h1>Title: ' . $values['title'] . '</h1>';
echo '<p>Genre: ' . $values['genre'] . '</p>';
echo '<p>Description: ' . $values['description'] . '</p>';

$results = mysql_query("SELECT name, comment FROM comments WHERE movie_id=$id");
while ($values = mysql_fetch_assoc($results)) {
     echo $values['name'] . '<br />' . $values['comment'] . '<br /><br />';
}
?>

 

Above is just a very simple example of a real world scenario, but with no real world coding (you would need to checks if it exists, apply real formatting, etc). Anyway, that should give the idea if that's what you needed.

[NateC16]

Yes, that is how I have the tables set up exactly. And as for getting the stuff from the database, dreamweaver is drag and drop so I just have to make things called server behaviors and well it does all the work for me so there's very little coding on my part. But I'll try to explain what I mean.

 

Say you go to the search page and type in Avatar. I have it so the movie information pops up, like this:

Movie Name: Avatar

Description: This takes place on pandora.

Rating: PG-13

Genre: Fantasy

 

Now what I would like to have is a Review section, where it has the name and comment section(which I have built).

 

Name: Joe Smoe

Review: This movie was wicked

 

I also have it to where that information is put into the database, AND displayed like a review/comment box.

 

 

NOW, lets say that I go back to search, and type in Sunshine.

The movie information pops up fine again like:

Movie: Sunshine

Description: Wicked wild

Rating: R

Genre: Action

 

My problem is that those reviews that I had typed in earlier, are still on that page. So Joe Smoe's post is still on the movie for Sunshine, even though he posted it for avatar.

 

What I would like is that his post only shows when people searched for Avatar. IF that makes any sense at all.

 

Thanks again for tryin to wrap your head around this =p

[Fadion]

I got your idea and that's what the code I posted in my previous post does. It shows the movie's information and it's reviews. If you search another movie, it will only show it's reviews. As I have no idea how behaviors in Dreamweaver work (I use DW myself, but just for raw editing). The only thing I know is that they're nasty and you should avoid them. Coding things totally yourself isn't hard as long as you get away with the initial learning curve. It will pay a lot, trust me.

 

Probably if you post the code you have, we can help better.

[NateC16]

Alright man thanks, I'll give this a try and attempt to get away from the dreamweaver server behaviors, I guess I became so accustomed to them that simple stuff like this just really doesn't make 100% sense. But I'll give it a go and see what happens and if all else fails I'll post my huge page of code!

[NateC16]

I seem to be failing, but as I was looking at your code it makes sense to me but the only thing is when you were talking about the "movie.php?id=10." I assume thats after you search and in the address bar, that's at the end. Well when I looked at my address bar it doesn't have that, mine has: Search=Avatar&search=Search. My simple code for the search box is this, am I going at it the wrong way?

<form action="search_results2.php" method="get" name="search">
  <p>
    <input type="text" name="Search" id="search" />
  </p>
  <p>
    <input type="submit" name="search" id="search" value="Search" />
  </p>
</form>

 

Also I did tweak the code you sent me to fit everything I have in my database perfectly, does this look right?

<?php
$id = (int) $_GET['MovieID'];
$results = mysql_query("SELECT MovieName, Description, Genre, Rating FROM movie WHERE id=$id");
$values = mysql_fetch_assoc($results);

echo '<h1>Title: ' . $values['MovieName'] . '</h1>';
echo '<p>Genre: ' . $values['Genre'] . '</p>';
echo '<p>Description: ' . $values['Description'] . '</p>';
echo '<p>Rating: ' . $values['Rating'] . '</p>';

$results = mysql_query("SELECT FirstName, Review FROM reviews WHERE ReviewID=$id");
while ($values = mysql_fetch_assoc($results)) {
     echo $values['FirstName'] . '<br />' . $values['Review'] . '<br /><br />';
}
?>

 

 

[Fadion]

I just assumed that, but you can easily use the search query in the url. The following form adds a <label> and replaces <input type="submit"> with a <button>. The later is a more flexible form element that doesn't show up in the url (&search=Search) after a get submit.

 

<form method="get" action="search_results2.php">
     <p>
          <label for="search">Search</label>
          <input type="text" name="search" id="search" />
     </p>
     <button type="submit">Search</button>
</form>

 

search_results2.php

if (isset($_GET['search'])) {
     $search = mysql_real_escape_string($_GET['search'];
     $results = mysql_query("SELECT id, MovieName, Description, Genre, Rating FROM movie WHERE MovieName='%$search% LIMIT 1'");
     $values = mysql_fetch_assoc($results);

     $movie_id = $values['id'];

     echo '<h1>Title: ' . $values['MovieName'] . '</h1>';
     echo '<p>Genre: ' . $values['Genre'] . '</p>';
     echo '<p>Description: ' . $values['Description'] . '</p>';
     echo '<p>Rating: ' . $values['Rating'] . '</p>';

     $results = mysql_query("SELECT FirstName, Review FROM reviews WHERE ReviewID=$movie_id");
     while ($values = mysql_fetch_assoc($results)) {
          echo $values['FirstName'] . '<br />' . $values['Review'] . '<br /><br />';
     }
} else {
     echo 'Please make a search first.';
}

 

The code above should theoretically work copy/paste, but it has 2 problems.

 

1) It shows only 1 result (hence the LIMIT 1 in the first query). If there will be a partial search with more than 1 result, it doesn't care. That's why it's a good option to make a search result page that just shows matched movie titles and when a user clicks a movie, finally it shows full movie info and reviews.

 

2) The search isn't really state-of-the-art. As I said before, FULLTEXT searching will provide a way better search functionality and I really suggest it. Just to give you the idea, in your case you would have a query like this:

 

SELECT id, MATCH(MovieName) AGAINST ('$search' IN BOOLEAN MODE) AS score
FROM movie WHERE MATCH(MovieName) AGAINST ('$search' IN BOOLEAN MODE)
ORDER BY score DESC

 

MovieName should be altered to be a FULLTEXT index and you're ready to go

 

Anyway, as you're a beginner learning his way into PHP and MySQL, I suggest you finish up the script as you've already started it. After that, return to it with a new perspective and try to write it better.

[NateC16]

Aha! I ended up getting this to work with some help from my teacher!!

$id = $_GET['movie_name'];
echo $id;
$results = mysql_query("SELECT MovieID, MovieName, Description, Genre, Rating FROM movie WHERE MovieName='$id'");
$values = mysql_fetch_assoc($results);

echo '<h1>Title: ' . $values['MovieName'] . '</h1>';
echo '<p>ID'		. $values['MovieID'] .' </p>';
echo '<p>Genre: ' . $values['Genre'] . '</p>';
echo '<p>Description: ' . $values['Description'] . '</p>';
echo '<p>Rating: ' . $values['Rating'] . '</p>';

$movieID=$values['MovieID'];
$results2 = mysql_query("SELECT Name, Review FROM reviews WHERE MovieID='$movieID'");
$values2 =mysql_fetch_assoc($results2);

/*foreach($values2 as $v) {
echo $values2['Name'] . '<br />' . $values2['Review'] . '<br /><br />';
}*/

while ($values2 = mysql_fetch_assoc($results2)) {          echo $values2['Name'] . '<br />' . $values2['Review'] . '<br /><br />';    
}


?>

 

Now I'm sure that looks ugly as ever but I mean it actually works for what I want, but I do have one more question for you. Well maybe 2, how would I change that field to full text? I tried multiple times and I even created a new table and made a full text field but I'm not sure if it is or not.

 

But the most important of all is my adding the comment part. Now I can get the stuff put in the Database alright, but my main concern is this: They search the movie, bam ^^ stuff pops up with the review and all, they start entering information Name: Joe, Comment: wicked movie...

 

How will the database know which movie id to put into that review? (if that makes any sense at all)

[NateC16]

Ugh sorry for all the double posts, but I did encounter something odd, when I search for avatar, all the avatar reviews come up along with the movie info, when I search for sunshine, the same thing happens, but when I search for another movie that has a review in it, nothing shows up. It's not a HUGE deal but I was just curious as to why this is happening.