Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
Pages: 1
#1 2008-03-03 23:24:49
- venkatesaya
- Member
- Registered: 2008-03-03
- Posts: 20
table diagram
I would like to have the diagram of the tables of the textpattern database
love
Om Namah Shivaya
Offline
Re: table diagram
mysql> SHOW TABLES;
+-------------------+
| Tables_in_txp |
+-------------------+
| textpattern |
| txp_category |
| txp_css |
| txp_discuss |
| txp_discuss_ipban |
| txp_discuss_nonce |
| txp_file |
| txp_form |
| txp_image |
| txp_lang |
| txp_link |
| txp_log |
| txp_page |
| txp_plugin |
| txp_prefs |
| txp_section |
| txp_users |
+-------------------+
17 rows in set (0.00 sec)Last edited by jm (2008-03-04 00:42:18)
Offline
#3 2008-03-04 06:22:07
- venkatesaya
- Member
- Registered: 2008-03-03
- Posts: 20
Re: table diagram
thank you for this quick reply
what I really want to discover is which id field or other field is relating the tables
with a program like http://www.navicat.com/ you easily connect to the mysql database and tables on your server – you can also easily transfer the data on your local machine, or to an other server
navicat also imports/exports perfectly the data from/to a microsoft access database
I do so for my data of www.venkatesaya.com , in order to print in access the data beautifully – but here I know the relating fields between the tables
if I would know the relations between the id’s in the different tables, it would help me
it is mainly the relating field between the tables ‘textpattern’ and ‘discuss’ – also ‘category’, ‘users’, and ‘section’
Om Namah Shivaya
Offline
Re: table diagram
Why don’t you just unleash Navicat on your Textpattern database? Or run MySQL from the command line?
txp_discussis “linked” to thetextpatterntable byparentid.- Sections and categories are stored as names in the
textpatterntable.
mysql> DESCRIBE textpattern;
+-----------------+--------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------------------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Posted | datetime | NO | MUL | 0000-00-00 00:00:00 | |
| AuthorID | varchar(64) | NO | | | |
| LastMod | datetime | NO | | 0000-00-00 00:00:00 | |
| LastModID | varchar(64) | NO | | | |
| Title | varchar(255) | NO | MUL | | |
| Title_html | varchar(255) | NO | | | |
| Body | mediumtext | NO | | | |
| Body_html | mediumtext | NO | | | |
| Excerpt | text | NO | | | |
| Excerpt_html | mediumtext | NO | | | |
| Image | varchar(255) | NO | | | |
| Category1 | varchar(128) | NO | MUL | | |
| Category2 | varchar(128) | NO | | | |
| Annotate | int(2) | NO | | 0 | |
| AnnotateInvite | varchar(255) | NO | | | |
| comments_count | int(8) | NO | | 0 | |
| Status | int(2) | NO | | 4 | |
| textile_body | int(2) | NO | | 1 | |
| textile_excerpt | int(2) | NO | | 1 | |
| Section | varchar(64) | NO | MUL | | |
| override_form | varchar(255) | NO | | | |
| Keywords | varchar(255) | NO | | | |
| url_title | varchar(255) | NO | | | |
| custom_1 | varchar(255) | NO | | | |
| custom_2 | varchar(255) | NO | | | |
| custom_3 | varchar(255) | NO | | | |
| custom_4 | varchar(255) | NO | | | |
| custom_5 | varchar(255) | NO | | | |
| custom_6 | varchar(255) | NO | | | |
| custom_7 | varchar(255) | NO | | | |
| custom_8 | varchar(255) | NO | | | |
| custom_9 | varchar(255) | NO | | | |
| custom_10 | varchar(255) | NO | | | |
| uid | varchar(32) | NO | | | |
| feed_time | date | NO | | 0000-00-00 | |
+-----------------+--------------+------+-----+---------------------+----------------+
36 rows in set (0.01 sec)
mysql> DESCRIBE txp_discuss;
+-----------+--------------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------------------+------+-----+---------------------+----------------+
| discussid | int(6) unsigned zerofill | NO | PRI | NULL | auto_increment |
| parentid | int(8) | NO | MUL | 0 | |
| name | varchar(255) | NO | | | |
| email | varchar(50) | NO | | | |
| web | varchar(255) | NO | | | |
| ip | varchar(100) | NO | | | |
| posted | datetime | NO | | 0000-00-00 00:00:00 | |
| message | text | NO | | | |
| visible | tinyint(4) | NO | | 1 | |
+-----------+--------------------------+------+-----+---------------------+----------------+
9 rows in set (0.00 sec)
mysql> DESCRIBE txp_category;
+--------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+----------------+
| id | int(6) | NO | PRI | NULL | auto_increment |
| name | varchar(64) | NO | | | |
| type | varchar(64) | NO | | | |
| parent | varchar(64) | NO | | | |
| lft | int(6) | NO | | 0 | |
| rgt | int(6) | NO | | 0 | |
| title | varchar(255) | NO | | | |
+--------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
mysql> SELECT * FROM txp_category;
+----+----------------------+---------+--------+-----+-----+----------------------+
| id | name | type | parent | lft | rgt | title |
+----+----------------------+---------+--------+-----+-----+----------------------+
| 13 | root | file | | 1 | 2 | root |
| 12 | root | image | | 1 | 4 | root |
| 11 | root | link | | 1 | 4 | root |
| 10 | root | article | | 1 | 8 | root |
| 5 | hope-for-the-future | article | root | 2 | 3 | Hope for the Future |
| 6 | meaningful-labor | article | root | 4 | 5 | Meaningful Labor |
| 7 | reciprocal-affection | article | root | 6 | 7 | Reciprocal Affection |
| 8 | textpattern | link | root | 2 | 3 | Textpattern |
| 9 | site-design | image | root | 2 | 3 | Site Design |
+----+----------------------+---------+--------+-----+-----+----------------------+
9 rows in set (0.00 sec)Last edited by jm (2008-03-04 07:42:24)
Offline
#5 2008-03-04 07:56:47
- venkatesaya
- Member
- Registered: 2008-03-03
- Posts: 20
Re: table diagram
a courageous senior will be happy with a confirmation
[textpattern] has <ID>
[discuss] has <discussid> and <parentid>
<ID> from [textpattern] and <parentid> from [discuss] are the relating field
please confirm jm
God bless you
Last edited by venkatesaya (2008-03-04 07:57:15)
Om Namah Shivaya
Offline
Re: table diagram
venkatesaya wrote:
<ID> from [textpattern] and <parentid> from [discuss] are the relating field
Correct.
mysql> SELECT textpattern.ID, txp_discuss.parentid FROM textpattern, txp_discuss WHERE textpattern.ID=1;
+----+----------+
| ID | parentid |
+----+----------+
| 1 | 1 |
+----+----------+
1 row in set (0.00 sec)Last edited by jm (2008-03-04 08:57:08)
Offline
Pages: 1