Two tables, one loop, and a hell of trouble

[empoor]

I'll start by publishing my code:

// query article
	$query1 = "SELECT page_title,author_id FROM articles WHERE category = 'fiction'";

	$mysql_result1 = mysql_query ($query1)
		or die ("Query '$query1' failed with error message: \"" . mysql_error () . '"');
	
	$fiction_articles = mysql_fetch_assoc($mysql_result1);
	
	// query author
	$auth_result = &$fiction_articles['author_id'];
	$query2 = "SELECT author FROM authors WHERE author_id = '$auth_result'";

	$mysql_result2 = mysql_query ($query2)
		or die ("Query '$query2' failed with error message: \"" . mysql_error () . '"');
	
	$auth_row = mysql_fetch_assoc($mysql_result2);
	
	// Parameters
	$fiction_page_title = $fiction_articles['page_title'];
	
	$fiction_author = $auth_row['author'];
	
	// Print
	
	$row = 0;
	while($row = mysql_fetch_assoc($mysql_result1))
	{
		echo '								<li>' . $fiction_author . ': "' . $row['page_title'] . '"</li>';
		$row++;
	}

Now, I have to admit, I know some PHP and MySQL stuff, but I'm still a-learning.

So, I basically have two tables in my database. "articles" and "authors". In this specific case I have to grab 'page_title' and 'author_id' from "articles" and 'author' from "authors". Why the last bit? Well, 'author_ids' in "articles" are set in code (so, for example, 'w001') and the corresponding author information is stored in "authors".

Now, I want to create an archive list of all the entries in "articles" which have as 'category' fiction.

Problem is, it's not working. It grabs a random fiction entry to use and processes that, but 1) only does it once and then stops and 2) doesn't connect the 'author_ids'.

I'm doing a lot wrong in this code, but I've changed it so often after having found a new way to do (found on the web) that it has become foggy.

Help?

[SimonMayer]

I would use MySQL LEFT JOIN to just run 1 query. Maybe something like this:

"SELECT `articles`.`page_id`, `articles`.`author_id`, `authors`.`author` FROM (`articles` LEFT JOIN `authors` ON `articles`.`author_id` = `authors`.`author_id`) WHERE `category` = 'fiction' "

This will bring `author` and `page_title` into the same query, which will be a lot easier.

What is your other query? I didn't understand what you were saying with "It grabs a random fiction entry to use and processes that, but 1) only does it once and then stops "

[empoor]

Thanks for the MySQL LEFT JOIN tip! That solved at least one problem. It now correctly identifies the author corresponding to its id.

The query problem was this: whatever I do, it doesn't pick all the entries. The table "articles" has three (test) entries in the category 'fiction', but it only identifies the second two. Tried solving that with "$row = 0;" but it's still not working.

[somedude3488]

This line:

$fiction_articles = mysql_fetch_assoc($mysql_result1);

is your problem.
mysql_fetch_assoc() only returns 1 row.

[SimonMayer]

This line:

$fiction_articles = mysql_fetch_assoc($mysql_result1);

is your problem.
mysql_fetch_assoc() only returns 1 row.

I don't think that's correct. PHP manual has the following on the mysql_fetch_assoc page:

// While a row of data exists, put that row in $row as an associative array
// Note: If you're expecting just one row, no need to use a loop
// Note: If you put extract($row); inside the following loop, you'll
// then create $userid, $fullname, and $userstatus
while ($row = mysql_fetch_assoc($result)) {
echo $row["userid"];
echo $row["fullname"];
echo $row["userstatus"];
}

I am not sure why you have added

$row = 0;

or

$row++;

. These shouldn't be necessary. I wonder if $row++ is causing the problem

[somedude3488]

I don't think that's correct. PHP manual has the following on the mysql_fetch_assoc page:

I am not sure why you have added

$row = 0;

or

$row++;

. These shouldn't be necessary. I wonder if $row++ is causing the problem

It looks like he is returning more than one row from that query, so yes that is correct.

[SimonMayer]

It looks like he is returning more than one row from that query, so yes that is correct.

I know he wants multiple rows, but I am pretty sure you can use mysql_fetch_assoc for multiple rows, like in the example I showed from the PHP manual.

[empoor]

Thanks for all the answers (and the little discussion :P)

I deleted the

$row = 0;

and

$row++;

And basically nothing changed. It still only displays two of the three entries. (So, I guess it was pointless code. Thanks.)

mysql_fetch_assoc actually does return more than one row, as it is doing now. The remaining problem is that it isn't processing the first row.

[SimonMayer]

Please could you post your code as it is now? It will be easier to understand what is happening now that you are just using the MySQL LEFT JOIN

[SimonMayer]

At last! I think I've spotted the problem. $fiction_articles = mysql_fetch_assoc($mysql_result1); is calling row 1 before while($row = mysql_fetch_assoc($mysql_result1)) is calling rows 2 onwards

You probably can just delete the top line. But if not, you can use mysql_data_seek to revert to row zero.

[empoor]

YES! That's exactly what solved it :) Not sure why I defined $fiction_articles, when I wasn't really using it, but, yes, thanks :)

The final, working, code is:

// query article
	$query1 = "SELECT articles.page_id,articles.page_title,articles.author_id,authors.author FROM (articles LEFT JOIN authors ON articles.author_id = authors.author_id) WHERE category = 'fiction' ORDER BY page_id";

	$mysql_result1 = mysql_query ($query1)
		or die ("Query '$query1' failed with error message: \"" . mysql_error () . '"');
	
	// Print
	
	while($row = mysql_fetch_assoc($mysql_result1))
	{
		echo '<li>' . $row['author'] . ': "' . $row['page_title'] . '"</li>';
	}

Thanks to all. Now I can move on to work on permalinks and URIs, without having to worry about this pesky little problem :)