You'll have to forgive me. I'm not a programmer by any stretch of the imagination, but have picked up enough basic know-how to webmaster my small business's website. Long story short, I own a karaoke company, and for about a year have wanted to put my karaoke catalog on my website in a searchable format. One of my competitors has this on his website, so I know it can be done, and probably very easily. I've managed to publish my catalog in my website's SQL database, and have looked and looked for a basic php template I could tweak (without knowing anything about coding php) in order to allow my visitors to search my catalog.

Here are the specifics:
SQL database name-- liquidkourage_net
Table name-- Karaokemusic
5 fields-- Title, Artist, Genre, Disc#, Track#

What I'm looking for...
A convenient way for visitors to fill out separate search forms for Title and/or Artist, a drop-down menu listing the different Genres (only one of those three fields would be required in the search), and a result page that lists all relevant "hits" in a small table which includes all 5 fields.

If there's a template that's completely idiotproof out there, I haven't found it yet. ANY help from the seasoned pros would be greatly appreciated.

have you tried creating the pages your self??? just to see if you can do it?


if you find something you like dl it and we can help you to get it operational. Most php templates are not idiot proof. they require some code editing

You're probably not going to find something that specific.

If you're prepared to pay a very small amount to have it done, I'll be happy to discuss it with you?

approximately 198,764,476 vietnamese dongs should do it, thats about the going rate?

Well, the good news is that after about a day of looking at tutorials and bits of script, I think I can build the "search form" page without too much difficulty-- but I have tried a few different types of code for actually processing the inputs, and I run into problems. Some of them I figured out are simply syntax errors, easily corrected, but one that keeps coming up is:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

Does that mean I have to define that function in the code, or am I possibly messing up some syntax that I don't know about?

Also, I'm not sure if this is the crux of that problem, but I know that I need to define host, username, and password for mySQL... I know the username and password, but I'm honestly not sure what I should be putting for the host. If my script is on the same server as my DB, can I just put something along the lines of "host" or "localhost", or is there a specific host name I need to be inputting? If so, I don't what exactly my host name would be...

Y'all are going to laugh at me... turns out the issue was a capitalization error in the table name!

So far, I have a basic search form page that will actually return matching results in a table-like format (all html formatting)...

Now, I'd like to make it a little "prettier"...

Issues I'd like to fix--

1. As it stands right now, my searches will only return rows that start with the search string I input, e.g. I enter "Black" into the title section and will return every song I have that starts with "Black", but isn't exclusive to only the word "Black", or won't return songs where "Black" isn't the first part of the title, like "Back in Black"...

2. My results appear relative to their position in my table, e.g. they are not necessarily in any alphabetical order. I would love to have a results table that either automatically sorts results alphabetically by artist, then title, or a results table that is alphabetizable on the client side, like a spreadsheet.

Simple enough for anyone who can code html/php, but again, I only know what I have seen, which is very little.

I'll keep working at it, but any ideas would help greatly. Thanks in advance.

cool LiquidKourage, good to see you managed to get this far! What you actually want to do can be done without PHP, that is it'll be done with the SQL query.

It take it you're using something like:

SELECT * FROM karaokemusic WHERE Title LIKE '%black%' ORDER BY Title ASC

Simply, for the latter part of your question, ORDER BY will sort the result set before actually returning anything back to you, you can change what is after ORDER BY and how it is ordered, ASC or DESC. You can also set more than one order clause, for example, you may want all the artists together, so you'd do ORDER BY Artist ASC, Title ASC

You'll notice around the work 'black' are some percentage signs. SQL uses these as wild-cards. Placing these at either end will find 'back in black', 'black november', 'bigblacknothingness'. So basically, anything with black in it! You can change where the wildcard goes depending on what you want to look for.

Hope that helps!

Thanks a million for the advice! I was racking my brain for over an hour trying to figure out the syntax for the "CONTAINS" operator instead of using "LIKE"... figured it was going to do what i needed, jurns out i just needed to add wildcards to the front of my variable names! I'm sure I would've seen that a mile away if i were thinking clearly about the whole thing...

leavethisplace, if you're ever in my area and need a DJ, there's no charge :-)

heh, unless you can do d'n'b very well I won't need a DJ

Liquid, now that your all caught up with php and mysql I would like to propose a different search strategy that will only affect your sql. MySQL, since version 4.1, offers full text searching which will provide much more relevant and usefull results than using a LIKE clause.

Building upon the same query that leavethisplace put together a full text search would look like this....

SELECT * MATCH(Title) AGAINST('black') FROM karaokemusic

Now in order for this to work you would need to make a fulltext index.

CREATE FULLTEXT INDEX index_name ON karaokemusic (col1,col2,col3)

I think that's the syntax although I cant remember correctly. It's much easier to do with phpmyadmin.

That's pretty cool, i'll have to put that to the test!

I should have mentioned above that there are limits to full text searching, namely there has to be at least three rows in the table your going to search and each search term has to be more than 3 letters. Also, full text searching automatically excludes stop words like and, the, or, etc.