You are not logged in.
#1 31 Dec 2006 9:18 am
- Butcher
- Moderator
- From: Norway
- Registered: Jul 2006
- Posts: 308
Databases
Wondering if I can use SQL databases, or any web databases in general, to store info like a Microsoft Access database does? I want to make a website containing my fathers cd archive and then use html/php to output it. Anyone know if this can be done?
Offline
#2 31 Dec 2006 2:30 pm
Re: Databases
databases allow you to store, search, and retrieve information quickly. you're better off storing the id3 info for each cd in the database, and a file path to the mp3 on the file system rather than storing all the info, and the mp3 itself in the database.
access is a poor excuse for a database. Microsoft makes it really easy to make a database and (a windows) user interface thats tied to it using of access, but in my opinion, access is a light-weight database that isn't cut out for larger scale applications. its probably fine for storing info about your cd collection with it, but If your web host provides a mysql or microsoft sql server, you're better off using them over an access database if you plan on making it a web accessible application (rather than a windows based app).
personally I find database work boring and monotonous, but there are folks who really like it. I blame an SQL class I had in college for that.
I ran across a fairly good series of articles on databases / sql here. I could have skipped my db class and just read that, but alas.
Offline
#3 01 Jan 2007 4:01 am
- Butcher
- Moderator
- From: Norway
- Registered: Jul 2006
- Posts: 308
Re: Databases
Just one thing about PHP that I've had problems with some time, how to submit forms and then get them to store themselves in a database and then be able to edit the information afterwards. I know there is supposed to be a way, even possible to output the information in html or php afterwards, but its hard to put a name to it.
Offline
#4 01 Jan 2007 10:23 am
- Butcher
- Moderator
- From: Norway
- Registered: Jul 2006
- Posts: 308
Re: Databases
Ok, had a look trough some information on the web, some code examples etc. Decided to test it, to see how it works, and made this:
Code:
-- phpMyAdmin SQL Dump -- version 2.9.0.2 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Jan 01, 2007 at 11:11 AM -- Server version: 4.1.21 -- PHP Version: 4.4.2 -- -- Database: `bambooco_cd` -- -- -------------------------------------------------------- -- -- Table structure for table `alphanumerical_a` -- CREATE TABLE `alphanumerical_a` ( `artist_title` varchar(100) NOT NULL default '' COMMENT 'Artist Name', `cd_title` varchar(100) NOT NULL default '' COMMENT 'CD Title', `year` varchar(5) NOT NULL default '' COMMENT 'Year of release', `url` varchar(250) NOT NULL default '' COMMENT 'More Information', KEY `artist_title` (`artist_title`), KEY `cd_title` (`cd_title`), KEY `year` (`year`), KEY `url` (`url`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `alphanumerical_a` --
Using these 4 fields, or as I hope to make the, input boxes, it should be possible making the start of a CD archive if nothing else. I figured it would go like this:
Access a php or html site, with a password of course, and you can choose to "Add a CD", where you would then get up a page with these 4 categories and an input box for each, write it in, check that its all correct, boom, saves it in the database and can be used in php documents and such. Like if I found a way to use search, so you could search between year of release, artist or cd title and then if the search found any, it would go to that url you put in when submitting it, where it would ouput the artist name, year of release, song titles (will make a sub-database or something fancy for that, just need to learn these basics first) and it would all be a quite a complete sheet showing all the info about the CD I wrote into the input field or the php document itself.
Wooo, I have had a vision 
Offline
#5 01 Jan 2007 3:29 pm
Re: Databases
here's a crappy sample I did in a few seconds. the database name is dev, the table name is albums, and the host / username & (lack of) password are hard coded (as host = "localhost", user = "root", and password is blank). to change this for your use, you'll need to change those 2 or 3 places where that is hard coded into the php code.
database & table:
Code:
CREATE DATABASE `dev`; USE DATABASE dev; CREATE TABLE `albums` ( `id` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT, `artist` VARCHAR(50) NOT NULL DEFAULT '', `album` VARCHAR(50) NOT NULL DEFAULT '', `title` VARCHAR(50) NOT NULL DEFAULT '', `url` VARCHAR(50) NOT NULL DEFAULT '', PRIMARY KEY(`id`) ) ENGINE = InnoDB;
list / add / remove / edit page:
Code:
<html>
<head>
</head>
<body>
<?php
$mode = isset($_POST['mode'])?$_POST['mode']:null;
switch($mode) {
case "add": {
show_edit();
}break;
case "edit": {
show_edit($_POST['id']);
}break;
case "delete": {
db_delete($_POST['id']);
show_list();
}break;
case "post": {
@db_update($_POST['id'], $_POST['artist'], $_POST['album'], $_POST['title'], $_POST['url']);
show_list();
}break;
default: {
show_list();
}break;
}
?>
</body>
</html>
<?php
function show_list() {
// list out all the items in the database
// open a db connection to a mysql database
$dbh = mysql_connect("localhost", "root");
mysql_select_db("dev");
// fetch all rows
$query_result = mysql_query("select * from albums");
?>
<table cellpadding="8px" border="2" width="100%">
<tr>
<td align="center">Artist</td>
<td align="center">Album</td>
<td align="center" colspan="2">Title</td>
</tr>
<?php
while ($row = mysql_fetch_assoc($query_result)) {
?>
<tr>
<td><?= $row['artist'] ?></td>
<td><?= $row['album'] ?></td>
<td><a href="<?= $row['url'] ?>"><?= $row['title'] ?></a></td>
<td>
<form action="" method="POST">
<input type="hidden" name="mode" value="edit" />
<input type="hidden" name="id" value="<?= $row['id'] ?>" />
<input type="submit" name="submit" value="edit" />
</form>
<form action="" method="POST">
<input type="hidden" name="mode" value="delete" />
<input type="hidden" name="id" value="<?= $row['id'] ?>" />
<input type="submit" name="submit" value="delete" />
</form>
</td>
</tr>
<?php
}
?>
</table>
<form method="POST">
<input name="mode" value="add" type="hidden" />
<input type="submit" name="submit" value="Add" />
</form>
<?php
mysql_close($dbh);
}
function show_edit($id = null) {
$id != null?$id:"null";
$artist = "";
$album = "";
$title = "";
$url = "";
if($id != null && $id != "null") {
$dbh = mysql_connect("localhost", "root");
mysql_select_db('dev');
$query_result = mysql_query("select * from albums where id = $id");
$row = mysql_fetch_assoc($query_result);
if(isset($row)) {
$artist = $row['artist'];
$album = $row['album'];
$title = $row['title'];
$url = $row['url'];
}
mysql_close($dbh);
}
?>
<form action="" method="POST">
<input type="hidden" name="id" value="<?= $id ?>"/>
<input type="hidden" name="mode" value="post" />
<table>
<tr>
<td>Artist</td>
<td><input name="artist" value="<?= $artist ?>" /></td>
</tr>
<tr>
<td>Album</td>
<td><input name="album" value="<?= $album ?>" /></td>
</tr>
<tr>
<td>Title</td>
<td><input name="title" value="<?= $title ?>" /></td>
</tr>
<tr>
<td>URL</td>
<td><input name="url" value="<?= $url ?>" /></td>
</tr>
</table>
<input type="submit" name="submit" value="Submit" />
</form>
<form method="GET"><input type="submit" value="Cancel"/></form>
<?php
}
function db_update($id = null, $artist = "", $album = "", $title = "", $url = "") {
$dbh = mysql_connect("localhost", "root");
mysql_select_db('dev');
$query = "insert into albums (artist, album, title, url) values ('$artist', '$album', '$title', '$url');";
if(record_exists($id, $dbh)) {
$query = "update albums set artist = '$artist', album = '$album', title = '$title', url = '$url' ";
$query .= " where id = $id";
}
mysql_query($query);
mysql_close($dbh);
}
function db_delete($id) {
$dbh = mysql_connect("localhost", "root");
mysql_select_db('dev');
$query = "delete from albums where id = $id;";
mysql_query($query);
mysql_close($dbh);
}
function record_exists($id, $dbh) {
$query = mysql_query("select count(*) as count from albums where id = $id");
$row = mysql_fetch_assoc($query);
return $row['count'] > 0;
}
?>if you plan on archiving all your music, you really should set up multiple tables, but that makes things slightly more complicated, so I didn't do that in the sample. really you should have a table named artist, that has an id as the primary key, and artist name and any other info associated w/ the artist. one table named album that has an id as the primary key, artist id as a foreign key (artist.id from the artist table), the album name / year / genre / and all the other details associated w/ the album, and one table named track / song (or whatever you want to call it) that has an id as the primary key, an album id as a foreign key (album.id of the album table), the track number, the url for the file on the server, and anything else you want to associate w/ that song. then in your query's you left join song / track on album id, and album on artist id, so what you end up w/ is the entire list of details on each artist -> album -> song, the you can add / edit artist independent of album, and you can edit album independently of a track, and it will cascade through without having to edit a million records (for each song or whatever). the trick there is that if you delete an album, you have to also delete any songs / tracks in the tracks table, and if you delete an artist, you'll have to delete all albums by them, and any tracks for each album...
but this should give you something to play with.
Offline
