padams

Brilliant, solved it. Thanks, I really should have thought of that myself...

I need to display the results of a query twice on one page and have run into a problem. I have used mysql_data_seek at the end of the first do while loop, setting the internal pointer back to 0, but when I echo out the results a second time I get a blank record at the top. You can see the results on a test page at http://elsombrero.stacnz.com/testing.php, the code is: $courses_sql = "SELECT * FROM courses"; $courses_query = mysql_query($courses_sql) or die(mysql_error()); $rsCourses = mysql_fetch_assoc($courses_query); <ul><?php do { ?> <li><a href="menu.php?courseID=<?php echo $rsCourses['courseID']; ?>"> <?php echo $rsCourses['cName']; ?> </a></li> <?php } while ($rsCourses = mysql_fetch_assoc($courses_query)); mysql_data_seek($courses_query, 0); ?> <?php do { ?> <li><a href="menu.php?courseID=<?php echo $rsCourses['courseID']; ?>"> <?php echo $rsCourses['cName']; ?> </a></li> <?php } while ($rsCourses = mysql_fetch_assoc($courses_query)); mysql_data_seek($courses_query, 0); ?></ul> </html>

Great, the inner join worked a treat. Thanks for the help. SELECT p.`playerID`, p.`playerFirstName`, p.`playerLastName`, IFNULL(g.gamescount,0) as gamescount, IFNULL(t.trycount,0) as trycount FROM players p INNER JOIN (SELECT playerID, COUNT(*) as gamescount FROM games GROUP BY playerID) as g USING (playerID) LEFT JOIN (SELECT playerID, COUNT(*) as trycount FROM tries GROUP BY playerID) as t USING (playerID) ORDER BY gamescount DESC

That shouldn't be a problem, as all we're after are summative statistics for each team in each season/term. I've had a play with that table structure and it all seems to be working fine, and in fact it has helped me tidy up a few other things that were bugging me. Very big help, thanks so much!

Yes. It's not common but has been known to happen.

I'm designing a site for my school sports program and the situation I am struggling with is that students can play for different teams each year (for example they could progress from a B team to an A team), and can play for the same team for several years. I need to be able to generate team lists and season statistics, and I can't work out how to organise my data so that I can select a team from a specific season, and have only those students who actually played for it returned from the query. Has anyone got an idea how to organise the information?

Result was another error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* ) FROM games g WHERE g . playerID = p . playerID ) as gamescount , ( SELECT CO' at line 1

Tried: SELECT p.playerID, p.playerFirstName, p.playerLastName, (SELECT COUNT(*) FROM games g WHERE g.playerID = p.playerID) gamescount, (SELECT COUNT(*) FROM tries t WHERE t.playerID = p.playerID) trycount FROM players p WHERE gamescount > 0 ORDER BY gamescount DESC; Got another error: #1054 - Unknown column 'gamescount' in 'where clause'

Tried that and got the following error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) FROM games g WHERE g.playerID = p.playerID) gamescount, (SELECT COUNT' at line 2

I'm running a query that combines results from three tables, including counting and grouping data. It has player details, tries scored and games played. Currently it returns the player names even if they have zero games played. Is it possible to modify the query so that if a player has zero games they do not appear in the results? I'm using a left join, but I know that includes results even if there is nothing in one of the columns. Can I get around the problem by using a different join type? I've tried reading up on them and played around with some but can't get the result I want. SELECT p.`playerID`, p.`playerFirstName`, p.`playerLastName`, IFNULL(g.gamescount,0) as gamescount, IFNULL(t.trycount,0) as trycount FROM players p LEFT JOIN (SELECT playerID, COUNT(*) as gamescount FROM games GROUP BY playerID) as g USING (playerID) LEFT JOIN (SELECT playerID, COUNT(*) as trycount FROM tries GROUP BY playerID) as t USING (playerID) ORDER BY gamescount DESC

But what about the style information? The quantity shouldn't increase if the style is different, it should create a new session variable.

I'm just not sure how to best store the information. I've got an array called $_SESSION['cart'][$productID], but whenever a new item is added we need to check if the product has already been purchased so we can increment quantity by 1. That said, the style is a complicating matter as if the product has already been ordered but it is a different style, then it should become a new item in the shopping cart. I just don't know how to best store the information, any ideas?

The other approach I considered was changing the permissions when I created the folder. So I used mkdir("flashgallery/$galleryName", 0777); However, the permissions reverted back to 755 as soon as the folder was created. Is there any way around this?

I've got multiple session variables called $_SESSION['cart'][$productID], with data in each called 'stylename', 'quantity' and 'price'. For example, there will be $_SESSION['cart'][1], $_SESSION['cart'][2], etc. Is there any way to combine data from each into one variable? So I could have "productID1, stylename1, quantity1 and product2, stylename2, quantity2", etc.

Not sure about the ob_flush stuff. I needed it in an old site and have been reusing a bit of code. Once removed it worked fine. Thanks.