|
|
Posted by Hilarion on 10/14/86 11:28
Hi
You'll find comments below between lines of your message.
> I have a page that outputs data about a tv show, all is working fine,
> however when a query returns for example 14 results I want to be able to
> output the number that corresponds to the position in the result, alongside
> the details.
>
> I can build a loop using mysql_num_rows to count the rows, but I don't know
> how to incorporate it within the while loop that is running to retrieve the
> actors details.
>
> Can anyone help please.
>
> code follows:
> <?php
> // open the connection
> $conn = mysql_connect("localhost", "user", "password");
Should be something like:
$conn = mysql_connect("localhost", "user", "password");
if (!$conn)
die( 'Error connecting to SQL server: ' . mysql_error() );
> // pick the database to use
> mysql_select_db("DB_name",$conn);
Should be something like:
if (!mysql_select_db("DB_name",$conn))
die( 'Error selecting database: ' . mysql_error() );
> $epid = $_REQUEST['id'];
Should add some input validation or transformation which
will prevent SQL injection. If the ID should be a integer
value, then this will work well:
$epid = intval( $_REQUEST['id'] );
> //query mysql
> $result = mysql_query( "SELECT * FROM actor a
> INNER JOIN episode_cast e on e.id = a.id
> INNER JOIN episode f on f.episode_number = e.episode_number
> WHERE e.episode_number = $epid");
> echo mysql_error();
Should be something like:
$result = mysql_query( "SELECT * FROM actor a
INNER JOIN episode_cast e on e.id = a.id
INNER JOIN episode f on f.episode_number = e.episode_number
WHERE e.episode_number = $epid");
if (!$result)
die( 'Error selecting data from database: ' . mysql_error() );
> // creates the page
Here we initialize a variable which we'll use to get the numbers:
$i = 1;
> while($row = mysql_fetch_array($result, MYSQL_BOTH))
> {
> list($id, $character_first_name, $character_last_name,
> $character_full_name, $character_height_feet, $character_height_inches,
> $character_weight, $character_color_eyes, $character_color_hair,
> $character_bio, $actor_first_name, $actor_last_name, $actor_full_name,
> $actor_height_feet, $actor_height_inches, $actor_birthdate,
> $actor_birth_place, $actor_maraital_status, $actor_film, $actor_tv,
> $actor_bio, $picture, $episode_number, $id, $episode_number1, $season,
> $season_episode_number, $episode_name, $episode_outline) = $row;
Wow! Wouldn't it be easier to access fields using $row and it's
associative indices? Like $row['id']? If you are going to keep the
"list" method which you are using now, then change
"mysql_fetch_array($result, MYSQL_BOTH)" to
"mysql_fetch_array($result, MYSQL_NUM)" or to
"mysql_fetch_row($result)".
This will prevent getting the data twice (with numeric and associative
indices).
> $content .= "<div class=\"character\">
> <h3><span>*This is where I want the Number*</span>
$content .= "<div class=\"character\">
<h3><span>" . ($i++) . "</span>
> $character_first_name $character_last_name</h3>
> <a href=\"$picture\"><img src=\"$picture\" alt=\"$actor_first_name
> $actor_last_name\" /></a>
> <ul>
> <li>Full name: <span>$character_full_name</span></li>
> <li>Height:
> <span>$character_height_feet$character_height_inches</span></li>
> <li>Weight: <span>$character_weight</span></li>
> <li>Eyes: <span>$character_color_eyes</span></li>
> <li>Hair: <span>$character_color_hair</span></li>
> </ul>
>
> <p>$character_bio</p>
>
> <ul>
> <li>Actor: <span>$actor_full_name</span></li>
> <li>Height:
> <span>$actor_height_feet$actor_height_inches</span></li>
> <li>Birthdate: <span>$actor_birthdate
> $actor_birth_place</span></li>
> <li>Marital Status: <span>$actor_maraital_status</span></li>
> <li>Filmography: <span>$actor_film</span></li>
> <li>TV: <span>$actor_tv</span></li>
> </ul>
>
> <p>$actor_bio</p>
>
> </div>";
You should use "htmlspecialchars" function on each text field you
get from DB to prevent the texts being interpreted as HTML.
> }
> $content = stripslashes($content);
What do you need "stripslashes" for? Does the data in the DB contain
extra slashes, or do you have magic_quotes_runtime turned on?
In the first case fix your data in the DB, in the second case
turn it off. Using "stripslashes" can break your HTML if it contains
backslashes as a content.
> echo mysql_error();
What is this one for? What error do you expect here? You have not
made any "mysql_*" call which could cause any error which has not
been previously checked for errors. "mysql_fetch_array" does not
produce any errors.
> mysql_close($conn);
>
> include 'library/header.php';
What does this "header.php" contain? If it is HTML header and such,
then maybe you should include it at the start of this script, so
the eventual errors would appear on the page allready having the
header. It also shows the user that the page is loading (the
header will be visible while the data from DB is retrieved).
> ?>
> <div id="episode_head"> <h2>
> <?php echo $episode_name; ?>
>
> </h2><h3>
> <span>#<?php echo $season. " - ";
> echo $season_episode_number; ?></span>
Are you sure that the variables $episode_name, $season,
$season_episode_number have correct values? What if the episode
selected is in the DB (in table "episode"), but you do not
have any info about the episode cast? You used "INNER JOIN",
so no info about the episode will appear even if it's in the
table.
> </h3>
> </div>
> <?php
> echo $content;
>
> include'library/footer.php' ;
> ?>
If you are not using any templates, then I think you should change
your code to something like this:
<?php
error_reporting( E_ALL );
set_magic_quotes_runtime( 0 );
include 'library/header.php';
flush();
// open the connection
$conn = mysql_connect('localhost', 'user', 'password');
if (!$conn)
die( 'Error connecting to SQL server: ' . mysql_error() );
// pick the database to use
if (!mysql_select_db('DB_name',$conn))
die( 'Error selecting database: ' . mysql_error() );
$epid = @intval( $_REQUEST['id'] );
//query mysql for episode data
$result = mysql_query(
'SELECT name, season, season_number ' .
'FROM episode ' .
'WHERE episode_number = ' . $epid
);
if (!$result)
die( 'Error selecting episode data from database: ' . mysql_error() );
$episode = mysql_fetch_array($result, MYSQL_ASSOC);
if (!$episode)
die( 'The selected episode does not exist.' );
// apply "htmlspecialchars" function to all field values
$episode = array_map( 'htmlspecialchars', $episode );
// output the episode data
?>
<div id="episode_head">
<h2><?php echo $episode['name']; ?></h2>
<h3>#<?php echo $episode['season'], ' - ', $episode['season_number']; ?></h3>
</div>
<?php
flush();
//query mysql for episode cast
$result = mysql_query(
'SELECT ' .
' e.first_name, ' . // 1
' e.last_name, ' . // 2
' e.full_name, ' . // 3
' e.height_feet, ' . // 4
' e.height_inches, ' . // 5
' e.weight, ' . // 6
' e.color_eyes, ' . // 7
' e.color_hair, ' . // 8
' e.bio, ' . // 9
' a.first_name, ' . // 10
' a.last_name, ' . // 11
' a.full_name, ' . // 12
' a.height_feet, ' . // 13
' a.height_inches, ' . // 14
' a.birthdate, ' . // 15
' a.birth_place, ' . // 16
' a.maraital_status, ' . // 17
' a.film, ' . // 18
' a.tv, ' . // 19
' a.bio, ' . // 20
' a.picture ' . //21
'FROM ' .
' actor a INNER JOIN ' .
' episode_cast e ON e.id = a.id ' .
'WHERE e.episode_number = ' . $epid
);
if (!$result)
die( 'Error selecting episode cast from database: ' . mysql_error() );
// there'll be row number under '22'
$cast_pattern = <<<EOD
<div class="character">
<h3>%22\$u, %1\$s %2\$s</h3>
<a href="%21\$s"><img src="%21\$s" alt="%10\$s %11\$s" /></a>
<ul>
<li>Full name: %3\$s</li>
<li>Height: %4\$s%5\$s</li>
<li>Weight: %6\$s</li>
<li>Eyes: %7\$s</li>
<li>Hair: %8\$s</li>
</ul>
<p>%9\$s</p>
<ul>
<li>Actor: %12\$s</li>
<li>Height: %13\$s%14\$s</li>
<li>Birthdate: %15\$s %16\$s</li>
<li>Marital Status: %17\$s</li>
<li>Filmography: %18\$s</li>
<li>TV: %19\$s</li>
</ul>
<p>%20\$s</p>
</div>
EOD;
$i = 1;
while($row = mysql_fetch_row($result))
{
// apply "htmlspecialchars" function to all field values
$row = array_map( 'htmlspecialchars', $row );
// insert pattern as the zero element
array_unshift( $row, $cast_pattern );
// append $i as the 22 element
$row[22] = $i++;
// call 'printf' with $cast_pattern as pattern and database row as values
call_user_func_array( 'printf', $row );
flush();
}
mysql_close($conn);
include 'library/footer.php';
?>
This script is not tested and will probably need colum names
(in the SELECT statements) adjusted, but should work in general.
Hilarion
Navigation:
[Reply to this message]
|