Help with outputting select statement results!

Hodges

#1 26. Januar 2014, 14:38

Members

Hi there,

I'm working on a plugin and I've run into something bizarre I can't figure out.

I've got the following exmaple DB table:

SQL

        
              CREATE TABLE IF NOT EXISTS `cot_test` (
  `test_id` int(10) NOT NULL auto_increment,
  `test_title` varchar(255) character set latin1 NOT NULL,
  PRIMARY KEY  (`test_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=6 ;
 
INSERT INTO `cot_test` (`test_id`, `test_title`) VALUES
(1, 'one'),
(2, 'two'),
(3, 'three'),
(4, 'four'),
(5, 'five');

And I've got this simple script to output the column 'test_title' in ascending order.

PHP

        
              $sql = $db->query("SELECT * FROM cot_test ORDER BY test_title ASC");
$row = $sql->fetch();
foreach($sql as $row)
    {
    $numberselect .= $row['test_title']."<br />";
    }

If I run the above SQL statement in phpMyAdmin I get:

five
four
one
three
two

If I run the script as part of my plugin I get:

four
one
three
two

The first row is always missing!! What silly mistake am I making?

Twiebie

#2 26. Januar 2014, 15:08

Team
Thanked: 74 mal

On line 2 you've put the first row in $row and then you started looping through the result.

Use fetchAll() like so:

PHP

        
              $sql = $db->query("SELECT * FROM cot_test ORDER BY test_title ASC");
 
foreach ($sql->fetchAll() as $row)
{
    echo $row['test_title'];
    echo "<br>";
}

Hodges	#3 26. Januar 2014, 15:18
Members	Thank you! I knew it had to be a simple mistake.