Jump to content

Mysql Code Tips


TheMovieman
 Share

Recommended Posts

I've finally got the coding to work on my new review pages, but now I have some general coding queries that I need help with.

 

1. On these pages, I list what genres they're listed under and the coding for that goes like this:

><?php

$genreinfo = @mysql_query("SELECT category, movieid, genreid, dvd_reviews.id FROM genre, moviegenre, dvd_reviews WHERE movieid='$id' AND genreid=genre.id AND dvd_reviews.id='$id'");

while ($ginfo = mysql_fetch_array($genreinfo)) {
$cat = $ginfo['category'];
echo "$cat /";
}
?>

 

And while the output is basically right (I'm considering adding another column to the "moviegenre" table so I can order them right), it does add the slash at the very end, but I need the slash when there are multiple categories. Is there a piece of code to eliminate that?

 

Also, I noticed that if there's a wrong address to a regular page, it would give a 404 error, but when I mistype in the "id" portion of the address (like www.site.com/read.php?id=this when its supposed to be id=that), can I redirect it to my homepage or even the 404 error page?

 

2. I plan on making a review index page to list all my reviews. Now, is there a code that I can split each up by the first letter and let's say the movie is The Sisters, how can I list it within the "S" section rather than "T"? I did make a column called "listtitle" that take the "The" and place it at the end (ie Sisters, The), but I notice sites like IMDb lists movies correctly.

 

I appreciate any help you can offer.

Edited by TheMovieman
Link to comment
Share on other sites

I'm going to try to tackle each of your questions with separate posts, as answering all of them in one post would be a pretty long post. ;)

 

I've finally got the coding to work on my new review pages, but now I have some general coding queries that I need help with.

 

1. On these pages, I list what genres they're listed under and the coding for that goes like this:

><?php

$genreinfo = @mysql_query("SELECT category, movieid, genreid, dvd_reviews.id FROM genre, moviegenre, dvd_reviews WHERE movieid='$id' AND genreid=genre.id AND dvd_reviews.id='$id'");

while ($ginfo = mysql_fetch_array($genreinfo)) {
$cat = $ginfo['category'];
echo "$cat /";
}
?>

And while the output is basically right (I'm considering adding another column to the "moviegenre" table so I can order them right), it does add the slash at the very end, but I need the slash when there are multiple categories. Is there a piece of code to eliminate that?

With the echo() statement in the loop, you're sending each category (with it's trailing slash) to the browser immediately. If you send one too many (like the one that appears after the last category), there's no way to go back and remove it - it's already left the server and viewable in the visitor's browser, where you can no longer change it.

 

I'd suggest restructuring the while loop a little differently:

>$cat_array = array();
while ($ginfo = mysql_fetch_array($genreinfo)) {
array_push($cat_array, $ginfo['category']);
}
$cat_string = implode(" / ", $cat_array);
echo $cat_string;

This code sets up an empty array, then adds each category to the array as you loop through the query results. After all of the categories have been added to the array, the implode() function takes each element of the array and combines them into a single string, separating them with " / ". The nice thing about implode() is that it automatically will not output a separator after the last element in the array, and there won't be any separator at all if there's only one category.

 

After the category string has been built, *then* it can be sent to the visitor's browser (echo()).

Link to comment
Share on other sites

Part 2 of my 3 part reply...

 

Also, I noticed that if there's a wrong address to a regular page, it would give a 404 error, but when I mistype in the "id" portion of the address (like www.site.com/read.php?id=this when its supposed to be id=that), can I redirect it to my homepage or even the 404 error page?

You can redirect to any URL as long you haven't outputted anything (used any echo() statements or output any plain HTML) yet to the visitor's browser:

><?php
if ($bad_id) {
header("Location: http://www.my-TCH-domain.com/"); /* Redirect browser */

/* Make sure that any subsequent code in script does not get executed when we redirect. */
exit;
}
?>

(...where $bad_id represents some condition you've tested, indicating an invalid id.)

 

If you have a custom 404 error page, you could just output a 404 header and include the file, rather than do an actual redirect. If you don't have a custom 404 error page (you're just relying on the Apache default 404 page), you can replicate it with the following code:

><?php
header("HTTP/1.1 404 Not Found");
echo <<<EOT
<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
<html><head>
<title>404 Not Found</title>
</head><body>
<h1>Not Found</h1>
<p>The requested URL {$_SERVER['REQUEST_URI']} was not found on this server.</p>
<hr>
{$_SERVER['SERVER_SIGNATURE']}
</body></html>
EOT;
?>

Link to comment
Share on other sites

Part 3 of my 3 part reply...

 

2. I plan on making a review index page to list all my reviews. Now, is there a code that I can split each up by the first letter and let's say the movie is The Sisters, how can I list it within the "S" section rather than "T"? I did make a column called "listtitle" that take the "The" and place it at the end (ie Sisters, The), but I notice sites like IMDb lists movies correctly.

Rather than create and use an actual column in your table, you may be able to use a calculated column instead.

 

To get a reliable list of "S" titles without combing through the entire database in PHP, your MySQL query is going to need to identify those titles itself.

 

I'm sure what IMDB uses is more complex, but here's a query that will pull movie titles beginning with "S", taking into account titles starting with "A", "An", or "The":

>SELECT title,
CASE WHEN SUBSTRING(title, 1,2) = "a "
THEN CONCAT(SUBSTRING(title, 3), ', ', SUBSTRING(title, 1, 1))
WHEN SUBSTRING(title, 1, 3) = "an "
THEN CONCAT(SUBSTRING(title, 4), ', ', SUBSTRING(title, 1, 2))
WHEN SUBSTRING(title, 1, 4) = "the "
THEN CONCAT(SUBSTRING(title, 5), ', ', SUBSTRING(title, 1, 3))
ELSE title
END AS list_title
FROM movies WHERE title REGEXP '^((a|an|the) )?s.*'
ORDER BY list_title ASC

This query select the movie title, and calculates a list_title for each title as follows:

 

1) If title starts with "a " (case insensitive), strip that from beginning of title, add ", " to end of title, followed by just the "a".

 

2) If title starts with "an " (case-insensitive), strip that from beginning of title, add ", " to end of title, followed by just the "an".

 

3) If title starts with "the " (case-insensitive), strip that from beginning of title, add ", " to end of title, followed by just the "the".

 

4) Otherwise, use the actual title.

 

The WHERE clause filters the records by only returning records that start with "s" (case-insensitive), optionally preceded by "a", "an" or "the" and a space.

 

Since list_title is an actual column returned in the query, it can be sorted on, and the above query sorts the results alphabetically by the new calculated list_title column.

Link to comment
Share on other sites

Thanks David, I appreciate the help :no2:

 

I got 1 and 3 to work but I'm not quite understanding the second one.

 

"where $bad_id represents some condition you've tested, indicating an invalid id"

 

How do I test a condition? Basically, I want to make sure if anyone manually typed in a page that doesn't exist, that it'll go to the 404 error page (default Apache page). My knowledge of MySQL (obviously) is limited, so maybe this is something I should wait on until I learn a little more.

 

 

I apologize for bringing up a new issue before I even understood this other one... but here it is:

 

I have to manually enter all my reviews into the database but some of them don't have images associated. I tried using an if, else statement like

 

>if (isset($image1)) {
echo "
code with $image1 variable";

}

else {
echo "
code without $image1 variable";

}

 

The page displayed but when I tried one page with the image and the other not, both seemed to display the code from the "else" section (sorry, but I'm not up to speed with sql lingo).

 

The $image1 variable is set like this within the code http://www.example.com/files/stills/$image1.jpg, does that affect the code or am I way off using the if/else like I am?

Edited by TheMovieman
Link to comment
Share on other sites

I figured out the if/else problem. Maybe there's a more proper way to do it, but for now, I'll just name the image files as "NULL" and then in the if statement:

 

>if ($image1 == 'NULL') { 

echo "...";

}

else { 

echo "...";
}

Edited by TheMovieman
Link to comment
Share on other sites

I have to manually enter all my reviews into the database but some of them don't have images associated.

I don't know what you're doing now (I guess not display an image at all...?), but what I would do is have a 'default' image available for reviews that do not have their own image.

 

In your PHP code, check to see if there was an image name in the query results. If so, set $image to the name returned in the query; otherwise, set $image to your default image name.

 

Then in the code you've shown here, $image will always be set, and there would be no need for an if block at that point.

 

How do I test a condition? Basically, I want to make sure if anyone manually typed in a page that doesn't exist, that it'll go to the 404 error page (default Apache page). My knowledge of MySQL (obviously) is limited, so maybe this is something I should wait on until I learn a little more.

Here, I was talking about someone entering an invalid movie id. You'd query the database, but the database would return no results in that case. After running the query, but before generating anything for the web page, you'd want to check and see if the initial query returned anything (whether the movie id was valid or not).

 

>$result = mysql_query("SELECT * FROM movie where movieid='$id'", $link);
if (!mysql_num_rows($result)) {
...Bad movie id - display 404 page here...
}
...continue with code to display requested page...

Link to comment
Share on other sites

I don't know what you're doing now (I guess not display an image at all...?), but what I would do is have a 'default' image available for reviews that do not have their own image.

 

In your PHP code, check to see if there was an image name in the query results. If so, set $image to the name returned in the query; otherwise, set $image to your default image name.

 

Then in the code you've shown here, $image will always be set, and there would be no need for an if block at that point.

 

Each review has specific production pics or (in the case of DVD reviews) screencaps; some reviews will not have screencaps and older reviews definitely do not. Anyways, I tested it out and both pages (one without the stills, other without) display fine.

 

 

And thanks for explaining the error page, I think I get that now, lol. I'll give it a try in a little bit.

 

:D

Link to comment
Share on other sites

Well, maybe I'm stretching myself but I've added a new wrinkle to adding a new review...

 

On my add new dvd review page, I can (via checkbox) pick more than one genre for a given review, but now I've added a rank column in my intermediate table (moviegenre) so I can order them. But now I'm not sure how to do this. Here's the code to list the genres:

 

><p>Place in Genre(s):<br />
<?php
 while ($genre = mysql_fetch_array($genres)) {
$gid = $genre['id'];
$gname = htmlspecialchars($genre['category']);
echo "<label><input type='checkbox' name='genres[]' " .
	"value='$gid' />$gname</label><br />\n";
 }
?>
</p>

 

I was going to make a separate page where for each dvd "id", it'll list the genres for that movie and next a text area to rank them, but I have no idea how to do this. How do I create a text box and name it so that it'll get listed with the correct row? Is there a simpler way to do this? I was going to use the code from my new review form but that won't work since I won't know how many rank text boxes there will be...

 

I hope that made sense, lol.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...