How to remove the blank fields from a SQL query?

Question

Hi as in http://answers.yahoo.com/question/?qid=20080902053142AAbmw89 I am using the following code for now

<?php

mysql_connect("localhost", "", "") or die(mysql_error());

mysql_select_db("") or die(mysql_error());

$data = mysql_query("SELECT Player,COUNT(Player) Total FROM

(SELECT `Home Scorer 1` Player FROM matches

UNION ALL SELECT `Home Scorer 2` FROM matches

UNION ALL SELECT `Home Scorer 3` FROM matches

UNION ALL SELECT `Home Scorer 4` FROM matches

UNION ALL SELECT `Home Scorer 5` FROM matches

UNION ALL SELECT `Home Scorer 6` FROM matches

UNION ALL SELECT `Home Scorer 7` FROM matches

UNION ALL SELECT `Away Scorer 1` FROM matches

UNION ALL SELECT `Away Scorer 2` FROM matches

UNION ALL SELECT `Away Scorer 3` FROM matches

UNION ALL SELECT `Away Scorer 4` FROM matches

UNION ALL SELECT `Away Scorer 5` FROM matches

UNION ALL SELECT `Away Scorer 6` FROM matches

UNION ALL SELECT `Away Scorer 7` FROM matches) PlayerTotals

GROUP BY Player DESC ORDER BY `Total` DESC ");

$result = mysql_query($data);

$num_rows = mysql_num_rows($data);

echo $num_rows;

?></b> different scorers</font><br><br><div align=\"left\"><table border=\"1\" bordercolorlight=\"#000000\" bordercolordark=\"#000000\"><tr><td>Name - <font size="2">Click to see details</td><td>Times</font></td></tr>

<?php

while ($row = mysql_fetch_assoc($data))

{

$text = "".$row['Player'] . "";

$url = urlencode($text);

echo '<tr><td><a href="./scorer.php?scorer='. $url .'" target="_blank">'. $text .'</url></td>';

echo "<td> ".$row['Total'] . "</td></tr>";

}

?>

</table></div>

This sorts my data for example

Player A 8

Player B 5

etc etc, however there is a row at the top with no content where a name is supposed to be and a number, this is because of my empty fields in some rows and it is including this in the query, is there any way to stop these being included (PHP?) as it messes the table up a bit!

Thanks

Zack

Answer 1

Zack,

   If you have access to the database you should really *normalize* the data as the responder on the previous question said.  The way that you have laid out your table is just going to cause you more problems like this.  Like for instance if you have an 8th scorer in a match you would have to extend everything to record the information.  Besides this SQL being hideous you are storing up problems for yourself.

Andy.

Answer 2

Inside of the while clause add if statement:

while ($row = mysql_fetch_assoc($data))

{

$text = &quot;&quot;.$row[&#039;Player&#039;] . &quot;&quot;;

if ($text != &quot;&quot;){

  $url = urlencode($text);

  echo &#039;&lt;tr&gt;&lt;td&gt;&lt;a href=&quot;./scorer.php?scorer=&#039;. $url .&#039;&quot; target=&quot;_blank&quot;&gt;&#039;. $text .&#039;&lt;/url&gt;&lt;/td&gt;&#039;;

  echo &quot;&lt;td&gt; &quot;.$row[&#039;Total&#039;] . &quot;&lt;/td&gt;&lt;/tr&gt;&quot;;

}

}

Answer 3

You might be able to remove the blank data from the SQL resultsset by adding a HAVING clause between the GROUP BY and ORDER BY.

Something like HAVING Player != &#039;&#039;