database design

[AndrewJames]

This is a bit of a long shot, but i really need some help and or directed to 
the best reading resources.


as i begun building my database (as i went along), i now realise i have to 
stop coding and sit back and design the database properly before i can go 
on.


However i am still unable to wrap my head around what data to put into what 
tables, and which columns i need to link to make the relationships.  so far, 
here is what i have.


TABLES:

users
-uid(pk)
-username
-password

articles
-article_id(pk)
-uid(fk)
-article_type(fk)
-article_subject
-article_body

article_types
-article_types_id(pk)
-article_type

So i want the user to be able to login and add articles.

I then want to be able to view all the articles the user has submitted.

So in my understanding i need to link the users.uid(pk) to the 
articles.uid(fk) (so i know which user the article belongs to, please 
correct and update me if i am wrong)


I am stuck at this point.

A) Have i created the right tables and columns for each table, AND
B) How do i link the articles.article_type to articles_type.type? (IF in 
fact that is even the correct linkage)?? 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: database design

[AndrewJames]

thank you all, i think

You probably wouldn't need Article_Type table if you're going to store 
Article_Type value directly.


is my answer.


--
From: Kyong Kim kykim...@gmail.com
Sent: Saturday, September 12, 2009 8:22 AM
To: Arthur Fuller fuller.art...@gmail.com
Cc: Claudio Nanni claudio.na...@gmail.com; AndrewJames 
andrewhu...@gmail.com; mysql mysql@lists.mysql.com

Subject: Re: database design


A) You would probably want to populate the Article.Article_Type column
with Article_Type.ID. You probably wouldn't need Article_Type table if
you're going to store Article_Type value directly.

I would also consider the use of natural primary key vs surrogate
primary key. We've seen good results with primary key lookups on large
tables (especially creating grouped subsets of data)

If you imagine your data set growing fairly large, you should take a
stab at projecting your workload to determine whether you would want
to optimize access speed vs insert.

For example, if you will be searching the article table by uid, you
might want to cluster the data by uid so all related articles will be
stored next to each other.

Kyong

On Fri, Sep 11, 2009 at 5:44 AM, Arthur Fuller fuller.art...@gmail.com 
wrote:
I agree with Claudio. You have your design correct. The only other thing 
you
need is the uid qualifier. Presumably you are using PHP or some other 
front
end to present your data. Your front end would request the user's name 
and

password, saving the uid in a variable and then issuing the select with a
WHERE clause that passes the uid in:
select * from articles A left joing article_types AT on A.article_type =
AT.Arcticle_types_id WHERE A.uid = insert your variable here

hth,
Arthur

On Fri, Sep 11, 2009 at 8:22 AM, Claudio Nanni 
claudio.na...@gmail.comwrote:



A.J., It sounds good to me!
You can be a little confused but you did it well,
It seems you have all you need there.

A) Yes
B)  select * from articles A left join article_types AT on 
A.article_type =

AT.article_types_id

Claudio




2009/9/11 AndrewJames andrewhu...@gmail.com

 This is a bit of a long shot, but i really need some help and or 
 directed

 to the best reading resources.

 as i begun building my database (as i went along), i now realise i 
 have

to
 stop coding and sit back and design the database properly before i can 
 go

 on.

 However i am still unable to wrap my head around what data to put into
what
 tables, and which columns i need to link to make the relationships. 
 so

far,
 here is what i have.

 TABLES:

 users
 -uid(pk)
 -username
 -password

 articles
 -article_id(pk)
 -uid(fk)
 -article_type(fk)
 -article_subject
 -article_body

 article_types
 -article_types_id(pk)
 -article_type

 So i want the user to be able to login and add articles.

 I then want to be able to view all the articles the user has 
 submitted.


 So in my understanding i need to link the users.uid(pk) to the
 articles.uid(fk) (so i know which user the article belongs to, please
 correct and update me if i am wrong)

 I am stuck at this point.

 A) Have i created the right tables and columns for each table, AND
 B) How do i link the articles.article_type to articles_type.type? (IF 
 in

 fact that is even the correct linkage)??

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com




--
Claudio







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

mysql_real_escape_string()

[AndrewJames]

Hey guys,

whenever i try to perform this function on my $variables before using them 
in sql queries it deletes them and returns my variable as nothing, ''.


this is how i am using it.

my login.php form
$username = check_input($_POST['username']);
$password = check_input($_POST['password']);

my check_input() function
function check_input($value)
   {
   // Stripslashes
   if (get_magic_quotes_gpc())
   {
 $value = stripslashes($value);
   }
   if (!is_numeric($value))
   {
   echo just before- . $value . -;
   $value = mysql_real_escape_string($value);
   echo just after- . $value . -;
   }
   return $value;
   }

my return values
just before-andrew-
just after--

any clues??

I call require in a php file which defines my functions used here. 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

a better way, code technique?

[AndrewJames]

is there a better way (hopefully simpler) to code this?

i want to get the user id of the logged in user to use in my next statement.

$q1 = sprintf(SELECT uid FROM users WHERE users.username='$username');
$result1 = mysql_query($q1);
$uid = mysql_fetch_array($result1);
$u = $uid['uid'];

it seems like a long way around to get 1 bit of data?? 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: AW: Re: a better way, code technique?

[AndrewJames]

hahah thank-you, love the responses here. you guys are awesome..

ps, where does the %s come from?

--
From: majk.sko...@eventim.de
Sent: Friday, September 04, 2009 9:11 PM
To: p...@computer.org; mysql@lists.mysql.com
Subject: AW:  Re: a better way, code technique?


-Ursprüngliche Nachricht-
Von: Per Jessen [mailto:p...@computer.org]
Gesendet: Freitag, 4. September 2009 13:05
An: mysql@lists.mysql.com
Betreff: Re: a better way, code technique?

AndrewJames wrote:


is there a better way (hopefully simpler) to code this?

i want to get the user id of the logged in user to use in my next
statement.

$q1 = sprintf(SELECT uid FROM users WHERE
users.username='$username');



The only improvement I can see is:

$q1 = sprintf(SELECT uid FROM users WHERE users.username='%s',
$username);


sprintf only adds overhead to this. There is no need to use it here.

You can just use $q = SELECT ...;
Or if you wanna have it more readable use heredoc style

$q = EOSQL
SELECT
uid
FROM
users
WHERE
username = '{$username}'
EOSQL;

But thats a bit off topic ;)

Majk



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

need help with relational tables/fields

[AndrewJames]

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)




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org