SQL search query

[Broomy]

Hi, i'm very new to sql so to say i'm a bit confused is a bit of an understatement! I'm tring to set up a search facility so that results from multiple databases will come from just one form. A better way of explaining would be to show an example of a site that does just what i'm after - buzzle.com. When a search is performed the results are split into different sections. On my site this would be for example "articles", "ebooks" and "links".

My question is how can I go about setting up a form and the search so that it would query the different databases and display just like the site i've provided above, with the different result "sections" on the same results page?

I hope you can help,
Thanks in advance,
Justin.

[hollystyles]

Hmmm.. this is not a small question.

There are various methods of collating results from more than one database depending on the databases in question, are they all the same for example (MS SQLserver or Oracle ir MySQL) and are they on the same server or spread all over, in one domain or several.

I suspect buzzle.com has one database in which each entry has a type atribute (chapter, article or link)

example:

----------------------------------------------------------------------------------------------------
| Table: Content |
========================================================
|ID |Type |Keywords |URL |
========================================================
|1 |Chapter |buzz buzzle | http://buzzle.com |
|2 |Article |buzz buzzle | http://buzzle.com/articles/buzzle.asp |
|3 |Link |buzz buzzle | http://buzzle.com/links/index.asp?id=1234 |
========================================================

The SQL query to retreive the data:

SELECT * FROM content WHERE keywords LIKE '%buzzle%' ORDER BY type

[Broomy]

The database I have at the moment is MySQL, and the other two that I want to add (I haven't yet but want to try and set up the search to make sure I can get things working before) will also be MySQL, all installed on the same server.

Not being a small question, does that mean that this may be too difficult for a newbie (me!) to be talked through to accomplish? I hope not as i really want this to work.

Hope you can help,
Thanks for the reply,
Justin.

[hollystyles]

Not being a small question, does that mean that this may be too difficult for a newbie (me!) to be talked through to accomplish? .

Well we are all newbies at some point. I must say for a newb your project does sound ambitious, but don't let that put you off. The key is to break it down into managable chunks. My first concern is why you feel the need to store this data in three seperate databases? instead of all together in one.

How familiar are the terms: relational data; normalisation; set theory; to you? Have you heard of a guy called F Codd ?

Your answers will help me to establish what stage you are at so I can point you in the right direction.

[Broomy]

How familiar are the terms: relational data; normalisation; set theory; to you? Have you heard of a guy called F Codd ?

Your answers will help me to establish what stage you are at so I can point you in the right direction.

Unfortunately i'm not familiar with any of those terms, and the only think i know about F Codd is what i've just read after a quick search!

The only reason i was going to use three seperate databases was purely due to instructions that usually mention creating a database for them. For example, the link script which i would like to add to my site which will then include the links added in the search i'm trying to add, will have a MySQL database. I usually stick to the instructions i'm given to stop myself from messing anything up, but i guess there's no reason i couldn't add the tables into one database if it would be easier and if there is no problem doing that.

Justin.

[Phaelax]

I suspect buzzle.com has one database in which each entry has a type atribute (chapter, article or link)

I thought the same thing. Alternatively though, you could connect to each database separately and perform the same query.

[hollystyles]

OK Justin,

Can we have a look at this link script then please?

Is your site online yet ? what's the URL?

Like Phaelax says if you have incorporated several add-on databases to your site it is possible to query each one and concatenate the results for display as one list split by catagory.

How depends on what you have, is your site using PHP ? or ASP for example.

[Broomy]

I haven't actually installed the link script yet, but I was checking that what i wanted doing could actually be done, and thought if i could work out how to do it then i could confugure the search facility as soon as I added the link script. It might be best if i install the link directory script first, and then re-open this thread, so that I can try to work through any instructions properly rather than struggle through any example instructions, as i'll probably get easily confused otherwise :o .

Thanks for the help so far, I appreciate it. By the way, my site is in php if that helps.

Justin.

[Broomy]

Would you recommend that I install the link script as a seperate database or shall I use my existing database when installing the script, as the install instructions just asks for the database name, password url.

I'm just wondering whether this would cause any problems, eg. could any tables be overwritten doing it this way? I'm just guessing, but wanted to find this out before I go ahead and install it.

Thanks for your help,
Justin.

[hollystyles]

Can you divulge what script/package you are proposing to use, so I can look at the instructions myself. I can't tell you unless I know what database you have already and what exactly it is your want to install.

[Broomy]

Yes, no problem, the script I plan on installing is this http://scripts.site-sift.com/ and my site if you need to take a look is at http://www.articleblotter.com .

Thanks,
Justin.

[hollystyles]

Ok the instructions expect you to create a new database and user for the script to install it's tables and data to. So I would go with that, as we have established it's no problem to query across databases.

[Broomy]

Ok, i've installed the link script (at last!), so the tables should all be setup correctly in the database. So what I need to do now is create the form and results page that will display the results from the article database and link database.

Any help on where to begin is greatly appreciated,

Thanks,
Justin.

P.s. Out of interest, would this be an expensive job to have done? I'm eager to learn but have a feeling this may have me confused in many places! If it's an easy job for a pro to do then I may opt for this, however if it is tricky then i will of course work through any guidelines that can be given. Thanks again.

[hollystyles]

Check these links: (all from the PHP site)

Handling HTML form input (where your site visitors will key their search string)
http://uk2.php.net/manual/en/tutorial.forms.php

Connecting and queryingyour MySql databases
http://uk2.php.net/manual/en/function.mysql-db-query.php

You are interested in mysql_connect and my_sql_query

You will need to take the search criteria from the form, and connect to each database in turn retreiving a recordset from each, then output them using a loop to generate HTML to the page from the results.

[DGStudios]

My suggestion is to hit the bookstore and look up Larry Ullman. His book on PHP and MySQL is an amazing book to teach you the language. It is very quick with the SQL teachings. So reading it helped me enough to build a Bulletin board. While my initial is a little ...storage intensive, I still had no problems with the queries. Good luck with that. Its got a lot of complications that require looking at in detail to set up. I would be happy to help. E-mail me if you would like some more attentive help. The queries would be based on what the set up for the Database(s) is. It would all be different based on how things were put together.