Page 1 of 1

How to make a php form which would search different database

Posted: Wed Apr 09, 2008 10:40 am
by carl gale
Hi

can someone let me know if this can be done please. I have made a website shop which has about 2million products in it. Because of the same of the database we have decided that it will be split into 10 different databases, one for each of the categories.

We seem to be having an issue making a search for at the moment. We can easily make a form to search x database but ideally on the shop we want to have a search form at the top of the screen where the user types in the keyword and then from a drop down list selects which category of the site they wish to search in. We somehow need the drop down bit to tell the form which of the 10 databases to look in for that result.

Is this something which could be done with php?


thanks
carl

Re: How to make a php form which would search different database

Posted: Wed Apr 09, 2008 10:54 am
by Christopher
Yes, PHP can get the value of the select and the program can then choose the database name based on that.

Re: How to make a php form which would search different database

Posted: Wed Apr 09, 2008 11:36 am
by onion2k
I'm curious ... why did you split it across lots of databases? 2 million records isn't much. MySQL, SQLLite, Postgres etc can easily handle that number of rows. What advantage was there in breaking it up?

Re: How to make a php form which would search different database

Posted: Wed Apr 09, 2008 11:51 am
by carl gale
hi, one of the reasons we chose to do it this way was because of loading times. even on a 2gb dedicated server we were seeing loading times over over 15 secs when doing a database query. Another reasons was that we installed it as ten individual sites linked up as one, this allows us to put advertising related to the niche on an indivudal site, something which wouldn't have been possible to do had we have made it as one big site.

Ive never made anything in PHP before so if someone could point me to a guide on setting up a search function which would search different databases depending on the section chosen i woudl be most grateful

Re: How to make a php form which would search different database

Posted: Thu Apr 10, 2008 2:33 am
by carl gale
anyone know the best way to do this

Re: How to make a php form which would search different database

Posted: Thu Apr 10, 2008 2:55 am
by onion2k
carl gale wrote:hi, one of the reasons we chose to do it this way was because of loading times. even on a 2gb dedicated server we were seeing loading times over over 15 secs when doing a database query.
Slow queries are rarely anything to do with the quantity of data in the database. I realise that sounds a bit mad, but it's entirely true. A well organised, indexed set of millions of rows can be queried in no time at all if your SQL is good enough. How do you think the likes of Google query billions of records so fast? It certainly isn't by splitting up the data into very small quantities.
carl gale wrote:Another reasons was that we installed it as ten individual sites linked up as one, this allows us to put advertising related to the niche on an indivudal site, something which wouldn't have been possible to do had we have made it as one big site.
Why can't you have advertising related to different sites in the one database? Surely it's just a matter of putting "site_id = 1" into the query.

Anyway.. schema design issues aside.. if you want to select between different databases you just need to pass a different value to mysql_select_db(). Eg

Code: Select all

$databaseLink = mysql_connect("localhost", "username","password");
switch ($_GET['db']) {
    case 1: mysql_select_db("teddybears", $databaseLink); break;
    case 2: mysql_select_db("toytrucks", $databaseLink); break;
    case 3: mysql_select_db("skateboards", $databaseLink); break;
    default: mysql_select_db("intercontinentalballisticmissiles", $databaseLink); break;
}