need help with relational tables/fields

am 03.09.2009 10:05:09 von AndrewJames

Re: need help with relational tables/fields

am 03.09.2009 11:34:12 von Wolfgang Schaefer

AndrewJames schrieb:
> Hey,
>
> i have a table called users which has my users in it, each have a uid
> field.
> I also have a stories table which has stories in it each with a sid field
> for each story but also a uid field so i know which user the story
> belongs
> to.
>
> i want to write a query that will display the story depending on the
> user..
> basically i guess it works like a word press blog. eg, Andrew logs
> into the
> site and only his stories are displayed, but if john logs in, only his
> stories are displayed.
>
> Here are my tables
>
> mysql> describe stories;
> +-----------+-----------+------+-----+-------------------+-- --------------+
>
> | Field | Type | Null | Key | Default |
> Extra |
> +-----------+-----------+------+-----+-------------------+-- --------------+
>
> | sid | int(8) | NO | PRI | NULL |
> auto_increment |
> | uid | int(8) | NO | MUL | NULL
> | |
> | story | text | NO | | NULL
> | |
> | storyDATE | timestamp | NO | | CURRENT_TIMESTAMP
> | |
> +-----------+-----------+------+-----+-------------------+-- --------------+
>
> 4 rows in set (0.02 sec)
>
> mysql> describe users;
> +-----------+-------------+------+-----+---------+---------- ------+
> | Field | Type | Null | Key | Default | Extra |
> +-----------+-------------+------+-----+---------+---------- ------+
> | uid | int(8) | NO | PRI | NULL | auto_increment |
> | username | varchar(12) | NO | | NULL | |
> | password | varchar(32) | NO | | NULL | |
> | firstName | varchar(15) | NO | | NULL | |
> | lastName | varchar(15) | NO | | NULL | |
> +-----------+-------------+------+-----+---------+---------- ------+
> 5 rows in set (0.01 sec)
>

I guess you include uid as part of your session, once a user has
authenticated successfully.
Since you have the mapping "uid <-> sid" already included in the
stories-table the query is very simple:
SELECT sid FROM stories WHERE uid = ${UID_OF_LOGGED_IN_USER}

cheers,
wolfgang

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: need help with relational tables/fields

am 03.09.2009 12:04:49 von Wolfgang Schaefer

AndrewJames schrieb:
> Hey,
>
> i have a table called users which has my users in it, each have a uid
> field.
> I also have a stories table which has stories in it each with a sid field
> for each story but also a uid field so i know which user the story
> belongs
> to.
>
> i want to write a query that will display the story depending on the
> user..
> basically i guess it works like a word press blog. eg, Andrew logs
> into the
> site and only his stories are displayed, but if john logs in, only his
> stories are displayed.
>
> Here are my tables
>
> mysql> describe stories;
> +-----------+-----------+------+-----+-------------------+-- --------------+
>
> | Field | Type | Null | Key | Default |
> Extra |
> +-----------+-----------+------+-----+-------------------+-- --------------+
>
> | sid | int(8) | NO | PRI | NULL |
> auto_increment |
> | uid | int(8) | NO | MUL | NULL
> | |
> | story | text | NO | | NULL
> | |
> | storyDATE | timestamp | NO | | CURRENT_TIMESTAMP
> | |
> +-----------+-----------+------+-----+-------------------+-- --------------+
>
> 4 rows in set (0.02 sec)
>
> mysql> describe users;
> +-----------+-------------+------+-----+---------+---------- ------+
> | Field | Type | Null | Key | Default | Extra |
> +-----------+-------------+------+-----+---------+---------- ------+
> | uid | int(8) | NO | PRI | NULL | auto_increment |
> | username | varchar(12) | NO | | NULL | |
> | password | varchar(32) | NO | | NULL | |
> | firstName | varchar(15) | NO | | NULL | |
> | lastName | varchar(15) | NO | | NULL | |
> +-----------+-------------+------+-----+---------+---------- ------+
> 5 rows in set (0.01 sec)
>
>

I assume you include the uid as part of your session, once a user has
authenticated successfully.
Since you have the mapping "uid <-> sid" already included in the
stories-table the query is very simple:
SELECT sid FROM stories WHERE uid = ${UID_OF_LOGGED_IN_USER}

cheers,
wolfgang

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: need help with relational tables/fields

am 03.09.2009 13:26:35 von Wolfgang Schaefer

AndrewJames schrieb:
> Hey,
>
> i have a table called users which has my users in it, each have a uid
> field.
> I also have a stories table which has stories in it each with a sid field
> for each story but also a uid field so i know which user the story
> belongs
> to.
>
> i want to write a query that will display the story depending on the
> user..
> basically i guess it works like a word press blog. eg, Andrew logs
> into the
> site and only his stories are displayed, but if john logs in, only his
> stories are displayed.
>
> Here are my tables
>
> mysql> describe stories;
> +-----------+-----------+------+-----+-------------------+-- --------------+
>
> | Field | Type | Null | Key | Default |
> Extra |
> +-----------+-----------+------+-----+-------------------+-- --------------+
>
> | sid | int(8) | NO | PRI | NULL |
> auto_increment |
> | uid | int(8) | NO | MUL | NULL
> | |
> | story | text | NO | | NULL
> | |
> | storyDATE | timestamp | NO | | CURRENT_TIMESTAMP
> | |
> +-----------+-----------+------+-----+-------------------+-- --------------+
>
> 4 rows in set (0.02 sec)
>
> mysql> describe users;
> +-----------+-------------+------+-----+---------+---------- ------+
> | Field | Type | Null | Key | Default | Extra |
> +-----------+-------------+------+-----+---------+---------- ------+
> | uid | int(8) | NO | PRI | NULL | auto_increment |
> | username | varchar(12) | NO | | NULL | |
> | password | varchar(32) | NO | | NULL | |
> | firstName | varchar(15) | NO | | NULL | |
> | lastName | varchar(15) | NO | | NULL | |
> +-----------+-------------+------+-----+---------+---------- ------+
> 5 rows in set (0.01 sec)
>
>
>

I assume you include the uid as part of your session, once a user has
authenticated successfully.
Since you have the mapping "uid <-> sid" already included in the
stories-table the query is very simple:
SELECT sid FROM stories WHERE uid = ${UID_OF_LOGGED_IN_USER}

cheers,
wolfgang

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org