Hey, Sorry for requesting help for the third time in two days, but I'm in a dilemma again, and with MySQL too. I have three tables; one table contains information about over three thousand video games. This table is Table A. Table B contains news articles. Table C has two fields, one of which is the ID of a news article, and the other of which is the ID of a game related to that news article. When I access the page for one of the games, I want to see a list of all the news articles related to the game. This is no bother so far, however I need to order the articles by the date they were published in descending order. $newsbindquery = mysql_query("SELECT * FROM Table C WHERE media_type = 'news' AND game_id = '$action'"); while($row = mysql_fetch_array($newsbindquery)){ $newsquery = mysql_query("SELECT * FROM Table B WHERE id = '$row[media_id]'"); $news = mysql_fetch_array($newsquery); echo '<tr><td><a href="http://zconnect.org.uk/article/'.$news['id'].'"">'.$news['name'].'</a></td><td>'.date('j M Y g:iA',strtotime($news['date'])).'</td></tr>'; } PHP: As you should be able to tell, if my coding isn't that messy, the date an article was published is stored in Table B and not Table C. How can I order the articles by date if the date information is in another table? Please respond as soon as possible. Thanks, Connor Beaton
How about if you change: $newsquery = mysql_query("SELECT * FROM Table B WHERE id = '$row[media_id]'"); PHP: To: $newsquery = mysql_query("SELECT * FROM Table B WHERE id = '$row[media_id]' ORDER BY date DESC"); PHP: Assuming the dates are stored in a column called date inside a table called Table B
No dice, changing the ordering in that query does absolutely nothing; that query in particular is only selecting one row. It's the first query that contains the actual issue, which is that its taking them out of the database as-is, because the field it should be ordered by is stored in a totally different table.
So basically your page is about a game displaying info from table A..but you want to display related news articles so.. Firstly you look in table C for realted news article id's related to this game id Then you match the found id's to an article in table 2 So you query is doing this: //LOOKING FOR NEWS ARTICLE ID'S RELATED TO THE GAME ID IN TABLE C $newsbindquery = mysql_query("SELECT * FROM Table C WHERE media_type = 'news' AND game_id = '$action'"); while($row = mysql_fetch_array($newsbindquery)){ //NOW WE ARE MATCHING THE FOUND ID'S FROM EARLIER TO THE ARTICLE IN TABLE B $newsquery = mysql_query("SELECT * FROM Table B WHERE id = '$row[media_id]'"); $news = mysql_fetch_array($newsquery); echo '<tr><td><a href="http://zconnect.org.uk/article/'.$news['id'].'"">'.$news['name'].'</a></td><td>'.date('j M Y g:iA',strtotime($news['date'])).'</td></tr>'; } PHP: I must be understanding wrong because I was thinking if you are getting the needed news ID's in step 1 then you can match them to the id's in table b and just sort them by date? Are you meant to be returning multiple related news articles
Yes, that's exactly my problem. The existing code shows the tables, but in the order that the first query pulled them out of the table in (which would be no order at all, just the order they were inserted). I need them in the order the actual articles were published, but this variable is stored in a different table.
But what I was thinking: Your first query is pulling out related news id's in a random order, correct. But with your second query can you not then match these id's to the news id's in table b, and sort it by the date column in table b then return these store it in another array.
$newsquery = mysql_query("SELECT * FROM Table B WHERE id = '$row[media_id]'"); $row[media_id] is unique, therefore that query is only pulling one row at a time. That is why your theory does not work.
Hi, The way I see it this is easily solved with a mysql JOIN. If you could post the fields in the tables involved, I can write the sql you need to be able to sort the articles by publishing date.
Sorry I have just realised that the second query would be returning one row at a time as you said as it is going through the loop. Yes as the earlier poster said it should be able to be done by MYSQL Joins for example: //Here is the join query $query = "SELECT table_b.*, table_c.* FROM table_b, table_c WHERE table_b.id = table_c.media_id AND table_c.game_id = $action AND table_c.media_type = 'news' ORDER BY date DESC"; //Run the Query or Report an Error $result = mysql_query($query) or die(mysql_error()); //Loop Through Array (Which is now sorted by date DESC! while($row = mysql_fetch_array($result)){ //Do whatever you like with fields from table b or table c e.g echo them as below echo "ID: "; echo $row['id']; echo "<br /> Date: "; echo $row['date']; echo "<br />"; echo $row['game_id']; echo "<br />"; } PHP: OK the join query explained: 1.SELECT table_b.*, table_c.* FROM table_b, table_c Here We are selecting all cols from both tables 2.WHERE table_b.id = table_c.media_id then stating that table_b.id = table_c.media_id as these are the related columns, correct? 3.AND table_c.game_id = $action AND table_c.media_type = 'news' Only return the results based on the current game 4.ORDER BY date DESC Last but not least sort the results by date in descending order. Then we run the query and loop through the query as normal, and use the results how you want. Obviously make sure tablenames and column names are correct to your tables and columns.
ok, lets assume you have a fourth table called table_date which consists of two fields: newsID and newsDate. The following JOIN solves your problem and sorts the news by the table_date newsDate: $query = sprintf("SELECT table_b.newsContent, table_b.newsID FROM ((zconnection.table_b table_b LEFT JOIN zconnection.table_date table_date ON (table_b.newsID = table_date.newsID)) LEFT JOIN zconnection.table_c table_c ON (table_c.newsID = table_b.newsID)) LEFT JOIN zconnection.table_a table_a ON (table_a.vgameID = table_c.vgameID) WHERE table_a.vgameID = '%d' ORDER BY table_date.newsDate ASC", $_GET[vgameID] ); $result = mysql_query( $query ); while( $row = mysql_fetch_array( $result ) ){ echo $row[newsContent] . '<br />'; } PHP: I've tested this code locally and it works just fine But, you need a query above this code that retrieves the actual game.. "zconnection" in the mysql query referes to the databasename. I suggest you do some research on JOINS if you havent already. But you could copy and paste this code into your project and it will work as long as you get the tablenames and fields right. Good luck!