Help looping through results and building links

PHP
1

I want to add links to Title, name, and format. I was able to add links to all three but when I click on them they only open the page linked to format when I want the links for Title and name to point to separate pages. Can you tell me where my error is? Here is part of the code for index.php:

try
{
  $sql = 'SELECT titles.id, 
  titles.Title, 
  directors.name,
  titles.link, 
  directors.link, 
  premieres.date, 
  availability.format, 
  availabilitylinks.link 
  FROM titles 
  INNER JOIN directors 
  ON directorid = directors.id
  INNER JOIN premieres 
  ON dateid = premieres.id
  INNER JOIN availability 
  ON availabilityid = availability.id
  INNER JOIN availabilitylinks
  ON availabilitylinkid = availabilitylinks.id';
  $result = $pdo->query($sql);
}
catch (PDOException $e)
{
  $error = 'Error fetching titles: ' . $e->getMessage();
  include 'error.html.php';
  exit();
}

foreach ($result as $row)
{
$titles[] = array(
'id' => $row['id'],
'text' => $row['Title'],
'titles.link' => $row['link'],
'name' => $row['name'],
'directors.link' => $row['link'],
'date' => $row['date'],
'format' => $row['format'],
'availabilitylinks.link' => $row['link']   
); 

}

include 'films.html.php';

And here is part of films.html.php:

<?php foreach ($titles as $film): ?>
      <form action="?deletefilm" method="post">
        <blockquote>
          <p>
            <a href="<?php
            echo htmlspecialchars($film['titles.link'], ENT_QUOTES,
                'UTF-8'); ?>"><?php
            echo htmlspecialchars($film['text'], ENT_QUOTES, 'UTF-8'); ?></a>
            <input type="hidden" name="id" value="<?php echo $film['id']; ?>">
            <input type="submit" value="Delete">
            (by <a href="<?php
            echo htmlspecialchars($film['directors.link'], ENT_QUOTES,
                'UTF-8'); ?>"><?php
            echo htmlspecialchars($film['name'], ENT_QUOTES,
                'UTF-8'); ?></a>) &nbsp; Premiered <?php
            echo htmlspecialchars($film['date'], ENT_QUOTES,
                'UTF-8'); ?> &nbsp; 
                <a href="<?php
            echo htmlspecialchars($film['availabilitylinks.link'], ENT_QUOTES,
                'UTF-8'); ?>"><?php
            echo htmlspecialchars($film['format'], ENT_QUOTES,
                'UTF-8'); ?></a>
          </p>
2

You should do a print_r of $row to see what data is available. I’m guessing you should qualify all link rows with a special name and then use that name, i.e.


SELECT
   titles.id
 , titles.Title
 , directors.name
 , titles.link [color="red"]AS title_link[/color]
 , directors.link [color="red"]AS directors_link[/color]
 , premieres.date
 , availability.format
 , availabilitylinks.link [color="red"]AS availability_link[/color]
FROM titles
  INNER
   JOIN directors
     ON directorid = directors.id
  INNER
   JOIN premieres 
     ON dateid = premieres.id
  INNER
   JOIN availability 
     ON availabilityid = availability.id
  INNER
   JOIN availabilitylinks
     ON availabilitylinkid = availabilitylinks.id

and then use title_link, directors_link and directors_link in your code. (it’s been a while since I worked with MySQL so I might be off here).

Also, I took the liberty to clean up your code a bit, hope you don’t mind


<?php
function escape($str)
{
    return htmlspecialchars($str, ENT_QUOTES, 'UTF-8');
}
?>

<?php foreach ($titles as $film): ?>
    <blockquote>
        <p>
            <a href="<?php echo escape($film['titles.link']) ?>">
                <?php echo escape($film['text']) ?>
            </a>
            <form action="?deletefilm" method="post">
                <input type="hidden" name="id" value="<?php echo $film['id']; ?>">
                <input type="submit" value="Delete">
            </form>
            (by
                <a href="<?php echo escape($film['directors.link']) ?>">
                    <?php echo escape($film['name']) ?>
                </a>
            )
            Premiered
            <?php echo escape($film['date']) ?>
            <a href="<?php echo escape($film['availabilitylinks.link']) ?>">
                <?php echo escape($film['format']) ?>
            </a>
        </p>
    </blockquote>
<?php endforeach; ?>

The <form> actually must be inside the loop, otherwise you can get very interesting results (deleting movies you didn’t want deleted)

The escape function I defined should be defined globally in your project somewhere, not repeated in each and every template.

3

Thanks! Giving each of the link columns a unique name solved the problem. I didn’t make the other changes you suggested yet because I’m just about to read Chapter 6 of Kevin Yank’s PHP and My SQL which is all about code structure.