Read Next Record

Question

I am filtering null values, in php on MYSQL. When a null value is read, I need the MySQL to read the next record.

How do I go about doing that?

Answer 1

Why not filtering these nulls out at the source, i.e. in the SQL query.

By adding something like the following in the WHERE clause.

WHERE ...  -- existing conditions
AND TheFieldOfInterest  IS NOT NULL

Answer 2

Exactly as mjv already mentioned, you want to tell MySQL to skip over rows that have a NULL value in a particular column. As it stands in your question 'When a null value is read, I need the MySQL to read the next record.' : This is exactly what MySQL will do when you tell it not to include NULLs in the result set by specifying the WHERE condition.

Have fun hacking :)

Answer 3

In php you can use the is_null() function to detect whether a variable is null or not

$result = mysql_query("SELECT foo FROM bar;");
while($values = mysql_fetch_assoc($result))
{
     if (is_null($values["foo"]))
         continue; //Skip processing of this row

     echo "Data: ".$values["foo"];
}

Answer 4

I agree that you shouldn't query all data and then filter the result set on the mysql-client (your php script). But:

done that, but I "just" want to know another way :D

There's nothing wrong with being curious. And: More power to PDO and SPL, esp. FilterIterator in this case.

class ElementIssetFilter extends FilterIterator {
  protected $index;
  public function __construct(Iterator $iter, $index) {
    parent::__construct($iter);
    $this->index = $index;
  }

  public function accept() {
    $c = $this->current();
    return isset($c[$this->index]);
  }
}

$pdo = new PDO('mysql:host=localhost;dbname=test', 'localonly', 'localonly');
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

// testtable and -data
$pdo->exec("CREATE TEMPORARY TABLE foo (id int auto_increment, v varchar(16), primary key(id))");
$pdo->exec("INSERT INTO foo (v) VALUES ('1'), (null), ('3'), ('4'), (null), ('6')");

$result = $pdo->query('SELECT id,v FROM foo');
$iter = new IteratorIterator($result);
$filterIter = new ElementIssetFilter($iter, 'v');

foreach( $filterIter as $e) {
  echo $e['id'], " ", $e['v'], "\n";
}

$filterIter will act like $result, except that rows with NULL values in ['v'] will be filtered out. You don't have to change the "consuming" code, i.e. the same foreach-loop (or function/method call or whatever) would work with $result instead of $filterIter.