PHP MySQL fetch result and display

Question

Trying to learn some more PHP and MySQL here now, and I'm having problems understanding how this works. I do know other languages, so I'm hoping I will understand PHP much better if I just can get some help with this example:

I have a table called files. This contains 3 rows (3 files), where I've defined fileID, thumbURL, fullRUL, and stuff like that.

on my webpage I want to fetch this whole list of items/files, and do a for loop to display them all.

This is my PHP code, which of course repeats itself 3 times but it displays the same information, because it only uses the same row:

    <div id="contentWrapper">
    <?php
    for($i = 0; $i < $numItems; $i++){
        echo "<div id='contentItem'>";
        echo "<a href='".$row['fullURL']."' title='".$row['description']."'><img src='". $row['thumbURL']."' width='200px' height='150px' /></a>";
        echo "</div>";
    }
    ?>
</div>

This is the code where I get the data from the database:

    <?php
$db = mysql_connect("localhost", "xxxxx", "xxxxx");
mysql_select_db("login",$db) or die("Kan ikke koble til databasen");

$filesSQL = mysql_query("SELECT * FROM files");
$numItems = mysql_num_rows($filesSQL);

$sql="SELECT * FROM `files` WHERE fileID=1";
$result=mysql_query($sql);

if (false === $result) {
echo mysql_error();
}

$row=mysql_fetch_array($result, MYSQL_BOTH);
?>

The current example here does only fetch the firsst row and stores that in an array, so thats why I get the same values on all 3 items.

But I've tried using $filesSQL[$i]['thumbURL'] and other similar methodes, but I cant get it to work.

I guess this is a very basic question, so I'm hoping for an answer that will help me understand how to work with databases, arrays and displaying it.

I can easily do this in Actionscript3 or other languages, but not in php! =S

Thanks! =D

Don't use mysql_* functions : [Read more...](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — DarkBee, Aug 09 '13 at 10:21

score 2 · Answer 1 · edited May 23 '17 at 12:05

Here is how it have to be

<?php
// connect (better to be moved into included file)
$dsn = "mysql:host=localhost;dbname=login;charset=utf8";
$opt = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$db = new PDO($dsn,"xxxxx", "xxxxx", $opt);

// getting files
$stmt = $db->query("SELECT * FROM files");
$files = $stmt->fetchAll();

//output
?>
<div id="contentWrapper">
<?php foreach($files as $row): ?>
    <div id='contentItem'>
        <a href="<?=$row['fullURL']?>" title="<?=$row['description']?>">
            <img src="<?=$row['thumbURL']?>" width='200px' height='150px' />
        </a>
    </div>
<?php endforeach ?>
</div>

Learn more on PDO

Matt · Accepted Answer · 2013-08-09T11:04:03.323

Instead of your for loop, try using:

while($row = mysql_fetch_assoc($result)) {
       echo "<div id='contentItem'>";
       echo "<a href='".$row['fullURL']."' title='".$row['description']."'><img src='". $row['thumbURL']."' width='200px' height='150px' /></a>";
       echo "</div>";
}

In this case, mysql_fetch_assoc is doing basically the same thing as mysql_fetch_array($result, MYSQL_BOTH) without the numeric keys. It's up to you which you use there really.

For future database interactions, though, I'd recommend reading into either MySQLi or PDO as the mysql prefix and functions using it are now deprecated.

Edit:

As requested by a few users, an example using the updated MySQLi functions instead:

<?php
$db = new mysqli('localhost', 'user', 'pass', 'db');
if($db->connect_error) {
    die("Connection error: ".$db->connect_error);
}
$filesSQL = $db->query("SELECT * FROM files");
$numItems = $db->num_rows; //this can also be done procedurally as mysqli_num_rows($filesSQL)
$sql = $db->query("SELECT * FROM files WHERE fileID = 1");
if(!$sql) {
    echo $db->error;
}
//personally, if I wanted an SQL error after a query I would use:
$sql = $db->query('query') or die($db->error);

//then to follow, your while loop becomes
while($row = mysqli_fetch_assoc($sql)) { //can also be done with object as $row = $sql->fetch_assoc
    //do your loop
}

Sadly, I'm not aware of PHP templates as suggested by Your Common Sense in the comments, so examples for that are welcome!

Thanks, will try this. However, as DarkBee said, I would like to learn this the proper way, and if mysql_ functions are outdated, what should I use instead? =) — Stian Berg Larsen, Aug 09 '13 at 10:26
I put links to the 2 successors to `mysql_` in my answer, MySQLi and PDO. I personally use MySQLi for most things these days, the syntax is very similar. Your connection just becomes `$sql = new mysqli($host, $user, $pass, $db);`, queries are as easy as `$result = $sql->query($query);` :) — Matt, Aug 09 '13 at 10:30
This answer is bad for two reasons: it encourage outdated library usage (an example outweigh a 1000 links) and ugly HTML/PHP mess instead of using templates and logic separation. Practically throws the OP back in PHP stone age. — Your Common Sense, Aug 09 '13 at 10:39
@YourCommonSense, I've updated the answer with a MySQLi example as well, however an example on PHP templating as you suggested is welcome. Thanks! — Matt, Aug 09 '13 at 11:05

PHP MySQL fetch result and display

2 Answers2