Topic awaiting preservation: SQL & PHP Question

This one may be reaching but I don't know where else to turn...

I volunteer for my church as an administrator for 38 websites globally, and there's me (for the design side) and a programmer that's super awesome at php, coding, etc. Problem is, he's super swamped and hasn't had time to help put something together that I think will make updating all the sites easier.

We have a database that we input articles in each week, what I'd like to do is create a pull to retreive the data and place it in each website through a php pull. Now I'm totally clueless to using databases so my question is this... Exactly what would I need to learn to be able to put something like this together?

If I were to have someone help me do this, what information would someone need to know to help me? I'm at the point where I'd almost be willing to offer a little something out of pocket to achieve this as right now I have to manually update many sites. UGH....

If anyone would care to help me out, please email me at rob(at)usd21.org

Thanks guys.

A few questions come to mind.

What kind of sites are they? Custom made or blogs/cms etc? Do they have the same/similar architecture?

IMO the simplest way for a beginner to approach this would be this:

1) Create a simple php script that can update 1 site. This script should have a simple form and all the fields you may need such as subject, body etc. It should connect to the database of one site and make a post there.
2) Start adding more sites. The insert form should stay the same for all of them.

Is this what you are looking for? What is it exactly that you don't understand?

Well right now we input the content into a database that I didn't develop. One central location. We do the same with audio and video files to distribute them to all of the sites. I'd like to do the same with the text content but I didn't create it and the person that did, just had a baby, and doesn't have too much free time to put this together.

The site is www.usd21.org and I'd like to just have one include to pull the content. Alot of the sites I look over are WordPress sites to allow web novices to contribute and maintain the sites themselves, so with runPHP enabled on the WP sites, it should work the same way.

Make sense?

Though I've already emailed Radical Rob with regards to this, I thought I'd take this opportunity to give a very high level overview of the simplest way of accessing a MySQL database with PHP.

There are four (sometimes five) things that need to be defined in order access a MySQL database: The username and password used to access it, the hostname, and the database name. Sometimes you also need a port, but the defaults will suffice for most systems.

Let's imagine that you have a table of articles that you want to display, first as a list, and then one specifically when that article's title is clicked. You may have a database structure that looks something like this:

+------+
| id   |
| date |
| name |
| text |
+------+

That just means that you have a unique id number tied to each article's name and a date that it was posted. Pretty simple, right? So how do you access this information from a website?

The first step is connecting to the database server. This is done through the PHP command mysql_connect() which requires those things you found out above. So open up, say, articles.php and you may have something like this:

<?php
$username = 'twitch';
$password = 'twitchistotallysweet';
$hostname = 'localhost'; // This is often the case, but hostnames can vary wildly.
$database = 'twitch-database';

mysql_connect($hostname, $username, $password); // Open a connection to the database
mysql_select_db($database); // Select a database to access

// Something happens here

mysql_close(); // Close the connection to the database
?>

So now you have a script that defines your connection strings, creates a connection, and then closes the connection. But how do you access that data? Well, you need to write an SQL statement that will plumb the depths of your database & return the values that you can then parse out. Let's say you want to collect all the articles in there, and display them in a list, ordered by the date they were posted, newest to oldest. Rather than give a breakdown of SQL (that's a whole other topic), here's what that might look like:

$sql = "SELECT id, date, name FROM articles ORDER BY date DESC";

That is pretty self-explanatory. You're selecting the id (necessary when creating a link--we'll get to that), the name of the article, and the date. You don't need the text on this one, since you're just creating a list. But what do we do with this statement? Well, it's surely going to go in the "// Something happens here" line, right? Correct. But we need to query the database with this statement, and the parse the results, row by row--just like in an Excel spreadsheet, data is stored in rows, with each column being headed by one of the options in the table description above (id, name, etc). And since we're expecting more than one row, we can't just throw it all up at once, that'd be ridiculous! So, we fetch it row by row once the query has returned results...

// Something happens here

$sql = "SELECT id, date, name FROM articles ORDER BY date DESC";
$result_set = mysql_query($sql);
while($row = mysql_fetch_array($result_set)) {

   // More stuff happens here!

}

So, what we've done is declare our SQL, sent it to the MySQL database as a query, and have begun parsing the results in a while() loop, with each row being declared as the variable $row. So we're almost there! But what do we do with the data, now that we've been able to capture it? Well, that's up to you. Let's say at the top of this you opened an HTML ordered list (<ol>) and you want to put in list items (<li>) with each of the articles...

// More stuff happens here!

   $name = $row['name']; // The brackets denote an array, and the 'name' is actually returning the name that MySQL is giving it, right from the table itself!
   $id = $row['id']; // It doesn't matter in which order you declare these variables.


   $date = $row['date']; // This is going to be a SQL datestamp, which you may want to modify if you don't like YYYY-MM-DD HH:MM:SS format.
   echo("<li><a href=\"detail.php?id=$id\">$name</a> - $date</li>"); // If you use double quotation marks, PHP will parse whatever happens inside it.

And that will generate a happy list item for each article stored in the database, creating a link out of each article's name to another page (detail.php) with the unique ID of the article passed through the querystring. Now, when you create detail.php, you're already going to be handed the unique ID to display the entire article. PHP treats querystring variables as $_GET variables. So, let's do a quick mock up of detail.php, with less interruption of my explanations...

<?php
$username = 'twitch';
$password = 'twitchistotallysweet';
$hostname = 'localhost'; // This is often the case, but hostnames can vary wildly.
$database = 'twitch-database';

mysql_connect($hostname, $username, $password); // Open a connection to the database
mysql_select_db($database); // Select a database to access
$article_id = $_GET['id']; // Here's where you pull the article ID from the querystring
$sql = "SELECT name, date, text FROM article WHERE id = " . mysql_real_escape_string($article_id) . " LIMIT 1"; // Please read the link there to begin to learn about SQL injection attacks
$result_set = mysql_query($sql);
$row = mysql_fetch_array($result_set); // You only have one result, so you don't need a while() loop
echo("<h1>".$row['name']." - " . $row['date'] . "</h1>");
echo("<p>".nl2br($row['text'])."</p>"); // nl2br is a quick way of displaying line breaks as <br /> tags.

mysql_close(); // Close the connection to the database
?>

So there it is. A really, really brief overview of how one could make a rudimentary system to access one table & display some results. Things get exponentially more complicated from there. However, keep in mind that you are opening yourself up to SQL injection attacks by passing anything from the querystring to the database. If you intend to use this example, please read up on it.

The rest of us, we use more of a database object to access our data, and sanitize input so that we can keep out the baddies. But that's a pretty good start... I think. Let me know if you have any other questions :)

-S

Edit: Apparently you can't do UBB url links inside of a CODE block. So... here is a glossary:

• mysql_connect()
• mysql_query
• mysql_fetch_array
• mysql_close()
• nl2br
• mysql_real_escape_string
• mysql_select_db

(Edited by twItch^ on 10-03-2008 22:32)