SQL Database to website

Butters · 20 Oct 2007 at 17:50

Hi,

Thanks to help from guys on here I've managed to get my enquiry form writing to a MySQL database with the use of php and phpmyadmin.

So, now that I am getting to grips with phpmyadmin I have anohther question.

I have created another table called 'news' with IDs and and text area for the article and a date and author field.

What I am interested in now is how to link this to a webpage.

so I have news.html (or news.php in my case) and I want the article to appear within a certain div (or at minimum withing the body tags).

How do I go about this?

Thanks

Sic · 20 Oct 2007 at 18:42

this should get you going. I always found that tizag explains things nice and clearly, so it's probably best to have a look at that

SiriusB · 20 Oct 2007 at 19:17

Not seen your other thread [I don't think] but retrieving data from a database is easy.

The below code is a very basic example and is written off the top of my head - so there may be errors

Code:

$connection = mysql_connect("localhost", "user", "pass");
if(!$connection)
{
     die ("Connection Error");
}

$database = mysql_select_db("databaseName");

if(!$database)
{
     die ("Database Selection Error");
}

$query = "SELECT * FROM news WHERE article_id = 1"; //changing the value as necessary

$result = mysql_query($query);

if(!$result)
{
     die ("Error in query: " . mysql_error());
}

$article = mysql_fetch_array($result)

$article now holds an array of values from the returned query. You can access the values like so:

Code:

echo $article['title'];

Where 'title' is the name of the fields in your database table.

When you are done accessing the database it is good practice to close the database connection with

Code:

mysql_close();

Obviously you will need to modify the SQL query to fit your needs. If you plan to use user input in your SQL string you will need to make sure you sanitize it. You should use Google to find out how to sanitize your SQL inputs.

Hope that helps

marc2003 · 20 Oct 2007 at 19:51

for a page like this, the obvious input is going to be using id via $_GET.

news.php?id=4

as siriusb said, we need to sanitise this - make sure no-one can mess with our db. this is easy because id will have to be a number so we use intval.

Code:

//if anything other than a number gets used as our id, it will set to 0.
$id = intval($_GET['id']);
if($id > 0) {
      $result = mysql_query("SELECT * FROM db WHERE id = '$id'")
      if(mysql_num_rows($result) == 1) {
        //article found. display it
      } else {
        //article not found. re-direct back to index.php
      }
} else {
      //id is not a valid number. re-direct back to index.php
}

Butters · 21 Oct 2007 at 22:51

Thanks guys, Sirus / Marc. I've managed to get it working, not least becasue phpmyadmin allows me to generate the code too (although not all of it).

Its still a bit messy at the moment but I'm getting there and your help is certainly appreciated.

One other question ( i hope its easy). The idea of this news page is for me to write in simple WYSIWYG text into an HTML form, that gets entered into the news table of the database and obviously automatically displays.

So, is there a way to firstly use WYSIWYG in a text box somehow which generated the appropriate HTML to insert into the database?

AND?

I obvioulsy want an admin only to access this part of the site, so I have created another table called Admins with username and password fields. The admin access www.mysite.com/admin.php, this links to a form where the user enters their name and password. I need help making this page and also, this should link to this WYSIWYG form.

Hope that makes sense.