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

Answer 1

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!

Answer 2

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