Re: [sqlite] Insert not working for sqlite3
On Thu, Jul 7, 2011 at 8:24 AM, James_21th wrote: > The table already created with some data inside, the existing data can be > display use "select * from tbl1". Are you 100% sure that the test.db being opened here is the same test.db being used in the rest of he code (same directory)? -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert not working for sqlite3
The table already created with some data inside, the existing data can be display use "select * from tbl1". - Original Message From: Michael Stephenson To: General Discussion of SQLite Database Sent: Thu, 7 July, 2011 1:59:40 PM Subject: Re: [sqlite] Insert not working for sqlite3 I don't see something like a "create table tbl1(one, two);" statement. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James_21th Sent: Thursday, July 07, 2011 1:52 AM To: General Discussion of SQLite Database Subject: [sqlite] Insert not working for sqlite3 Dear all, I'm able to connect to sqlite3 DB and display the data inside, but when insert data, no matter how to try, just don't work, there's no error, but no data inserted. Below is the simple PDO SQL I'm using, table name is "tbl1", the two fields name is just "one" & "two": $dbh = new PDO('sqlite:test.db'); $result=$dbh->query("INSERT INTO tbl1(one,two) VALUES ('new1','new2')"); Any feed back will be greately appreciated! Regards! James ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert not working for sqlite3
On Thu, Jul 7, 2011 at 7:52 AM, James_21th wrote: > Dear all, > > I'm able to connect to sqlite3 DB and display the data inside, but when > insert > data, no matter how to try, just don't work, there's no error, but no data > inserted. > ... > Any feed back will be greately appreciated! > See: http://www.php.net/manual/en/pdo.error-handling.php and enable exception throwing. Then any errors will throw an exception and the problem will be easy to track down. -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert not working for sqlite3
I don't see something like a "create table tbl1(one, two);" statement. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James_21th Sent: Thursday, July 07, 2011 1:52 AM To: General Discussion of SQLite Database Subject: [sqlite] Insert not working for sqlite3 Dear all, I'm able to connect to sqlite3 DB and display the data inside, but when insert data, no matter how to try, just don't work, there's no error, but no data inserted. Below is the simple PDO SQL I'm using, table name is "tbl1", the two fields name is just "one" & "two": $dbh = new PDO('sqlite:test.db'); $result=$dbh->query("INSERT INTO tbl1(one,two) VALUES ('new1','new2')"); Any feed back will be greately appreciated! Regards! James ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Insert not working for sqlite3
Dear all, I'm able to connect to sqlite3 DB and display the data inside, but when insert data, no matter how to try, just don't work, there's no error, but no data inserted. Below is the simple PDO SQL I'm using, table name is "tbl1", the two fields name is just "one" & "two": $dbh = new PDO('sqlite:test.db'); $result=$dbh->query("INSERT INTO tbl1(one,two) VALUES ('new1','new2')"); Any feed back will be greately appreciated! Regards! James ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multidimensional representation
On 06-07-2011 17:30, e-mail mgbg25171 wrote: > 19901991 year <= dimension > > north sales108 > cogs (5) (4) > southsales 6 5 > cogs (2) (1) > > regionline item <== dimensions > > I just want to be able to specify different dimensional values and get back > rectangles of data that I can manipulate. I've already done the manipulation > stuff so it's just how to layout the data. > Query examples would be... > > total = north + south returning 9, 8 select data from where region in ('north','south') > total cogs = north.cogs + south.cogs returning -7, -5 select data from where category = 'cogs' ( 'sales', and 'cogs' are in the dimension 'category') > 1990 returning 10,-5,6,-2 select data from where year=1990 > north:1991 returning 8,-4 select data from where region='north' and year=1991 > north:1990..1991 returning 10,8 select data from where region='north' and year between 1990 an d 1991 > > Once you've created region I don't think you can delete it and all it's > entries cos that would delete everything i.e. I believe you have to leave at > least one value in the column whether it be null or north. > I looks like pivot table, like Roger calls them but that is layout, and has nothing much to do with SQL -- Luuk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multidimensional representation
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/06/2011 08:30 AM, e-mail mgbg25171 wrote: > I just want to be able to specify different dimensional values and get back > rectangles of data that I can manipulate. Is what you are trying to do the same thing as pivot tables in spreadsheets? If so you can Google SQL based solutions, or just use a spreadsheet. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk4UpccACgkQmOOfHg372QThKQCfdRSgXY65rqbbXN7WfUKO05ci ApUAoJ3Gq7ifuvTk90fs+MndqcWnIp7s =zmHd -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multidimensional representation
19901991 year <= dimension north sales108 cogs (5) (4) southsales 6 5 cogs (2) (1) regionline item <== dimensions I just want to be able to specify different dimensional values and get back rectangles of data that I can manipulate. I've already done the manipulation stuff so it's just how to layout the data. Query examples would be... total = north + south returning 9, 8 total cogs = north.cogs + south.cogs returning -7, -5 1990 returning 10,-5,6,-2 north:1991 returning 8,-4 north:1990..1991 returning 10,8 Once you've created region I don't think you can delete it and all it's entries cos that would delete everything i.e. I believe you have to leave at least one value in the column whether it be null or north. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multidimensional representation
Simon only just saw your post so hope my image didn't come through. It didn't on mine but makes my post pretty meaningless. I'll try to do it in text ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multidimensional representation
On 6 July 2011 15:39, e-mail mgbg25171 wrote: > I've done it in the email body > eg total income = north.total + south.total where north and south are from dimension "region" and sales, cogs are from dimension "line item" ie all I need the queries to do is return "rectangles" data cells as a result of various means of specifying them such as 1 the method above or 2 north.income.sales:1990 --->returns 1 3 north.total > returns 5000,4000 The dimensions will be dynamic so I want to be able to return the data to it's form before the extra dimensions eg in this case region was added i.e. Edit...nce you've created an extra dimension it doesn't look like you can dimension it seems to me that you can't just delete it cos everything would disappear. It seems the best you can do is just reduce it down to one value or as you say null but even then you need one entry. Hope this helps. BTW "income" is not a dimension value but just a convenience to automatically select sales and cogs. I haven't quite worked out how to represent this yet. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multidimensional representation
On 6 Jul 2011, at 3:39pm, e-mail mgbg25171 wrote: > A picture paints 1000 words > Would it be ok to attach .png files of what I'm trying to do? Please don't do that to a message on the mailing list. A picture takes a million bits, too. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multidimensional representation
Thank you for your response I didn't quite catch what you mean't but would be happy to give examples of the app. A picture paints 1000 words Would it be ok to attach .png files of what I'm trying to do? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multidimensional representation
On Wed, Jul 06, 2011 at 01:41:13PM +0100, e-mail mgbg25171 scratched on the wall: > The layout where x and y are dimensions eg > might be represented the following fact table > xid yid data > If I added another dimension eg yy > might be represented by this extended fact table > yy xid yid data > That's all well and good until you decide you want to DROP the yy dimension. > How do you best organise table to represent a star schema type arrangement > so that you can easily INSERT/DELETE dimensions and the new data that > accompanies them. > Hope I've made myself clear. If you have a reasonable, bound number of dimensions, I would just create a column for each dimension. You can insert NULL for any values that don't have a value in a specific dimension. This also gives somewhat fine grain control over your queries, as you can search for values with specific values, or with specifically no value (e.g. "x IS NULL"). If you have a more dynamic set of dimensions, then a "star" schema is exactly right. You want to setup attribute tables for the data rows. You could have a different table for each dimension, or a single attribute table with a (dimension, d_value, data_id) type configuration. In that case, you would have one row per dimension per data value. Regardless, each dimension has a reference back to the data row it represents. The issue with attribute designs is that they can be very tricky to query. You often find yourself needing to do a "relational divide", which is kind of the opposite of a JOIN. It is one of the core Relational operations, but it isn't supported natively by SQL. There are ways of doing it in SQL, but the query syntax can get quite messy. I would put together a few example cases of what you're trying to do with your application. In addition to the data layout, pay specific attention to the types of queries you need to run and how you're going to set those up. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] multidimensional representation
Luuk Reading your email again...I think you've misunderstood me Each element of yy represent an instance of the WHOLE original array before the dimension was added. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] multidimensional representation
Thanks for your response. I think your question is my point i.e. indeed...which one do you keep? It's a little ambiguous isn't it? I suppose it makes most sense to return back to the data BEFORE you added the yy dimension which result in returning to... xid yid data 1 1 3 2 1 4 1 2 5 2 2 6 So to "remove" the dimension you just seem to need to reduce it's elements to ONE otherwise you'd be removing all data. An explanation of multidimensional arrays explained that the elements of each new dimension held an array in itself. If they were separate arrays they'd be fairly easy to get rid of. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multidimensional representation
On 06-07-2011 14:41, e-mail mgbg25171 wrote: > The layout where x and y are dimensions eg > > x > 12 > > y 1 | 3 4 > 2 | 5 6 > > might be represented the following fact table > > xid yid data > 1 1 3 > 2 1 4 > 1 2 5 > 2 2 6 > > If I added another dimension eg yy > then the following layout > x > 1 2 > yy y > 11 3 4 > 2 5 6 > 21 7 8 > 2 9 10 > > might be represented by this extended fact table > yy xid yid data > 1 1 1 3 > 1 2 1 4 > 1 1 2 5 > 1 2 2 6 > + > 2 1 1 7 > 2 2 1 8 > 2 1 2 9 > 2 2 2 10 > > That's all well and good until you decide you want to DROP the yy dimension. > How do you best organise table to represent a star schema type arrangement > so that you can easily INSERT/DELETE dimensions and the new data that > accompanies them. > Hope I've made myself clear. which value of data do you want to keep for different values of yy dimension? > yy xid yid data > 1 1 1 3 and > 2 1 1 7 when yy is dropped you can choose 3, or 7, or ? -- Luuk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] multidimensional representation
The layout where x and y are dimensions eg x 12 y 1 | 3 4 2 | 5 6 might be represented the following fact table xid yid data 1 1 3 2 1 4 1 2 5 2 2 6 If I added another dimension eg yy then the following layout x 1 2 yy y 11 3 4 2 5 6 21 7 8 2 9 10 might be represented by this extended fact table yy xid yid data 1 1 1 3 1 2 1 4 1 1 2 5 1 2 2 6 + 2 1 1 7 2 2 1 8 2 1 2 9 2 2 2 10 That's all well and good until you decide you want to DROP the yy dimension. How do you best organise table to represent a star schema type arrangement so that you can easily INSERT/DELETE dimensions and the new data that accompanies them. Hope I've made myself clear. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users