Mysql + Arrays

[lordfirex]

I have a problem that i'm not quite sure how to do.

 

I have to create a system that allows me to enter several postcodes associated with a particular event. (These are 4 digit  postcodes).  Somebody then has to enter a Postcode and it should pull up all events associated with that postcode. The problem being that each event (For i can't see another way of storing it easily) will have the postcodes stored in a mysql database in an array. How do i get SELECT to search through the postcode arrays and then print out any rows that have that particular postcode in it.

[Pikachu2000]

What makes you think you have to store multiple pieces of data, in an array, in a single field?

[lordfirex]

I have come to the conclusion to use relationships to store the postcodes. Although now i need to figure out how to do a table-wide search of all columns.

 

What i have now is

$result = mysql_query("SELECT * FROM postcode WHERE *='3912'");

 

Where the * is a wildcard, although of course it doesn't work

 

So i am completely lost

 

I can't think of where to go from there.

[jcbones]

MySQL is relational, so you need a relational table, in fact, a many to many.

 

So, you would have a table with id and postcode.

And, a table with postcode_id and event_id that would tie both of these tables together.

 

This way postcode and event is stored once, and then a list of the relationships between the events and postcodes is stored.  Which is easily sorted.

 

SELECT event.* FROM event  WHERE  event.id IN (SELECT event_id FROM events_postcodes JOIN postcodes ON events_postcodes.postcode_id = postcodes.id WHERE postcodes.postcode = '5432')

 

I think that is close, un-tested though.

[jcbones]

To clarify, these are the extra two tables.

CREATE TABLE `postcodes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `postcode` int(4) NOT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `events_postcodes` (
  `event_id` int(11) NOT NULL,
  `postcode_id` int(11) NOT NULL
)

[Andy-H]

$result = mysql_query("SELECT * FROM postcode WHERE postcode LIKE '3912%' ORDER BY id DESC");

 

 

 

That will select anything with a postcode that begins with 3912

[lordfirex]

Gosh i love the internet. Thanks guyz!!