MargateSteve

For the more naive people, like myself, a lot of questions will not only involve the MySQL backend of things but also, subsequently, the PHP frontend to get the data to display in the way we want it to. This can lead to the same sort of question being asked in both forums. How about a joint PHP & MySQL forum for questions like these? This would stopped replicated posts and also be easier for anyone searching for a similar solution in future. Steve

That explains it perfectly. Thanks David. Steve

It was something I picked up in a previous script and assumed it was a required part. However I can now see that exactly the same thing in the 'WHILE' statement so removed it. I also removed the similarly superfluous line from the receiving page and everything now works fine. As part of my learning curve, can anyone please explain why the extra line was causing the first record to not appear? I really do want to learn how to figure these problems out for myself!!! Thanks, as always Steve

I have a table which contains a list of football seasons. A season runs from around July until around May and is named after the two years it covers. For example the season that ran from July 2008 until May 2009 is the 2008/09 season. I have a table named "Seasons" table and in the column 'season_name' I have a list of the seasons named as above. I have set up a list of links that take the first three characters of 'season_name' to create some ad-hoc decade links. <?php //Query for the DECADES INDEX at the top $links = mysql_query("SELECT DISTINCT SUBSTRING(season_name,1,3) as decade FROM seasons ORDER BY 1") or die(mysql_error()); // Store first three numbers of SEASON NAME into $linkrow $linkrow = mysql_fetch_array($links ); // Fetch the records of the DECADE $row while($linkrow = mysql_fetch_array($links)) { // Show links to DECADE queries and add "0's" to the result echo '<a href="seasonslistdecades.php?searchstring='; echo $linkrow['decade']. '">'.$linkrow['decade'].'0\'s</a> '; } ?> In the receiving page (seasonslistdecades.php) I have the following <?php //Show which DECADE the table is showing by adding "0's" to searchstring results echo $_GET["searchstring"].'0\'s'; // Retrieve the SEASONS for the correct DECADE $result = mysql_query("SELECT * FROM seasons WHERE season_name LIKE '".$_GET["searchstring"]."%' ") or die(mysql_error()); // Fetch the records of the SEASON $row $row = mysql_fetch_array( $result ); // Print the TABLE HEADERS echo '<table width="95%" border="1" cellspacing="0" cellpadding="0"> <tr> <th>ID</th> <th>Name</th> <th>From</th> <th>To</th> </tr>'; // Fetch the SEASONS while($row = mysql_fetch_array($result)) { // Print the SEASONS echo '<tr> <td>'.$row['season_id'] .'</td> <td>'.$row['season_name'] .'</td> <td>'.$row['season_start'] .'</td> <td>'.$row['season_end'] .'</td> </tr> ' ; } echo '</table> '; ?> It works almost correctly but it misses out the first season of each decade. For example the 1990's show up 1991/92, 1992/93 all the way up to 1999/00 for the season that crosses over into 2000. It does not, however, show 1990/91. This is the same for all the decades - the 1980's has everything except 1980/81 and the 2000's has everything except 2000/01. It seems to be passing everything correctly but anything that has 0 as it's fourth digit is being ignored and I am, yet again, stumped by something that is probably very simple to sort out! As always, any suggestions, including a complete rewrite, would be gratefully received. Thanks in advance Steve

That works absolutely perfectly. Thanks a lot. There will be some more questions in my quest to build a useful search page but will post those in another thread.. Thanks again Steve

Sorry if this should be in the HTML forum but as it is relating to something I am trying to do with php I thought this was the right place! I am creating a search page and one of the columns has only two potential values, 'H' for Home Games and 'A' for Away Games. Using radio buttons I can get the search page to filter correctly but there will also be times, once there are a few more search options on there, when a search would want to show All Games. The current form I have is <form id="form1" name="form1" method="post" action="fixrestestsearch.php"> <p> <label for="OPP">OPP</label> <input type="text" name="OPP" id="OPP" /> <br /> <label> <input type="radio" name="HA" value="H" id="HA_0" /> Home</label> <br /> <label> <input type="radio" name="HA" value="A" id="HA_1" /> Away</label> <br /> <label> <input type="radio" name="HA" value="" id="HA_2" /> All</label> <br /> <br /> SUB <input type="submit" name="SUB" id="SUB" value="Submit" /> </p> </form> and the query in the results page contains WHERE team_name='$OPP' AND h_a='$HA' If either the 'Home' or 'Away' buttons are checked everything works fine. If I check the 'All' button nothing is returned as it is looking for blank cells. I have also tried using 'value="*"' but that did not work. Is there anything that I can put as the value of a radio button that would select all records or would it have to be something amended in the query on the results page? Thanks in advance Steve

Fair points one and all!! Hopefully a few more Tutorials/Articles will appear and give me a few nudges in the right direction!! I have a bit of a problem with learning bits and pieced unless it is used entirely in the context I need it (ie. I really struggled with grouping and COUNTS but as soon as someone explained it using my own data it made sense) I really find it hard to grasp. Thankfully, these forums are filled with patient people will to take time to help the unenlightened. Steve

Has anyone ever considered setting up some Community Projects on at PHP Freaks? From what I have seen in my short time on here there is a lot of knowledge and ability and I think it would be easy for people in the community to create CMS's, News Systems, Photo Galleries, Admin Tools and Forums that would rival a few of the other free solutions out there. These ready-made scripts would be another attraction to bring people to the site and everyone would be able to make suggestions on the development whether they be hardened coders or hobbyists looking at it through end-user eyes. Also there are a few people that are struggling to set up their own project that would be of great use to other people and these can be other things that the community may want to help develop. My 'project' has been in my head for ten years and is something that there is not currently an adequate free version of available anywhere and with my current rate of learning it will be another ten years before completion!! If all the projects codes were extensively /*commented*/ then people, like myself, who find it easier to learn from working code than actually reading how to do things, would be able to increase their knowledge based around something familiar. I really do think that there are a lot of people on here that would make this work and lot more that would benefit from it. Steve

Ignore most of the last post as I have got it all sorted but, presumably, not in the correct way!! However, it works so that will suit me for now!! The final code is $query_Recordset1 = "SELECT COUNT(CASE games.competition WHEN 1 THEN goals.goal_id ELSE NULL END) AS lgegls, COUNT(CASE games.competition WHEN 2 THEN goals.goal_id ELSE NULL END) AS facgls, COUNT(CASE games.competition WHEN 3 THEN goals.goal_id ELSE NULL END) AS fatgls, COUNT(CASE games.competition WHEN 4 THEN goals.goal_id ELSE NULL END) AS kscgls, COUNT(CASE games.competition WHEN 5 THEN goals.goal_id ELSE NULL END) AS lgecgls, COUNT(goals.goal_id) AS tgls, goals.scorer, players.firstname, players.surname, games.competition FROM goals, games INNER JOIN players ON goals.scorer = players.player_id WHERE goals.match = games.match_id AND games.date BETWEEN '2010-07-01' AND '2011-06-31' AND games.competition <> 6 GROUP BY goals.scorer ORDER BY tgls DESC, lgegls DESC "; moving the exclusion of 'competition' 6 from the COUNT to the WHERE. However, I would still be interested if there was a way of adding the created COUNTS together (SUM lgegls + facgls) etc for future use. Finally, and I promise this will be the last 2 questions on the subject..... Is there a way of using a WHEN statement to exclude data? For example COUNT(CASE games.competition WHEN NOT 6 THEN goals.goal_id ELSE NULL END) AS tgls Also, can multiple 'CASE's' be used in a COUNT? Such as COUNT(CASE games.competition WHEN 6 AND games.date BETWEEN '2010-07-01' AND '2011-06-31' THEN goals.goal_id ELSE NULL END) AS tgls I do not need these to finalise this particular page but can see their use coming up in future pages!! Steve

When I was using it without the quotes I was getting this error message 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 'CASE) AS gls1, COUNT(CASE games.competition WHEN 2 THEN goals However by leaving out the CASE after END it all works fine but has lead to two more questions...... Here is my current working query $query_Recordset1 = "SELECT COUNT(CASE games.competition WHEN 1 THEN goals.goal_id ELSE NULL END) AS lgegls, COUNT(CASE games.competition WHEN 2 THEN goals.goal_id ELSE NULL END) AS facgls, COUNT(CASE games.competition WHEN 3 THEN goals.goal_id ELSE NULL END) AS fatgls, COUNT(CASE games.competition WHEN 4 THEN goals.goal_id ELSE NULL END) AS kscgls, COUNT(CASE games.competition WHEN 5 THEN goals.goal_id ELSE NULL END) AS lgecgls, COUNT(CASE WHEN games.competition <> 6 THEN goals.goal_id ELSE NULL END) AS tgls, goals.scorer, players.firstname, players.surname, games.competition FROM goals, games INNER JOIN players ON goals.scorer = players.player_id WHERE goals.match = games.match_id AND games.date BETWEEN '2010-07-01' AND '2011-06-31' GROUP BY goals.scorer ORDER BY tgls DESC "; This is working almost the way I need it to at http://www.margate-fc.com/content/1st_team/stats/scorers.php The last COUNT counts all records except for those in 'competition' 6 as they are exhibition games and do not count towards the statistics and are there just for reference only. This is a bodged together way of getting the Total Goals (tgls). I tried all manner of ways of getting this done using SUM and adding together 'lgegls', 'facgls', 'fatgls' etc but all gave me another Syntax error. On top of this, although goals in 'competition' 6 should not be counted, it still shows the name of a player who only got a goal in that competition. I tried using AND tgls > 0 but get Unknown column 'tgls' in 'where clause' So all in all, nearly there, but if anyone can give any suggestions for the last couple of bits it will also open up a lot of other possibilities within the whole site, not just this page!! Thanks, as always Steve

Still no joy with SUM either I am afraid. I have tried a mixed query of $query_Recordset1 = "SELECT SUM('if(games.competition = 1)') AS gls1, COUNT('if(games.competition = 2)') AS gls2, COUNT('CASE games.competition WHEN 3 THEN goals.goal_id ELSE NULL END CASE') AS gls3, goals.scorer, players.surname, games.competition FROM goals, games INNER JOIN players ON goals.scorer = players.player_id WHERE goals.match = games.match_id GROUP BY goals.scorer ORDER BY gls1 DESC "; gls1 shows up as 0 gls2 and gls3 both show the total goals for the player, not just restricted to the goals in the specific competition. The 'games' table is CREATE TABLE IF NOT EXISTS `games` ( `match_id` int(11) NOT NULL AUTO_INCREMENT, `date` date DEFAULT NULL, `time` time DEFAULT NULL, `competition` int(11) DEFAULT NULL, `round` tinyint(2) DEFAULT NULL, `replay` char(1) DEFAULT NULL, `h_a` varchar(45) DEFAULT NULL, `opponent` int(11) DEFAULT NULL, `wdl` varchar(45) DEFAULT NULL, `for` tinyint(4) DEFAULT NULL, `against` tinyint(4) DEFAULT NULL, `attendance` int(11) DEFAULT NULL, `report_url` longtext, `photo_url` longtext, `stadium` int(11) DEFAULT NULL, `manager` varchar(45) DEFAULT NULL, `live` varchar(255) DEFAULT NULL, `notes` varchar(255) DEFAULT NULL, `extra_time` char(1) DEFAULT NULL, PRIMARY KEY (`match_id`) ) and a selection of the data is INSERT INTO `games` VALUES (60, '2010-08-28', '15:00:00', 1, NULL, NULL, 'A', 17, 'L', *remaining columns after 'wdl' snipped); INSERT INTO `games` VALUES (100, '2010-09-11', '15:00:00', 2, 2, '', 'A', 38, 'W', *remaining columns after 'wdl' snipped)); INSERT INTO `games` VALUES (101, '2010-10-16', '15:00:00', 3, 2, NULL, 'H', 39, 'W', *remaining columns after 'wdl' snipped)); INSERT INTO `games` VALUES (103, '2011-06-30', '19:45:00', 4, 7, '', 'H', 41, '', *remaining columns after 'wdl' snipped)); INSERT INTO `games` VALUES (106, '2010-09-25', '15:00:00', 2, 3, '', 'H', 16, 'D' *remaining columns after 'wdl' snipped)); INSERT INTO `games` VALUES (107, '2010-10-12', '19:45:00', 5, 7, '', 'H', 44, 'L', *remaining columns after 'wdl' snipped)); INSERT INTO `games` VALUES (108, '2010-09-27', '19:45:00', 2, 3, 'R', 'A', 16, 'D', *remaining columns after 'wdl' snipped)); The rest of the table structure is in my original post. I can fully understand what each suggestion is trying to do but none of them can seem to recognise the 'number' in the competition field. I have also tried judda's suggestion of grouping by competition $query_Recordset1 = "SELECT COUNT(players.player_id) AS gls1, goals.scorer, players.surname, games.competition FROM goals, games INNER JOIN players ON goals.scorer = players.player_id WHERE goals.match = games.match_id GROUP BY goals.scorer, games.competition ORDER BY gls1 DESC "; and although this works all the results show in one column and replicates the players name for each competition for example Surnamegls1 Smith10 Smith4 Smith1 Jones7 Jones2 When I would want it to show Surnamegls1gls2gls3 Smith1041 Jones720 I have had a read around to try to work out how to get the results from each competition into a separate column but am, once again, stumped!!! As always thanks for your patience and any suggestions would be extremely welcome!!! Steve

It won't let me edit the post but there is a test page with the result at http://www.margate-fc.com/content/1st_team/stats/scorers.php

I gave this a try with a bit of a tweak but all three columns showed the total goals, not just the one for the respective competition SELECT COUNT('CASE games.competition WHEN 1 THEN goals.goal_id ELSE NULL END CASE') AS gls1, COUNT('CASE games.competition WHEN 2 THEN goals.goal_id ELSE NULL END CASE') AS gls2, COUNT('CASE games.competition WHEN 3 THEN goals.goal_id ELSE NULL END CASE') AS gls3, goals.scorer, players.surname, games.competition FROM goals INNER JOIN players ON goals.scorer = players.player_id INNER JOIN games ON goals.match = games.match_id GROUP BY goals.scorer I am presumably missing something simple here but cannot work it out! Steve PS. Apologies for posting this in the PHP forum initially but that was force of habit!!!

Its been a while but I have another bit I am stuck on that will probably be a piece of cake to you guys but has left me stumped. These are the three tables I am working with CREATE TABLE IF NOT EXISTS `goals` ( `goal_id` int(11) NOT NULL AUTO_INCREMENT, `match` int(11) DEFAULT NULL, FK to games.match_id `scorer` int(11) DEFAULT NULL, FK to players.player_id `goal_type` int(11) DEFAULT NULL, `goal_time` int(11) DEFAULT NULL, PRIMARY KEY (`goal_id`) ) CREATE TABLE IF NOT EXISTS `players` ( `player_id` int(11) NOT NULL AUTO_INCREMENT, `surname` varchar(255) DEFAULT NULL, `firstname` varchar(255) DEFAULT NULL, `date_of_birth` date DEFAULT NULL, `position` int(11) DEFAULT NULL, `image` varchar(255) DEFAULT NULL, `date_joined` date DEFAULT NULL, `date_left` date DEFAULT NULL, `previous_clubs` varchar(255) DEFAULT NULL, `place_of_birth` varchar(255) DEFAULT NULL, `home_sponsor` varchar(255) DEFAULT NULL, `away_sponsor` varchar(255) DEFAULT NULL, `profile` longtext, `Triallist` varchar(10) DEFAULT NULL, PRIMARY KEY (`player_id`) ) CREATE TABLE IF NOT EXISTS `games` ( `match_id` int(11) NOT NULL AUTO_INCREMENT, `date` date DEFAULT NULL, `time` time DEFAULT NULL, `competition` int(11) DEFAULT NULL, `round` tinyint(2) DEFAULT NULL, `replay` char(1) DEFAULT NULL, `h_a` varchar(45) DEFAULT NULL, `opponent` int(11) DEFAULT NULL, `wdl` varchar(45) DEFAULT NULL, `for` tinyint(4) DEFAULT NULL, `against` tinyint(4) DEFAULT NULL, `attendance` int(11) DEFAULT NULL, `report_url` longtext, `photo_url` longtext, `stadium` int(11) DEFAULT NULL, `manager` varchar(45) DEFAULT NULL, `live` varchar(255) DEFAULT NULL, `notes` varchar(255) DEFAULT NULL, `extra_time` char(1) DEFAULT NULL, PRIMARY KEY (`match_id`) ) In games.competition the entry will either be 1, 2, 3 or 4 depending on which competition the game was played in (this links to another table with the competition names but that is not needed here). I have managed to get a query that will do the job for just one competition at a time SELECT COUNT(goals.goal_id) AS gls1, goals.scorer, players.surname, games.competition FROM goals INNER JOIN players ON goals.scorer = players.player_id INNER JOIN games ON goals.match = games.match_id WHERE games.competition = 1 GROUP BY goals.scorer This works exactly how I need it to with just the goals from competition 1, but I want there to be a column for the total goals scored in each competition I have been trying to find a way to use a SELECT statement within the COUNT along the lines of COUNT (SELECT....WHERE games.competition = 1) AS gls1, COUNT (SELECT....WHERE games.competition = 2) AS gls2, COUNT (SELECT....WHERE games.competition = 3) AS gls3 but every way I have tried has failed!! I have tried putting the JOINS in the COUNT (SELECT...'s and also tried leaving them where they are but every attempt has been the same, a blank page. I am sure that I read somewhere before that this can be done, which is why I attempted it in the first place, but now I can find no trace. Any advice would be greatfully received! Thanks in advance Steve

Cheers David. I will take a look at psuedo queries but to be perfectly honest I like the looks of the way CREATE VIEW works in regard to some of the other pages I have so am going to try to set up a MySQL5 database. The fact that the account is at it's limit of databases means I just have to make sure I carefully export everything before deleting the database and creating a new one. As you suggest, a VIEW may not be the right thing for this page anyway. In all fairness there would only be two variations on the query WHERE date_left IS NOT NULL WHERE date_left IS NULL so there is not an enormous amount of replication anyway. But as I said, I think views could be useful in other pages. Thanks again Steve

Aaaaaaah. After extremely exhaustive reading up to try to find why it will not work I eventually discovered that CREATE VIEW does not work with MySQL4, which is what the database is on. I will export all data, delete the database and create a MySQL 5 one. Steve

Following a couple of days of reading up on VIEWS and executing queries I am still stumped!!! I do understand that the query has to executed and as far as I can see (and please correct me if I am wrong) this can be done in two ways, either directly in the query with $first_query = mysql_query("SELECT....) or afterwards with first_query = "SELECT...." $query_results = mysql_query ($first_query) so have tried $squad = mysql_query("CREATE VIEW StaffList AS SELECT * FROM player_season, players, player_positions WHERE player_season.season = '104' AND player_season.player = players.player_id AND player_positions.player_position_id = players.position ORDER BY player_positions.position_order, players.position, players.surname ASC LIMIT 0,$squad_limit"); $squad2 = "SELECT * FROM StaffList WHERE date_left IS NOT NULL"; $results = ($squad2); to no avail. I have also tried to change the last line to $results = ($squad); so it is pulling data directly from the view but that still brings up a blank screen. If I change the VIEW to a direct query and pull $results from that directly everything works $squad = mysql_query ("SELECT * FROM player_season, players, player_positions WHERE player_season.season = '104' AND player_season.player = players.player_id AND player_positions.player_position_id = players.position ORDER BY player_positions.position_order, players.position, players.surname ASC LIMIT 0,$squad_limit"); $squad2 = "SELECT * FROM StaffList"; $results = ($squad); but as soon as I try to use a VIEW it all falls over!!! Could someone please point me in the direction of how to get this done in laymens terms? Instructions at places like php.net hurt my head! Thanks for your patience. Steve

Sorry, left the "WHERE date_left IS NOT NULL" from '$squad2' on the full php code I posted but it is actually there. Steve

Me again! Had a go at this but could not get it to work. I have created a view $squad = "CREATE VIEW StaffList AS SELECT * FROM player_season, players, player_positions WHERE player_season.season = '104' AND player_season.player = players.player_id AND player_positions.player_position_id = players.position ORDER BY player_positions.position_order, players.position, players.surname ASC "; and then set up a second query that will just pull the rows that have data in the 'date_left' field $squad2 = "SELECT * FROM StaffList WHERE date_left IS NOT NULL"; but the page comes up blank. Presumably I have set this up wrong somewhere but as always have tried and failed to work it out! Any pointers would be gratefully received! The full php code is <?php mysql_select_db($database_Test, $Test); $squad = "CREATE VIEW StaffList AS SELECT * FROM player_season, players, player_positions WHERE player_season.season = '104' AND player_season.player = players.player_id AND player_positions.player_position_id = players.position ORDER BY player_positions.position_order, players.position, players.surname ASC "; $squad2 = "SELECT * FROM StaffList"; $results = mysql_query($squad2); ?> and the html is <?php if($results) { if(mysql_num_rows($results)) { $last_player_postion_id = 0; while($row = mysql_fetch_assoc($results)) { // when the players positon id changes output a new heading if($last_player_postion_id != $row['player_position_id']) { echo '<div class="Title_Lt_Blue_Bg">' . strtoupper($row['position']) . '</div>'; $last_player_postion_id = $row['player_position_id']; } echo '<table width="590" border="0" align="center" cellspacing="0"> <tr> <td colspan="2" class="opposition_name">' . $row_squad['surname'] . ', ' . $row['firstname'] . '</td><td width="78" rowspan="3" valign="top" class="Normal_Table_Column"><img src="'. $row['image'] .'" width="120" height="120" /></td> </tr> <tr> <td width="189" rowspan="2" valign="top" class="Normal_Table_Column"><strong>DATE OF BIRTH: </strong><br />'; if ($row['date_of_birth'] == NULL) echo 'TBA'; else echo date('jS F Y',strtotime($row['date_of_birth'])); echo '<br /> <strong>JOINED<br /> </strong>'. date('F Y',strtotime($row['date_joined'])) .'</td> <td width="309" align="left" ><strong>HOME SPONSOR<br /> </strong>'; if ($row['home_sponsor']<>"") echo $row['home_sponsor']; else echo '<a href="http://www.margate-fc.com/content/commercial/sponsorship/shirt_sponsors.php">AVAILABLE - Click here for info</a>'; echo' <br /><strong>AWAY SPONSOR<br /> </strong>'; if ($row['away_sponsor']<>"") echo $row['away_sponsor']; else echo '<a href="http://www.margate-fc.com/content/commercial/sponsorship/shirt_sponsors.php">AVAILABLE - Click here for info</a>'; echo' <br /></td> </tr> <tr> <td height="19" align="right" class="Normal_Table_Column"><a href="squad_details.php?recordID='. $row['player_id'] .'"> See '. $row['firstname'] .'\'s Profile</a> </td> </tr> </table>'; } } else { echo 'No results!'; } } ?> Thanks in advance Steve

Excellent advice as always! I had not come across ternary operator's before but have added them to my 'read up' list, but the other two suggestions worked a treat. In my attempts I had come frustratingly close to getting them right (just a superfluous echo and a couple of missing ;'s between the two) so I am beginning to understand it all. Slowly! Thanks again Steve

I started implementing all of the solutions I have been given by this forum this evening and this one works fine at the test page (http://www.margate-fc.com/content/1st_team/squad2.php) apart from a couple of stumbling blocks!! Basically it comes from a couple of IF statements and a strtotime call to get the dates to show up the way I want ('dd/mm/yy' for dates of birth and 'mmm/yy' joined and left dates). This is the html from my original page <tr><td width="189" rowspan="2" valign="top" class="Normal_Table_Column"><strong>DATE OF BIRTH: </strong><br /> <?php echo date('jS F Y',strtotime($row['date_of_birth'])); ?> <br /><strong>JOINED<br /></strong> <?php echo date('F Y',strtotime($row['date_joined'])); ?></td> <td width="309" align="left" ><strong>HOME SPONSOR<br /></strong> <?php if ($row['home_sponsor']<>"") echo $row['home_sponsor']; else echo '<a href="http://www.margate-fc.com/content/commercial/sponsorship/shirt_sponsors.php">AVAILABLE - Click here for info</a>' ?> <br /><strong>AWAY SPONSOR<br /></strong> <?php if ($row['away_sponsor']<>"") echo $row['away_sponsor']; else echo '<a href="http://www.margate-fc.com/content/commercial/sponsorship/shirt_sponsors.php">AVAILABLE - Click here for info</a>' ?><br /></td></tr> I have tried every way that I can think of to get the 'IF's and the 'strtotime's to work with this but I cannot work out how to put these into an already running echo (that is clearly not the right terminology but hopefully you get what I mean!) and get it to parse correctly. Obviously the <?php ?> tags are superfluous as they are already opened in the code you gave but not sure if this can even be done. I have tried concatenating them '. echo date('jS F Y',strtotime($row['date_of_birth'])) .' '. if ($row['home_sponsor']<>"") echo $row['home_sponsor']; else echo '<a href="http://www.margate-fc.com/content/commercial/sponsorship/shirt_sponsors.php">AVAILABLE - Click here for info</a> .' but that obviously did not work and I have tried enclosing them in all the { }'s and [ ]'s I can find on my keyboard! Is there actually away to make these work or does anyone know a better way to try it? The whole code on the page, which works except for the IF's and the date formats is <?php if($results){ if(mysql_num_rows($results)) { $last_player_postion_id = 0; while($row = mysql_fetch_assoc($results)) { // when the players positon id changes output a new heading if($last_player_postion_id != $row['player_position_id']) { echo '<div class="Title_Lt_Blue_Bg">' . strtoupper($row['position']) . '</div>'; $last_player_postion_id = $row['player_position_id']; } echo '<table width="590" border="0" align="center" cellspacing="0"> <tr> <td colspan="2" class="opposition_name">' . $row['surname'] . ', ' . $row['firstname'] . '</td><td width="78" rowspan="3" valign="top" class="Normal_Table_Column"><img src="'. $row['image'] .'" width="120" height="120" /></td> </tr> <tr> <td width="189" rowspan="2" valign="top" class="Normal_Table_Column"><strong>DATE OF BIRTH: </strong><br />'. $row['date_of_birth'] .'<br /> <strong>JOINED<br /> </strong>'. $row['date_joined'] .'</td> <td width="309" align="left" ><strong>HOME SPONSOR<br /> </strong>' . $row['home_sponsor'] .' <br /><strong>AWAY SPONSOR<br /> </strong>'. $row['away_sponsor'] .'<br /></td> </tr> <tr> <td height="19" align="right" class="Normal_Table_Column"><a href="squad_details.php?recordID='. $row['player_id'] .'"> See '. $row['firstname'] .'\'s Profile</a> </td> </tr> </table>'; } } else { echo 'No results!'; }} ?> Thanks in advance Steve

Cheers David. Yet again, another one of the clear and helpful answers that sets this forum aside from any of the others!! I never knew about 'VIEWS' before but that will be immensely helpful in my attempts to improve my current code. Thanks again Steve

Is it possible to use a 'SELECT FROM' against a query in a new query? For example (and I know this would be bad practice but it is just a simple example so I can understand!!!), if you had a table called 'staff' with this sort of set up ID | NAME | SEX | AGE | DEPARTMENT and this query $staffquery = SELECT * FROM staff would it be possible to then use $males = SELECT * FROM $staffquery WHERE sex = 'Male' $females = SELECT * FROM $staffquery WHERE sex = 'Female' $males_in_despatch = SELECT * FROM $staffquery WHERE sex = 'Male' AND department = 'Despatch' or would I have to run the full query each time? The example I have given is not a good one as it only queries one table anyway but if it was querying several joined tables then it would obviously be a lot of repeated code. I am trying to set up a page which does various counts and sums from a query that links several different tables and I am trying to work out the best way to set it all up before worrying about actually getting the data. I can post the full query that I am actually using and some sample data if required. Thanks in advance Steve

Also, even though I still have to put a little bit more in to get all the details I need on the page, the page is currently 680ish lines shorter!! That can only be a good thing! Steve

wildteen88 - You are a star!!! I had tried to use NULL values before but it would not work for some reason. It is possible that I originally had the fields set that to NOT NULL until I started tidying everything up. Anyway, the test page I was working on (http://www.margate-fc.com/content/1st_team/squad2.php) it is all working and just needs finishing off. I will not pretend for one second that I even begin to understand how it all works (although I can clearly see which part checks to see if there are any players left for a position and if not to move on to the next - it is just the first part that I still do not understand) so am going to try to learn exactly what it is actually doing! As for Dreamweaver, I really only used it out of a necessity in that I had a very tight deadline to meet with it. However, the whole point of my posting on here is to try to take the overcomplicated dreamweaver code and learn how to do the same thing with less code and better functionality!! Anyway, massive thanks to everyone for the help. Beware, though, there will be more questions and requests!! Steve