Skip to main content
N
nicko2
Inspiring
February 19, 2012
Answered

mysql_fetch_assoc Problem!

  • February 19, 2012
  • 1 reply
  • 798 views

Hi All

I'm trying to develop a photo gallery that is categorized simply by year, gallery and photos. ie The ability to be able to create a year, a gallery associated to the year and photos associated to a particular gallery. I've come a little unstuck when tryimg to retreive the information from the database.

There are 3 tables in the database, Year, Gallery and Photos. The year table stores the name of the year, the gallery stores the names of the gallery and the id key of the year table, and the photos table stores details of the photographs and the id key of the gallery table. (please see images below)

The code so far retreives and displays all the available years and when a particular year is selected, the relevant year and associated galleries are displayed, but, and here's the problem, only the photos associated to the first gallery in each year are being retreived and displayed. (see screen shots below)(Left Image: Page displayed :: Center & Right Image; PHP/Mysql Code)

As you can see from the tables above, gallery 4 should display photo 2, I think the problem is with the code circled in the center image in that it's only retreiving the gallery id for one record instead of for all records. I'm sure the solution to this will be quite simple, but I've hit a brick wall with it, so any help or suggestions will be much appreciated.

Many Thanks

Nick

This topic has been closed for replies.
Correct answer bregent

>The code commented 'find gallery id', I used to try and find the gallery

>id's linked to the respective year to enable me to pass the id results

>the array $gallery_id which is required by the code commented 'find gallery2'

The variable $gallery_id is not an array. It is a simple variable holding the value of the gallery_id for a single row. If it were an array, it would not work at all in the SQL statement - SQL does not work with arrays.

If each year has multiple galleries, then you need to loop through all of the rows in $row to output thte galleries, and then build a nested loop inside to output the photo's. That's much more complicated than it needs to be. You can accomplish all of this with a single SQL statment instead of 4, and a single loop, by learning how to join the tables in SQL.

SELECT Gallery.name, Year.year, Photos.caption

FROM

Gallery, Year, Photos

WHERE

Gallery.year_id = Year.year_id AND

Gallery.gallery_id = Photo.gallery_id AND

Year.year  = '2010'

1 reply

B
bregent
Participating Frequently
February 19, 2012

First of all, it's better to post code rather than screen shots as it makes it difficult for us to point out the mistakes.

Your second SQL statement (SELECT * from gallery...) will obviously return a recordset with multiple rows, but you never loop through this recordset so it will only every display the first gallery. You'd need to create a nested loop.

Frankly, it would probably be much simpler to do this entire thing with a single SQL statement and recordset/loop by joining your tables in your SQL SELECT statement.

N
nicko2Author
Inspiring
February 20, 2012

Thanks for the reply, but I don't follow! The code commented 'find gallery id', I used to try and find the gallery id's linked to the respective year to enable me to pass the id results the array $gallery_id which is required by the code commented 'find gallery2'. I was unable to extract the gallery id's without using the 'Find gallery id code. I'm sure this is a messy way of doing it, but I just can't see how to do it!  Thanks 

//Find gallery1
//connect to mysql
$conn = dbConnect ('query');

//prepare the SQL query

$sql2 = "SELECT * FROM gallery WHERE year_id = '$year_id'";

//submit the query and capture the result

$result2 = mysql_query($sql2) or die (mysql_error());

//extract record  

//Find gallery id
//connect to mysql

$conn = dbConnect ('query');

//prepare the SQL query
$get_id = "SELECT gallery_id FROM gallery WHERE year_id = '$year_id'";

//submit the query and capture the result
$id_result = mysql_query($get_id) or die (mysql_error());

//extract record

$row = mysql_fetch_assoc($id_result);

$final_result = $row['gallery_id'];

$gallery_id = $final_result; 

//Find gallery2
//connect to mysql
$conn = dbConnect ('query');

//prepare the SQL query

$sql3 = "SELECT caption FROM photos WHERE gallery_id = '$gallery_id' ";

//submit the query and capture the result
$result3 = mysql_query($sql3) or die (mysql_error());

//extract record     

//Find all years and order in desc order
//connect to mysql
$conn = dbConnect ('query');

//prepare the SQL query

$sql = 'SELECT * FROM year ORDER BY year DESC';

//submit the query and capture the result

$result = mysql_query($sql) or die (mysql_error());

B
bregentCorrect answer
Participating Frequently
February 20, 2012

>The code commented 'find gallery id', I used to try and find the gallery

>id's linked to the respective year to enable me to pass the id results

>the array $gallery_id which is required by the code commented 'find gallery2'

The variable $gallery_id is not an array. It is a simple variable holding the value of the gallery_id for a single row. If it were an array, it would not work at all in the SQL statement - SQL does not work with arrays.

If each year has multiple galleries, then you need to loop through all of the rows in $row to output thte galleries, and then build a nested loop inside to output the photo's. That's much more complicated than it needs to be. You can accomplish all of this with a single SQL statment instead of 4, and a single loop, by learning how to join the tables in SQL.

SELECT Gallery.name, Year.year, Photos.caption

FROM

Gallery, Year, Photos

WHERE

Gallery.year_id = Year.year_id AND

Gallery.gallery_id = Photo.gallery_id AND

Year.year  = '2010'

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices