MySQL setup for large address system

jrobbio · #1 06-Jun-2006, 09:49

Hello all its been a long time, but I hope you are well and can help me!

I've converted an access database into MySQL that I now need to query. When it is completed, it is going to be a massive database ~ 50mb of MySQL data.

Here are the details I am using to create the tables.

Code:

DROP TABLE IF EXISTS `Addresses`;
CREATE TABLE `Addresses` (
  `Postcode` VARCHAR(8) NULL,
  `Homes` DOUBLE(15, 5) NULL,
  `Leads` INT(10) NULL,
  `Map Easting` INT(10) NULL,
  `Map Northing` INT(10) NULL,
  PRIMARY KEY (`Postcode`),
  INDEX `Map Easting` (`Mappoint Easting`),
  INDEX `Map Northing` (`Mappoint Northing`)
)
ENGINE = INNODB;

SET FOREIGN_KEY_CHECKS = 1;

 EOF

And here is some sample data:

Code:

INSERT INTO `Addresses` (`Postcode`, `Homes`, `Leads`, `Map Easting`, `Map Northing`)
VALUES ("AL1 1AG ", 6.0, 0, 515486, 206497),
  ("AL1 1AJ ", 52.0, 0, 515488, 206412),
  ("AL1 1BH ", 48.0, 0, 514835, 206655),
  ("AL1 1BX ", 17.0, 0, 514616, 206889),
  ("AL1 1BY ", 7.0, 0, 514585, 206994),
  ("AL1 1BZ ", 11.0, 0, 514666, 207043),

I am incredibly rusty since it has been a while since I dealt with this type of thing, but I want to create a form so when someone enters their postcode e.g. AL1 1BH, it gives them the number of homes and Leads in response and the coordinates of the postcode from Map Easting and Map Northing.

Any other suggestions too would be appreciated.

Thanks in advance,

JR

JdS · #2 07-Jun-2006, 03:39

Hello Rob,

It's nice to 'see' you again

Unless I have misunderstood your question, this is a very simple query:

PHP Code:


// init variable holding data
$postcode_data = array();

// SQL
$sql =    'SELECT *'
    . ' FROM `Addresses`'
    . ' WHERE `Postcode` ='
    . " '".mysql_escape_string( $_POST['postcode'] )."'";

if( $result=@mysql_unbuffered_query($sql, $conn) )
{
    $postcode_data = @mysql_fetch_assoc( $result );
}

// preview result:
echo '<pre>'; print_r( $postcode_data ); die( '</pre>' ); //*/

jrobbio · #3 08-Jun-2006, 15:27

Its been a long long time since I've dealt with MySQL and PHP so I am close to step 1 again, though I'm pretty sure I will pick it up slowly.

When I have the database set up, I'll give this query a try.

JR

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Pointer Usage in C++: Beginner to Advanced	varunhome	C++ Forum	0	19-Aug-2005 09:25
[Tutorial] Pointers in C (Part II)	Stack Overflow	C Programming Language	0	27-Apr-2005 17:36
Macromedia DWMX and PHP MySQL setup	soulja90	MySQL / PHP Forum	1	17-Mar-2004 04:50
Windows: From only £20p/y,Linux: from $10p/m. ASP, ASP.NET, PHP, Free MySQL, +More	EyotaHosts	Web Hosting Advertisements & Offers	0	28-Jun-2003 13:54

MySQL setup for large address system

Re: MySQL setup for large address system

Re: MySQL setup for large address system