hi, ive nearly finished making my searchengine, but I have one more question. My searchengine has the option to search neighbouring suburbs for shops if the textbox is ticked. I thought the best way to do this was to give each suburb a unique id number, and then use +1 +2 +3 -1 -2 -3 to find its neighbouring suburbs, as i have given them numbers in the way that the numbers closest to them are their neighbours, this took along time as there are 430 suburbs in sydney. it works basically like this if the checkbox is ticked it will look up the suburb database, find the suburb, get its id number, get the id numbers of its neighbours, and tell the browser to display the listings for these suburbs. the only possible problem is that their are 430 suburbs in the database. Is this too many for the script to be able to handle?
yes that might be a problem. since most of the hits will come at a certain time i am worried that 400 suburbs might make the server overload. But I am a newbie so im not really sure if 400 is even a big deal.
um....... i probably should know this but.. how do i design a database properly?
my database is as follows
main: list of all the names of the shops, type, suburb and id number
suburbs: list of all 400 suburbs, with id numbers
basically my script takes the input from the user, selects all the entries which match the type and suburb they have specified from the main database, then if they have checked the box (search neighbouring suburbs) it looks up the suburbs database, picks out the neighbouring suburbs, then tells the main database to display the entries which match the type, and new suburbs. Is that how a database should work?
also i havent yet written the code for this, my code at the moment can just match up suburbs and types of shops from the main database.
The problem is how to store neighbouring suburbs. Generally you have 3 options:
1) add 'neighbours' varchar field to 'suburbs' table and store comma separated list of neighbouring ids ie '1,100,200' etc. This is effective, but you're limited to 255 chars for this field.
2) use adjacency list ie. a table with two integer columns, if x-y pair is in the table, x and y are neighbours. This is more flexible, but you'll need an extra query.
3) store geographical coordinates of each suburb and caclulate distance between them. This allows you to implement cool 'vicinity search' feature, but the formula is rather complex, escpecially when you need precise results.
hehe i just realised an even bigger problem. My suburbs table is nothing compared to the main one, which has 1000 shops with their name and a approx 75 word description, i really need to know, roughly how long in sec it would take for a script to go through 1000 shops. heres the script below- tell me if i can get it to work better
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM better WHERE cuisine='$cusine' and suburb='$suburb' ";