searching using php

Do you want to know both or just one? Here is just a rough PHP code to search a table and return the results...

wow thanks!

is it possible to have the user input the search parameters in a form?

In my case, we are storing emails in the database, with the fields being things like sender, receiver, date, subject and so on. Is it obscenely complicated to create a search feature that would allow you to specify something like "search for Fred as receiver"? I'm thinking a drop down box where you'd check the field (sender, reciever etc).

Honestly I've biten off more than I can chew here and any ideas would be wonderful.

-Kristen.

No, that's actually pretty simple.. All you have to do is a pull down with a few options. Lets say you name the pull down "in" and the options have the values of "sender", "receiver", "subject" and "message". Now if a user chooses "receiver" from the pulldown and searched for "john", the form will submit search.php?term=john&in=receiver

Then you have a PHP script that connects to the MySQL and does a query..

The above code gets the value of "in" ($_REQUEST[in]) and "term" ($_REQUEST[term]) and uses them to customize the MySQL query. The %'s around $_REQUEST[term] are wildcards which means anything can be on either side of the search term.

Great thank you! With a little help from Suzanne I think I can work it out. I'll be spending the day on it tomorrow, so no doubt I'll be back with more questions!

thanks again,
Kristen.

is it possible to search the entire database?

I just realised this db is made up of several tables, there's one that has the sender and the subject, a different one that has the message and yet another that has the receiver information.

Would I have to set up separate searches to extract the information depending on what table it's in, or can the whole db be searched by field name?

Yeah, it should be possible to loop through each table

Or something like that. Not too sure without checking the documentation. Someone please correct me if I'm wrong.

Still, it seems odd that you would use different tables for each piece of info. Normally, I would expect it to be in one table...

The information is in different tables because the person who designed the DB normalized it.

If the tables can be linked together using foreign keys, then you could search all the tables using a single JOIN query.

Abhishek, I would hard coded at least an array of tables to search. This will search every table in the DB for $_REQUEST[in]. First, we may not want to search certain tables. Second, not every table will have a field named $_REQUEST[in].

Mark,

I did some reading on database normalization a while back and I do use it at times but I think I've missed a little info about it. Lets say you have 2 tables, one with song names and the other with the artist names and you want to select all songs from the database..

SELECT s.song_name, a.artist_name FROM songs s, artists a WHERE s.artist_id = a.id;

Wouldn't this be slower to execute rather than storing the artist name for each song? I know that if you don't normalize it will take a lot of diskspace, but what if diskspace is not a problem?

Any thoughts on this?

PS. I tried this on our music database with about 1300 songs and it looks like the results are returned 25% faster without the normalization.

Normalized databases are slower. Saving disk space is a small benefit. The real benefit is scalability. Another plus is that it's easier to mass update records.

Imagine that one of your artists legally changed their name. In a normalized database, you'd have one record to update. In a non-normalized DB, you'd have to update every record that contains the artist's name.

Yeah, but I don't see how that is harder to update..

UPDATE table SET artist_name = 'Ozzy' WHERE artist_name LIKE 'Black Sabbath';

In some situations it would be a better choice perhaps, but for normal users I think it's too much.

I am (very very) new to this, but I see the advantage to being normalized. For me, it's not a matter of updating the database, it's expanding it to include other tables at the drop of a hat. For instance, if the database went from artists and songtitles to artists and songtitles and merchandise based on each song title or based on each artist.

As I understand it, adding other relations in a normalized database is easy, but in a non-normalized database, becomes time-consuming and brutally ineffective with large scale data.

Is that right?

Well, if you were going to do it per artist, it would be a lot better and easier if the database was normalized. However, if you were going by the song, you could simply add another column to the table and input the data there.

Correct me if I'm wrong here, but what I don't like about a normalized database is the fact that... Well, let’s say you have 100 artists and 1000 songs. If you do a query, it will compare the 100 artists to the 1000 songs which is about 100,000 comparisons where a non-normalized database would have given you the same result by going through only 1000 rows.

Well, ideally you would have a primary key with each table and you would have indexed the foreign keys. This makes searching much easier.

I haven't tested this, but it seems logical that it's easier to search an indexed numeric (int) field rather than a text (varchar) field (indexed or not).

Quote: Originally posted by zollet
Yeah, but I don't see how that is harder to update..

UPDATE table SET artist_name = 'Ozzy' WHERE artist_name LIKE 'Black Sabbath';

Not necessarily easier, but faster. A normalized DB would only change one record. A non-normalized DB would change many records.

Quote: Originally posted by zollet
Correct me if I'm wrong here, but what I don't like about a normalized database is the fact that... Well, let’s say you have 100 artists and 1000 songs. If you do a query, it will compare the 100 artists to the 1000 songs which is about 100,000 comparisons where a non-normalized database would have given you the same result by going through only 1000 rows.

let the software developers worry about optimized implementation, and know they do a damned good job.

i'm by no means an expert, but what i would imagine happens is something as follows:
- database parses the query and determines you're joining the tables 'artists' and 'songs' presumably on 'artistid'.
- artists.artistid is a primary key and songs.artistid is indexed, or at better be otherwise you have some reading to do
- database iterates through the 'artists' table matching any criteria specified in the where clause, using indexes if possible
- when a match in the 'artists' table is found, the 'songs' database is used searched for join criteria, returning valid results and caching non-valid ones in memory
- since non-valid results are in memory there are no more disk seeks necessary in the 'songs' table after the first iteration.
and, if you properly index your tables the database will know exactly where to seek.

dont doubt that optimization techniques are far beyond my knowledge or understanding, just know normalization IS good, and expected if youre getting paid to do this.

There are cases where normalization really is not a good choice. I'll use VBulletin for an example, although horribly implemented and completely ineffective they had the right idea. VB has a user table and a thread table, with primary/foreign keys being user.userid/thread.userid. user (obviously) has a username field, but, so does thread (or maybe it's called 'author'). anyways, i presume this was to avoid a join when displaying a list of threads and their authors. considering threads is probably the most hit table in a vb forum it was a good idea, but they join anyway for other frivilous crap. anyways, the point is there is an in-between, although there are purists who would argue with me.

Even though there are exceptions, those are just that, exceptions to the rule. IF YOU EVER WANT TO MAKE A LIVING DEVELOPING APPLICATIONS LEARN HOW TO, WHY TO, WHEN TO, AND WHAT TO NORMALIZE, AND DO IT!!! My god, i've downloaded freeware scripts before that made me want to barf, crap like this (a fictional example):

TABLE CARS
name
password
email
make
model
year
color

TABLE MUSIC
name
password
email
likesrock
likescountry
likesbritneyspears
likesbluesclues
likesamericanidol
bandname1
bandname2
bandname3
singer1
singer2
singer3
favoriteguysinger
favoritegirlsinger
favoriteguydrummer
...

and to think the same people who wrote databases like this wrote the install scripts that asked for the root password to my database (note: never ever give your root password to mysql (or *nix or ANYTHING for that matter) to any program. it's not meant for that.)

anyways, 'normalization' isn't just a fad that's hip today and gone tomorrow. people dont do it because it's fun. people do it because that's the right way to do it. i could try to further explain exactly why that's the correct way, but i'd only be repeating thousands of documents at your local Google store.

Cheers

The database was written for oracle by a someone else, and yes, it is normalized.

I was given the oracle sql syntax and have to change it to work in mySQL. I haven't the first clue how to do this, but I did manage to create the appropriate tables and their rows etc. I don't know how to link up the foreign keys etc and can't work it out from the mysql.com information. Basically I just have 5 separate tables at this point, so any help in getting them to work together would be greatly appreciated!

Also, I'm not sure how to get the dropdown working to submit a request.

I hate asking for so much help but I'm really in a jam with this - I need to have it done by next week and have a lot of other things going on right now as well (my cat is very sick) and am just having trouble working it out on my own.

any help would be greatly appreciated!
-Kristen.

Oracle? Wow. Would love to get me a copy of that... just a tad bit too expensive though.

mySQL currently doens't have foreign key support. All you can do is index them. You'll specify which fields to join on in your queries. I believe that foreign key integrity is on the features list for future versions.

Heh, it's actually a student Oracle account at my university - so we didn't buy it!

Okay so I need to just specify the fields in the search itself?

You can try some of the examples in this thread. I have no idea how your DB looks, so I can't say exactly what you need.

Me again.

It's coming down to the crunch for this assignment and I'm willing to pay one of you guys for some serious help. I've had a death in the family and am just not up to figuring it all out and at this stage study is taking a backseat.

Basically, I need the database created. I have the exact specifications for it, as we have it running in oracle as well and will just be putting that same db into mySQL. So all that needs to be done there is take the tables.sql and create them in mySQL. Then there is the search feature. Basically I'd like just a simple form where the user can input their search object (e.g. John) and then choose from a drop-down menu the field to search (e.g. sender). Then the result will display all emails with John in the sender field within the db. If possible!

please email if you can help.

forgot to mention - the database has been changed since the original specifications and now most of the important search fields are within the same table. Might make things easier.

you can actually download search scripts (free) from hotscripts.com I got some the other day but havent had time to try them yet. Some search flat files, some databases, some index, some dont ...

sorry can't be more help

thanks - I'll check it out.