Display SQL query results

Question

I am having trouble displaying results from a SQL query. I am trying to display all images and prices from a products table.

I am able to display the echo statement "Query works" in the browser. But, the results are not displaying in the browser.

        if ($count > 0) {
            echo "Query works";
        } else {
            echo "Query doesn't work" ."<br/>";
        }

PHP Code:

$con = getConnection();
        $sqlQuery = "SELECT * from Products";

        // Execute Query -----------------------------           
        $result = mysqli_query($con, $sqlQuery);
            if(!$result) {
                echo "Cannot do query" . "<br/>";
                exit;
            }

            $row = mysqli_fetch_row($result);
            $count = $row[0];

            if ($count > 0) {
                echo "Query works";
            } else {
                echo "Query doesn't work" ."<br/>";
            }

          // Display Results -----------------------------

            $num_results = $result->numRows();

            for ($i=0; $i<$num_results; $i++) {
                $row = $result->fetchRow(MDB2_FETCH_ASSOC);
                echo '<img src="'.$row['Image'].'>';
                echo "<br/>" . "Price: " . stripslashes($row['Price']);

}

Screenshot 1
Screenshot 2: removed the images from the database, and used a filepath instead

Screenshot 3: print_r($row)

Answer 1

$row is the first result-row (if any) from your query. $row[0] is the first column in this query (which, since you use select *, depends on the order of the columns in your database). So, whether $row[0] > 0 depends on the content of your database.

Answer 2

I think

$row = mysqli_fetch_row($result);
$count = $row[0];

should be

$count = $result->numRows();
if ($count > 0) {
  echo "Query produced $count rows";
} else {
  echo "Query produced no rows" ."<br/>";
  return;
}

And your for loop should use fetch_assoc as:

while ($row = $result->fetch_assoc()) {
  echo '<img src="'.$row['Image'].'>';
  echo "<br/>" . "Price: " . stripslashes($row['Price']);
}

Answer 3

Mysqli doesn't have fetchRow(), that's part of the Pear::MDB2 library

See the docs: http://www.php.net/manual/en/mysqli-result.fetch-assoc.php

Change your loop to the following:

while ($row = $result->fetch_assoc()) {
    echo '<img src="'.$row['Image'].'>';
    echo "<br/>" . "Price: " . stripslashes($row['Price']);
}

Also, by doing this:

$row = mysqli_fetch_row($result);
$count = $row[0];

before the loop you are essentially skipping the first row and not displaying its image in the loop.

Answer 4

to print all results from a query you can use a while loop

while($row=mysqli_fetch_assoc($result)){
    echo 'Price '.$row['Price'].'<br/>';
}

Answer 5

try

$sqlQuery = "SELECT * from Products";

        // Execute Query -----------------------------           
        $result = mysqli_query($con, $sqlQuery);
            if(!$result) {
                echo "Cannot do query" . "<br/>";
                exit;
            }

            $row = mysqli_fetch_row($result);
            $count = $row[0];

            if ($count > 0) {
                echo "Query works";
            } else {
                echo "Query doesn't work" ."<br/>";
            }

          // Display Results -----------------------------

            $num_results =mysqli_num_rows($result);

            for ($i=0; $i<$num_results; $i++) {
                $row = mysqli_fetch_assoc ($result);
                //print_r($row);
              echo '<img src="'.$row['Image'].'>';
                echo "<br/>" . "Price: " . stripslashes($row['Price']);
            }

Answer 6

It's displaying characters because that is how you have stored the image. In order to show the image you are going to have to draw the image with something like:

echo '<img src="data:image/gif;base64,'.base64_encode($row['Image']).'" />';