|
|
Posted by Rincewind on 10/14/08 11:28
On Tue, 4 Oct 2005 19:18:06 +0200, Hilarion wrote:
> 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() );
OK
>> // 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() );
OK
>> $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'] );
Thanks for that the value is from a pre-coded url
>> //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() );
what is the difference between that and "echo mysql_error();" which I only
put there for testing and was going to remove?
>> // creates the page
>
> Here we initialize a variable which we'll use to get the numbers:
> $i = 1;
OK
>> 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).
>
Yes it is a bit of a mess isn't it, for some reason it got out of hand,
probably because in trying to get it to work, this way did, so I stayed
with it. Would using the associative indices be a better or quicker way to
do it.
>> $content .= "<div class=\"character\">
>> <h3><span>*This is where I want the Number*</span>
>
> $content .= "<div class=\"character\">
> <h3><span>" . ($i++) . "</span>
Sorry this just outputs . (1++) . to the page for each record.
>> $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.
>
Ok will do.
>> }
>> $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.
>
Yes the data does have slashes, I'll have to change the data:-(
>> 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.
>
Left over from when I was trying to get it to work.
>> 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).
>
Yes it is the HTML for the start of the page, I don't want to include it
here because if I have to make changes I only have to make them to 1 page.
>> ?>
>> <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.
>
I'm not quite sure what you mean but the data that is output to the page is
correct for the 21 episodes that I have tested so far.
>> </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:
> <snipped code>
I'm giving that a go at the moment, thanks for the help.
Navigation:
[Reply to this message]
|