CODEWITHSUNDEEP
phphtmlmysqlsql
Tony
Tony

Reputation: 25

Displaying the newest year from a PHP table

I have to display the recent image that has been uploaded but only the first entry keeps on being displayed, which is the earliest image we have. Our database is built like this:

jos_campaigns_children

campaign_id | child_id |      code        |
    1921    |    1921  |  GTM-0103-A00627 |

jos_campaigns_children_detail

detail_id | child_year | campaign_id |
   5788   |    2013    |     1920    |
   1921   |    2011    |     1921    |
   3656   |    2012    |     1921    |
   5789   |    2013    |     1921    |
   1922   |    2011    |     1922    |

HTML Code on source:

<img src="/child_images/<?php echo $row_children_list[code]; ?>-<?php echo $row_children_list[child_year]; ?>-S-1.jpg"

Current HTML output:

<img src="/child_images/GTM-0103-A00627-2011-S-1.jpg"

Desired output:

<img src="/child_images/GTM-0103-A00627-2013-S-1.jpg"

I just don't know the syntax to replace $row_children_list[child_year] with so that it only displays the most recent year.

Here's the query that I found (this is someone else's code so I don't exactly know what I'm doing :) )

$query_children_list = "SELECT * FROM `jos_campaigns_children` a, `jos_regions` b, jos_campaigns_children_detail c WHERE (a.published ='1' and a.old_sponsor_id = '' and a.region_id = b.id and a.paypal_profile_status = '0' and a.campaign_id = c.campaign_id)" . $age_query . "" .$gender_query . "" . $location_query . " GROUP BY a.child_id ORDER BY a.campaign_id DESC LIMIT " . $starting_row . ", " . $row_per_page . "";

$result_children_list = mysql_query($query_children_list);

Upvotes: 2

Views: 66

Answers (2)

David Stetler
David Stetler

Reputation: 1451

If you want a quick an easy fix, which it sounds like you do, just change this

$query_children_list = "SELECT * FROM `jos_campaigns_children` a, `jos_regions` b,   jos_campaigns_children_detail c WHERE (a.published ='1' and a.old_sponsor_id = '' and a.region_id = b.id and a.paypal_profile_status = '0' and a.campaign_id = c.campaign_id)" . $age_query . "" .$gender_query . "" . $location_query . " GROUP BY a.child_id ORDER BY a.campaign_id DESC LIMIT " . $starting_row . ", " . $row_per_page . "";

to this

$query_children_list = "SELECT * FROM `jos_campaigns_children` a, `jos_regions` b, jos_campaigns_children_detail c WHERE (a.published ='1' and a.old_sponsor_id = '' and a.region_id = b.id and a.paypal_profile_status = '0' and a.campaign_id = c.campaign_id)" . $age_query . "" .$gender_query . "" . $location_query . " GROUP BY a.child_id ORDER BY c.child_year DESC LIMIT " . $starting_row . ", " . $row_per_page . "";

the relevant change is sorting by the child_year instead of campaign_id.

Upvotes: 1

Santosh Achari
Santosh Achari

Reputation: 3006

I see you are not using inner joins in your query. Without them, it's easy to get unpredictable results. Use inner join on table 1 and table 2 on campaign_id. Ref.

Upvotes: 0

Related Questions