Announcement

Collapse
No announcement yet.

How make a select to find n record at time

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • How make a select to find n record at time

    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
    Attached Files
    Last edited by giovannino; 02-13-2014, 06:20 AM.

  • #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:
    Code:
    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;
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

    Comment


    • #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

      Comment

      Working...
      X