Today I’ve improved the starting page of this domain, www.fireflake.com. It looked very booring and I thought I’d have to do something with it but at the same time I knew I was to lazy to acctually maintain some interesting subjects there as well. The solution was simple! Use my blogs to feed the information to the starting page! Here are some of my php and sql code used, in case you want to try something similar yourself.
First of all, if you’ve never done any PHP before, do not fear, it’s super easy! Without explaining it, here are my open and close database files (I keep them seperate since they will most likely be included in many pages):
open_db.php:
<?php
$dbhost = ‘localhost’;
$dbuser = ‘username’;
$dbpass = ‘password’;$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die (‘Error connecting to mysql’);
$dbname = ‘dbname’;
mysql_select_db($dbname);
?>
close_db.php:
<?php
mysql_close($conn);
?>
Just modify these files with the values for your database and you are ready to do some mysql-powered-php-scripting! Next simply include these files in the file where you want to use the database, in my example index.php:
<!DOCTYPE html PUBLIC “-//W3C//DTD HTML 4.01 Strict//EN” “http://www.w3.org/TR/html4/strict.dtd”>
<?php include ‘open_db.php’; ?>
<html>
…
</html>
<?php include ‘close_db.php’; ?>
Now lets make the MySQL database print something to the index-page! Here is an example:
<?php
$query = “select now() as mysqltime”;
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo $row[‘mysqltime’];
}
?>
This will print a timestring from your MySQL. I used this as an example just because it should work everywhere. Now simply substitute the query with something you want from your database and the printout inside the loop with what (and how) you want it printed!
To take a WordPress example, I want to use the tags/categories as keywords for the META-tag of my start page. I use a cleaverly written SQL query that will give me all the unique keywords for all my three blogs sorted in order of usage:
<?php
$query = “SELECT name,`count` FROM tech_terms t join tech_term_taxonomy tt on (t.term_id = tt.term_id)
union distinct
SELECT name,`count` FROM game_terms t join game_term_taxonomy tt on (t.term_id = tt.term_id)
union distinct
SELECT name,`count` FROM 3wp_terms t join 3wp_term_taxonomy tt on (t.term_id = tt.term_id)
order by `count` desc limit 0,50;”;
$result = mysql_query($query);
$keywords = array();
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
array_push($keywords,$row[‘name’]);
}
$keywords = array_unique($keywords);
foreach ($keywords as $key){
$meta_key_string .= $key.”,”;
}
?>
<meta name=”keywords” content=”<?php echo $meta_key_string ?>fireflake”>
This will make my home meta tag always up to date with what I write about! The keywords will also be listed in order of relevance since they are ordered by “count” from the database.
Another great little script takes out the latest posts in a blog and prints them (and links it to the main article):
<?php
$query = “select * from tech_posts where post_status = ‘publish’ order by post_date desc limit 0,2”;
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo “<h3><a href=”” . $row[‘guid’] . “”>” . $row[‘post_title’] .”</a></h3>”;
echo “<p>” . str_replace(“n”,”<br />n”,$row[‘post_content’]) .”</p><br clear=”all”>”;
}
?>
There are probably lots of fun you can have with the WordPress database, it’s very simple and easy to learn so it’s very easy to start writing code like this!
Hope you found something useful here!