More database fun

[Kezzer]

Ok so what i'm doing is incorporating 3 tables into one. I had "articles", "tutorials" and "reviews" these can easily put into one which i named "atr" (obvious )

Right another question. Should i also incorporate the news into this table? It seems possible i think but what benefits are there to putting all the tables into one?

Also i'm trying to make one page to retrieve all the data from the database. i.e. click on reviews you would then get php?section=tutorials if you get what i mean. At the moment i'm trying to figure this out.

Can i just change the link in the nav bar (the nav bar is on an include() function) so it says /template?section=tutorials and it would then display the tutorials? I'm still unsure about this yet i've got my php and mysql book next to me.

The reason i'm doing it is because it saves me having to create so many different pages.

Thanks in advance peeps

[Shad]

There's no real gain in your situation by combining all of the information into one table. How you've done it isn't how I'd necessarily do it, but it's obviously a solution that works and I can't see any real need to make it any more complicated by introducing the extra algorithms needed to differentiate news records from turotial records, for example.

Can't really help you with the PHP side of things though.

[Kezzer]

It would save me time in coding the MySQL to retrieve data. This means i don't have to code for each seperate table. We'll see how it goes, i just need more help on displaying the information without having to use seperate pages :/

[Iain]

If you;re going to be querying different tables with the same structure then just use a function e.g.

function getData($table)
    {
        $query = "SELECT * FROM ".$table;
        $result = mysql_query($query)
        return $result;
    }
    
    $articlesResult = getData ("articles");
    $tutorialsResult = getData ("tutorials");
    $reviewsResult = getData ("reviews"); 

Alternatively you could just use a simple UNION query again assuming the tables all have the same format e.g.

SELECT * FROM articles
UNION
SELECT * FROM tutorials
UNION
SELECT * FROM reviews

This would get all the data in one query

[Kezzer]

Well the plan is i want to click on the nav bar say on "tutorials" and then the site display the data for the "tutorials" section of the "atr" table by saying "article.php?section=tutorials" but if i'm reading what you're saying i can understand this. Instead it would say (if the file is called article.php) "article.php?tutorialsResult" and it would display the tutorials.

This looks extremely handy if i'm understanding it correctly

What does UNION do?

[Kezzer]

    <?php
    $link = mysql_connect("localhost", "kez", "pwd")
        or die("Could not connect : " . mysql_error());
    mysql_select_db("kez") or die("Could not select database");

$result = mysql_query("SELECT id,title,author,date,category,explanation FROM `tutorials` WHERE category='photoshop' ORDER BY title ASC", $link);
      
      while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "<a href=\"read_article.php?id=" . $line['id'] . "&category=" . $line['category'] . "\">" . $line['title'] . "</a> by " . $line['author'] . ", posted on the: " . $line['date'] . "<br><blockquote>" . $line['explanation'] . "</blockquote>";
}

mysql_close();
?>

That's the first file which shows you the titles of each row and a snippet with the author and date.

    <?php
ini_set("include_path", "/home/kez/public_html/");

if (!is_null($id)) { 

$link = mysql_connect("localhost", "kez", "pwd") OR DIE("Avast! Cant connect to mysql DB yarrr!");
mysql_select_db("kez");

$result = mysql_query("SELECT * FROM `atr` WHERE ID='$id' LIMIT 0,1", $link);
$line = mysql_fetch_array($result, MYSQL_ASSOC);

echo "<B>" . $line['title'] . "</B> By " . $line['author'] . " (" . $line['date'] . ")
<P>" . $line['content'] . "</P>";

} else {

echo "Ambigous request you n00b";

}

?>

That's the read_article file which displays all the results within the table

This is the method i'm using at the moment. This was when the tutorials, articles and reviews were all seperate tables. Now they are all combined and have a field called "section" to define what data should be displayed

[Iain]

Use the following on articles.php

    // this function will retrieve the data from the appopriate table and return an array of arrays
    function getData($table, $orderBy)
    {
        $query = "SELECT * FROM ".$table.$orderBy;
        $result = mysql_query($query)
        return $result;
    }
    
    // get the section from the URL and define the table accordingly
    switch ($_GET["section"])
    {
        case "articles":
            $table = "articles";
            $orderBy = " ORDER BY title ASC";
            break;
        
        case "tutorials":
            $table = "tutorials";
            $orderBy = " ORDER BY title ASC";
            break;
        
        case "reviews":
            $table = "reviews";
            $orderBy = " ORDER BY title ASC";
            break;
        
        default:
            $table = "articles";
            $orderBy = " ORDER BY title ASC";    
    }
    
    $link = mysql_connect("localhost", "kez", "pwd") or die("Could not connect : " . mysql_error());
    mysql_select_db("kez") or die("Could not select database");

    // run the SQL query and get the results
    $result = getData ($table, $orderBy);
    // loop through the results and output them
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC))
    {
        echo "<b>".$line['title']."</b> By ".$line['author']." (". $line['date'].")<p>" . $line['content'] . "</p>";
    } 

Leave your URLs as you have at the moment. The switch() statement will trap the value of section in the URL and set the $table and $orderBy values appropriately. You can then use one function to get the data. If you're only making the one SQL call then you can do without the function and just use the query.

A UNION takes the results from each SELECT and joins them into one large resultset. It only works if the columns being returned by each SELECT statement are the same. So if your articles, tutorials and reviews tables all have the same columns then it will return all the data from those tables in one large resultset

[Iain]

Oh, and looking at your code you might be putting dates into text fields in the database. NEVER do this, it'll cause you all sorts of headaches later on. Put the date into a DATETIME column and use code to parse it if necessary.

[Kezzer]

Ok after a good couple of hours of mucking around with it i managed to get it to work. New tables had to be created and links had to be changed and so forth. Problem is there's a certain way i need it to be listed.

In this order:
Category,
Title,
Article.

So you would click on tutorials, it would then give you a list saying "hardware, software" etc. and once you click on these it would then take you into the article itself.

/me goes and reads up

This could be difficult

Ta for the help Az that was extremely useful

[Mart]

What is actually different between "articles", "tutorials" and "reviews". Do articles have some attributes that others do not? I guess they would all have an author, title, date and category.

If they share the same attributes I would put them all in a single table with an extra attribute "type". You would define the "type" in a seperate table (1-articles, 2-tutorials, 3-reviews, etc).

For a new content type all you need to do is add a new entry to the type table. (Rather than create a new table, change the db code.....)

Thats my 2p on table design

[Iain]

Mart is right. Without knowing your database design it's hard to say but I'm guessing you need something like this

CREATE TABLE `categories` (
  `ID` int(11) NOT NULL auto_increment,
  `category` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`ID`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;


CREATE TABLE `item_types` (
  `ID` int(11) NOT NULL auto_increment,
  `item_type` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`ID`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;


CREATE TABLE `items` (
  `ID` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `author_ID` int(11) NOT NULL default '0',
  `creation_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `categories_ID` int(11) NOT NULL default '0',
  `explanation` longtext NOT NULL,
  PRIMARY KEY  (`ID`),
  KEY `author_ID` (`author_ID`,`categories_ID`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;


CREATE TABLE `users` (
  `ID` int(11) NOT NULL auto_increment,
  `username` varchar(20) NOT NULL default '',
  `first_name` varchar(30) NOT NULL default '',
  `last_name` varchar(30) NOT NULL default '',
  `email` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`ID`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

Use foreign keys on your table as much as possible to maintain the flexibility

[Kezzer]

Blimey! I haven't really read up on database design, i did it at college but it's completely different to this (and i need some more books )

By using the switch i have to have seperate tables to choose from "articles", "tutorials" and "reviews". If they're in one table how to i tell the switch to select certain categories?

[Iain]

Depends on the ID of your categories but you're going to want something like this

// this function will retrieve the data from the appopriate table and return an array of arrays
     function getData($itemTypeID, $orderBy)
    {
        $query = "SELECT items.*, item_types.item_type 
        FROM items, item_types
        WHERE items.item_types_ID=item_types.ID AND item_types.ID=".$itemTypeID;
        $result = mysql_query($query)
        return $result;
    }
        
    // get the section from the URL and define the table accordingly
    switch ($_GET["section"])
    {
        case "articles":
            $itemTypeID = 1;
            $orderBy = " ORDER BY title ASC";
            break;
        
        case "tutorials":
            $itemTypeID = 2;
            $orderBy = " ORDER BY title ASC";
            break;
        
        case "reviews":
            $itemTypeID = 3;
            $orderBy = " ORDER BY title ASC";
            break;
        
        default:
            $itemTypeID = 1;
            $orderBy = " ORDER BY title ASC";    
    } 

In actual fact you should do away with the word in the URL and replace it with the ID, would make it a lot easier e.g.

article.php?section=1

code would be

$query = "SELECT items.*, item_types.item_type
        FROM items,item_types
        WHERE items.item_types_ID=item_types.ID AND item_types.ID=".$_GET["section"]." ORDER BY title ASC";
        $result = mysql_query($query)
        return $result; 

That way you don't need the switch unless you want other variables in the SQL e.g. the order by statement. Also you can change the name of any of the item types at any time and there will be no effect on the code

[Kezzer]

right, let me have a play and i'll tell you how i do. This may take a while

[Kezzer]

by the way, go to http://www.kezzer.co.uk/index1.php

I've done what you said, just getting that error now which is the line:

while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) 

I've done something wrong obviously, i created all the tables using the mySQL as well. Not entirely sure.

[Mart]

Warning: Supplied argument is not a valid MySQL result resource in /home/kezzeruk/public_html/article.php on line 59

My guess would be your SQL query might be failing so it doesn't return a resultset.

- Are you connected to the database
- Check your SQL statement carefully, especially table and column names. (Might help to write it back to the screen for debugging)

Disclaimer: I don't use php or mySQL