Jump to content

MargateSteve

Members
 View Profile  See their activity

  • Posts

    240

  • Joined

  • Last visited

 Content Type 

Posts posted by MargateSteve

    PHP & MySQL joint forum

    in PHPFreaks.com Website Feedback

    Posted

    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

    Searchstring missing out a row on each set of records

    in MySQL Help

    Posted

    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

    Searchstring missing out a row on each set of records

    in MySQL Help

    Posted

    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

    Radio Buttons on a php search page

    in PHP Coding Help

    Posted

    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

    PHP Freaks Projects

    in PHPFreaks.com Website Feedback

    Posted

    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

    PHP Freaks Projects

    in PHPFreaks.com Website Feedback

    Posted

    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

    Using SELECT queries within a COUNT()

    in MySQL Help

    Posted

    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

    Using SELECT queries within a COUNT()

    in MySQL Help

    Posted

    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

     

     

    Using SELECT queries within a COUNT()

    in MySQL Help

    Posted

    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

    Using SELECT queries within a COUNT()

    in MySQL Help

    Posted

    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!!!

    Using SELECT queries within a COUNT()

    in MySQL Help

    Posted

    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

     

    Querying a query

    in PHP Coding Help

    Posted

    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

    Querying a query

    in PHP Coding Help

    Posted

    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

    Querying a query

    in PHP Coding Help

    Posted

    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

    Querying a query

    in PHP Coding Help

    Posted

    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

    Streamlining Dreamweaver generated PHP code

    in PHP Coding Help

    Posted

    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

    Streamlining Dreamweaver generated PHP code

    in PHP Coding Help

    Posted

    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

    Querying a query

    in PHP Coding Help

    Posted

    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

    Querying a query

    in PHP Coding Help

    Posted

    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

    Streamlining Dreamweaver generated PHP code

    in PHP Coding Help

    Posted

    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

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.