Article Sys Mysql Help

[colicab-d]

Hi all im working on an article system for artorg and so far its coming along nicely, my problem is how would i get

the mysql to say get all the articles from a certain category

the article category is a sep table as i couldnt hav to auto increments in 1 table

i need it to get all the articles inserted into a cateogry and arrange the then resulting articles by there seperate tables id,

then once the link is clicked it,ll just load the article with that id (this i can do its the part before with th categorys im stuck at)

ive kind a fleshed out 90% of the code i just need some hlp with this..

also if anyone wants to take my code and develop it further/better for me then be my guest


these are the two mysql table im using sorry if there badly done i know f### all about mysql really

PHP Code:


  $DB_site->query ("CREATE TABLE articlesystemcat (
  catid int(5) NOT NULL auto_increment,
  catname varchar(60) NOT NULL,
  PRIMARY KEY (catid)
 )");
  
  $DB_site->query ("CREATE TABLE articlesystem (
  artid int(5) NOT NULL auto_increment,
  artname varchar(60) NOT NULL,
  artfull varchar(255) NOT NULL,
  artdate varchar(60) NOT NULL,
  artauth varchar(60) NOT NULL,
  artpword varchar(60) NOT NULL,
  PRIMARY KEY  (artid)
  
 )"); 


[JulianD]

I think you should read more about relational databases.

There is no way to write a query to get all the articles on a specific category, because there's no relationship between the two tables.

You need to add an extra field to your articlesystem table (for example, catid) so you can have a relation between articlesystem and articlesystemcat. And now, with every article on the articlesystem table, you will have the correct category id for that article.

I hope you understand me

[colicab-d]

see my original idea was to have to id fields in one table catid and artid but it wouldbt allow this as they were both auto increment..

how could i achieve 2 ids in one table?

or am i bs`ing throught being stupid and not understanding lol?

[colicab-d]

anyone wanna help me code this lol..

[JulianD]

The field 'catid' on the articlesystemcat table is autoincrement... But you can add a field called 'catid' on the articlesystem table and it doesn't need to be an auto increment field. THe catid field on the articlesystem table will only have the category id number for every article...

Like this:

TABLE: articlesystem 
---------------------------
artid catid artname artfull
  1     1   my life  This is the history of my life...
  2     1    kevin!  About my brother kevin...
  3     2    PHP     A mini PHP manual..

TABLE: articlesystemcat
------------------------
catid catname
  1    Personal life
  2    Programming stuff...

You see the relationship between the two tables?

[colicab-d]

so by adding that it should allow for it to work on the basis

cat = 1
article1 = 1 . 1
article2 = 1 . 2

is that basucally it soz if i seem like an idiot..

so if thats the case woul di just add and addition $catid and catid in the mysql insert when creating a new article?

and this would work fine?

i assume as cat id was already set up as an auto increment in the other table it would then just insert its value again this new query?

also if this is the case how would i then query it to show the articles for the category?

[colicab-d]

bumpty bump bump

[JulianD]

Yesterday at 07:47 PM colicab-d said this in Post #6
so by adding that it should allow for it to work on the basis

cat = 1
article1 = 1 . 1
article2 = 1 . 2

is that basucally it soz if i seem like an idiot..

so if thats the case woul di just add and addition $catid and catid in the mysql insert when creating a new article?

and this would work fine?

i assume as cat id was already set up as an auto increment in the other table it would then just insert its value again this new query?

also if this is the case how would i then query it to show the articles for the category?

Yeah, you're getting the idea...

Now, let's suppose you already has the catid field on your articlesystem table.
Then, you would query the server like this:

[sql]
SELECT * FROM articlesystem WHERE catid=1
[/sql]

and you will get all the articles on the category id #1.

[pie]

ok i will try this, just gotta code in the cat id to its new home and figure out i can get the form for submission to include the ids for the article to be inserted to, in saying that tho a simple few querys will fix that up

hmm julian you wanna add me on msn? colicab@artorg.co.uk

[pie]

soz im on my cousin computer but is me colicab-d lol