Jump to content

Building a PHP Search with MySQL - script advice needed


CountryGirl

Recommended Posts

Hi all,

 

I am building my first PHP search database with MySQL. I have all the data entered in the correct tables (16 tables) in PHPMyAdmin/MySQL. Now I need to be able to build a search feature on my website and make it searchable. Does anyone have any suggestions on good sites that would explain some of how to do this? I'm pretty new to writing PHP and such and am not exactly sure where to start! The MySQL database tables I have figured out, it's just some of the specifics like putting the actual search and such onto the webpage.

 

Thanks for any and all tips :)!

 

Qadoshyah

Country Girl Designs

Link to comment
Share on other sites

I have a question off of reading the Simple SQL Search Tutorial. It makes sense for the most part, but I still have this one question.

 

At the part of the code below . . . am I supposed to put my information on there? Like where it says ""SELECT sid, sbody, stitle, sdescription" should that be where the information I need goes. Like it should say "SELECT name, address, account number." Or, should those places be the name of the tables in the database that the information will be pulled out of? I hope my question make sense :)!

 

If there are no errors, lets get the search going.

  if (count($error) < 1) {

      $searchSQL = "SELECT sid, sbody, stitle, sdescription FROM simple_search WHERE ";

     

      // grab the search types.

      $types = array();

      $types[] = isset($_GET['body'])?"`sbody` LIKE '%{$searchTermDB}%'":'';

      $types[] = isset($_GET['title'])?"`stitle` LIKE '%{$searchTermDB}%'":'';

      $types[] = isset($_GET['desc'])?"`sdescription` LIKE '%{$searchTermDB}%'":'';

 

Thanks!!

Qadoshyah

Link to comment
Share on other sites

sid, sbody, stitle, sdescription are examples of column names in table, so if your column are 'name','address','account number' (don't use spaces in column names BTW) that's what you should put instead.

 

simple_search is example of table name, so you put your table name instead.

Link to comment
Share on other sites

sid, sbody, stitle, sdescription are examples of column names in table, so if your column are 'name','address','account number' (don't use spaces in column names BTW) that's what you should put instead.

 

simple_search is example of table name, so you put your table name instead.

 

Okay, cool! That's what I was thinking, but just wanted to be sure.

 

Now, if I have multiple tables that will need to be able to be searched, I just list all the table names, right?

 

Sorry for all the questions! I've just gotta make sure I get this right :).

 

Qadoshyah

Link to comment
Share on other sites

Now, if I have multiple tables that will need to be able to be searched, I just list all the table names, right?

 

Not exactly. It depends on how your tables relate to each other. Perhaps do the single table search first, and get to more difficult things later on ;)

Link to comment
Share on other sites

Now, if I have multiple tables that will need to be able to be searched, I just list all the table names, right?

 

Not exactly. It depends on how your tables relate to each other. Perhaps do the single table search first, and get to more difficult things later on ;)

 

Okay, I see. They all relate to each other by having a KeyID & same account number.

 

Where would be a good place to look into doing a search from multiple tables?

 

Thanks so much for the help!

Qadoshyah

Link to comment
Share on other sites

By the way, depending on what you're doing, that search solution might be horribly slow.  Using %keyword% doesn't use indexes (since any wildcard string starting with % cannot use an index).  That means that for each row, the entire description will have to be scanned and compared.  (It might not be all rows if other constrains are used, but it can still be bad.)

 

 

If you're going to have a lot of rows or a lot of content per row, you might want to consider something else like full text searching (it has limitations too though, such as being limited to the MyISAM engine).

Link to comment
Share on other sites

You can also use things like Lucene or Sphinx.

If you are new to php/mysql then the above maybe a bit out of your depth, however Sphinx is fantastic for search functionality, especially if your database tables are using text fields and have a large number of records. Definately worth looking at when you get more confident.

Link to comment
Share on other sites

Alright guys, I got a search up! That tutorial is awesome - pretty easy ;)!! This has been such a brain-wracking project ;).

 

This is what I am messing with right now - http://www.wagonerassessor.com/testsearch.php. If you put in "730000001" and then check both boxes, you can pull up a record :)

 

Now, this is what I need to be able to figure out next:

 

I have 16 tables that have data in them that I need people to be able to search. This search is being built for an Assessor's office, so the tables are thinga like, ownership (which is the table I have in the search right now), tax area, land, parcel numbers, commercial, agricultural, address, legal, sale information, etc. These tables all have their base account number and then the correct info is in each table.

 

How do I incorporate each table into the search so that someone can access all the information they need? I need people to be able to pull up Name, Address, Account #, Parcel #, Legal, School District (tax area), Description (that'll include Sq. ft, year house was built, outbuildings, etc).

 

Thanks for the help! You guys have been great :).

 

Qadoshyah

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.