Good Morning,

I am building a hockey pool site and here is some background on my problem. Our league allows each player to be drafted twice. So we have given each player an A or B listing (ie Sydney Crosby A or Sydney Crosby B). In my database, I have two columns called CFHL_A and CFHL_B and in this column I put in the abbreviation for each Fantasy Team that the player belongs too. Then when I do SELECT, I can populate the various teams by search by the abbreviation. No issues there.

In some player's cases, only one of the two have been drafted. So the player who is not drafted is a Free Agent and as such I list them as FREE in my database. For exmaple, D Luke Schenn was drafted by the Habs only. So his listing is CFHL_A = 'HABS' and CFHL_B = 'FREE'.

I am trying to create a page that lists all the available FREE AGENTS by position. So, in this example Luke Schenn B should be listed in the QUERY result. No problem with any player that is on one team and a free agent on the other.

In some cases, a player has not been drafted at all and is listed as FREE-FREE. So, in this case I want the player to be listed in the Free Agent list - twice. Once as the A and once as the B. An example of this would be D P.K. Subban. You can see my progress here:

http://www.cornwallfantasyhockey.com/cfhl/free_agents_D/

SO HERE IS MY PROBLEM: I can get the FREE-FREE guys to list twice in my output table, but I cannot get the A or the B to show properly. Here is my code:

<?php

$result = mysql_query("SELECT * FROM playerdb where (CFHL_A = 'FREE') AND POS= 'D' UNION ALL SELECT * FROM playerdb where (CFHL_B = 'FREE') AND POS= 'D' order by PTS0910 DESC, GP0910 ASC, TEAM ASC")
or die(mysql_error());  

echo "<table width='350' border='1' cellspacing='0' cellpadding='1' bgcolor='ffffff'>";
echo "<tr> 
<td width='15' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>#</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>POS</b></td>
<td width='170' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>PLAYER</b></td>
<td width='10' bgcolor='000000' align='center'><font face='arial' size='1' color='000000'><b>.</b></td>
<td width='30' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>TEAM</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>AGE</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>GP</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>PTS</b></td>
<td width='30' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>PPG</b></td>
</tr>";
// keeps getting the next row until there are no more to get

$n=1;
while($row = mysql_fetch_array( $result )) {

	// Print out the contents of each row into a table
	
	
	echo "<tr><td width='20' bgcolor='ffffff' align='center'><font face='arial' size='2' color='000000'>"; 
 	
 	echo "".$n++;
	echo "</td><td width='15' bgcolor='ffffff' align='center'><font face='arial' size='2' color='000000'>";
	echo $row['Pos'];
	echo "</td><td width='170' bgcolor='ffffff' align='left'><font face='arial' size='2' color='000000'>"; 
	echo $row['Last'];
	echo ", ";
	echo $row['First'];
	echo "</td><td width='10' bgcolor='ffffff' align='center'><font face='arial' size='1' color='000000'>";  
 	
 	if (($row['CFHL_A']=="FREE") and ($row['CFHL_B']!="FREE")) echo "A";
	if (($row['CFHL_A']!="FREE") and ($row['CFHL_B']=="FREE")) echo "B";
	
	echo "</td><td width='30' bgcolor='ffffff' align='center'><font face='arial' size='2' color='000000'>";  
	echo $row['Team'];
	echo "</td><td width='20' bgcolor='ffffff' align='center'><font face='arial' size='2' color='000000'>";   
	echo calculateAge($row['BDay']);
	echo "</td><td width='20' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'><b>";  
	echo $row['GP0910'];
	echo "</td><td width='20' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'><b>"; 
	echo $row['PTS0910'];
	echo "</td><td width='30' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'><b>"; 
	echo number_format($row['PTS0910']/$row['GP0910'],2); 
		error_reporting (E_ERROR);
		echo "</td></tr>";
	

}
echo "</table>";
?>

My challenge is with the coding in and around line 38 and 39. I have tried adding the following, but it hasnt worked: (it gives me AB)

if (($row['CFHL_A']=="FREE") and ($row['CFHL_B']!="FREE")) echo "A";
if (($row['CFHL_A']!="FREE") and ($row['CFHL_B']=="FREE")) echo "B";

if ((($row['CFHL_A']=="FREE") and ($row['CFHL_B']=="FREE"))) and ($row['CFHL_A']=="FREE") echo "A";
if ((($row['CFHL_A']=="FREE") and ($row['CFHL_B']=="FREE"))) and ($row['CFHL_B']=="FREE") echo "B";

I have tried many things and am still struggling. Any help would be greatly appreciated!

Cheers,
Chris

instead of the keyword "and" try using "&&"

Tried this:

if (($row['CFHL_A']=="FREE") && ($row['CFHL_B']=="FREE")) && ($row['CFHL_A']=="FREE") echo "A";
	if (($row['CFHL_A']=="FREE") && ($row['CFHL_B']=="FREE")) && ($row['CFHL_B']=="FREE") echo "B";

got this error message:

Parse error: syntax error, unexpected T_BOOLEAN_AND in D:\Inetpub\vhosts\cornwallfantasyhockey.com\httpdocs\cfhl\includes\rostfree.rw.inc.php on line 50


Thoughts?

you have back-to-back closing parentheses in the middle condition. Essentially you are closing the if prematurely. You need to move one of those parentheses to the end of the statement right BEFORE the echo.

Thanks Heilo....I am getting the same thing I have gotten a few different ways. Any player that is CFHL_A = 'FREE' and CFHL_B = 'FREE' is returning AB in that column. What I want is one to return A and the other to return B.

I thought by splitting and grouping the conditions then it might work...basically where the two first variables are the same and A = Free than post A. But my coding leaves a blank space. I think I am close but am looking for other eyes to see what I might be missing.

Try the code below. Be sure to read comments in the code:

<?php
error_reporting (E_ERROR);

//this should give you all the records where either `CFHL_A`="FREE" or `CFHL_B`="FREE (including the case where BOTH = "FREE"
//$query="SELECT * FROM `playerdb` WHERE (UCASE(TRIM(`CFHL_A`)) = 'FREE' OR UCASE(TRIM(`CFHL_B`)) = 'FREE') AND `Pos`= 'D' ORDER BY `PTS0910` DESC, `GP0910` ASC, `TEAM` ASC";

//this should give you all the records where you only have CFHL_A=="FREE" or CFHL_B==FREE, but not both
$query="SELECT * FROM `playerdb` WHERE (UCASE(TRIM(`CFHL_A`)) = 'FREE' OR UCASE(TRIM(`CFHL_B`)) = 'FREE') AND (UCASE(TRIM(`CFHL_A`))!=UCASE(TRIM(`CFHL_B`))) AND `Pos`= 'D' ORDER BY `PTS0910` DESC, `GP0910` ASC, `TEAM` ASC";
$result = mysql_query($query) or die("<hr/>Problems executing:<br/>$query<br/>" . mysql_error() );  

echo '<table width="350" border="1" cellspacing="0" cellpadding="1" bgcolor="ffffff">
<tr> 
<td width="15" bgcolor="000000" align="center"><font face="arial" size="1" color="ffffff"><b>#</b></td>
<td width="20" bgcolor="000000" align="center"><font face="arial" size="1" color="ffffff"><b>POS</b></td>
<td width="170" bgcolor="000000" align="center"><font face="arial" size="1" color="ffffff"><b>PLAYER</b></td>
<td width="10" bgcolor="000000" align="center"><font face="arial" size="1" color="000000"><b>.</b></td>
<td width="30" bgcolor="000000" align="center"><font face="arial" size="1" color="ffffff"><b>TEAM</b></td>
<td width="20" bgcolor="000000" align="center"><font face="arial" size="1" color="ffffff"><b>AGE</b></td>
<td width="20" bgcolor="000000" align="center"><font face="arial" size="1" color="ffffff"><b>GP</b></td>
<td width="20" bgcolor="000000" align="center"><font face="arial" size="1" color="ffffff"><b>PTS</b></td>
<td width="30" bgcolor="000000" align="center"><font face="arial" size="1" color="ffffff"><b>PPG</b></td>
</tr>';
// keeps getting the next row until there are no more to get

$n=1;
while($row = mysql_fetch_assoc( $result )) {

	// Print out the contents of each row into a table
	
	
	echo '<tr><td width="20" bgcolor="ffffff" align="center"><font face="arial" size="2" color="000000">' . $n++ .'</td>'; 
	echo '<td width="15" bgcolor="ffffff" align="center"><font face="arial" size="2" color="000000">' . $row['Pos'] .'</td>';
	echo '<td width="170" bgcolor="ffffff" align="left"><font face="arial" size="2" color="000000">' . $row['Last'].', ' . $row['First'] . '</td>';
	echo "<td width='10' bgcolor='ffffff' align='center'><font face='arial' size='1' color='000000'>";  
/* 	
	//use this block if you are using the first query above (the one I commented out)
 	if ( $row['CFHL_A']=="FREE" && $row['CFHL_B']!="FREE" )
		echo "A";

	if ( $row['CFHL_A']!="FREE" && $row['CFHL_B']=="FREE" )
		echo "B";
*/	
	//use this if you are using the second query above (the one that is NOT commented out)
 	if ( $row['CFHL_A']=="FREE" )
		echo "A";

	if ( $row['CFHL_A']=="FREE" )
		echo "B";
	echo '</td><td width="30" bgcolor="ffffff" align="center"><font face="arial" size="2" color="000000">' . $row['Team'] . '</td>';
	echo '<td width="20" bgcolor="ffffff" align="center"><font face="arial" size="2" color="000000">' . calculateAge($row['BDay']) . '</td>';
	echo '<td width="20" bgcolor="CCCCCC" align="center"><font face="arial" size="2" color="000000"><b>' . $row['GP0910'] .'</b></td>';
	echo '<td width="20" bgcolor="CCCCCC" align="center"><font face="arial" size="2" color="000000"><b>' .  $row['PTS0910'] . '</b></td>';
	echo '<td width="30" bgcolor="CCCCCC" align="center"><font face="arial" size="2" color="000000"><b>'; 
	echo number_format($row['PTS0910']/$row['GP0910'],2); 
	echo '</b></td></tr>';
}
echo "</table>";
?>

Thanks Heilo...I just want to make sure I understand. I think this will give me one or the other but not both. Based on my first description I would like the following output:

PK Subban A
PK Subban B
Luke Schenn B
Joe Blow A

Make sense? Will this yield that result?

What I am saying is that if your table looks as follows:

id...First...Last.....CFHL_A...CFHL_B..Pos
=========================================
1....John....Smith....FREE.....NULL....D
2....John....Smith....NULL.....FREE....D
3....Sally...Jones....FREE.....FREE....D
4....Jack....Brown....FREE.....NULL....D

You should see the records with id = 1, 2, and 4. (Ignore the dots above. I incorporated them just to force alignment of the columns)

Thanks Hielo...

I appreciate your patience. Based on your table example, what I would like to output is the following:

1. D John Smith A
2. D John Smith B
3. D Sally Jones A
4. D. Sally Jones B
5. D. Jack Brown A

Only difference is my table would not have two players with same name in different rows. (ie John Smith would be on one row - I would simply specify their team by the CFHL_A or CFHL_B column.)

Any thoughts on how to get this output?

In that case use the first query ( the one I left commented out ) and leave everything else the same.

So with the first query you should see:
D John Smith A B
D Sally Jones A B
D Jack Brown A

Please take a look at this page as this is what outputted:

http://www.cornwallfantasyhockey.com/cfhl/free_agents_d/


However:
Kurtis Foster should be listed as #1 and #2

1. D Kurtis Foster A ...
2. D Kurtis Foster B ...

PK Subban should be listed as
16. D PK Subban A ...
17. D PK Subban B ...

Now, these FREE-FREE guys are only appearing once and not showing either A or B.

I have read your post a number of times and I think I followed your direction properly but my code is below just in case.

Thanks again!

<?php
error_reporting (E_ERROR);
$query="SELECT * FROM `playerdb` WHERE (UCASE(TRIM(`CFHL_A`)) = 'FREE' OR UCASE(TRIM(`CFHL_B`)) = 'FREE') AND `Pos`= 'D' ORDER BY `PTS0910` DESC, `GP0910` ASC, `TEAM` ASC";
$result = mysql_query($query) or die("<hr/>Problems executing:<br/>$query<br/>" . mysql_error() );  

echo "<table width='350' border='1' cellspacing='0' cellpadding='1' bgcolor='ffffff'>";
echo "<tr> 
<td width='15' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>#</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>POS</b></td>
<td width='170' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>PLAYER</b></td>
<td width='10' bgcolor='000000' align='center'><font face='arial' size='1' color='000000'><b>.</b></td>
<td width='30' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>TEAM</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>AGE</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>GP</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>PTS</b></td>
<td width='30' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>PPG</b></td>
</tr>";
// keeps getting the next row until there are no more to get

$n=1;
while($row = mysql_fetch_assoc( $result )) {

	// Print out the contents of each row into a table
	
	
	echo "<tr><td width='20' bgcolor='ffffff' align='center'><font face='arial' size='2' color='000000'>"; 
 	
 	echo "".$n++;
	echo "</td><td width='15' bgcolor='ffffff' align='center'><font face='arial' size='2' color='000000'>";
	echo $row['Pos'];
	echo "</td><td width='170' bgcolor='ffffff' align='left'><font face='arial' size='2' color='000000'>"; 
	echo $row['Last'];
	echo ", ";
	echo $row['First'];
	echo "</td><td width='10' bgcolor='ffffff' align='center'><font face='arial' size='1' color='000000'>";  
 	
 	if ( $row['CFHL_A']=="FREE" && $row['CFHL_B']!="FREE" )echo "A"; 	
 	if ( $row['CFHL_A']!="FREE" && $row['CFHL_B']=="FREE" )echo "B";
	
	echo "</td><td width='30' bgcolor='ffffff' align='center'><font face='arial' size='2' color='000000'>";  
	echo $row['Team'];
	echo "</td><td width='20' bgcolor='ffffff' align='center'><font face='arial' size='2' color='000000'>";   
	echo calculateAge($row['BDay']);
	echo "</td><td width='20' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'><b>";  
	echo $row['GP0910'];
	echo "</td><td width='20' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'><b>"; 
	echo $row['PTS0910'];
	echo "</td><td width='30' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'><b>"; 
	echo number_format($row['PTS0910']/$row['GP0910'],2); 
		error_reporting (E_ERROR);
		echo "</td></tr>";
	

}
echo "</table>";
?>

OK, I read your follow-up problems again and I believe I have a clear understanding of your problem. Try:

<?php
error_reporting (E_ERROR);
$query="	SELECT	`Pos`
				,`Last`
				,`First`
				,`CFHL_A` AS `CFHL`
				, `Team`
				,`BDay`
				,`GP0910`
				,`PTS0910` 
			FROM `playerdb` 
			WHERE `CFHL_A`='FREE' 
				AND `Pos`='D' 
	UNION ALL 
		SELECT	`Pos`
				,`Last`
				,`First`
				,`CFHL_B` AS `CFHL`
				, `Team`
				,`BDay`
				,`GP0910`
				,`PTS0910` 
			FROM `playerdb` 
			WHERE `CFHL_B`='FREE' 
				AND `Pos`='D' 
	ORDER BY `PTS0910` DESC, `GP0910` ASC, `Team` ASC";

$result = mysql_query($query) or die("<hr/>Problems executing:<br/>$query<br/>" . mysql_error() );  

echo "<table width='350' border='1' cellspacing='0' cellpadding='1' bgcolor='ffffff'>";
echo "<tr> 
<td width='15' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>#</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>POS</b></td>
<td width='170' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>PLAYER</b></td>
<td width='10' bgcolor='000000' align='center'><font face='arial' size='1' color='000000'><b>.</b></td>
<td width='30' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>TEAM</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>AGE</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>GP</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>PTS</b></td>
<td width='30' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>PPG</b></td>
</tr>";
// keeps getting the next row until there are no more to get

$n=1;
while($row = mysql_fetch_assoc( $result )) {

	// Print out the contents of each row into a table
	
	
	echo "<tr><td width='20' bgcolor='ffffff' align='center'><font face='arial' size='2' color='000000'>"; 
 	
 	echo "".$n++;
	echo "</td><td width='15' bgcolor='ffffff' align='center'><font face='arial' size='2' color='000000'>";
	echo $row['Pos'];
	echo "</td><td width='170' bgcolor='ffffff' align='left'><font face='arial' size='2' color='000000'>"; 
	echo $row['Last'];
	echo ", ";
	echo $row['First'];
	echo "</td><td width='10' bgcolor='ffffff' align='center'><font face='arial' size='1' color='000000'>";  

 	echo $row['CFHL'];

	echo "</td><td width='30' bgcolor='ffffff' align='center'><font face='arial' size='2' color='000000'>";  
	echo $row['Team'];
	echo "</td><td width='20' bgcolor='ffffff' align='center'><font face='arial' size='2' color='000000'>";   
	echo calculateAge($row['BDay']);
	echo "</td><td width='20' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'><b>";  
	echo $row['GP0910'];
	echo "</td><td width='20' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'><b>"; 
	echo $row['PTS0910'];
	echo "</td><td width='30' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'><b>"; 
	echo number_format($row['PTS0910']/$row['GP0910'],2); 
		//error_reporting (E_ERROR);
		echo "</td></tr>";
	

}
echo "</table>";
?>
commented: Great help! thanks so much! +1

This seems to be making progress. Although this time it is giving me the word "FREE" in the column that A or B should be. I am wondering now if I simply modify the data in my table would that solve my problem?

What I am thinking is because you can get it to output 'FREE' if I put 'A' in those rows that are in CFHL_A that are currently "FREE" and the change your query to 'A' instead of FREE then it might work.

Thoguhts?

It works! It works! Awesome Heilo.

It will mean a bit of work to change FREE to A in CFHL_A and FREE to B in CFHL_B but it gives me the output that I need! Awesome job...thank you so much! I wish I could by you are beer!

i see you are from Illinois. Are you a fellow BEARS fan?

Heilo - do you know how to output the date and time of the last Database entry/modification. No bites on my earlier thread!

i see you are from Illinois.

If you know what a proxy server is, you wouldn't believe everything you see on the internet :)

Are you a fellow BEARS fan?

Not a chance!

do you know how to output the date and time of the last Database entry/modification

That is not something "automatic". In other words, in MySQL there isn't a "hidden" property/field that would give you this information. YOU (the developer) would need to actually create a TIMESTAMP field.

Things to note:
a. After you insert the field, it is NOT "retroactive". In other words, existing records will not be updated with the actual timestamp of when they were actually inserted/updated in the table. In other words, the records affected would be the new/future records you insert into the table.

b. You can only have ONE TIMESTAMP field per table. You can have other DATETIME fields, but on those fields you need to actually record the date/time your self.

In your case, since the table already exists, you need to use the ALTER TABLE statement. IF you execute the sql statement below, it should insert a timestamp field named `lastUpdate` immediately AFTER the field named `CFHL_B`

ALTER TABLE `playerdb` ADD `lastUpdate` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `CFHL_B`

Thanks Heilo....

I am a long time BEARS fan and yes, we have lived through some tough seasons! Hoping you aren't a Packers fan though!

I have already added a TIMESTAMP column to my playerdb table. What I am struggling with is to get it to output the date and time. All I am getting is 0000-00-00 00:00:00

Any ideas on what I am missing?

Thanks Heilo....

It's Hielo

Hoping you aren't a Packers fan though!

No I'm not :)

All I am getting is 0000-00-00 00:00:00

Like I said, if you already had existing records, those records will NOT reflect the timestamp of the date/time when they were originally inserted because mysql doesn't keep track of this. This responsibility falls on the developer.

If you insert new records on that table, then it should automatically include the date and time.

Thanks Hielo....sorry for the typo.

Also, I didnt explain what I am trying to do very well. I want to find a way to output something like DATABASE Last Updated On: 0000-00-00 00:00:00. This date would be the last date and time that I made ANY adjustment to the page.

Any ideas?

DATABASE Last Updated On

If you are referring to the playerdb TABLE, then you will need to execute another query just to get that value:
$result=mysql_query("SELECT MAX(`lastUpdate`) as `lastUpdate` FROM `playerdb`") or die(mysql_error());

$row=mysql_fetch_assoc($result);
echo 'Page Last updated on: ' . date('m/d/Y',strtotime($row));

This is perfect...just what I was looking for. I have inserted the CODE below and it is working fine, other than my time is one hour behind. I thought I had addressed the time zone, but I am still getting central time, but I want it to be eastern standard. Any thoughts?

<?php
date_default_timezone_set('America/Toronto');
$result=mysql_query("SELECT MAX(`Timestamp`) as `Timestamp` FROM `playerdb`") or die(mysql_error());

$row=mysql_fetch_assoc($result);
echo "<font face='arial' size='1' color='ffffff'>Database Last Updated: <font face='arial' size='2' color='ffffff'><b>";
echo ' ' .date('M. d,Y @ g:i A',strtotime($row['Timestamp'])); 

?>

Just add an hour, literally:

echo ' ' .date('M. d,Y @ g:i A', strtotime( $row['Timestamp'].' +1 hour') );
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.