[thelist] Using php/mysql to retrieve URL's

[Jon Haworth]

Hi Trev,

> So I'm trying to get a script to retrieve a URL to the image path
> by using the id of the URL in my DB,

Yep, good solution - probably better than cluttering your table with BLOBs
anyway.

How about a table like this:

CREATE TABLE 'images' (
  'id' INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  'filename' VARCHAR(50) NOT NULL,
  'alt' VARCHAR(255) NOT NULL,
  'width' INT NOT NULL,
  'height' INT NOT NULL
);

Then you can generate your links like this:

<?php

// assume db connection is up and running
// assume image id is held in a variable called $id

$path = "/path/to/my/images/";

$s = "SELECT filename, alt, width, height FROM images WHERE id = '". $id.
"'";
$q = mysql_query ($s);
$r = mysql_fetch_array ($q);

?>

<img src="<?=$path . $r["filename"]?>
     alt="<?=$r["alt"]?>
     width="<?=$r["width"]?>
     height="<?=$r["height"]?> />


Storing only the filename in the db and having the folder in the $path
variable means that you can easily move this around, or re-use it for other
sites, with a minimum of changes.

Give us a shout if that wasn't what you were after :-)

Cheers
Jon