visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: SELECT DISTINCT not returning all rows?
By: haydenchambers U.S.A.  Date: 22/04/2003 00:00:00  English  Points: 50 Status: Answered
Quality : Excellent
Not usre whether this is more of a PHP issue or not but here goes...
I'm using PHP Version 4.3.1 and Mysql Version 4.0.12 and myTable looks like this:

subSection | entryNum | content
intro 1 blah blah
intro 2 blah blah
intro 3 more blah
history 1 blah
history 2 blah
values 1 even more blah
company 1 etc ad infinitum

my query is
$sql="SELECT DISTINCT subSection FROM myTable";
$result=@mysql_query($sql,$db);
$row=mysql_fetch_array($result);
while ($row=mysql_fetch_array($result)) {
$navArr[] = $row["subSection"];
}

then to spit out my navigation I use
$i = 0;
$iEnd = count($navArr);
while ($i < $iEnd ) {
echo($navArr[$i]);
$i++;
}

the problem is that it only returns
history values company

the dropping of the first entry in the array is a common problem for me and I can't figure out why.
I worked around it on occassion by entering a dummy first row but this is ugly so any help would be appreciated.

H
By: VGR Date: 22/04/2003 18:12:00 English  Type : Answer
it's evident

you do this :

$row=mysql_fetch_array($result);
while ($row=mysql_fetch_array($result)) {
}

thus you fetch the 1st row , don't do anything with it, and begin while-ing with fetching the next row ;-)

no surprise the 1st isn't in the results set :D

change this to :

while ($row=mysql_fetch_array($result)) {
}
By: haydenchambers Date: 22/04/2003 18:22:00 English  Type : Comment
Such a simple solution. I didn't see it because I didn't understand what the $row was doing! Thanks so much for the eloquent explanation.
By: VGR Date: 22/04/2003 18:33:00 English  Type : Comment
ok, some more information :
$sql="SELECT DISTINCT subSection FROM myTable"; // good idea to separate the query string from the actual cazll (for error messages display)

$result=@mysql_query($sql,$db); // good, but you suppressed warnings and don't do "or die(mysql_error())" [that's PHP code], so you're SUPPOSED TO test the validity of the "resource" names $result after this call

// you don't
//you should have here :
// if ($result) {
// while ()...
// } else echo "bad query $sql, error was ".mysql_error();
// [ this is still PHP code ;-) ]

while ($row=mysql_fetch_array($result)) { // this takes the first row, if any
$navArr[] = $row["subSection"]; // this accesses the returned $row using the adaptative property of PHP arrays, it's good. BUT you could enhance slightly performance by using judiciously single and double quotes.
// single are for constants (where no php variable appear)
// double are for to-be-parsed-it-s-slower strings
} // end while // I suggest to always comment your control structures, so that you take a good habit that can save you hours of hair-pulling :D
By: haydenchambers Date: 22/04/2003 18:44:00 English  Type : Comment
thanks again VGR,
I took out
if (!$result) {
exit();
}
for the purpose of bare-boning the question.
Can you point me to a link to explain the single vs double quote?
Are you saying $navArr[] = $row['subSection']; would have been more effective?
And couldn't agree more on the commenting (in any language!) cheers H.
By: VGR Date: 22/04/2003 19:02:00 English  Type : Comment
for the quotes :

$string='this is a constant string'; // faster (not parsed)
$string="this is also a constant string"; // slower (parsed)

$string='this is a constant string with $variable'; // string is not parsed, thus $variable is not substituted with its value
$string="this is a constant string with $variable"; // string is parsed, thus $variable is substituted with its value

$string="this is a parsed string with $thearray[$i] inside"; // won't work
$string="this is a parsed string with {$thearray[$i]} inside"; // works
$string='this is a parsed string with '.$thearray[$i].' inside'; // works (note the change in quoting for the constant parts
By: haydenchambers Date: 22/04/2003 19:11:00 English  Type : Comment
VGR for king/prime minister/president/presidente!

ta muchly.
By: VGR Date: 22/04/2003 20:34:00 English  Type : Comment
you are welcome; I hate seeing people being blocked by oddities of the language.

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 305.289980 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page