Unique page for each row in database with PHP

Question

I have been trying to create a unique page for each row in my database. My plan is to create a dictionary.php?word=title url, where I can display the description and title of that specific ID. My datbase is contains id, term_title and term_description.

I'm fresh outta the owen when it comes to PHP, but I've managed to atleast do this:

<?php
   $servername = "localhost";
   $username = "root";
   $password = "";
   $dbname = "dbname";

   $conn = mysqli_connect($servername, $username, $password, $dbname);

   if (!$conn) {
      die("Cannot connect to database." . mysqli_connect_error());
   }

   if (isset($_GET['id']))
   {
      $id = (int) $_GET['id'];
      $sql = 'SELECT * FROM dbname WHERE id = $id LIMIT 1 ';
   }

$sql = "SELECT * FROM terms";
$result = $conn->query($sql);

mysqli_close($conn);
?>

I'm really stuck and I dont know what the next step is, I've added the <a href='dictionary.php?=".$row["id"]."'> to each word I want to be linked, and this is properly displayed in the main index.php file (where all my words are listed with <li>. This is my code for this:

<?php
   if ($result->num_rows > 0) {
      while($row = $result->fetch_assoc()) {
      echo "<a href='dictionary.php?=".$row["id"]."'><li class='term'><h4 class='term-title'>" . $row["term_title"]. "</h4></li></a>";
   } else {
     echo "No words in database.";
   }
 ?>

How do I create this unique page, only displaying title and description for that id? How do I add ?word= to the url?

Thanks for taking your time to help me.

Answer 1

Update from years later: Please, please use parameters when composing your SQL queries. See Tim Morton's comment.

You're on the right track, and ajhanna88's comment is right, too: you want to be sure to include the right key ("word" in this case) in the URL. Otherwise, you're sending a value without telling the page what that value's for.

I do see a couple other issues:

1. When you click on one of the links you created, you're sending along $_GET["word"] to dictionary.php. In your dictionary.php code, however, you're searching for your word by "id" instead of by "word". I'm guessing you expect users to search your dictionary for something like "celestial" and not "1598", so try this instead:

   if (isset($_GET['word'])) { $word = $_GET['word']; $sql = 'SELECT * FROM dbname WHERE word = $word LIMIT 1 '; }

BUT! Also be aware of a security problem: you were letting the user put whatever they want into your query. Take a look at the classic illustration of SQL injection. To fix that, change the second line above to this:

`$word = $conn->real_escape_string($_GET['word']);`

Another problem? You're looking for the word exactly. Instead, you'll probably want to make it case insensitive, so "Semaphore" still brings up "semaphore". There are plenty of ways to do that. The simplest way in my experience is just changing everything to lowercase before you compare them. So that $word assignment should now look like this:

`$word = $conn->real_escape_string(strtolower($_GET["word"]));`

And your query should look something like this:

`$sql = "SELECT * FROM dbname WHERE word = LOWER('$word') LIMIT 1 ";`

2. Next! Further down, you overwrite your $sql variable with SELECT * FROM terms, which totally undoes your work. It looks like you're trying to show all the words if the user doesn't provide a word to look up. If that's what you're trying to do, put that line in an else statement.

3. Your $result looks fine. Now you just have to use it. The first step there is to do just like you did when you tested the connection query (if(!$conn)...) and check to see that it came back with results.

4. Once you have those results (or that one result, since you have LIMIT 1 in your query), you'll want to display them. This process is exactly what you did when printing the links. It's just that this time, you'll expect to have only one result.

Here's a real basic page I came up with from your code:

<?php

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "dbname";

$conn=new mysqli($servername,$username,$password,$dbname);
if($conn->connect_errno){
    die("Can't connect: ".$conn->connect_error);
}
?>
<!DOCTYPE html>
<html>
    <head>
        <title>Dictionary!</title>
    </head>
    <body>
    <?php
        if(isset($_GET["word"])){
            $word = $conn->real_escape_string(strtolower($_GET["word"]));
            $sql = $conn->query("SELECT * FROM dictionary WHERE word=LOWER('".$word."') LIMIT 1");
            if(!$sql){
                echo "Sorry, something went wrong: ".$conn->error_get_last();
            } else {
                while($row=$sql->fetch_assoc()){
                    echo "<h2>".$row["word"]."</h2>";
                    echo "<p>".$row["definition"]."</p>";
                }
            }
        } else {
            $sql = $conn->query("SELECT word FROM dictionary");
            if(!$sql){
                echo "Sorry, something went wrong: ".$conn->error_get_last();
            } else {
                echo "<p>Here are all our words:</p><ul>";
                while($row=$sql->fetch_assoc()){
                    echo "<li><a href=\"dictionary.php?word=".$row["word"]."\">".$row["word"]."</a></li>";
                }
            }
            echo "</ul>";
        }
    ?>
    </body>
</html>

You should also take care to be consistent in your terminology. For this, my MySQL table had three columns: id, word, and definition. I dropped term since your URLs were using word. In my experience, it's best to keep the same terminology. It avoids confusion when your application gets more complicated.

Lastly, to answer your question about creating separate pages, you can see there that for a simple page like this, you may not need a separate page to display the definitions and the links -- just an if/else statement. If you want to expand what's in those if/else blocks, I'd suggest looking at PHP's include function.

You have a great start. Keep at it!