Jump to content


Photo

More Complex databases


  • Please log in to reply
1 reply to this topic

#1 Funky Monk

Funky Monk
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 21 October 2005 - 05:16 PM

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.

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 29 October 2005 - 05:54 PM

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' "
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users