[RESOLVED] Automatically generating HTML content from mySQL

[Maharg105]

Hi Guys,

I'm hoping somebody might be able to help me with this, because I've been scratching my head over it for the past few days.

I have a website that displays the title and a quick description of a number of news articles in the left hand column. This information is pulled down using an SQL query that displays the four most recently added articles from the database.

Stored in this database there is also the complete news article. I want my site to assign a link to each of the title variables and for the user to be taken to a page that shows the complete article when they click on it. Obviously I don't want to create a page for each individual article, so I would like for a template to be automatically populated using information from the database.

Unfortunately, I'm quite new to mySql and have no idea where to start with this. Any help anyone could offer, either advice or pointing me towards another resource would be hugely appreciated.

I've tried my best to clearly explain the problem, if anything is unclear let me know. For reference purposes, the site I am working on can be found at www.aventisrecruitment.co.uk/trial/
please ignore the content, it is only there for testing purposes.

Thanks in advance

[scrupul0us]

you could create say article.php and pass it say id= in the query string with a matching article id...

then just perform a SQL query to grab the article based on the ID passed

so your links in the left hand nav would be like:

/article.php?id=3

and in article.php

select * from articles where id=3

then just process the results

once you get it working roughly make sure to cleanse the id so your site cannot be SQL injected... in this case you just want numbers so you can use [man]ctype_digit[/man]

so

if(ctype_digit($_GET['id']))
{
     //SAFE VALUE PERFORM QUERY
}
else
{
     //NOT A DIGIT REDIRECT TO HOME PAGE
}

[Maharg105]

Thanks for replaying scrupul0us, that was exactly the kind of thing that I was thinking of - so it's good to know that I was looking in the right direction.

My problem is that the articles in the news coloumn are not static and change depending on which articles are newest. Therefore, the link needs to be generated on the fly, this is the issue that I am having.

Any ideas how I can achieve this?

Thanks again,

[johanafm]

I'd just stick the four links in a file: article_links.html. When an article is posted or deleted, after you update the database, SELECT id, title FROM article ORDER BY id DESC. Then build complete element links from that: <a href="article.php?id=N">title</a>. Save those four links in a file. Next time someone updates or deletes, you repeat the process and overwrite the file. Your main page just needs to

include 'article_links.html';

in the right place.

[Maharg105]

Thanks for taking the time to look in johanafm. If I fully understand your solution, then I would need to manually create and update article.php each time the database was updated? That wouldn't be viable as it needs to be user friendly, I won't always be the one updating it, and I would need to use the same solution but on a larger scale on my news page where all the articles from the news table are shown.

My code for this section currently looks like this

<?php
$con = mysql_connect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("database", $con);

//get the latest items
$getitem = mysql_query("SELECT id, title, blurb, article FROM news ORDER BY id DESC LIMIT 4")
or die(mysql_error());
while ($rows = mysql_fetch_array($getitem)) {
extract($rows);
$rows['n_sets_per_page'] = 2;

echo "<h3>$title</h3>$blurb<br /><hr />";

}
?>

What I would really like to do when I echo the query results, is have something like this

echo "<h3><a href="/articles.php?id=$id">$title</a></h3>$blurb<br /><hr />";

but I've no idea how to go about implementing this. Any suggestions?

Thanks again for your help guys

[Maharg105]

I'm an idiot, I have now got it working. The resulting code was

echo "<h3><a href='/articles.php?id=$id'>$title</a></h3>$blurb<br /><hr />"; 

My problem was that I was trying to nest double apostrophe within double apostrophe, where as I actually needed single apostrophes around the a href tag.

Thanks for your help guys, it was your ideas that eventually got me there. Thankfully this is now resolved 🙂

[johanafm]

Maharg105;10937035 wrote:

then I would need to manually create and update article.php each time the database was updated?

I was assuming you were using a web page to create and update articles.