Sql retrieve images from folder

[malc090350]

Hi, new here

Wonder if anyone can help

I am learning sql and have searched for the answer for over a day, with no luck. I found this site and it seems you all help each other.

I have a database called "listing_photo"

In that there is row "photo_listing" (unique property number)
& row "photo_caption" (holds the caption for the photo)

The images are kept in a folder called mysite/photo_big

I would like to show the relevant photos for each particular property. in the property template in my site. Also show the photo caption under each. There are 8 photos for each property.

Can anyone show me the correct way to do this in sql php?

[Atli]

Hey.

I'm not getting how your photos and captions are linked to the listing. Could you maybe post an example of real data from your database?

In general, we just do something like this:

<?php
// Location where the images are stored
$file_path = '/path/to/images/';
 
// Fetch the data for the pictures
$sql = "SELECT `file_name`, `title`, `caption`
        FROM `image_table`
        LIMIT 10";
$result = mysql_query($sql) or trigger_error(mysql_query(), E_USER_ERROR);
 
// Display each picture
while($row = mysql_fetch_assoc($row))
{
    $src = $file_path . $row['file_name'];
 
    echo '<div class="Image">';
    echo "<img src="{$src}" alt="{$row['title']}" title="{$row['title']}"><br>"
    echo "<span>{$row['caption']}</span>";
    echo '</div>';
}
?>

Not sure exactly how well that will fit your particular situation, though.

[malc090350]

Hi Atli

Thanks for your help, really appreciate it!

I have attached a view of the database that accesses the pictures.
This is for a single property 127, to give you an idea.

The pictures are in folder
/holidays/files/photo_big

How would I implement this in your code exactly?

I would like the pictures to display...

Pic1 Pic2
Pic3 Pic4
Pic5 Pic6
etc.

Regards

[malc090350]

Anyone help with this please, its driving me crazy

[Atli]

@malc090350
There are only a few modifications you would have to make to the code I posted above so that it would display the images from your database.

1. On line #3, insert your actual location instead of my example.
2. On line #6, change the SQL query to fit your database. I'm not sure exactly how you intend to filter your results, though. I will leave that to you to decide.
3. On line #14. Assuming your images are saved as their respective photo_id from the database, alter this so that the string compiled uses that field rather than the file_name field I used in my example. You should also add the appropriate image extension.

And that should display them. Now all you need to do is get them to display the way you want.

I would like the pictures to display...

Pic1 Pic2
Pic3 Pic4
Pic5 Pic6
etc.

Again, using the code I posted as a base, you should start by adding the "float: left" CSS style to the "div.Image" class. (You could of course also add it inline, but I wouldn't recommend that.) - That would make the images line up in a horizontal row, one after the other.

Then, to have them drop down to the next "line" every other image, you would have to add a counter to the while loop that goes through the results. Using that counter, add a "<br>" before you print the image every time the counter is an odd number (assuming you start at 0). - You can use the modulus operator (%) to figure that out. (See Arithmetic Operators in the manual.)

<?php
$number = 2;
if($number % 2 == 0) {
    echo "$number is an even number!";
}
else {
    echo "$number is an odd number!";
}
?>

Let us know if you are having any problems with that!

[malc090350]

I have the code below but I am getting the following error...

Parse error: syntax error, unexpected '{', expecting ',' or ';' in /usr/local/home/spainhs/www/holidays/templates/english/public/test.php on line 38

Line 38 is the one in bold...
<Line #21 in the snippet --Atli>

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
 
// Location where the images are stored
$file_path = 'http://www.mysite.com/holidays/files/photo_big/';
 
// Fetch the data for the pictures
$sql = "SELECT `photo_id`, `photo_caption_1` , `photo_listing`
        FROM `listing_photo`
    WHERE `photo_listing`= 127
        LIMIT 10";
$result = mysql_query($sql) or trigger_error(mysql_query(), E_USER_ERROR);
 
// Display each picture
while($row = mysql_fetch_assoc($row))
{
    $src = $file_path . $row['photo_id'];
 
    echo '<div class="Image">';
    echo "<img src="{$src}" alt="{$row['photo_caption_1']}" title="{$row['photo_caption_1']}"><br>";
    echo "<span>{$row['photo_caption-1']}</span>";
    echo '</div>';
}
?>

Just to clarify
listing_photo (is the database)
photo_id (is = to the image file name (i.e. 120.jpg in folder http://www.mysite.com/holidays/files/photo_big/)
photo_caption_1 (is the image caption)
photo_listing (is the unique property id)
See image in above thread.

I guess this line

$src = $file_path . $row['photo_id'];

should include the file type i.e. .jpg or .gif but I am not sure how.

I am not sure how to implement you suggestion to get the images in 2 columns but for now I will be happy just to get the images to show.

Thanks again for you help.

[Atli]

The error is caused by mismatching quote tags.

That is, when you wrap a string in double-quotes, that string can not contain double-quotes, or they will end the string early. - You can get past this by escaping them (adding a \ in front of them).

// This is wrong. Will cause an error.
$str = "Simon says: "What?"";
 
// It should be.
$str = "Simon says: \"What?"\";
 

Also, note that on line #22, "photo_caption-1" should be "photo_caption_1".

I guess this line
should include the file type i.e. .jpg or .gif but I am not sure how.

You just add it to the end.

$src = $file_path . $row['photo_id'] . ".jpg";

[malc090350]

That is, when you wrap a string in double-quotes, that string can not contain double-quotes, or they will end the string early. - You can get past this by escaping them (adding a \ in front of them).

So how would this line be displayed?

echo "<img src="{$src}" alt="{$row['photo_caption_1']}" title="{$row['photo_caption_1']}"><br>";

[Atli]

You would prefix any double quote that is meant to be a part of the string with a \ (back-slash).

[malc090350]

I have got this far and it displays the images in 1 column...

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
 
// Location where the images are stored
$file_path = 'http://www.mysite.com/holidays/files/photo_big/';
 
// Fetch the data for the pictures
$sql = "SELECT `photo_id`, `photo_caption_1` , `photo_listing`
        FROM `listing_photo`
        WHERE `photo_listing`= 127
        LIMIT 10";
$result = mysql_query($sql) or trigger_error(mysql_query(), E_USER_ERROR);
 
// Display each picture
while($row = mysql_fetch_assoc($result)){
    $src = $file_path . $row['photo_id'] . ".jpg";
    $number = 2;
if($number % 2 == 0) {
    echo '$number';
}
else {
    echo '$number';
}
?>
 
    <div class="Image">
    <img src="<?=$src?>" alt="<?=$row['photo_caption_1']?>" title="<?=$row['photo_caption_1']?>"><br>
    <span><?=$row['photo_caption_1']?></span>
    </div>
 
<?php } ?>

Not sure how to fix this...

$number = 2;
if($number % 2 == 0) {
    echo '$number';
}
else {
    echo '$number';
}

Results http://www.spain-holiday-sun.com/hol...ublic/test.php

[Atli]

Ok. That works to :)

Not sure how to fix this...

The idea there is to create a counter, to count the number of times the while loop executes. So you would have to create a variable outside the loop, and then increment it inside the loop.

<?php
$i = 0;
while($something == true) {
    $i++;
    echo $i;
}
?>

This would keep looping while that $something remained true, incrementing $i by one each loop.

Then you could use the modulus (%) to calculate whether or not the number in $i is even or odd, and based on that print a <br> to add a line.

O, and you would also need to add the CSS float: left; to your .Image class (or add it to the <div> style attribute) so the images line up next to each other, rather than below each other.

[malc090350]

Thanks Alti

But could you show me an exact example for my code above to show 2 columns