Displaying Image from a Multiple Tables Select Query.

[drayarms]

Below is a page which is supposed to output the name, blog contribution and picture of contributing members of a website.

<div id="blog_content" class="" style="height:90%; width:97%; border:5px solid #c0c0c0; background-color: #FFFFFF;"> <!--opens blog content--> 



                             
<?php


//address error handling

ini_set ('display_errors', 1);
error_reporting (E_ALL & ~E_NOTICE);



//include the config file
require_once("config.php");



//Define the query. Select all rows from firstname column in members table, title column in blogs table,and entry column in blogs table, sorting in ascneding order by the title entry, knowing that the id column in mebers table is the same as the id column in blogs table.


$sql = "SELECT


    blogs.title,blogs.entry,members.firstname,images.image
FROM
    blogs
LEFT JOIN
    members
ON
    blogs.member_id = members.member_id
LEFT JOIN
    images
ON
    blogs.member_id = images.member_id

ORDER BY
     blogs.title ASC

"; 

$query = mysql_query($sql);



			                 if($query !== false && mysql_num_rows($query) > 0)
			                 {
				         while(($row = mysql_fetch_assoc($query)) !== false)
				         {
					 echo

                
                              '<div id="blog_content1" style="float:left; position:relative;bottom:18px;left:13px; background-color: #FFFFFF; height:16.7%; width:100%; border:0px none none;"                              
                              <!--opens blog_content1  same as main center top 1 and 2 from index page everything scaled down by a factor of 3, heightwise--> 
        

                                   <div class="red_bar" style="height:3%; width:100%; border:1px solid #959595;"> <!--a--> 

                                       <div class="shade1" style="height:5px; width:100%; border:0px none none;"> </div>                                      
                                       <div class="shade2" style="height:5px; width:100%; border:0px none none"> </div> 
                                       <div class="shade3" style="height:5px%; width:100%; border:0px none none"> </div>


                                   </div> <!-- closes red bar--> 



                                   <div class="content" style="height:28.3%; width:100%; border:0px none none;"> <!----> 

                                       
                                           <div class="slideshow" id="keylin" style="float:left; width:20%;  border:0px none none;"> <!--a-->

                                                  
                                                  

                                                 <div><img header("Content-type: image/jpeg"); name="" alt="" id="" height="105" width="105" src="$row[image]" /></div>                              


                                       
                                           </div> <!-- closes pic--> 



                                           <div class="content_text"  style="float:right;  position:relative;top:7px;left:0px; max-height:150px; width:78.5%; border-width:4.5px; border-bottom-style:solid; border-right-style:solid; border-color:#c0c0c0; "> <!--a-->'; 

                                           

                                               
                                                echo "<h3>".$row['title']."</h3>";
					echo "<p>" .$row['entry']."<br />".$row['firstname']."</p>";
				         
                                             
                                                                                           
                                           echo                                         
                                           '</div> <!-- closes content text--> 

  
                                   </div> <!-- closes content--> 

                                   
                                                                                                                                                                                                                            
                            </div> <!-- closes blog_content1-->';



                                                 }
			                 }
			                 else if($query == false)
			                 {
			               	 echo "<p>Query was not successful because:<strong>".mysql_error()."</strong></p>";
				         echo "<p>The query being run was \"".$sql."\"</p>";
			                 }
			                 else if($query !== false && mysql_num_rows($query) == 0)
			                 {
				         echo "<p>The query returned 0 results.</p>";
			                 }


                                                 mysql_close(); //Close the database connection.





                                                 ?> 

                      
                        </div> <!-- closes blog content-->

 

The select query is designed to retrieve all the blog contributions(represented by the fields blogs.title and blogs.entry) from the database, alongside the contributing member (member.firstname) and the member's picture(images.image), using the member_id column to join the 3 tables involved, and outputs them on the webpage.  The title, entry and firstname values are successfully displayed on the resulting page.  However, I can't seem to figure out how to get the picture to be displayed.  Note that the picture was successfully stored in the database and I was able to view it on a separate page using a simple select query.  It is now just a question of how to get it to display on this particularly crowded page.  Anyone knows how I can output the picture in the img tag?  I tried placing the header("Content-type: image/jpeg"); statement at the top of the php segment, then just right below the select query and finally just right above the img tag, but in every case, I just got a big white blank page starring at me.  How and where should I place the header statement? And what else am I to do to get this picture displayed?  Any help is appreciated.

[BlueSkyIS]

images stored in databases are great fun. not.

 

you'll need a secondary script for the images, and you'll use that script as the SRC for the image tag, for instance:

 

<img src='/image_script.php?img=23'>

 

where image_script.php is the script you'll write and img=23 identifies the record for which you'd like to get the image. so in image_script.php, you will take the img passed, look up the record, pull the image data and THEN use the content-type headers to send the image to the browser.

 

if you stored the images as regular files, you would just echo the image link to the page and be done.

[PFMaBiSmAd]

What happed to the script you were just using in your last thread about this with your picscript.php script that was doing exactly what BlueSkyIS just posted that you need to do?

[drayarms]

@ PFMaBiSmAd

I thought about using picsript like you reccomend

  $image = stripslashes($_REQUEST[imname]);
                             $rs = mysql_query("SELECT* FROM images WHERE member_id = '".$_SESSION['id']."' AND image_cartegory = 'main' ");
                             $row = mysql_fetch_assoc($rs);
                             $imagebytes = $row[image];
                             header("Content-type: image/jpeg");
                             print $imagebytes;

 

my problem is, wouldnt' I be doing a double select query if i used this picsript file?  I have already selected the image in the join query.  Or should I just eliminate the select statement of picscript?

 

[PFMaBiSmAd]

A) You are aware that it is the browser that requests the image, which is why there is an <img > HTML tag in the first place. You must use an <img> tag for each image you want on a web page.

 

B) The picsript code must retrieve the correct image from the database and output it in response to the request the browser makes for the image.

 

However, your SELECT query in the main code should NOT select the image column (as that wastes time and memory retrieving a large amount of data that you cannot even use in the main code) and your SELECT query in the picsript code only needs to select the image column.

 

If you plan to have any image types beside image/jpeg, you should add a 'type' column to your table to store the corresponding Content-type: for each image.

 

Your picsript code also needs to use the $image variable in the query to retrieve the correct image data.

[drayarms]

Ok  PFMaBiSmAd I get everything you are trying to say.  So basically you are advising me to eliminate the section of the main code that selects the image and request the image in the img tag using picscript as the source.  But that raises a new problem.  How should I tweak the select statement in picsript to ensure that the image being selected, corresponds to the member that posted a particular blog entry? Is that even possible?  Is there any way I can use a join statement to link two tables from two separate scripts (in this case picsript and the main code)?

[PFMaBiSmAd]

The URL you put into the src="...." attribute of the <img> tag needs to have a GET parameter on the end of the url that identifies which image the picsript code should retrieve and display. Assuming you have an auto-increment id column in your images table, I would use the id.

[drayarms]

@PFMaBiSmAd  Well I used your latest hint and passed a variable called $picture, into the picsript url, and assigned it the value of the member_id in question.  So I ended up with the following

 

<div><img  name="" alt="" id="" height="105" width="105" src="picscript1.php?$picture = member_id" /></div>

 

Then in the select statement of picsript, I assinged the value $picture to the member_id field ending up with something like this

$image = stripslashes($_REQUEST[picture]);
                             $rs = mysql_query("SELECT* FROM images WHERE member_id = 'picture' AND image_cartegory = 'main' ");
                             $row = mysql_fetch_assoc($rs);
                             $imagebytes = $row[image];
                             header("Content-type: image/jpeg");
                             print $imagebytes;

 

So now, what in your opinion am I doing wrong now? Needless mentioning that didnt't work.

[jay7981]

couldnt you use an INNER JOIN fuction to pull the images ...

 

basically have 2 tables ...

1 the user info

2 the image info

 

since you have the user table already add a field called image.id

 

then in the second table design it like this

ID (auto inc)

USER (the ID of the user from  table 1 would go here to link)

IMAGE (the actual image so this would be set as blob)

IMAGE.NAME (the file name)

 

 

then join the 2 tables in your query using inner join where usertable.image_id is imagetable.id

[optikalefx]

is it outputting anything onto the screen? If you don't have the header, what do you see?

 

this

 

src="picscript1.php?$picture = member_id"

 

doesn't look right.  it would need to be picscript1.php?picture=5  or whatever number.

then you can do $_GET['picture'] in your picscritp1.php file.