More Complex databases

[Funky Monk]

I've been using MySQL for quite a while now but have always used quite simple SQL statements. Now I need to do something a bit more complex (well, it is for me at least).

 

I am creating a web site that will allow people to regsiter events from all over Europe. The details go into a database for display on their own specific country page. The only way that I can think of doing this at the moment is to create one table and duplicate it 50 times:

 

#

# Table structure for table `albania`

#

 

CREATE TABLE `albania` (

`organisation` varchar(255) NOT NULL default '',

`id` int(11) NOT NULL auto_increment,

`event` varchar(255) NOT NULL default '',

`description` text NOT NULL,

`start_date` text NOT NULL,

`end_date` text NOT NULL,

`venue` varchar(255) NOT NULL default '',

`contact_name` varchar(255) NOT NULL default '',

`email` varchar(255) default NULL,

`phone` varchar(80) default NULL,

`website` varchar(255) default NULL,

PRIMARY KEY (`id`)

) TYPE=MyISAM PACK_KEYS=0 AUTO_INCREMENT=2 ;

 

#

 

.....and so on.

 

Then I insert into this table using the following code (created using Dreamweaver MX2004):

 

<?php require_once('../../Connections/connEWT.php'); ?>

<?php

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

 

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . date("Y-d-m",strtotime($theValue)) . "'" : "NULL";

break;

case "time":

$theValue = ($theValue != "") ? "'" . date("H:i:s",strtotime($theValue)) . "'" : "NULL";

break;

case "datetime":

$theValue = ($theValue != "") ? "'" . date("Y-d-m H:i:s",strtotime($theValue)) . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

 

$editFormAction = $_SERVER['PHP_SELF'];

if (isset($_SERVER['QUERY_STRING'])) {

$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);

}

 

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "frmAddEvent")) {

$insertSQL = sprintf("INSERT INTO albania (organisation, event, description, start_date, end_date, venue, contact_name, email, phone, website) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",

GetSQLValueString($_POST['organisation'], "text"),

GetSQLValueString($_POST['event'], "text"),

GetSQLValueString($_POST['description'], "text"),

GetSQLValueString($_POST['start_date'], "date"),

GetSQLValueString($_POST['end_date'], "date"),

GetSQLValueString($_POST['venue'], "text"),

GetSQLValueString($_POST['contact_name'], "text"),

GetSQLValueString($_POST['email'], "text"),

GetSQLValueString($_POST['phone'], "text"),

GetSQLValueString($_POST['website'], "text"));

 

mysql_select_db($database_connEWT, $connEWT);

$Result1 = mysql_query($insertSQL, $connEWT) or die(mysql_error());

 

$insertGoTo = "events.php";

if (isset($_SERVER['QUERY_STRING'])) {

$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";

$insertGoTo .= $_SERVER['QUERY_STRING'];

}

header(sprintf("Location: %s", $insertGoTo));

}

 

mysql_select_db($database_connEWT, $connEWT);

$query_rsEvents = "SELECT * FROM albania";

$rsEvents = mysql_query($query_rsEvents, $connEWT) or die(mysql_error());

$row_rsEvents = mysql_fetch_assoc($rsEvents);

$totalRows_rsEvents = mysql_num_rows($rsEvents);

?>

 

Is there a better way of doing this? What I would like is so that I can from an admin area view events from each country without having to flick through 50 pages and also so that the central admin can add to any of the 50 countries using a drop-down box that has each of the countries in it - ie. 1 page that will allow admins to add to any country page, and 1 page that will display all events by country.

[Barand]

A single events table with a country_code column sounds a better way to me.

 

That way, if someone wants to find all the European ferret racing events this weekend, you don't have to search 50 tables.

 

To display all events for a country just use "... WHERE country_code = '$country' "