column alignment of views and tables;

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

column alignment of views and tables;

Hans M. van der Meer
Hi users,

I am building a simple bookkeeping.
With PHP and SQLite i now have tables and views with columns for values and
prices: not nicely aligned because decimal values are aligned different
from values that are interpreted as integers.

I like to create reports in which the column of prices and
*values are aligned to the right and all figures with two decimals behind
the decimal point.*
I can not find a solution, so the question is how to achieve this

Hans
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: column alignment of views and tables;

Simon Slavin-3

On 4 Apr 2017, at 11:25am, Hans M. van der Meer <[hidden email]> wrote:

> I am building a simple bookkeeping.
> With PHP and SQLite i now have tables and views with columns for values and
> prices: not nicely aligned because decimal values are aligned different
> from values that are interpreted as integers.

Numeric values stored in a SQLite database should be stored as numbers.  This allows you to do calculations on them.  They should not have alignment because they should not be text.

> I like to create reports in which the column of prices and
> *values are aligned to the right and all figures with two decimals behind
> the decimal point.*
> I can not find a solution, so the question is how to achieve this

SQLite is a database system.  It is used for storing and retrieving information, not formatting it for people.  When presenting your numbers to people, do your formatting in PHP using sprintf() or vprintf().

http://php.net/manual/en/function.sprintf.php
http://php.net/manual/en/function.vprintf.php

See example #5 for sprintf() for "padding" which a word relating to alignment.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: column alignment of views and tables;

Hans M. van der Meer
Simon, thanks.
Now at last, I know how to continue.

2017-04-04 15:03 GMT+02:00 Simon Slavin <[hidden email]>:

>
> On 4 Apr 2017, at 11:25am, Hans M. van der Meer <[hidden email]>
> wrote:
>
> > I am building a simple bookkeeping.
> > With PHP and SQLite i now have tables and views with columns for values
> and
> > prices: not nicely aligned because decimal values are aligned different
> > from values that are interpreted as integers.
>
> Numeric values stored in a SQLite database should be stored as numbers.
> This allows you to do calculations on them.  They should not have alignment
> because they should not be text.
>
> > I like to create reports in which the column of prices and
> > *values are aligned to the right and all figures with two decimals behind
> > the decimal point.*
> > I can not find a solution, so the question is how to achieve this
>
> SQLite is a database system.  It is used for storing and retrieving
> information, not formatting it for people.  When presenting your numbers to
> people, do your formatting in PHP using sprintf() or vprintf().
>
> http://php.net/manual/en/function.sprintf.php
> http://php.net/manual/en/function.vprintf.php
>
> See example #5 for sprintf() for "padding" which a word relating to
> alignment.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: column alignment of views and tables;

Dominique Devienne
On Tue, Apr 4, 2017 at 3:47 PM, Hans M. van der Meer <[hidden email]>
wrote:

> Simon, thanks.
> Now at last, I know how to continue.
>

But SQLite itself ships with a portable version of printf too.

So you can convert your integer or real typed columns into text typed ones
for display, directly in SQL, as an alternative to doing it in PHP. --DD

https://sqlite.org/lang_corefunc.html#printf


> 2017-04-04 15:03 GMT+02:00 Simon Slavin <[hidden email]>:
> > SQLite is a database system.  It is used for storing and retrieving
> > information, not formatting it for people.  When presenting your numbers
> to
> > people, do your formatting in PHP using sprintf() or vprintf().
> >
> > http://php.net/manual/en/function.sprintf.php
> > http://php.net/manual/en/function.vprintf.php
> >
> > See example #5 for sprintf() for "padding" which a word relating
> to alignment.
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: column alignment of views and tables;

Paul Sanderson
In reply to this post by Hans M. van der Meer
Just shooting out so no time to test. But could you try something like

select substring('                ', 1,  length(printf("%2.f", price))) ||
printf("%2.f", price) from prices




Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 4 April 2017 at 14:47, Hans M. van der Meer <[hidden email]> wrote:

> Simon, thanks.
> Now at last, I know how to continue.
>
> 2017-04-04 15:03 GMT+02:00 Simon Slavin <[hidden email]>:
>
> >
> > On 4 Apr 2017, at 11:25am, Hans M. van der Meer <[hidden email]>
> > wrote:
> >
> > > I am building a simple bookkeeping.
> > > With PHP and SQLite i now have tables and views with columns for values
> > and
> > > prices: not nicely aligned because decimal values are aligned different
> > > from values that are interpreted as integers.
> >
> > Numeric values stored in a SQLite database should be stored as numbers.
> > This allows you to do calculations on them.  They should not have
> alignment
> > because they should not be text.
> >
> > > I like to create reports in which the column of prices and
> > > *values are aligned to the right and all figures with two decimals
> behind
> > > the decimal point.*
> > > I can not find a solution, so the question is how to achieve this
> >
> > SQLite is a database system.  It is used for storing and retrieving
> > information, not formatting it for people.  When presenting your numbers
> to
> > people, do your formatting in PHP using sprintf() or vprintf().
> >
> > http://php.net/manual/en/function.sprintf.php
> > http://php.net/manual/en/function.vprintf.php
> >
> > See example #5 for sprintf() for "padding" which a word relating to
> > alignment.
> >
> > Simon.
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: column alignment of views and tables;

Paul Sanderson
oops

you need to select the string length from the column width

select substring('                ', 1,  16 - length(printf("%2.f",
price))) || printf("%2.f", price) from prices


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 4 April 2017 at 15:07, Paul Sanderson <[hidden email]>
wrote:

> Oops would need to subtract the string length from the column width you
> want - but hopefully you get the idea :)
>
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786 <+44%201326%20572786>
> http://sandersonforensics.com/forum/content.php?195-SQLite-
> Forensic-Toolkit -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
> On 4 April 2017 at 15:05, Paul Sanderson <[hidden email]>
> wrote:
>
>> Just shooting out so no time to test. But could you try something like
>>
>> select substring('                ', 1,  length(printf("%2.f", price)))
>> || printf("%2.f", price) from prices
>>
>>
>>
>>
>> Paul
>> www.sandersonforensics.com
>> skype: r3scue193
>> twitter: @sandersonforens
>> Tel +44 (0)1326 572786 <+44%201326%20572786>
>> http://sandersonforensics.com/forum/content.php?195-SQLite-F
>> orensic-Toolkit -Forensic Toolkit for SQLite
>> email from a work address for a fully functional demo licence
>>
>> On 4 April 2017 at 14:47, Hans M. van der Meer <[hidden email]>
>> wrote:
>>
>>> Simon, thanks.
>>> Now at last, I know how to continue.
>>>
>>> 2017-04-04 15:03 GMT+02:00 Simon Slavin <[hidden email]>:
>>>
>>> >
>>> > On 4 Apr 2017, at 11:25am, Hans M. van der Meer <[hidden email]>
>>> > wrote:
>>> >
>>> > > I am building a simple bookkeeping.
>>> > > With PHP and SQLite i now have tables and views with columns for
>>> values
>>> > and
>>> > > prices: not nicely aligned because decimal values are aligned
>>> different
>>> > > from values that are interpreted as integers.
>>> >
>>> > Numeric values stored in a SQLite database should be stored as numbers.
>>> > This allows you to do calculations on them.  They should not have
>>> alignment
>>> > because they should not be text.
>>> >
>>> > > I like to create reports in which the column of prices and
>>> > > *values are aligned to the right and all figures with two decimals
>>> behind
>>> > > the decimal point.*
>>> > > I can not find a solution, so the question is how to achieve this
>>> >
>>> > SQLite is a database system.  It is used for storing and retrieving
>>> > information, not formatting it for people.  When presenting your
>>> numbers to
>>> > people, do your formatting in PHP using sprintf() or vprintf().
>>> >
>>> > http://php.net/manual/en/function.sprintf.php
>>> > http://php.net/manual/en/function.vprintf.php
>>> >
>>> > See example #5 for sprintf() for "padding" which a word relating to
>>> > alignment.
>>> >
>>> > Simon.
>>> > _______________________________________________
>>> > sqlite-users mailing list
>>> > [hidden email]
>>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>> >
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...