What d'ya think of this?

[Kezzer]

/me imagine the bubble petruding from Az's head "oh not another one from kez"

Ok, this will probably be second to last title for my database on my site.

Now i've decided to ditch tutorials and reviews 'cause we all know why so i'm just sticking with articles. No you can't know what the articles section is going to contain until it's there

Right, so two files. Reminder i'm using a switch. One to pre-view the information and one to view the content.

File one, to pre-view.

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

$result = mysql_query("SELECT ID,title,creation_date,explanation FROM `items` ORDER BY title ASC", $link);
      
      while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "<a href=\"article.php?page=read_tutorials" . "&id=" . $line['ID'] .  "\">" . $line['title'] . 
"</a>: "  . $line['creation_date'] . "<br />" . $line['explanation'];
;
}

mysql_close();
?>

File read_article.php to display the content.

<?php

if (!is_null($id)) { 


$link = mysql_connect("localhost", "kez", "kez") OR DIE("Awww, can't connect to the database :(");
mysql_select_db("kez");

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

echo "<div id=\title\>" . $line['title'] . "</div>" . $line['creation_date'];
<P>" . $line['content'] . "</P>";

} else {

echo "Can't display data.";

}

?>

These are my own concuctions so i expect there to be lots of nasty errors.

Is this the correct method to do it? If not, is there a better way to do it *kez mumbles function* or is there something i'm doing completely wrong.

[DaBeeeenster]

I dont write PHP, but you could make your code a bit more modular by pulling the SQL stuff out and placing it in a seperate function. So the function would receive an Article ID as an input and return the content as some sort of data structure.

That way, if you change the table format, you dont have to change the SQL code umpteen times, you only need to change the 1 function...

Aim to avoid code duplication everywhere you find it.

[Iain]

- Use single quotes unless it's a SQL statement you are defining as it will be quicker to run
- Look on google for the term "SQL Injection Hack" as you are leaving yourself wide open to one
- All HTML tags should be in lower case to be XHTML compliant
- Your code assumes there will only ever be one page for an item. Is this the case ? If so then you don't need a separate item_pages table
- have one page that connects to the database and use that as an include. Otherwise as beenster says you'll end up making changes to multiple files

What you've got at the moment could be tidied up to

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

$result = mysql_query("SELECT ID,title,creation_date,explanation FROM items ORDER BY title ASC", $link);
      
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) 
{
echo '<a href="article.php?page=read_tutorials&id='.$line['ID'].'">'.$line['title'].'</a>: '.$line['creation_date'].'<br />'.$line['explanation'];
}

mysql_close();
?>

In fact, I'll rewrite it later, got to go to a meeting just now

[Kezzer]

Yeh i'll only have the articles page. So there would be three tables that the db would use which is for the shoutbox, the news table and an articles table. I don't need 'author' in there 'cause it's only me writing stuff on my website. All i'll need is, ID, title, creation_date, explanation, content and page.

I won't worry too much about how to get it to go on a different page for now though.

Should i get rid of the related tables and create a new one based on the fields i suggested? Also, what attributes should be applied to the fields?

[Iain]

To be honest you've changed the direction of this so many times you'll end up with an inefficient database and inefficient code. Your best bet is to go back to the requirements stage and work out exactly what you want to do and then build from there. Starting with a database that has redundant tables and redundant code will only cause issues later on

[Kezzer]

I'm actually proud of myself, i didn't know that i could fix problems and that. I actually skipped the gym to do this! Alllll by myself

It now works. I'm not sure if i applied the correct attributes to the fields in the table but here's a big long overview:

I put the following in the table (something should be wrong here):
ID int(11) auto_increment primary key
title varchar(255)
creation_date datetime
explanation longtext
content longtext
page_number text

Then the two files. First up, articles.php to display all the rows in the table:

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

$result = mysql_query("SELECT ID,title,creation_date,explanation FROM articles ORDER BY title ASC", $link);
      
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) 
{
echo '<a href="index.php?page=read_article&id=' . $line['ID'] . '">' . $line['title'] . '</a><br />' . $line['explanation'] . '<br /><div id="title"> created on the: ' . $line['creation_date'] . '</div>';
}

mysql_close();
?>

The second file, read_article.php to display the actual content:

<?php

if (!is_null($id)) {

$link = mysql_connect('localhost', 'kezzeruk', 'ilikeboobies') or die('Could not connect : ' . mysql_error());
mysql_select_db('kezzeruk') or die('could not select the database');

$result = mysql_query("SELECT title,creation_date,content FROM articles", $link);
while ($line = mysql_fetch_array($result, MYSQL_ASSOC))

echo '<div id="title">' . $line['title'] . '</div><br />' . $line['content'] . '<br /><br /><div id="title">' . $line['creation_date'] . '</div>';
}

?>

http://www.kezzer.co.uk/index.php?page=articles

Check that page out, click on Test01 and technically it should display what is within it which should be something blah blah blah just to test it.

I make myself proud, i feel grown up for once

[Iain]

Looks like it will do the job I'd make page_number an INT field though, after all it's only ever going to store an integer and this will make it faster to retrieve a page.

Also, look up the nl2br() function for PHP. It will format your content with line breaks

[Kezzer]

Format my content with line breaks? I've read up on it but i never understand the explanation properly. \n i thought was a standard line break in PHP. *confuzzled*

[Iain]

if you enter carriage returns in a box (just like the quick reply box I'm now typing in) then it gets saved in the database as \n

nl2br() which stands for new line to <br> converts all the \n to <br> tags i.e. it preserves the line breaks the user entered into the form

[Kezzer]

ok this isn't going to work. I just put some more examples in and i've just realised i'm telling the read_article page to extract ALL data from the database. How do i get it to display only the information that you click on? lol

[Kezzer]

doh, got it now. I'm too quick to ask

[Gordy]

Also, look up the nl2br() function for PHP. It will format your content with line breaks

Ooh I will be needing that

I had a look at some of the google results for injection hacks and I think I get it .

I have stuck all my admin scripts in a folder which is password and username protected to prevent unauthorised access will this prevent the hacks?

[Iain]

not really, it's more about protecting your database. So make sure you strip out any characters which are likely to cause problems e.g. ' when a user submits a form using the function addslashes() and stripslashes(). Just to complicate things there's a etting in php call gpc_magic_quotes which will automatically parse form posts and gets from slashes so you need to watch whether that's turned on or not. Use a function like this as an include at the top of every page

function magicQuotesHandler()
    {
        if (get_magic_quotes_gpc()) 
        {
            $_GET = array_map('stripslashes',$_GET);
            $_POST = array_map('stripslashes',$_POST);
            $_COOKIE = array_map('stripslashes',$_COOKIE);
        }
    
    } 

Anyway, the SQL injection hack is something similar to the following. Let's say you had some PHP which takes the username and password from a login form and builds a SQL statement as follows

$sql = "SELECT ID FROM users WHERE username='".$_POST['username']."' AND password='".$_POST['password']."'"; 

Now if you can imagine that someone entered the username as
gordy' --

then the first single quote would close the username in the SQL and -- is the comment tag for a SQL query so you'd end up with

SELECT ID FROM users WHERE username='gordy' -- AND password='';

Everything after the -- would be ignored as a comment so if the hacker knows any username on the table they would be able to gain access ! Even worse they could use
gordy'; DELETE FROM users; INSERT INTO users SET username='hacker', password='hacker' --

and therefore remove all other users from the table and set themselves up as the only user. By simply adding a \ before every ' the SQL will treat it as a string and not an escape character

hope that helps

[Kezzer]

fancy stuff there. All i need to do now is create the content management system