Back To Programming / Scripting / Coding Forum

Empty rows in MySQL data base

  • wpas
  • Graduate
  • Graduate
  • User avatar
  • Joined: Jul 12, 2010
  • Posts: 214
  • Loc: Canada
  • Status: Offline

Post November 12th, 2010, 8:32 pm

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>";
  2. $result = mysql_query("SELECT * FROM visitors_table ORDER BY visitor_bb");
  4. echo "<table border='1'>
  5. <tr>
  6. <th>Visitor BB</th>
  7. </tr>";
  9. while($row = mysql_fetch_array($result))
  10.  {
  11.  echo "<tr>";
  12.  echo "<td>" . $row['visitor_bb'] . "</td>";
  13.  echo "</tr>";
  14.  }
  16. 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
http://www.schembrionics.com
The Ultimate Solutions Center
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post November 12th, 2010, 8:32 pm

  • wpas
  • Graduate
  • Graduate
  • User avatar
  • Joined: Jul 12, 2010
  • Posts: 214
  • Loc: Canada
  • Status: Offline

Post November 12th, 2010, 9:11 pm

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");
  2. $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
http://www.schembrionics.com
The Ultimate Solutions Center
  • nuclei
  • Graduate
  • Graduate
  • User avatar
  • Joined: Sep 12, 2003
  • Posts: 147
  • Loc: On a mountain
  • Status: Offline

Post November 20th, 2010, 8:22 pm

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
  • Joined: Sep 22, 2003
  • Posts: 6130
  • Loc: Seattle, WA
  • Status: Offline

Post December 1st, 2010, 5:53 pm

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

Page 1 of 1

Post Information

  • Total Posts in this topic: 4 posts
  • Users browsing this forum: ScottG and 199 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
 
cron
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.