How to do a searchable database?

[Cy]

With my list of residents getting so long, I was thinking about transforming it into a search-able database. Allow people to search for a term, such as breed, color, name, gender, etc. That way it could be as specific or non-specific as they wanted.
How would I go about this? Is there a Java script code? I'm not exactly sure what I'm looking for here ^_^'

[StarFyre]

Your easiest method of doing it is having a sql database that you enter all the information into, then build a php search page similar to DF's. I really don't know if it can be done efficiently any other way -- javascript can theoretically do it, but it wouldn't (really) be able to dynamically alter pages or spit out a nicely formatted result string. And it would still need a relatively "clean" information set to search through.

[Yakima]

XD I still want to do something like this. And I still want to do my "All-Weyr Record Database". But SLQdatabase and I had issues. heh...

[Cy]

*head swims* H'okay... maybe not then lol
Looked through the provided links
and holy crap.
It's like trying to read a different language o.o Granted, I do speak Spanish, French, German, and a bit of Latin and Italian... Therefore it must be Danish. Indeed. Or Swahili. I definitely can't do the clicking.

I have a friend, Zafir, who moved to the US from Africa with his family. And when he calls his mom to talk to her, I have basically the same face o.o

'Scuse me. I've had a few drinks. But even sober, I didn't understand that. Is there, like, a Database for Dummies?

I tried looking up the code for DF's database, but hers doesn't fully work in the first place... Do a search, and half the results are gibberish. On my end anyways.

[StarFyre]

^^;; Here's the very, very important thing to remember about PHP. You are indeed learning another language, this one governed not by human rules, but by computer rules. As such, a lot of the syntax will look weird, incomplete, or out-right mind-boggling at times. In fact, a lot of the easier syntax you see is called "syntactic sugar" -- things that have been added to the language in order to make it more human-friendly. (Congruent to that is "syntactic salt" - hoops that I have to jump through to prove that I know what I'm doing. Java typecasting is an example, as is the VB.net standard of having to type "end if"/"end else"/"end while"/etc to end loops instead of using } to end it)

If you want to learn PHP, http://www.w3schools.com/PHP/DEfaULT.asP is a good place to start, then once you have an understanding of what's going on, this is a decent look at how to search a sql database: http://www.designplace.org/scripts.php?page=1&c_id=25

The code itself on the second page is very well commented, though it would need to be heavily adapted to the type of stuff we want to use it for.

Conversely, you could wait a bit (and use that time to start building the database to pull from), I could start building the PHP search for my page, and I could try and help anyone who'd like to learn PHP learn from the script I end up writing. Either way, at least start reading the W3Cschools pages -- I always recommend them as the first place to start for anything internet related, they're just small overviews, but they often let you play around with the code on their pages, and see how things react as you change them.

Yakima: What part of the database stuff did you have trouble with? If people are having trouble with that, too, I can try to help as best I can.

[Yakima]

Well, I don't know how to make the site that will display it - none of it. LOL I also tried making my tables/database in my spreadsheet program so that I could just edit easily then upload it. It didn't work.

Truthfully, what goes in the database' would be up to the general public. I mean, I like have the complete records (rider, ect) but I know most people don't give a shell about the Riders, just the dragons, for pedigrees. LOL So, I guess I need help deciding that, too.

I pretty much need to know everything. DF was going to help me, but I think RL sucked her up using one of Phe's huge space worms! XD

[StarFyre]

Mmm, space worms...

The site that will display the contents of the database is the PHP/html mixture that I was discussing with Cy. It's important to remember that the SQL database and the display of the contents are two completely separate things. SQL can only store things, PHP can manipulate and display it.

What I'm doing at the moment is hand-loading the database though the phpmyadmin interface through the cpanel of my website, because it's relatively simple to do. Theoretically, you can create a php script that will display a pretty html page through which you can enter things into the database that way, but that's up to user preference.

NULL, by the way, means that a field is empty, or can contain no option.

You may also notice, as you look at some of the ENUMs I have, that there's some specific races/people missing -- this is because I'm adding things to those ENUMs as I run across examples of those races, and just haven't run across specific things yet (for example - you're not listed yet, and neither are any of your species, of which I have quite a few at this point =P but I haven't gotten to entering them yet)

Currently, my database is set up like this:
3 tables: Dragon, Rider, Whorling
18 fields in Dragon:

• DrgnID (the key of the table, otherwise known as the unique identifier for every row in the table: type bigint(255), to support up to 255-bit numbers (255 bits is 2^255, in other words, BIGNUMS ^^) -- this field is set to autoincrement and cannot be NULL)
  
  DragonName (name of the dragon: type text, cannot be NULL)
  
  RdrID (an int that can be cross-referenced to the Rider table, to link a Dragon and a Rider together as a unit: type: bigint(255), can be NULL)
  
  DrgnBID (an int that can be cross-referenced back to the DrgnID field, to link two dragons together: type bigint(255), can be NULL)
  
  DrgnBID2 (as above, to link a second dragon: type: bigint(255), can be NULL)
  
  DragonColor (to give a simple categorization of every dragon: type ENUM (enumeration - a list of values that can be chosen between, ala a drop down menu), at current, this ENUM consists of: 'Red','Orange','Yellow','Green','Blue','Purple','Black','White','Brown','Cream','Copper','Silver','Bronze','Gold','Rainbow','White Opal','Opal','Black Opal','Green-Blue','Grey','Pink','Gradient'; cannot be NULL)
  
  Notes (this field is the most flexible, strange field in the entire database -- if DragonColor or Marking Color is listed as "Gradient" this field will be polled to see what type of gradient (such as red-orange-yellow) it is, if the Species is listed as Myrsilkain, this field will be polled to see what element/season the Myrsilk is, if the species is Drak, this field will be polled to see what type (Plant/Ore/etc) the Drak is: type TEXT, can be NULL)
  
  Rainbow (this field is a simple "yes/no" field that tells whether the dragon has rainbow on it somewhere or not: type TinyInt(1) (can be 0 or 1, 0 is no, 1 is yes), cannot be NULL)
  
  Markings (used to denote markings: type ENUM ('Stripes','Spots','Points','Chrome','Secondary Color','Belly Stripe','Belly Scales','Snowflakes','Symbols','Gems','Stars','Swirls'), can be NULL)
  
  MarkingColor (same exact field as DragonColor, except for Markings: type ENUM, exactly as DragonColor, except can be NULL)
  
  Gender (the gender: type ENUM ('Male', 'Female', 'Hermaphrodite', 'Neuter'), cannot be NULL)
  
  Size (the size of the dragon, given in JKat's Standard Sizes: type ENUM ('Very Small','Small','Medium-Small','Medium','Medium-Large','Large','Very Large','Massive'), cannot be NULL)
  
  Species (what type of dragon it is: type ENUM ('Myrsilk', 'Alaiskya', 'Alevaharin', 'Cy Dragonstake', 'Hathian', 'Mutt', 'Eastern Nazo 1', 'Eastern Nazo 2', 'Zalor', 'Dragon Whorling', 'Kegawa', 'Geperna', 'Old World', 'New World', 'Jewelcrest', 'Bonder Daemon 1', 'Drak', 'Wyvern', 'Lesser Kynnese', 'Robot', 'Welzi', 'Dar\'keria', 'Kyren', 'Clawtongue', 'Desertrunner', 'Kirin', 'Scaly Dragon', 'Lantessama Dragon', 'Fright', 'Glenn Dragon', 'Gryphon', 'Xenodragon', 'Xenomutt', 'Danachian', 'Cupid Dragon'), cannot be NULL)
  
  Origin (who the creator was: type ENUM ('Clan Akelara','Castello Hollic','Everrealm','Healing Den','Cy Dragonstake','Unknown','Nidus Corona','Dray','Avengaea','Drockh-Tallahn Castle','Starburst','Kynn','Moonstruck','Phoenix','DNS','Veritosos','Astra','Darkling Dawn','Sil','Moon Forest','Lantessama','The Bower','Abstract Destiny','DF','Planet Twenty','Mythicalae'), cannot be NULL)
  
  Allegiance (this is a bit of my own recordskeeping here, Allegiance tells me what group the dragon regularly associates with, and therefore how to sort it -- it also tells me what symbol I'm going to need to place on their page when I get to it, in place of the Clan Akelara symbol: type ENUM ('Clan Akelara','Koshi Keidai','Ammia Pride','Clan Vecira','Devyr','Blood Rose','Loner','Lord Arazasin','Cetirnen','Kiamar Pride','Sikre Vashiur','Ithaniri','Kaesirl Zahndar','Yue Yerasai','Yulaiko Hive'), cannot be NULL)
  
  Position (this tells me what the dragon normally does -- for example, Leader, Clan Member, Warrior, Knight, etc: type Text, can be NULL)
  
  Link (very simple: the link to where I can find the dragon -- normally a simple link such as yikaeri.html, because the php code I will eventually write will place the proper link segment in front of that: type Text, cannot be NULL)
  
  Thumbnail (the link to where the thumbnail of the dragon is located, typically in the form images/thumbnails/[dragonnamehere]_small.png, because that's the standard form I've chosen: type Text, cannot be NULL)

9 fields in Rider:

• RdrID (as DrgnID: type bigint(255), auto-increment, cannot be NULL)
  
  RiderName (the name of the rider: type Text, cannot be NULL)
  
  DrgnBID (an int used to cross-reference a DrgnID from Table Dragon, to link rider and dragon together: type bigint(255), can be NULL)
  
  DrgnBID2 (as above, used to link a second dragon in: type bigint(255), can be NULL)
  
  Gender (as the same field in Table Dragon: type ENUM, cannot be NULL)
  
  Allegiance (as the same field in Table Dragon: type ENUM, cannot be NULL)
  
  Position (as the same field in Table Dragon: type text, can be NULL)
  
  Link (as the same field in Table Dragon: type Text, cannot be NULL)
  
  Thumbnail (as the same field in Table Dragon: type Text, cannot be NULL)

Table Whorlings has about the same setup as Dragon does, minus the Origin, since all Whorlings will be from Myrror. Table Whorlings covers all Whorlings not Dragon or Cat-Dragon type.

Those are the details I chose to work with, and my decisions will be reflected in the PHP script I eventually write, which will build tables for the index page of the /candis/ directory. A search function will also be available, searching for specific features (color/gender/name/etc) and producing tables based on the output of that search.

[Guest]

Yakima, you are more then welcome to ask me about stuff on MSN.

I don't have a Dragon/Rider database set up... but you've seen my horse sites, and they're basically just the same thing but with horses.

I too enter my info directly into my 'PHPmyAdmin' databases though, I could never convince it to work from an excel document. I'm getting awesome at displaying data though... so adding the PHP onto your HTML/CSS sites shouldn't be hard... I think I've even figured out how we could do your progeny tables.

As we've discussed though, I've not yet started making my databases searchable (I've never needed to with my horses... but it I do database my riders/dragons it is something I plan to do)...

[delyar]

OpenOffice Calc can turn your spreadsheets into a comma separated value sheet. The only annoyance, then, is that you can't have any commas anywhere or the apocalypse will ensue. (Or, well, PHPmyadmin will refuse to input that data.)

Star: Thanks for posting your setup! There are a lot of things that I was having trouble with (re: complicated colours, multiple bonds, etc.) that you managed to incorporate rather nicely.

How do you get your search to encompass various fields? For example, I want to search "Blue Rabth" and have it bring up "Rabth, W'han, blue, male, Falas, Shintaru". I can get it to work for specific fields (Name, Gender, Location) but not across multiples. I assume it's something simple but whenever I try to change my SELECT * FROM query to encompass more than one field, it doesn't work. (Just to clarify, these are all in the same database so I'm not trying to cross-reference those, which I found a tutorial on.)

My question is whether or not it would be possible to include more detailed fields like Personality Blurbs, Ability lists etc in a reasonable way so that the entire info page could be generated from the database using a template.

[StarFyre]

Star: Thanks for posting your setup! There are a lot of things that I was having trouble with (re: complicated colours, multiple bonds, etc.) that you managed to incorporate rather nicely.

Thanks. Took a bit of thought on my part, but I think the way I've done it makes sense.

How do you get your search to encompass various fields? For example, I want to search "Blue Rabth" and have it bring up "Rabth, W'han, blue, male, Falas, Shintaru". I can get it to work for specific fields (Name, Gender, Location) but not across multiples. I assume it's something simple but whenever I try to change my SELECT * FROM query to encompass more than one field, it doesn't work. (Just to clarify, these are all in the same database so I'm not trying to cross-reference those, which I found a tutorial on.)

Since you're using SELECT * FROM already, it's already grabbing the entire associated row. What you need to do, then, is use a function like this ( http://us2.php.net/manual/en/function.m ... -array.php ) to be able to iterate through the results gathered (a useful thing if some of your searches are likely to come up with, say, EVERY blue dragon you have). Inside that while loop that you see there in those examples on that page, you'd basically place your code for building that part of the page (the table, say, to display results in). Now, you will have to tell it what rows to display where, but that shouldn't be too hard.

My question is whether or not it would be possible to include more detailed fields like Personality Blurbs, Ability lists etc in a reasonable way so that the entire info page could be generated from the database using a template.

The answer is yes. Just create either another table that is cross-referenced to the Dragon table, or just add more fields to the Dragon table. Make those fields text fields, of course. Then, of course, the page would begin as a query (what dragon does the view wish to see?), grab the template, grab the data, then build the page using the exact same method as used above to build the table. I'm not 100% solid on the "what is the code to do this", but that's the pseudo-code/concept to build what you want.

By the way, I completely forgot about this in the last post about how to learn PHP: Anyone who wants to work with PHP should either have this page bookmarked: ( http://us2.php.net/manual/en/index.php ) or get the offline documentation that is exactly the same thing. (Click on Documentation, then Documentation Downloads, then choose your file of choice)

[delyar]

Yeah I have it returning an array already, but I can't get it to search multiple fields at once. I can do "select * from "Name" like %trimmed%" which works for "Rabth" but I can't figure out what to do for "blue Rabth" (ie. how to have it select from any).

I managed to get the echo syntax down fairly ok, luckily, and cross-referencing doesn't seem too bad. It just takes a LOT of time to convert 500 dragons and 330 bonders (+hydras etc) into database data. :P

[StarFyre]

Oh! =P My fiance and I debated a bit over what you meant, he read it as what you just asked, I read it (obviously) as what I responded with ^^;;

To match multiple fields, you need an AND thrown in, ala this page: http://www.w3schools.com/SQl/sql_and_or.asp (OR is also a very useful function)

[delyar]

Doh. OR. I tried AND and it didn't work and I was like "OMG WHY DON'T YOU WORK". Obvious reason is obvious.

Thank you!

[StarFyre]

Well, since I just made the same mistake as you, don't feel so bad ^^;; It's a common mistake in comp-sci, whether to use the AND operator or the OR operator. That mistake is second only to the dreaded Off-By-One error.

[delyar]

It's ok. After staring at so much code for so long, your brain tends to just melt. (I started reading "Pernese" as "Permese" and then it started wiggling... and that was just cleaning up the database!)

[DragonFlight]

Ok so I've been MIA quite a bit.

As a side note, mine works just fine. The gibberish is a result of one of those stupid automated spamming webcrawlers getting onto my update form before I locked it out and I've been way too reality-focused to clean it.

@_@

Random note, I'm actually working in Ruby now, that search engine will be obsolete soon enough. >3

[Cy]

Hahaha I'll just wait til someone has a fully functional code, and then tweak it to my needs ^_^; it's easier for me to see what I'm doing; being able to change a section, and seeing how that affects the rest.

[StarFyre]

So, I got annoyed with not being able to find certain things. And, when I get annoyed with doing things by hand, I make machines do it for me, if at all possible.

As a result, I now have a fully functional search code at http://www.silveredmagic.com/candis/search.html

You can download and view the html and php here: http://www.silveredmagic.com/downloads/ ... nction.zip


I might have gone a bit overboard with the comments ^^;; sorry. But I seem to only know two methods - no comments, or a ton of comments everywhere. Do note, however that this is set up for the way my database is ordered. If anyone has questions as to what's what, feel free to ask.

[delyar]

Holy complicated search page batman. :P Mine is just a simple keyword search.

Any knowledge of how to search keywords across two columns? ie. "blue Rabth" will return the proper row? Mine still only returns if I give it data that all fits in one column.
Example: http://lambeosaurine.com/characters-new/search.html

That said! I'm slowly creating the template for individual dragon information. My pedigree and offspring pages aren't quite compiled yet, hence the errors at the bottom of the page, but here is it so far: http://lambeosaurine.com/characters-new ... e.php?id=1

[StarFyre]

=P Well, I wanted to be able to cross-reference any amount of data.

Mmm.. an idea, yes, but I'm not sure how to implement it at the moment. What you'd need to do is split the string that you're accepting from the input box (based on spaces as delimiters), then send as "Blue AND Rabth" - however you're building the query.

And nice template. I'd do that, but that'd require me to redo over 100+ pages, and... yeah, don't really feel like that =P Call me lazy.

[delyar]

I have over 500 dragons and riders. :P I figured the php way was horribly tedious to get up, but worth its weight and gold once I got it going. (Especially if I can refer to it from individual rider pages and keep all the info current.)

Hmm ok, I'll poke around and see if there's a way to do that. Thanks for the tip!