Pulling image titles out of a database...

and placing each title below its respective image.

[Patrick Taylor]

I have a MySQL table called "titles" containing two columns: "entry_id" (primary key) and "picture_titles". There are about 50 records - 50 different picture titles each with a unique entry_id. I would like to use PHP/MySQL to place each title below its respective image in a page - the images are arranged using html tables.

I would appreciate some guidance on how to write the query and how to pull out each title for placing below its image. So far, I have:

// Make the query.
$query = "SELECT entry_id, picture_titles FROM titles";

// Run the query.
$result = mysql_query ($query);

if($result){
while ($row = mysql_fetch_array($result)) {

... but I'm struggling to know what to do next. Obviously printing out all the rows isn't much help - I need to extract each unique image title and use a PHP echo statement where the hardcoded text for the title would normally go. If I have 50 images and 50 titles, do I have to make 50 separate queries or is there a better way?

Regards,

Patrick

[Elijah]

Try something like this:
I think you need to include a picture name in the database so that you can associate the title with the picture kinda like this:
¦ entry_id ¦ picture_title ¦ filename ¦
¦ ----1--- ¦ At the beach- ¦ beach.jpg ¦

 // Make the query.
$query = "SELECT entry_id, picture_title, filename FROM titles";
// Run the query.
$result = mysql_query ($query);
if($result){
 echo '<table>';
 while ($row = mysql_fetch_array($result)) { 
 echo '<tr><td><img src="'.$row['filename'].'"><br>';
 echo $row['picture_title'].'</td></tr>';
 }
 echo '</table>';
}

I'll be glad to help you more if you need it,

Elijah

[Patrick Taylor]

Thanks, and well... there's no need for a logical association between the actual image in the page and the written title. You see, all I'm trying to do is extract each picture_title from the column and be able to print it somewhere on the page - it just happens that I want to print it in a place below the image, but it could theoretically be anywhere in the page.

With:

// Fetch all results as array.
if($result){
while ($row = mysql_fetch_array($result)) {
$picturetitles = $row['picture_titles']; // Create picturetitles array.
echo '<p class="text1">' . $picturetitles . '</p>'; // Outputs the whole picturetitles array! - not what I want.
}

echo '<p class="text1">' . $picturetitles . '</p>'; // Outputs last record only! Not what I want either.

... all the picture titles are printed (the first echo) and then just the last of ten (the second echo). I'm assuming $picturetitles is my array and I somehow need to pick out each of the titles from that array and print them somewhere... anywhere, in fact - it just happens to be below each picture.

[Elijah]

Note: I didn't test this code
Maybe you need something like this?

PHP code:

 // Fetch all results as array.
if($result){
while ($row = mysql_fetch_array($result)) {
$picturetitles[] = $row['picture_titles']; // Create picturetitles array.
// echo '<p class="text1">' . $picturetitles . '</p>'; // Outputs the whole picturetitles array! - not what I want.
}
// echo '<p class="text1">' . $picturetitles . '</p>'; // Outputs last record only! Not what I want either. 
// Now you can print any of the picture titles by doing this:
echo $picturetitles[0]; // The first picture title
echo $picturetitles[1]; // The second picture title
echo $picturetitles[49]; // The 50th picture title

Is that what you want?
Using this method you would have to know exactly how many picture titles that you are going to use.

Elijah

[Patrick Taylor]

I've tried what you suggested:

// Now you can print any of the picture titles by doing this:
echo $picturetitles[0]; // The first picture title
echo $picturetitles[1]; // The second picture title

The first echo prints the last of ten picture titles in the column, not the first one. The second echo prints the first 2 letters of title number ten!

This would be funny if it wasn't so frustrating.

[Patrick Taylor]

No, sorry... The first echo prints the first letter of picture title #10 - there are ten titles in the column. The second echo prints the second letter of title #10.

[willybfriendly]

I would suggest a bit different DB structure. Try a field for picID, picTitle, picName.

Then you can run your query like:

$q = "SELECT * FROM pics WHERE picID = '$picID';

This will return an array containing everything in that row. So:

extract($row);

And then:
<table>
<tr><td><img alt="<? echo $picTitle;?>" src=" images/<? echo $picName;?>"></td></tr>
<tr><td><? echo $picTitle;?></td></tr>

This stores your actual images outside the DB, but allows you to access them dynamically, and it keeps everything related to the images in one table. You can cross reference the images from another table by using the picID field (which is an index). Or, you can add another field to the pics table for product ID and such.

WBF

[Elijah]

Does this work for you?
Mysql Code:

# phpMyAdmin SQL Dump
# version 2.5.6
#
# Host: localhost
# Generation Time: Apr 25, 2004 at 09:25 PM
# Server version: 3.23.54
# PHP Version: 4.2.2
# 
#
# Table structure for table `titles2`
#
CREATE TABLE `titles2` (
 `id` int(11) NOT NULL auto_increment,
 `picture_titles` varchar(255) NOT NULL default '',
 PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;
#
# Dumping data for table `titles2`
#
INSERT INTO `titles2` VALUES (1, 'title 1');
INSERT INTO `titles2` VALUES (2, 'title2');

Php Code:

 // Make the query.
$query = "SELECT id, picture_titles FROM titles2";
// Run the query.
$result = mysql_query ($query); 
 // Fetch all results as array.
if($result){
while ($row = mysql_fetch_array($result)) {
$picturetitles[] = $row['picture_titles']; // Create picturetitles array.
// echo '<p class="text1">' . $picturetitles . '</p>'; // Outputs the whole picturetitles array! - not what I want.
}
// echo '<p class="text1">' . $picturetitles . '</p>'; // Outputs last record only! Not what I want either.
// Now you can print any of the picture titles by doing this:
echo $picturetitles[0]; // The first picture title
echo $picturetitles[1]; // The second picture title
}

It works fine for me and prints out "title 1title2"

Does it work for you?
<edit>Fixed bug in code. Sorry, please copy again if you already tried it.</edit>

[Patrick Taylor]

I have it working now. The query is:

// Make the query.
$query = "SELECT picture_titles FROM titles";

// Run the query.
$result = mysql_query ($query);

// Fetch all results as array.
if($result){
while ($row = mysql_fetch_array($result)) {
$picturetitles[] = $row['picture_titles']; // Create picturetitles array.
}
}

... and the output is:

<?php echo $picturetitles[0];?>

or:

<?php echo $picturetitles[7];?>

... which can now be placed anywhere in the page (and I used the existing table). I'm not sure what I was doing wrong before, but it works now, and I've learnt something in the process. Thanks very much for your help.

As I said, there's really no logical connection between the image and the title, so there's no reference needed to the image filename in the MySQL table. It was just a matter of being able to create the array and pull out each individual title for printing somewhere.

Thanks again. People on WW are incredibly helpful.

Patrick