Display Images from Database Issue

[Ben2]

Hi guys its me again,

 

I am having a problem that I cant figure out...

 

Here is my code:

<?php 
				$sqlCommand = "SELECT image FROM background"; 
				$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error()); 

				$sqlCommand2 = "SELECT backgroundimage FROM site"; 
				$query2 = mysqli_query($myConnection, $sqlCommand2) or die (mysqli_error()); 

				while ($row = mysqli_fetch_array($query))
				{
					while ($row2 = mysqli_fetch_array($query2))
					{
						if($row['image'] == $row2['backgroundimage']){
							echo '<img src="site_background/'.$row['image'].'" width="75px" height="75px" style="border:2px solid red;" /><br /><br />';
						}
						if($row['image'] != $row2['backgroundimage']){
							echo '<img src="site_background/'.$row['image'].'" width="50px" height="50px" style="border:2px solid black;" />';
						}
					}
				}
				mysqli_free_result($query);
				mysqli_free_result($query2);
			?>	

 

What is will do is get the images from the "backgrounds" table and the image from the "site" table (the current image). I am then wanting to pick out the current image and give it a red border and then display the other left over images smaller with a black border.

 

I can get the images to all display with the black border or the current image to display with a red border but the other images dont show... I have tried mixing things around but I have not been able to get all the images to display with the formatting I want.

 

I dont know if it is a simple syntax error or I am doing things completely wrong... I have been looking at it for so long its just become one big mess of code to me lol

 

Any help to get this working as I want would be great!

 

Cheers

Ben

[joel24]

you should try and use one query with a join if possible??

 

if you have a look in the generated pages source code, I dare say only one iteration of your while loop is executing? I'm not 100% sure on this, but to execute the code the way you've set it out, you would have to call the 2nd query for each iteration of the while loop... bad for performance though, use a join if you can

<?php 

$sqlCommand = "SELECT image FROM background"; 

$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error()); 

while ($row = mysqli_fetch_array($query))
{

$sqlCommand2 = "SELECT backgroundimage FROM site"; 
$query2 = mysqli_query($myConnection, $sqlCommand2) or die (mysqli_error()); 

while ($row2 = mysqli_fetch_array($query2))

{

if($row['image'] == $row2['backgroundimage']){

echo '<img src="site_background/'.$row['image'].'" width="75px" height="75px" style="border:2px solid red;" /><br />';

}


if($row['image'] != $row2['backgroundimage']){

echo '<img src="site_background/'.$row['image'].'" width="75px" height="75px" style="border:2px solid black;" /><br />';

}



}


}


mysqli_free_result($query);



mysqli_free_result($query2);

?>

[Ben2]

Thank you for the very quick reply,

 

The code you provided works great but I will look into using a join as you mentioned to keep the performance as best as possible.

 

Once again thanks for the advice.

 

Cheers

Ben

[Ben2]

I dont mean to be cheeky but I thought Id ask a quick question while we are on this topic...

 

When I have got the code we have talked about sorted (done using a join) I want to make the image clickable so that when one of the smaller black images are clicked it then updates the "site" table so that "image" changes from the current image to the image that has been clicked on.

 

Do you happen to know any easy way of doing this?

 

Cheers

Ben

[joel24]

to have a JOIN you need to associate the two tables through a primary key and a foreign key...

google 'joining mysql tables'

what are the table columns in both the `site` and `background` tables? what are the primary keys etc

a join would be like so... say you have two tables, user & company. user has columns userID, username, password, email, companyID  AND

company has columns companyID, companyName, backgroundImage

You can see in the user table, companyID is a foreign key which links the user to a company

your join would be like

SELECT u.*,c.* FROM user u JOIN company c ON u.companyID = c.companyID

that will return the user's name, pwd, email AND the companyName, background image etc

 

and as for the second question, yes you can do this. You will need to either make the red image a link/anchor

echo "<a href='yourPage.php?image={$row['image']}><img src='theImageFromDatabase.jpg'/></a>"

Then have a bit in the page like

if (isset($_GET['image'])) {
$background_image=$_GET['image'];
}

 

you can use jquery/ajax to load this a bit nicer, though for now just try the aforementioned

[Ben2]

Hi Joe,

 

After having a look around my database I decided that it would be much simplier to simply modify my tables so that all the information needed for the backgrounds is in one place. After doing this I have been able to simplify the code that we discussed to display the images how I want. Below is the code I am now using:

 

<?php 
				$sqlCommand = "SELECT image, current, id FROM background ORDER BY id"; 
				$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error()); 
				while ($row = mysqli_fetch_array($query))
				{
						if($row['current'] == "1")
						{
							echo '<img src="site_background/'.$row['image'].'" width="75px" height="75px" style="border:2px solid red;" /><br /><br />';
						}
						if($row['current'] == "0")
						{
							echo '<img src="site_background/'.$row['image'].'" width="50px" height="50px" style="border:2px solid black;" />  ';
						}
				}
				mysqli_free_result($query);
			?>	

 

As for the other part... I havent tested this but does this look like it might do the job?

 

Show Images:

					$sqlCommand = "SELECT image, current, id FROM background ORDER BY id"; 
				$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error()); 
				while ($row = mysqli_fetch_array($query))
				{
						if($row['current'] == "1")
						{
							echo '<img src="site_background/'.$row['image'].'" width="75px" height="75px" style="border:2px solid red;" /><br /><br />';
						}
						if($row['current'] == "0")
						{
							echo '<a href="thispage.php?changeimage='.$row['image'].'"><img src="site_background/'.$row['image'].'" width="50px" height="50px" style="border:2px solid black;"/><a/>  ';
						}
				}
				mysqli_free_result($query);

 

Get Image to change:

					if (isset($_GET['changeimage'])) {
					$background_image = $_GET['changeimage'];
					$query = mysqli_query($myConnection, "UPDATE background SET current='0' WHERE current='1'") or die (mysqli_error($myConnection));
					$query = mysqli_query($myConnection, "UPDATE background SET current='1' WHERE image=$background_image") or die (mysqli_error($myConnection));

				}

[Ben2]

Hello again,

 

I have managed to test some code and I have got it working beautifuly, Thank you very much for your support Joe!

 

Just incase you are curious to what code I have ended up with here it is:

 

if (isset($_GET['changeimage'])) {
$background_image = $_GET['changeimage'];
$query = mysqli_query($myConnection, "UPDATE background SET current='0' WHERE current='1'") or die (mysqli_error($myConnection));
$query = mysqli_query($myConnection, "UPDATE background SET current='1' WHERE image='$background_image'") or die (mysqli_error($myConnection));
}


$sqlCommand = "SELECT image, current, id FROM background ORDER BY current DESC"; 
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error()); 

while ($row = mysqli_fetch_array($query))
{
if($row['current'] == "1")
{
	echo '<img src="site_background/'.$row['image'].'" width="75px" height="75px" style="border:2px solid red;" /><br /><br />';
}
if($row['current'] == "0")
{
	echo '<a href="sitebackground.php?changeimage='.$row['image'].'"><img src="site_background/'.$row['image'].'" width="50px" height="50px" style="border:2px solid black;"/> </a>  ';
}
}

mysqli_free_result($query);	

 

Cheers

Ben