Hi, I was wondering how to do this.

Scooby_Doo

Hi, I'm testing some code and trying to learn how to do this.

My goal is to have a form, that sends data into the database. (I've already built a working one).

In the form I would like to choose a category list.
(example, if I'm adding an article on trees I want it to go into the outdoor section).

and if they go to the to the outdoor section is list all the articles that have been added to that category and they can click on a link and it takes them to the whole article.

I can put data into the database and pull it back out with ease, I just don't know how to do the rest.

I don't expect anyone to give me the whole script, I just want someone the point me in the right direction.

Thanks in advance.

greensweater

This is a very broad topic but this may give you some ideas. I am assuming you are using MySQL as a database for this example.

Two database tables:

categories: id (auto_increment primary key), name, description
articles: id (auto_increment primary key), categoryid, title, content

Two web forms:
category edit: textbox "name", textbox "description" (this will allow the user to create categories)

article edit: textbox "title," textarea "content", drop-down "category". Populate the category drop-down with "SELECT * FROM categories ORDER BY title".

When you add the article to the database, include the categoryid in the article record:

$category = mysql_real_escape_string($_POST['category']);
$title = mysql_real_escape_string($_POST['title']);
$content = mysql_real_escape_string($_POST['content']);
mysql_query("INSERT articles (categoryid, title, content) VALUES '{$category}','{$title}','{$content}'");

You can create a menu of categories with something like:

[[menu.php]]
$result = mysql_query("SELECT * FROM categories");
while($row = mysql_fetch_assoc($result))
{
    echo "<div class='menuitem'><a href='category.php&id={$row['id']}'>$row['name']</a></div>";
}

To get all of the articles in a particular category:

[[category.php]]
$catid = mysql_real_escape_string($_GET['id']);
$result = mysql_query("SELECT * FROM articles WHERE categoryid = {$catid}");
while($row = mysql_fetch_assoc($result))
{
    echo "<p class='articlelink'><a href='article.php&id={$row['id']}>$row['title']</a></p>";
}

The mysql_real_escape_string is there to prevent sql injection attacks when using user input in database queries. I will leave it to you to determine the CSS classes for menuitem and articlelink.

Scooby_Doo

Thank you so much for giving me that information. That is so much more than I expected. I have run into an error though on the last part for the category though an I can't figure it out.

For some reason it won't display the link here.

<?php
$conn = mysql_connect(HOST, USERNAME, "PASSWORD") 
or die
    ('Error connecting to mysql');
mysql_select_db(DATABASE);						

$catid = mysql_real_escape_string($_GET['id']);
$query = "SELECT * FROM articles WHERE categoryid = $catid";
echo "$query";
$res = mysql_query($query);
while($row = mysql_fetch_array($res))
{
    echo "<a href='/articles/outdoor.php&id={$row['id']}'>";
	echo $row['title'];
	echo " Theme</a>";
}

mysql_close($conn);
?>

I echoed the query and it won't display the id number.

Again thank you so much.

greensweater

change:

while($row = mysql_fetch_array($res))

while($row = mysql_fetch_assoc($res))

The mysql_fetch_array returns an array with the columns numerically indexed. (i.e. $row[0], $row[1] etc.) You have to use the mysql_fetch_assoc to return an array with key/value pairs with column names. (i.e. $row['id'], $row['title'] etc.)

Scooby_Doo

I'm sorry its taken me so long to respond, I've been really busy. Back on subject I got a lot of it to work but now my problem is in the display category page. For some reason it displays the categories id instead of the articles. I don't know why and can't figure it out thanks.

<?php
$conn = mysql_connect(HOST, USERNAME, "PASSWORD") 
or die
    ('Error connecting to mysql');
mysql_select_db(DATABASE);						

$id = mysql_real_escape_string($_GET['id']);
$query = "SELECT id, title FROM info WHERE id = $id";
$res = mysql_query($query);
echo "$query";
	echo "<table border=\"1\">";
	echo "<tr>";
while($row = mysql_fetch_assoc($res))
{
	echo "<td>";
    echo "<a href=\"/articles?id=";
	echo $row['id'];
	echo "\">";
	echo $row['title'];
	echo "</a>";
	echo "</td>";
}
	echo "</tr>";
	echo "</table>";
mysql_close($conn);
?>

roscor

what is the structure of your mysql table?

you could change your query

$query = "SELECT * FROM info WHERE id = $id";

where * means everything

Scooby_Doo

roscor wrote:
what is the structure of your mysql table?

What do you mean? like how many tables I have in it, if so then 16. I am trying to build a couple of pages to show what I have built recently. like a portfolio.

I don't know how clear so I will try to explain myself better.

It pulls the same id number from info as the categories.

Like this

Category id = 5

Info id = 4

but it pulls

Info id = 5

instead of 4

I hope you understand its hard to explain.

greensweater

You are setting the query parameter according to the input $_GET['id'] ... therefore, you are selecting the record from the "info" table with the same id. You need an intermediate result where you match the category_id to the info_id. Something like:

$id = mysql_real_escape_string($_GET['id']);
$qry = "select infoid from categories where id = '$id'";
$res = mysql_query($qry);
$infoid = mysql_result($res,0,0);

$qry = "select id,title from info where id = '$infoid'";
$res = mysql_query($qry);
while ($row = mysql_fetch_assoc($res))
{ ...

laserlight

You need an intermediate result where you match the category_id to the info_id.

Are you sure? It looks possible to do a join, e.g.,

$qry = "SELECT info.id, info.title FROM info, categories
        WHERE info.id = categories.infoid AND categories.id='$id'";
$res = mysql_query($qry);
while ($row = mysql_fetch_assoc($res))
{ ...