dynamical links to database articles

iaoleary

I have a mysql database with articles in it.
I query the database and create a list of the articles in my page.

But this list is just static content.

I want to write a script that will list the articles in the database and present them on the page as links, so that when I click on the link, it will load another page that has the contents of the article.

This seems like such a basic thing but no book I have looked at has this example.

Anyone know what code I need to write.

Here is what I have so far...

<html>
<head>
<title>List of John's United Articles</title>
</Head>

<body>

<?php

//ESTABLISH A CONNECTION

$dbcnx = @mysql_connect('localhost','root','');
if(!$dbcnx){
echo('<p>Unable to connect to the database!</p>' );
exit();
}

//SELECT A DATABASE

if(! @mysql_select_db('osheaarticles')) {
	die( '<p>Unable to locate the "osheaarticles" database!</p>' );
}

<p> Here are all the united articles in the database </p>
<blockquote>

<?php
//SEND SQL QUERY

$results = @mysql_query('SELECT title FROM unitedarticles');
if(!$results) {
	die('<p> Error performing "title FROM unitedarticles" query: '.mysql_error().'</p>');
}

while ( $row = mysql_fetch_array($results)) {
	echo('<ul>'.$row['title'].'</ul>');
}

?>
</blockquote>
</body>
</html>

matt_4013

It's pretty straight forward. You are nearly there, you just really need to change your query a little, and add in some html 🙂

I'm assuming each article has a unique ID, with the fieldname of "id" here, if that's not it, just replace each instance of "id" with whatever it should be 🙂

You change your query so it gets the id as well, and use that as a unique identifier, then create a link to the page that will display the specific article, with a query string.

Here is the code:

<html>
<head>
<title>List of John's United Articles</title>
</Head>

<body>

<?php

//ESTABLISH A CONNECTION

$dbcnx = @mysql_connect('localhost','root','');
if(!$dbcnx){
echo('<p>Unable to connect to the database!</p>' );
exit();
}

//SELECT A DATABASE

if(! @mysql_select_db('osheaarticles')) {
die( '<p>Unable to locate the "osheaarticles" database!</p>' );
}
?>

<p> Here are all the united articles in the database </p>
<blockquote>


<?php
//SEND SQL QUERY

$results = @mysql_query('SELECT id, title FROM unitedarticles');
if(!$results) {
die('<p> Error performing "id, title FROM unitedarticles" query: '.mysql_error().'</p>');
}

while ( $row = mysql_fetch_array($results)) {
echo('<ul><a href="article.php?id='.$row['id'].'">'.$row['title'].'</a></ul>');
}

?>
</blockquote>
</body>
</html>

bj_

I would use a javascript that listens for clicks on article links. You would have to make each article link of class='article' or something common to all articles so that the javascript could distinguisth the link. Then have the javascript POST or Get the name of the article to the same PHP page. Basically, make it so you have a $POST['ARTICLE'] = article_name when you return to the page. If the $POST['ARTICLE'] variable is set then use a query and a function to display the article.

Here is my Idea. I'll try to get back with the javascript. I need to write something like this for one of my projects.

example:
<html>
<head>
<title>List of John's United Articles</title>
<script language = "javascript">
// I don't know much java script so it's just a skelaton

getArticle ( alink ) {
if ( alink.class = 'article' ) {
// write an invisible form ( action="current php page" method = "post" )
// ( invisible input with name = ARTICLE and value)
// ( value set to your article name )
// use form_name.submit() // this will bring you back to the
}
else (
//navagate to alink
}
}

getArticle = onClick( href );
</script>
</Head>

<body>

<?php

//ESTABLISH A CONNECTION

$dbcnx = @mysql_connect('localhost','root','');
if(!$dbcnx){
echo('<p>Unable to connect to the database!</p>' );
exit();
}

//SELECT A DATABASE

if(! @mysql_select_db('osheaarticles')) {
die( '<p>Unable to locate the "osheaarticles" database!</p>' );
}
?>

<p> Here are all the united articles in the database </p>
<blockquote>

<?php
//SEND SQL QUERY

// check if user already selected an article
if ( !isset( $_POST('ARTICLE') {
$results = @('SELECT title FROM unitedarticles');
if(!$results) {
die('<p> Error performing "title FROM unitedarticles" query: '.mysql_error().'</p>');
}

while ( $row = mysql_fetch_array($results)) {
echo('<ul>'.$row['title'].'</ul>');
}
}
// user has already selected an article
else {
displayArticle( $_POST('ARTICLE') );
}

function displayArticle( $article ) {
// mySql query
// display formating
}

?>
</blockquote>
</body>
</html>

iaoleary

So in the code you posted (much thanks!) I am passing the id in the url to the article.php page, right?

How do I get that id value into my query sql to retrieve the article content?

Regards, Ian

matt_4013

In my example, yes. I'm not entirely sure why bj_ is going into the complexity he is, because it's not at all necessary, and wont work for people with JavaScript turned off ...

Anyway, to create your query (in article.php), you would do this:

// I'm guessing your fieldnames
$sql = "SELECT title, author, body FROM unitedarticles WHERE id='".mysql_escape_string($_GET['id'])."'";

// Then run your query
$result = mysql_query($sql) or die(mysql_error());

We use mysql_escape_string so people can't be assholes and destroy our query 🙂

bj_

Matt is right, his way is much better. I just like keeping the address bar clean, but it isn't worth all the extra effort for something most people wouldn't notice.

matt_4013

ahhh, ok, that makes sense 😉 If you want to give that a go, here's some example code 🙂

Create your hidden form:

<form name="article_form" action="<?=$_SERVER['PHP_SELF']?>" method="post">
<input type="hidden" name="id" value="">
</form>

	
	// And your links (I'm assuming you've already queried the database with the results in $result)
	while($row = mysql_fetch_assoc($result)) {

	echo "<a href=\\"#\\" OnClick=\\"javascript: SubmitForm('".$row['id']."');\\">".$row['title']."</a>";
}

Your javascript should look like this:

document.forms['article_form'].elements['id'].value = id;
document.forms['article_form'].submit();

}
</script>

bj_

Thanks. That was the code I was looking for. Your right about people haveing javascript turned off though. Also, you have to start worrying about cross browser stuff. I was trying for hours to come up with code like that but I am horible with javascript.

Thanks again.

iaoleary

can either of you guys see what is wrong with this?

$sql = "SELECT title, contents FROM unitedarticles WHERE id='".mysql_escape_string($_GET['id'])."'";
$result = mysql_query($sql) or die('<p> Error performing "id, title FROM unitedarticles" query: '.mysql_error().'</p>');
while ($row = mysql_fetch_array($results)) {
echo('<tr><td><p>'.$row.'</p></td></tr>');
}

The error I get says that I am not passing the right argument in the mysql_fetch_array($results) function.

Basically, I have asked for the article title and contents but don't know how to get them out of the results set.

Any thoughts?

Thanks, Ian

bj_

Change
mysql_fetch_array($results) to
mysql_fetch_array($result)

You have two different variables there.

I think you might want to do it like this:

$result = mysql_query($sql) or die('<p> Error performing "id, title
FROM unitedarticles" query: '.mysql_error().'</p>');

while ( $rows = mysql_fetch_array($result) ) {
echo('<tr><td><p>'.$row['title'].$row['contents'].'</p></td></tr>');
}

if there is only one article you don't need the looping structure;
you just need this:
$rows = mysql_fetch_array($result);
echo('<tr><td><p>'.$row['title'].$row['contents'].'</p></td></tr>');