Asked  10 Months ago    Answers:  5   Viewed   4 times

Is it possible and if so, how can I do it, to select all entries in a table in my database and then display five results at the time in one group.

Meaning: A example is that I've 15 records total in my database, then I want to present my data like this:

<div class="1-5">Record[1], Record[2], Record[3], Record[4], Record[5]</div>

<div class="6-10">Record[6], Record[7], Record[8], Record[9], Record[10]</div>

<div class="11-15">Record[11], Record[12], Record[13], Record[14], Record[15]</div>

I'm not completely sure if I can do it with an SQL statement or I've to write some sort of "do...while" or loop to retrieve each set of data. I've also thought about something with arrays but haven't got up with a result.

Thanks

  • Mestika

 Answers

4

I find array_chunk() to be pretty useful for this kind of thing.

// pull all the records into an array
$query = mysql_query('SELECT * FROM mytable');
$rows = array();
while ($row = mysql_fetch_array($query)) {
  $rows[] = $row;
}

// this turns an array into an array of arrays where each sub-array is
// 5 entries from the original
$groups = array_chunk($rows, 5);

// process each group one after the other
$start = 1;
foreach ($groups as $group) {
  $end = $start + 4;

  // $group is a group of 5 rows. process as required
  $content = implode(', ', $group);

  echo <<<END
<div class="$start-$end">$content</div>

END;
  $start += 5;
}

You can of course do this without reading them all in first but if you're going to read them all anyway, it doesn't make much difference and the above version will probably be far more readable than implementing the appropriate break condition(s) as you read the rows from the DB.

Wednesday, August 18, 2021
 
Viralk
 
5

You also might use base64 encoding to build in the image. Like

<img src="data:image/jpeg;base64,/9j/4AAQSkZJRgABAQEAWgBaAAD/4gxYSUNDX1BST0ZJTEUAAQEAAAxITGlubwIQAAB..." />

UPDATE, base64 encoding example

You can do that easily:

<?php
$imageId = intval($_GET["id"]);

$query = mysql_query("SELECT img FROM images WHERE id = ". $imageId);
$row = mysql_fetch_array($query);

$mime = null;
// place $type init. here
if ($type=="pjpeg") // <<< where do you get $type btw?
    $mime = "image/jpeg";

$b64Src = "data:".$mime.";base64," . base64_encode($row["img"]);
echo '<img src="'.$b64Src.'" alt="" />';
?>
Thursday, April 1, 2021
 
ALH
 
ALH
4

Build an array up as you iterate with the while loop.

$result = mysql_query("SELECT * FROM `Departments`");
$results = array();
while($row = mysql_fetch_assoc($result))
{
   $results[] = $row;
}

Alternatively, if you used PDO, you could do this automatically.

Thursday, April 1, 2021
 
Fredy
 
1

I think the CodeIgniter documentation is actually very clear, and very easy to follow. Nevertheless, here's an example of how you might structure your app.

Controller:

<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class Members extends CI_Controller {
    function __construct() {
        parent::__construct();

        $this->load->model('members_model');
    }

    function index() {
        $data['members'] = $this->members_model->get_members();

        $this->load->view('header', $data);
        $this->load->view('members');
        $this->load->view('footer');
    }
}

Model:

<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class Members_model extends CI_Model {

    function get_members() {
        $query = $this->db->get('members');
        return $query->result_array();
    }
}

View:

<?php

print_r($members);

To access the above page, you would visit http://yoursite.com/index.php/members.

You can use a .htaccess file to remove the index.php segment from the URI. The index() method is automatically called when you run a controller.

Saturday, May 29, 2021
 
Sauleil
 
3

I am not sure about DO-WHILE IN MS SQL Server 2008 but you can change your WHILE loop logic, so as to USE like DO-WHILE loop.

Examples are taken from here: http://blog.sqlauthority.com/2007/10/24/sql-server-simple-example-of-while-loop-with-continue-and-break-keywords/

  1. Example of WHILE Loop

    DECLARE @intFlag INT
    SET @intFlag = 1
    WHILE (@intFlag <=5)
    BEGIN
        PRINT @intFlag
        SET @intFlag = @intFlag + 1
    END
    GO
    

    ResultSet:

    1
    2
    3
    4
    5
    
  2. Example of WHILE Loop with BREAK keyword

    DECLARE @intFlag INT
    SET @intFlag = 1
    WHILE (@intFlag <=5)
    BEGIN
        PRINT @intFlag
        SET @intFlag = @intFlag + 1
        IF @intFlag = 4
            BREAK;
    END
    GO
    

    ResultSet:

    1
    2
    3
    
  3. Example of WHILE Loop with CONTINUE and BREAK keywords

    DECLARE @intFlag INT
    SET @intFlag = 1
    WHILE (@intFlag <=5)
    BEGIN
        PRINT @intFlag
        SET @intFlag = @intFlag + 1
        CONTINUE;
        IF @intFlag = 4 -- This will never executed
            BREAK;
    END
    GO
    

    ResultSet:

    1
    2
    3
    4
    5
    

But try to avoid loops at database level. Reference.

Wednesday, June 9, 2021
 
TuomasR
 
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :