PhP mysql - how to show records from database?

Question

This is my first post in this community. I am a new to PHP and MySQL. Here is the code that I am trying to run.

I'm using MAMP. I have tested that I am able to establish connection to the "pet" database. Checked my user name and password, that's correct too. I have data in the database with "horse" in the field "petType".

There is got to be something wrong in the code below. I have scratched my head and looked in books and online, but couldn't figure out what I am doing wrong? With the code below, my webpage comes blank.

I'm trying to display records from the database as the first part and worry about formatting later.

---

<?php

$host = 'localhost';
$username = ' ';
$password = ' ';
$database = 'pet';

$conn = mysqli_connect($host, $username, $password, $database);

$query= "SELECT * FROM pet WHERE petType = 'horse'";

while($row = mysqli_fetch_array($query)):
{   
$petDescription = $query [$petDescription];
$petName = $query [$petName];
$petType = $query [$petType];

echo "$petType <br> <br> $petName <br> $petDescription";

}

?>

Answer 1

Ok, here's the rundown.

• You're not querying. This means that you need to use the mysqli_query() function, which requires a successful DB connection established and passed as the first parameter.

• Then you're assigning $row in the while loop to fetch the array, yet you're using $query as its replacement for the intended rows to be echo'd. It should be $row and not $query.

(Edit) You also have a colon in while($row = mysqli_fetch_array($query)): <= delete it.

Now, it's hard to know for certain as to what your actual column names are in your table, so I'll just replace them with generic column_x, column_y and column_z for now.

You can replace those with the ones that are in your table. More on this a bit further below.

Replace your current block of code with the following:

$query = mysqli_query($conn, "SELECT * FROM pet WHERE petType = 'horse'");

    while($row = mysqli_fetch_array($query))
    {   
        $petType = $row['column_x'];
        $petName = $row['column_y'];
        $petDescription = $row['column_z'];

        echo "$petType <br> <br> $petName <br> $petDescription";

    }

If what your other columns are indeed called petType, petName and petDescription, then you can replace the above with:

$petType = $row['petType'];
$petName = $row['petName'];
$petDescription = $row['petDescription'];

The way it works is this, and I will show you in a graphical way:

$petType = $row['petType'];
    ^        ^      ^
    |        |      | column name in table
    |        |
    |        | variable in loop
    |
    | variable to echo

However, there is something that seems a bit unclear and that both your DB and table hold the same name of pet.

Make sure that is indeed the case.

Add or die(mysqli_error($conn)) to mysqli_query().

Sidenote: Error reporting should only be done in staging, and never production.

---

Footnotes:

• Consider using mysqli with prepared statements, or PDO with prepared statements, they're much safer.

• Otherwise, you will be open to SQL injection.