limit db query limit output

[powerhouseRay]

Hi folks,

 

I'm using php 5  - recent mysql  - 

 

This code was designed to display 2 colums of user input.  10 in each column

 

Both cols display just fine-  my problem is after the colums display the rest of the db

is displayed underneath of them.

 

Why is this happening and how can i fix it so that only the 20 items chosen are returned?

 

This db is expected to grow quite large so even querying the entire db will eventually be

costly to server performance.

 

Appreciate your time.  TIA

 

 

<div id = "leftcontent">  <p>

<?php

 

$query = 'SELECT `a`,`b`,`c` FROM `table` WHERE `posttime` !=\'\' ORDER BY `posttime` DESC LIMIT  0, 10;';

 

 

 

$result=mysql_query($query);

 

 

while($row = mysql_fetch_array($result, MYSQL_ASSOC))

 

            { echo "<font size = \"3\"><b><a href =\"{$row['a']}\" target=\"_blank\"><br>" .

                        "{$row['b']}</a></b></font><br>" .

                "<font size = \"2\">{$row['c']}</font><br>";

 

            }  ?> 

 

</p></div> <div id="rightcontent"> <p>

<?php

 

$query2 = 'SELECT `a`,`b`,`c` FROM `table` WHERE `posttime` !=\'\' ORDER BY `posttime` DESC LIMIT 10, 20;';

 

$result2=mysql_query($query2);

 

 

 

while($row2 = mysql_fetch_array($result2, MYSQL_ASSOC))

 

  {  echo "<font size = \"3\"><b><a href =\"{$row2['a']}\" target=\"_blank\"> <br>" .

                        "{$row2['b']}</a></b></font><br>" .

                        "<font size = \"2\">{$row2['c']}</font><br>";

 

}

 

?>

 

The result is 10 appear in the left col as expected

 

10 appear in the right col as expected

 

all other pots in the db appear on the page underneath them.

 

TIA for your help.

 

 

Ray

[GingerRobot]

Well, i can only assume there is some other code? However, to tidy things up - and make everything a little clearer (and to avoid the repeat query) try replacing what you've posted above with:

 

<?php
$sql = "SELECT a,b,c FROM `table` WHERE posttime != '' ORDER BY posttime DESC LIMIT 20";
$result = mysql_query($sql) or die(mysql_error());
echo '<div id="leftcontent"><p>';
$i = 0;
while($row = mysql_fetch_assoc($result)){//same as mysql_fetch_array($result,MYSQL_ASSOC) but less typing
echo '<font size = "3"><a href ="'.$row['a'].'" target="_blank">'.$row['b'].'</a></font><font size="2">'.$row['c'].'</font>';
$i++;
if($i == 10){//we've had 10 results
	echo '</p></div><div id="rightcontent"><p>';
}
}
echo '</p>';
?>

 

As i say, i do wonder if there is other code below though.

[powerhouseRay]

Ben

 

Thanks for the added elegance and the fix.

 

No there isn't any other code (some js) below that.

 

I have a question --  perhaps you can help me to understand this more

 

where did the added trail of results come from in the result sets

 

and why were they being printed??

 

I did assume that once the LIMIT parameters were satisfied the mysql would not

need to look further in the the db.  Is this correct  My real concern here is future use.

 

As this getsbusier the resulting server drain could get quite large if it's searching the

whole thing then delivering the result set.  How can i check this?  Any clues or places to look.

 

I do appreciate your time.

 

 

Ray

delivering the

 

 

[kratsg]

The LIMIT function is what you don't quite understand.

 

LIMIT [start_record],[num_of_records]

 

LIMIT 0,10 would show 10 records starting from the record #0.

 

LIMIT 10,20 would show 20 records starting from record #10.

[powerhouseRay]

Ahhhhh

 

I see said the blind man....

 

tyvm

 

Ray