PHP SQL query to print results in webpage

Question

I am trying to get my PHP script to print all rows i have in my database in a neat order. Currently Im not getting anything. My table has 4 columns, Name, Address, Long and Lat, and 2 rows with data. The table is called Locations. I am using the following code but im not getting to to work:

<?php

$con=mysqli_connect("localhost","user","pass","db");

if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql = "SELECT * FROM `Locations` ";

if ($result = mysqli_query($con, $sql))
{
 $resultArray = array();
 $tempArray = array();

 while($row = $result->fetch_object())
 {
  $tempArray = $row;
     array_push($resultArray, $tempArray);
 }

 echo json_encode($resultArray);
}

// Close connections
mysqli_close($con);
?>

Are you getting a blank page or what is the output? Maybe json_encode() doesn't work because your $resultArray contains objects rather then arrays because you use $result->fetch_object(). — t.h3ads
– t.h3ads, Commented Jul 22, 2015 at 15:22
just a blank page. i have tried fiddling with the connection and i am getting errors back as soon as i do so i know i have the connection down. the results are all VARCHAR - if that helps? what should i change it to? — Saucepan
– Saucepan, Commented Jul 22, 2015 at 15:23
Try: while($row = $result->fetch_object()) { array_push($resultArray, $row); } — Twisty
– Twisty, Commented Jul 22, 2015 at 15:25
What happens if you try to var_dump($row) inside the while loop? — t.h3ads
– t.h3ads, Commented Jul 22, 2015 at 15:27

Adam Joseph Looze · Accepted Answer · 2015-07-22 15:49:18Z

2

Here is a simple example using pdo instead of mysqli

    $dbHOST = 'localhost';
    $dbNAME = 'nilssoderstrom_';
    $dbUSER = 'nilssoderstrom_';
    $dbPASS = 'Durandal82!';
    $pdo = new PDO('mysql:host=' . $dbHOST . ';dbname=' . $dbNAME, $dbUSER, $dbPASS); // create connection

    $stmt = $pdo->prepare("SELECT Name, Address, Long, Lat FROM Locations");
    //you should never use *, just call each field name you are going to use

    $stmt->execute(); // run the statement
    $arr = $stmt->fetchAll(PDO::FETCH_ASSOC); // fetch the rows and put into associative array

    print_r($arr); // print all array items, unformatted

and you can echo out the data and format it yourself using a for loop like so

    for($i=0; $i<sizeof($arr); $i++) { // this will loop through each row in the database. i prefer this method over while loops as testing has shown this is much faster for large scale tables
        echo 'Name: ' . $arr[$i]['Name'] . '<br />'; // $arr is the array name, $i is the number of the array item, or iterator, ['Name'] is the field name
        echo 'Address: ' . $arr[$i]['Address'] . '<br>';
        echo 'Long: ' . $arr[$i]['Long'] . '<br>';
        echo 'Lat: ' . $arr[$i]['Lat'] . '<br>';
    }

If the names are correct, this would echo out your row ID and row CITY. Just change the names to your field names. If you want further assistance, feel free to ask.

However, if you want to stick with mysqli, give the following code a wirl.

    $dbHOST = 'localhost';
    $dbNAME = 'nilssoderstrom_';
    $dbUSER = 'nilssoderstrom_';
    $dbPASS = 'Durandal82!';
    $mysqli = mysqli_connect($dbHOST, $dbUSER, $dbPASS, $dbNAME);
    $query = "SELECT Name, Address, Long, Lat FROM Locations";
    $result = mysqli_query($mysqli, $query);

    if($result) {
        while($row = mysqli_fetch_assoc($result)) {
            echo 'Name: ' . $row['Name'] . '<br />';
            echo 'Address: ' . $row['Address'] . '<br>';
            echo 'Long: ' . $row['Long'] . '<br>';
            echo 'Lat: ' . $row['Lat'] . '<br>';    
        }
    }

change fieldname to the field you want to display

EDIT: Paste the following code. It will echo out the number of rows. This will tell you if the query statement is correct.

    $dbHOST = 'localhost';
    $dbNAME = 'nilssoderstrom_';
    $dbUSER = 'nilssoderstrom_';
    $dbPASS = 'Durandal82!';
    $pdo = new PDO('mysql:host=' . $dbHOST . ';dbname=' . $dbNAME, $dbUSER, $dbPASS);

    $stmt = $pdo->query("SELECT Name, Address, Long, Lat FROM Locations");
    echo $stmt->rowCount();

edited Jul 22, 2015 at 15:49

answered Jul 22, 2015 at 15:30

Adam Joseph Looze

1,04110 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Saucepan Over a year ago

thank you for that, however it still gives me a blank page back. i tried removing the ['fieldname'] . '<br>' from the echo statement and then i got ArrayArray back so something is happening...

Adam Joseph Looze Over a year ago

Your Welcome, can you paste your entire code? Also, add this code to your page. error_reporting(E_ALL); ini_set('display_errors', 1); this will print your errors on the page.

Adam Joseph Looze Over a year ago

I have made some changes so you may try copy and pasting the above code now. If it still does not work, there are other issues. Such as., Is the table name Locations correct? it is case sensitive.

Saucepan Over a year ago

I am using the code you gave me just inside a <?php statement. i put the error code snip inside the php statement as well but still blank.

brazilianldsjaguar Over a year ago

If you're still not getting anything printed on screen, I'd add an else{} statement after the if($result), stick in some type of echo there, and see if that's what shows up.

|

Toki · Accepted Answer · 2019-02-10 17:41:43Z

0

Fetch query result as associative array and use for each to print all results

 <?php

$con=mysqli_connect("localhost","user","pass","db");

if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql = "SELECT * FROM `Locations` ";

if ($result = mysqli_query($con, $sql))
{
while($rows = mysqli_fetch_assoc($result)) {     
      foreach($rows as $key => $val)
{
     echo $val;
}      
}
}

mysqli_close($con);
?>

answered Feb 10, 2019 at 17:41

Toki

2944 silver badges14 bronze badges

Collectives™ on Stack Overflow

PHP SQL query to print results in webpage

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related