MYSQL/PHP SELECT DISTINCT

Question

I have this function called on another page.

function adminnav (){
    $pagewcoms = mysql_query("SELECT DISTINCT pageid FROM comments") or die(mysql_error());
    $idnavrow = mysql_fetch_row($pagewcoms);
    while ($itest = mysql_fetch_row($pagewcoms)) {
        echo "$itest[0] <br />";
    }
}

adminnav('');

The table is.

CREATE TABLE `comments` (
  `commentid` int(5) NOT NULL auto_increment,
  `pageid` int(5) NOT NULL default '0',
  `name` text NOT NULL,
  `email` text NOT NULL,
  `comment` text NOT NULL,
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`commentid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=481 ;

INSERT INTO `comments` VALUES(480, 1, '3', '3', '3', '2011-09-13 22:43:06');
INSERT INTO `comments` VALUES(479, 1, '2', '2', '2', '2011-09-13 22:43:01');
INSERT INTO `comments` VALUES(476, 1, '1', '1', '1', '2011-09-13 17:22:49');
INSERT INTO `comments` VALUES(477, 2, 'taylordcraig', '[email protected]', 'this is page two', '2011-09-13 17:26:09');
INSERT INTO `comments` VALUES(478, 3, 'this is page3', 'this is page3', 'this is page3', '2011-09-13 22:28:59');

It's only pulling two results. "2 3" [I asked this before but worded it poorly.]

Jon · Accepted Answer · 2011-09-14 06:33:02Z

2

Looks like the query is actually pulling 3 results as it should. You are just letting one of them go:

function adminnav (){
    $pagewcoms = mysql_query(...);

    // HERE YOU FETCH ONE ROW BUT DO NOTHING WITH IT
    $idnavrow = mysql_fetch_row($pagewcoms);

    while ($itest = mysql_fetch_row($pagewcoms)) {
        echo "$itest[0] <br />";
    }
}

If you just remove that line, or tweak it a little, it should display everything just fine:

function adminnav (){
    $pagewcoms = mysql_query(...);
    $idnavrow = null;

    while ($itest = mysql_fetch_row($pagewcoms)) {
        if (empty($idnavrow)) {
            $idnavrow = $itest;
        }
        echo "$itest[0] <br />";
    }
}

answered Sep 14, 2011 at 6:33

Jon

439k85 gold badges756 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Taylor Over a year ago

I ad no idea it worked like that. Thanks so much. EDIT: that leaves a bunch of nifty idea of uses for setting diff variables like that. XD EDIT2: 8 mins till i can accept.

Taylor Over a year ago

Yeah I just did: // Try to find all pages with comments. function adminnav (){ $pagewcoms = mysql_query("SELECT DISTINCT pageid FROM comments") or die(mysql_error()); while ($idnavrow = mysql_fetch_row($pagewcoms)) { echo "$idnavrow[0] <br />"; } }

Collectives™ on Stack Overflow

MYSQL/PHP SELECT DISTINCT

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related