Empty rows in MySQL data base

  • wpas
  • Proficient
  • Proficient
  • User avatar
  • Posts: 322
  • Loc: Canada

Post 3+ Months Ago

Hi All

I am using PHP to retrieve data from MySQL data base. Sometimes the row is empty as there is no data was required for that particular row under the column.

My code is as follows;

Code: [ Select ]

$result = mysql_query("SELECT * FROM visitors_table ORDER BY visitor_bb");

echo "<table border='1'>
<tr>
<th>Visitor BB</th>
</tr>";

while($row = mysql_fetch_array($result))
 {
 echo "<tr>";
 echo "<td>" . $row['visitor_bb'] . "</td>";
 echo "</tr>";
 }

echo "</table>";
  1. $result = mysql_query("SELECT * FROM visitors_table ORDER BY visitor_bb");
  2. echo "<table border='1'>
  3. <tr>
  4. <th>Visitor BB</th>
  5. </tr>";
  6. while($row = mysql_fetch_array($result))
  7.  {
  8.  echo "<tr>";
  9.  echo "<td>" . $row['visitor_bb'] . "</td>";
  10.  echo "</tr>";
  11.  }
  12. echo "</table>";


The results, including empty rows, are all sorted.
When my table displays, it shows a big blank, followed by data in the other rows as shown below:

================
visitor_bb
================


----------------
bb1
----------------
bb2
----------------
bb3
================

I want to get rid of this blank portion of my table.
How can I prevent empty rows from being retrieved during the SQL query

Thanks
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • wpas
  • Proficient
  • Proficient
  • User avatar
  • Posts: 322
  • Loc: Canada

Post 3+ Months Ago

It looks like I had the answer all along

I used the following to get rid of empty fields:

Code: [ Select ]

$result = mysql_query("SELECT * FROM visitors_table WHERE visitor_bb!='' ORDER BY visitor_bb");
  1. $result = mysql_query("SELECT * FROM visitors_table WHERE visitor_bb!='' ORDER BY visitor_bb");


I had put the ORDER BY before WHERE which was causing me the problem.
Now it works ok and does not retrieve the empty rows
  • nuclei
  • Graduate
  • Graduate
  • User avatar
  • Posts: 147
  • Loc: On a mountain

Post 3+ Months Ago

either what wpas said above, or in the event that there are spaces in the field, you can do this:

Code: [ Select ]
while($row = mysql_fetch_array($result)){
 if(trim($row['visitor_bb']) != ''){
  echo "<tr>";
  echo "<td>" . $row['visitor_bb'] . "</td>";
  echo "</tr>";
 }
}
  1. while($row = mysql_fetch_array($result)){
  2.  if(trim($row['visitor_bb']) != ''){
  3.   echo "<tr>";
  4.   echo "<td>" . $row['visitor_bb'] . "</td>";
  5.   echo "</tr>";
  6.  }
  7. }
  • spork
  • Brewmaster
  • Silver Member
  • User avatar
  • Posts: 6252
  • Loc: Seattle, WA

Post 3+ Months Ago

A better approach would be to ensure that blank entries are never inserted into the database in the first place.

Post Information

  • Total Posts in this topic: 4 posts
  • Users browsing this forum: No registered users and 100 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.