CODEWITHSUNDEEP
phpmysql
Alex
Alex

Reputation: 13

Selecting specific MYSQL rows

I have a page that's suppose to create a Div for every 9 entries in the database. Every Div will consist of 3 ULs and every UL will consist of 3 LIs. Something like this:

Demo

So, every LI is where each entry is displayed and every Div is essentially a unique page.

This is my code so far:

    $sql = mysql_query("SELECT * FROM `reviews`") or die(mysql_error());
    $e_count = mysql_num_rows($sql);
    $r_pages = ceil($e_count / 9);
    $x = 1;
    $y = 1;
    if($e_count > 9){ // if there are more than 9 entries in the database           
        for($x=1;$x<=$r_pages;$x++){ // creates a div for every 9 items in the database 
            echo '<div class="rp_pages" id="rp_page_'.$x.'">';
                for($y=1;$y<=3;$y++){ // creates 3 ULS in each "page" 
                    echo '<ul>';
                       // 3 lis should appear here      
                    echo '</ul>';                       
                }
            echo '</div>';
        }                           
    }

The problem is, i don't want to use multiple queries with a LIMIT in them to select the respective entries. Can this be done with just a single query?

Upvotes: 0

Views: 63

Answers (1)

Jorge Fuentes Gonz&#225;lez
Jorge Fuentes Gonz&#225;lez

Reputation: 11809

I only see one query (the first select). After that, instead of using it to count the number of rows, parse each row and extract the review text, this way:

Let's imagine that the reviews table has a text column where the review text is saved. Having your code:

$sql = mysql_query("SELECT * FROM `reviews`") or die(mysql_error());
$e_count = mysql_num_rows($sql);
$r_pages = ceil($e_count / 9);
$x = 1;
$y = 1;
if($e_count > 9){ // if there are more than 9 entries in the database           
    for($x=1;$x<=$r_pages;$x++){ // creates a div for every 9 items in the database 
        echo '<div class="rp_pages" id="rp_page_'.$x.'">';
            for($y=1;$y<=3;$y++){ // creates 3 ULS in each "page" 
                echo '<ul>';

                // Here the code that extract the rows from the query.
                for($z=1;$z<=3;$z++){
                    echo '<li>';
                    if($data = mysql_fetch_array($sql, MYSQL_ASSOC)) {
                        echo $data['text']; // Use some type of parser to show special characters like < or > as text, so you are safe from code injection.
                    }
                    echo '</li>';
                }


                echo '</ul>';                       
            }
        echo '</div>';
    }                           
}

As you can see, it creates a new loop to create the 3 lis and in each li extracts the text selected in the query, then echoes it.

As http://php.net/manual/en/function.mysql-fetch-array.php says:

Returns an array that corresponds to the fetched row and moves the internal data pointer ahead.

So the next time you fetch a row, it will be the next one.

PD: PHP's mysql is deprecated. Use mysqli instead. Is the same but with another library that is currently under developement, not like mysql.

Upvotes: 1

Related Questions