Stuck....

[Matt]

aranoid: Me thick....

Me need help........



Whats wrong with this please? I am so confused with what this even does lol so if someone could exlplain that would be great

$query = "SELECT fanfic_authors.name, fanfic_authors.email, fanfic_contents.author, fanfic_contents.title, fanfic_contents.rating FROM fanfic_authors, fanfic_contents WHERE fanfic_authors.id = fanfic_contents.author GROUP BY fanfic_contents.author, fanfic_contents.title ORDER BY " . $sort . ",title LIMIT " . $start . ", 50";
$result = mysql_query($query);

while($row = mysql_fetch_array($result)){
print($row[title] . "<br>");
}

[Dark_Wizard]

Whoa! Can you post the db schema? Both tables that is...then we can go from there...

[Matt]

Thanks!

fanfic_authors
id int(11) NOT NULL auto_increment,
name varchar(100) NOT NULL default '',
email varchar(100) NOT NULL default '',
PRIMARY KEY (id)
TYPE=MyISAM;

fanfic_contents
id int(11) NOT NULL auto_increment,
title varchar(255) NOT NULL default '',
disclaimer text NOT NULL,
summary text NOT NULL,
spoilers text NOT NULL,
season int(11) NOT NULL default '0',
author int(11) NOT NULL default '0',
rating int(11) NOT NULL default '0',
content text NOT NULL,
notes text NOT NULL,
PRIMARY KEY (id)
TYPE=MyISAM;

[Matt]

Just so you know what its for I am trying to do a fan fiction archive for my site and looked at someone elses code which was set out similar to this. Before that I had :

SELECT * FROM fanfic_contents ORDER BY..........

I just wanted somehow to make it a bit easier and to involve less queries. The problem being that the author info and the actual fan fiction are stored in different tables.

So I basically have this code and have a vague idea of what its doing but no real understanding of it :S

[Logician]

You didnt write what kind of result you want your query to return but at the first glance it seems that GROUP BY is redundant in this query..

If what you want to return is ALL results in fanfic_authors table (combined with relevant results from the contents table), this should work fine:

$query = "SELECT * FROM fanfic_authors a, fanfic_contents c WHERE a.id = c.author";

[Matt]

Basically I am trying to code the page where it lists the authors/titles/ratings etc 50 to a page. With a next link at the bottom if there are more than 50 results stored in that category.

I just thought that there would be an easier way to me doing like 10 queries. One to get the authors name and email, one to get the rating, one to get the title etc....

So I really want :

AUTHOR TITLE RATING CHAPTERS
Matt Title Here G [1] [2]

Something like that basically. Then when you click on the header of each column it will sort the results by that.

[Logician]

Ok, modifying my code with LIMIT and SORT should do the trick, like:

$query = "SELECT * FROM fanfic_authors a, fanfic_contents c WHERE a.id = c.author AND a.id>$var ORDER BY ".$sort." LIMIT 50 ";

Now you have to pass your script the last pulled author id as $var, like:

while($row = mysql_fetch_array($result))
{
print($row[title] . "<br>");
$var=(int)$row[author];
}

If there are more than 50 records call your script like:
'<a href="yourscript.php?var='.$var.'>click here to get more</a>'

Needless to say in the first run, $var=0

[Matt]

Anywhere you would suggest that I would find a good tutorial for all this as I still don't totally get it and I will need to adapt it to other areas as well.

Thanks for your help so far

[Logician]

sorry I dont know a basic tutorial for this feature on the net, but if you can find it somewhere and study it for a while then you can check "forumdisplay.php" and "showthread.php" of vb to get an advanced lesson. They use the same method. But their structure is way to complex for a newbie