Knowledgebase creation using SQL back-end

@thekoreJun 25.2004

me and my friend are wanting to create a knowledge base, full of technical articles.
We would require a SQL db and a form to insert the following:
Article ID (need to increment with each addition)
Article Title :
Article summary:
Article Full:
Date Added:

we want to be able to use a web-based form to add articles to the db,
we also want to search for an article by ID number or from a list of article titles to edit.
A list of all the article titles must be able to be inserted into an XHTML Strict (php) page i want everything to validate well.
as well as the 10 latest additions.

if anyone can help it would be very much appreciated ?

thanking you in advance

to post a comment

PHP

11 Comments(s) _↴

@ShrineDesignsJun 25.2004 — #the table:CREATE TABLE articles (id MEDIUMINT NOT NULL AUTO_INCREMENT, title TINYTEXT NOT NULL, summary TEXT NOT NULL, article LONGTEXT NOT NULL, added DATE NOT NULL, PRIMARY KEY (id), INDEX (title (128)), INDEX (summary (255)), INDEX (added))[/quote]list last 10:SELECT * FROM articles ORDER BY id DESC LIMIT 10[/quote]

@thekoreauthorJun 25.2004 — #thanks, i now have my fields within my table but now how do i go about coding (in php) my forms now to enter data into those fields?

@ShrineDesignsJun 26.2004 — #try this:

[code=php]<?php
 if($_POST)
 {
 if(ini_get('register_globals') == "0")
 {
 foreach($_POST as $key => $value)
 {
 $$key = $value;
 }
 }
 $link = mysql_connect('localhost', $username, $password);
 mysql_select_db($database, $link);
 $result = mysql_query("INSERT INTO articles VALUES ('', '$title', '$summary', '$article', NOW())", $link);
 
  if($result !== false && mysql_affected_rows($link) != 0)
  {
   echo "Article successfully added to database.n";
  }
  else
  {
   echo mysql_error($link);
  }
 }
 ?>
 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 <html>
 <head>
 <title>Untitled Document</title>
 <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
 <style type="text/css">
 <!--
 table, input, textarea, select {
 font-family: Verdana, Arial, Helvetica, sans-serif;
 font-size: 10px;
 }
 .textfield {
 width: 250px;
 }
 -->
 </style>
 </head>
 <body>
 <form action="" method="post">
 <table border="0" align="center" cellpadding="0" cellspacing="5">
 <tr>
 <td align="right" valign="baseline" nowrap>Title:</td>
 <td><input name="title" type="text" class="textfield" id="title"></td>
 </tr>
 <tr>
 <td align="right" valign="baseline" nowrap>Summary:</td>
 <td rowspan="2"><textarea name="summary" rows="3" class="textfield" id="summary"></textarea>
 </td>
 </tr>
 <tr>
 <td>&nbsp;</td>
 </tr>
 <tr>
 <td align="right" valign="baseline" nowrap>Article Body:</td>
 <td rowspan="2"><textarea name="article" rows="8" class="textfield" id="article"></textarea>
 </td>
 </tr>
 <tr>
 <td>&nbsp;</td>
 </tr>
 <tr>
 <td>&nbsp;</td>
 <td align="center"><input name="s1" type="submit" id="s1" value="Submit"> <input name="r1" type="reset" id="r1" value="Reset"></td>
 </tr>
 </table>
 </form>
 </body>
 </html>
 [/code]

@thekoreauthorJun 26.2004 — #thanks, but i had a go myself. I have two files for adding articles with only one part not working... the date?

all the dates are being added as 0000-00-00 so i am guessing that it isnt an automatic feature so i must me missing a section in my code.

here are the two file:

index.html

&lt;HTML&gt;
 &lt;HEAD&gt;
 &lt;TITLE&gt; Windows-Hints Knowledge Base &lt;/TITLE&gt;
 &lt;/HEAD&gt;
 &lt;BODY BGCOLOR="#FFFFFF"&gt;
 &lt;center&gt;
 &lt;FORM METHOD=POST ACTION="add.php"&gt;
 &lt;input type="hidden" name="id" value="NULL"&gt;
 &lt;TABLE&gt;
 &lt;TR height="20"&gt;&lt;TD colspan="2"&gt;&lt;FONT SIZE="+0" face="verdana"&gt;
 ADD ARTICLE&lt;/TD&gt;&lt;/TR&gt;
 &lt;TR height="50"&gt;
 &lt;td&gt;&lt;/td&gt;&lt;/TR&gt;
 &lt;TR&gt;&lt;TD align="left"&gt;&lt;FONT SIZE="+0" face="verdana"&gt;
 &lt;b&gt;Title &lt;p&gt; &lt;INPUT TYPE="text" NAME="title"&gt; &lt;p&gt;
 Summary &lt;p&gt; &lt;INPUT TYPE="text" NAME="summary"&gt;&lt;p&gt;
 Full Article &lt;p&gt; &lt;textarea rows="20" cols="50" name="article"&gt;&lt;/textarea&gt;
 &lt;/b&gt;&lt;/td&gt;
 &lt;td&gt;&lt;br&gt;&lt;/TD&gt;
 &lt;/TR&gt;
 &lt;tr&gt;&lt;td colspan="2"&gt;&lt;center&gt;
 &lt;p&gt;&lt;INPUT TYPE="submit" value="Add Article"&gt;&lt;/td&gt;&lt;/tr&gt;
 &lt;/TABLE&gt;&lt;/FORM&gt;&lt;/BODY&gt;&lt;/HTML&gt;

and add.php

[code=php]<?
 $DBhost = "localhost";
 $DBuser = "my_user";
 $DBpass = "my_password";
 $DBName = "my_database";
 $table = "articles";
 mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");
 
 @mysql_select_db("$DBName") or die("Unable to select
 database $DBName");
 
 $sqlquery = "INSERT INTO $table
 VALUES('$id','$title','$summary','$article','$added')";
 
 $results = mysql_query($sqlquery);
 
 mysql_close();
 
 print "<HTML><TITLE> PHP and MySQL </TITLE><BODY
 BGCOLOR="#FFFFFF"><center><table border="0"
 width="500"><tr><td>";
 print "<p><font face="verdana" size="+0"> <center>You
 Just Entered This Information Into the
 Database<p><blockquote>";
 print "Title : $title<p>Summary : $summary<p>Article :
 $article</blockquote></td></tr></table>
 </center></BODY></HTML>";
 ?>
 [/code]

I will try again to get the date function working correctly but any help would be appreciated ?

@thekoreauthorJun 26.2004 — #i have decided to drop the data added field, because it was uneccessary.

What i need to know now is, how do i pull the information from the SQL DB into a php file to display a list of all the fields i.e

id - title

1 - article 1

2 - article 2

but when clicked open up a full view of

id - 1

title - article 1

article - bla bla bla bla bla

is printed into a php page?

@ShrineDesignsJun 26.2004 — #example:

[code=php]<html>
 <head>
 <title>Untitled Document</title>
 <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
 </head>
 <body>
 <?php
 $link = mysql_connect('localhost', 'username', 'password');
 mysql_select_db('database', $link);
 $result = mysql_query("SELECT * FROM articles", $link);
 
 if($result !== false)
 {
 ?>
 <table border="0" cellpadding="0" cellspacing="5">
 <tr>
 <th>id</th>
 <th>title</th>
 <th>summary</th>
 <th>article</th>
 </tr>
 <?php
 while($row = mysql_fetch_array($result, MYSQL_NUM))
 {
 list($id, $title, $summary, $article) = $row;
 ?>
 <tr valign="top">
 <td><?php echo $id; ?></td>
 <td><?php echo $title; ?></td>
 <td><?php echo $summary; ?></td>
 <td><?php echo $article; ?></td>
 </tr>
 <?php
 }
 mysql_free_result($result);
 ?>
 </table>
 <?php
 }
 mysql_close($link);
 ?>
 </body>
 </html>
 [/code]

@shimonJun 28.2004 — #Regarding the date issue - might be nicest to use the MySQL now() function to automatically storee the current date/time.

@thekoreauthorJun 28.2004 — #[i]Originally posted by ShrineDesigns [/i]

[B]example: blah blah blah[/B][/QUOTE]

Ok that worked but im still a little unsure as to how to get that [b]one[/b] article displayed on its own... i.e on the list provided by the above php file, i need to get links created which when click will only display that one article ?

@ShrineDesignsJun 29.2004 — #you could do something like this:

[code=php]<?php
 $link = mysql_connect('localhost', 'username', 'password');
 mysql_select_db('database_name', $link);
 
 if(isset($_GET['view']) && !empty($_GET['view']))
 {
 // displays an article
 $result = mysql_query("SELECT title, article FROM articles WHERE id={$_GET['view']}", $link);
 
  if(($row = mysql_fetch_array($result, MYSQL_NUM)) !== false)
  {
   list($title, $article) = $row;
   $article = wordwrap($article, 76, 'n  ', 0);
   echo "<p><b>{$title}</b><br />n  {$article}</p>n";
  }
  else
  {
   echo "Error, invalid article id: ({$_GET['view']}).<br />n<a href="articles.php">view all articles</a>";
  }
 }
 else
 {
 // displays a list of articles
 $result = mysql_query("SELECT id, title, summary FROM articles", $link);
 
  while($row = mysql_fetch_array($result, MYSQL_NUM))
  {
   list($id, $title, $summary) = $row;
   echo "<p><a href="articles.php?view={$id}">{$title}</a><br />n  {$summary}</p>n";
  }
 }
 mysql_free_result($result);
 mysql_close($link);
 ?>[/code]

@thekoreauthorJun 29.2004 — #ok, that works to a degree. When i got directly to articles.php i receive Fatal error: Call to undefined function: mysql_fetch_aray() in /home/thekore/public_html/whkb/articles.php on line 25[/quote] i dont really understand that other than there is a function which hasnt been defined :rolleyes:

when i go to articles.php?view=(id) it does display the article ? ty

But any idea what i have done in the first bit... or did i missunderstand that should there be no view=(id) parameter then a full list of articles are displayed along with their links? ?

{edit} that error was my fault, typed too fast and missed an 'r' in array :p

@thekoreauthorJun 29.2004 — #ok, after a bit of editing i have the full thing validating to xhtml strict and working correctly. Im going to need to get a function created so i can pull the information out of the SQL database so i can edit it and save changes.

I have no idea how to do that but i think i will look around and see what i can find. If you want to add the way in which you would do it then please do post it, i might be back asking for more help ?

Also in #PHP _↴

Redirect only once IFRAME Problem Php Hyperlinking

Success!

Help @thekore spread the word by sharing this article on Twitter...

Tweet This

Knowledgebase creation using SQL back-end

11 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

Knowledgebase creation using SQL back-end

11 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

11 Comments(s) _↴

Also in #PHP _↴