I'm quite new to databases so i apologise in advance if this sounds silly. Im creating a basic web application that simulates a micro blogging website. I have three tables authors, posts & comments.
The authors table is described as follows:
aId int(20) NO PRI NULL auto_increment
aUser varchar(30) NO UNI NULL
aPass varchar(40) NO NULL
aEmail varchar(30) NO UNI NULL
aBio mediumtext YES NULL
aReg datetime NO NULL
the posts table is described below:
pId int(20) NO PRI NULL auto_increment
pAuthor int(20) NO MUL NULL
pTitle tinytext NO NULL
pBody mediumtext NO NULL
pDate datetime NO NULL
I understand the basics of relationships, but could i ask, if on my web application i want to display the posts and include who posted them, is there a way of doing this so the result set will show the actual username, rather than the numeric ID ? each time a post is created i capture the users ID, so every post created is by a valid user ID and the post table records the user ID of the person who created it, but when viewing the posts in a select query it shows the numbers and not the names associated with them in the authors table. us there a query i could use to do this or a way of doing it so when use a select * from authors, it shows the usernames rather than the user ID. Thank you guys.
I think you are looking for a SQL query:
SELECT pTitle, pDate, aUser
FROM posts
LEFT JOIN authors ON aId=pAuthor
ORDER BY pDate DESC
After the SELECT you tell the MySQL what columns you want to see, with the LEFT JOIN you connect the tables together (by aId and pAuthor) and with ORDER BY you tell the mysql to give them to you ordered by date starting from newest pDate DESC (highest date first)
SELECT posts.*, authors.aUser
FROM posts
LEFT JOIN authors ON aId=pAuthor
WHERE pTitle LIKE "%news%"
ORDER BY pDate DESC, aUser ASC
to see the author's name when searching for posts with title containing "news" sorted from the newest posts, and in case two posts having the same timestamp, show them ordered by users name (Adam will go before Zachariash)
In case you do not need to see more than title, date and users name, use the 1st row from the first query above