Embedding MySQL into HTML hyperlink using PHP

Question

I'm pretty new to web development and teaching myself from scratch. I'm running through a few drills to teach myself and get myself familiar with PHP and MySQL as languages and using a simple sweetshop as an example. I want to produce a page which simply lists the names of my sweets and hyperlinks to a new page which tells you more info. I've written the following code from scratch and it makes sense in my head, but there must be an error hidden in there. Would massively appreciate it if anyone could spot my mistake and/or give any tips for what to look out for in the future. Thanks!

<?php //sweetshop.php
    require_once 'login.php';
    $conn = new mysqli($hn, $un, $pw, $db);
    if ($conn->connect_error) die("Error Sweetshop.");
    
    $query = "SELECT * FROM products";
    $result = $conn->query($query);
    if (!$result) die("Fatal Error");
    
    $rows = $result->num_rows;
    
    for ($j = 0; $j < $rows; ++$j)
    {
        $row = $result->fetch_array(MYSQLI_ASSOC);
        
        ?><a href="product?product_id=<?php echo htmlspecialchars($row['product_id'])?>"><?php echo htmlspecialchars($row['sweet'])?></a><br/><?
    }
    
    $result->close();
    $conn->close();
?>

First error is `new mysqli`. You'd be much better off using PDO, if not a real database library. Second error is going to be doing `$query = "SELECT * FROM products WHERE product_id=" . $_GET["product_id"];` so don't do it! — miken32, Dec 12 '20 at 20:20

oguzhancerit · Accepted Answer · 2020-12-12T19:05:17.407

I just add easy to use fetch and printing option to your snippet and it works. In addition, using die or try/catch to handle your MySQLi and PHP errors is hard to understand what's really going on there. Therefore configuring PHP and MySQLi to report errors is essential while you are developing a project.

You can open error reporting in PHP and MySQLi using below lines, top of the your PHP file.

ini_set('display_errors', 1);
error_reporting(E_ALL);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

These lines print all errors like; Uncaught mysqli_sql_exception: Table 'tevrakdb.prod5uct' doesn't exist in /var/.. or Fatal error: Uncaught mysqli_sql_exception: Access denied for user 'randomuser'@.... For a better understanding of PHP and MYSQL errors, you can look here.

Here is your enhanced snippet.

<?php 
    require_once 'login.php';

    ini_set('display_errors', 1); //display errors on the screen
    error_reporting(E_ALL); // All errors and warning
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); //Report errors and warnings from mysqli function calls

    $conn   = new mysqli($hn, $un, $pw, $db);
    $query  = "SELECT * FROM products";

    $result = $conn->query($query);

    if ($result->num_rows > 0) {
      while($row = $result->fetch_assoc()) { ?>
        <a href="product?product_id=<?php echo htmlspecialchars($row['product_id']); ?>">
            <?php echo htmlspecialchars($row['product_name_lang_1']); ?>        
        </a><br/>
        <?php
      }
    }

    $result->close();
    $conn->close();
?>

By the way, I recommend that do not forget the semicolon end of the lines in inline php codes.

Thank you for you comment @Dharman I was already editing my answer the way you say. I will publish it in a short time. — oguzhancerit, Dec 12 '20 at 16:09
@Dharman I tried this snippet with some records my sample database. 5 records with different charachters and one sample failed while printing with this function. Just try string that i gave in my answer and give it to htmlspecialschars. — oguzhancerit, Dec 12 '20 at 17:12

Embedding MySQL into HTML hyperlink using PHP

1 Answers1