A little help with some PHP and MySQL

dbmzk1 · 20 Jan 2007 at 09:27

Hey, wonder if anyone can help me...

I have a MySQL database which contains a list of filenames of images and captions that go with them. Using PHP I want to insert the captions as titles in the link of the correct image.

For example, in the db I may have test1.jpg with "This is a test". How can I get it to enter the title in the link using the filename as the reference? I mean I know how to enter the data itself into the page, but I don't know how to make it look at the filename of the link and enter the correct caption from the db.

eg <img src="test1.jpg" title="This is a test" />

Hope you understand what I mean!

Cheers.

Gman · 20 Jan 2007 at 11:27

car't you use something like the following

Code:

$result = mysql_query("SELECT imageURL, imageTitle FROM imageTable");

while($values = mysql_fetch_array($result))
{
     echo "<img src='". $values['imageURL'] . "' title='" . $values['imageTitle'] ."' />";
}

if you need it for a specific image name etc just alter the sql statement with a WHERE clause.

dbmzk1 · 20 Jan 2007 at 19:47

Thanks for that, it gets me one step closer, but it not quite fully what I'm after.

It shouldn't get the filename from the db, thats already there in the html file. What I want it to do is see that filename in the file, check it against the ones in the db and then insert the related caption into the images title attribute.

Cheers.

JonB · 20 Jan 2007 at 20:00

Ok, so set the filename as a variable.

Then using something along the lines of

$result = mysql_query("SELECT imageTitle FROM imageTable WHERE imageURL = $filename");

Then use a where, like the statement below to insert the imageTitle to the html as below.

dbmzk1 · 21 Jan 2007 at 06:41

Hmm I see what you mean, but I can't for the life of me figure out how to actually do it lol

Basically say I have 3 images on a standard page.

1.jpg
2.jpg
3.jpg

Then in the database I have the captions that go with some images. I want the script to look at the html file, see 1.jpg in it, find the caption for that in the database, then stick it in the html file as the images title.

Maybe I should have added I'm pretty new to this stuff!

Gman · 21 Jan 2007 at 11:50

can you post the full coding for the web page AND the layout of your database tables that's holding the info your wanting.

Like we've said IF your database table holds both the filename and the title description it should just be a case of using the code we've posted and sending it the image name in the where clause, then looping through your code and inserting the retrieved title descriptions.

dbmzk1 · 21 Jan 2007 at 20:09

Right now I have a basic page with some images in.

...<body>
<img src="1.jpg" alt="1.jpg" title="" />
<img src="2.jpg" alt="2.jpg" title="" />
<img src="3.jpg" alt="3.jpg" title="" />
<img src="4.jpg" alt="4.jpg" title="" />
</body>...

The db table "files"...

id, fn1 (the filename), tit1 (the title)

Gman · 21 Jan 2007 at 21:25

dbmzk1 said:
Right now I have a basic page with some images in.

The db table "files"...

id, fn1 (the filename), tit1 (the title)

well then whats stopping you from just replacing the img code with what I sugested above , this

Code:

$result = mysql_query("SELECT imageURL, imageTitle FROM imageTable");

while($values = mysql_fetch_array($result))
{
     echo "<img src='". $values['imageURL'] . "' title='" . $values['imageTitle'] ."' />";
}

if your wanting to do it like I think your planning your going to have a database call for every image just to get its title description. It's a lot better to just use the database to populate the image src and the title at the same time.

dbmzk1 · 21 Jan 2007 at 23:35

Not every image will have a caption and in the final version the images will not be put there manually but via a simple gallery script, so I can't have it put the src from the database.

dbmzk1 · 25 Jan 2007 at 22:42

Anyone have any ideas at all please?

joeyjojo · 25 Jan 2007 at 22:57

I see what you mean. I can't code this without doing some work (don't know enough php yet) but I can give you the gist of it so somebody else can code it.

Create an array with the image filenames which you want on the page. You edit this bit to change which images are shown.
Get the images from the database which match the files in the array.
Echo these as you see fit.

You can do the comparison using foreach and some jiggery pokery.

Beansprout · 26 Jan 2007 at 00:39

dbmzk1 said:
Not every image will have a caption and in the final version the images will not be put there manually but via a simple gallery script, so I can't have it put the src from the database.

Add a field to the database called 'Caption' and put a 1 for images with captions and a 0 for images without

Berserker · 26 Jan 2007 at 00:48

Beansprout said:
Add a field to the database called 'Caption' and put a 1 for images with captions and a 0 for images without

Isn't that what NULL values are for? Saves that extra column anyway.

Beansprout · 26 Jan 2007 at 02:23

Berserker said:
Isn't that what NULL values are for? Saves that extra column anyway.

Too true. For some reason I've been liking columns recently o.o

dbmzk1 · 26 Jan 2007 at 02:53

joeyjojo said:
Create an array with the image filenames which you want on the page. You edit this bit to change which images are shown.

Beansprout said:
Add a field to the database called 'Caption' and put a 1 for images with captions and a 0 for images without

I don't want to enter every image (ie those both with and without captions) into the database. Say theres 5 images on a page, only 3 may have a caption. But I don't want to have to enter details for all 5.

joeyjojo · 26 Jan 2007 at 07:36

dbmzk1 said:
I don't want to enter every image (ie those both with and without captions) into the database. Say theres 5 images on a page, only 3 may have a caption. But I don't want to have to enter details for all 5.

Why not?

Beansprout · 26 Jan 2007 at 11:11

dbmzk1 said:
I don't want to enter every image (ie those both with and without captions) into the database. Say theres 5 images on a page, only 3 may have a caption. But I don't want to have to enter details for all 5.

Berserker's way is best [unless you need a column to set whether an image should display or not, in which case you need my idea], and unfortunately if you don't enter every image there's probably no other simple way to do it

robmiller · 26 Jan 2007 at 12:26

Beansprout said:
unless you need a column to set whether an image should display or not, in which case you need my idea

Code:

SELECT image FROM images WHERE image != NULL

Unless I'm misunderstanding...

Beansprout · 26 Jan 2007 at 13:02

robmiller said:
Code:

SELECT image FROM images WHERE image != NULL

Unless I'm misunderstanding...

But if he then wants it to display, he has to enter the info in again if the info is either present or NULL, unless I'm missing something

(change my column name from caption to display)