How make a select to find n record at time

Developers Programming
#1

Hi all,

I got an A4 doc template with 10 cards to be printed (see picture).
I need to select from mysql db 10 names (records) at time in order to populate the related doc fields.

The standard version is something like

SELECT name, nr, year FROM TABLE WHERE selected = 1

I have to write something like that :

SELECT name1, nr1, year1 (first record)… name2, nr2, year2(second record)… name10, nr10, year10 FROM TABLE WHERE selected = 1

On the document template I have 10 cards with fields like following to populate:

Name: ${name1} Nr: ${nr1} Year: ${year1} Name: ${name2} Nr: ${nr2} Year: ${year2}
Name: ${name3} Nr: ${nr3} Year: ${year3} Name: ${name4} Nr: ${nr4} Year: ${year4}
Name: ${name5} Nr: ${nr3} Year: ${year5} Name: ${name6} Nr: ${nr6} Year: ${year6}
Name: ${name7} Nr: ${nr7} Year: ${year7} Name: ${name8} Nr: ${nr8} Year: ${year8}
Name: ${name9} Nr: ${nr9} Year: ${year9} Name: ${name10} Nr: ${nr10} Year: ${year10}

How can write the select to have the right stepped output ?
First page: records from 1 to 10
Second page: records from 11-20
…
and so on

Many thanks

Tessere_A4.zip (32.3 KB)

#2

I don’t know how you template it, but one solution might be to create a rtf template as this is a simple textfile. The you can do the select behind a ‘process’ button and process 10 records at a time until the result set is empty. To fill the template you can use the php strreplace and append to a file. At the end the rtf can be downloaded for printing.

in pseudocode something like:


select * from...
count = 1;
card = 1;
while not eof do
  Template = strreplace(Template, {card}, myvalues)
  inc(count); inc(card); 

  if card = 11 then
    newpage
    card = 1;
  endif;

  readnextrecord;
end;

setreadyfordownload;
#3

Hi,
since your template is a docx I suppose you are utilizing the PHPword library.
Here is Albert’s suggestion adapted to your needs. I hope it works.

$sql = "SELECT name, nr, year FROM members WHERE selected;
sc_select(result,$sql);
if($result !== false && !$result->EOF)
{
$PHPWord = new PHPWord();
$document = $PHPWord->loadTemplate(your_template);
$i = 1;
while(!$result->EOF)
{
$document->setValue(‘name’.$i, htmlentities( $result->fields[‘name’] ));
$document->setValue(‘nr’.$i, htmlentities( $result->fields[‘nr’] ));
$document->setValue(‘year’.$i, htmlentities( $result->fields[‘year’] ));
$result -> MoveNext();
$i ++;
if($i > 10 && !$result->EOF)
{
$document->AddPage();
$i = 1;
}
}
$result->close();
$document->save(your_out_file);
}

jsb