Need Help With A Concept... I can think it... but I cant do it...

[nachtgeist03 0]

Ok, first off, thinking things through and looking at the basics of mySQL, I decided that this most likely will be the best way for me to display the info I'm trying to get out instead of generating one huge file for people to download every time they want to look at it.I'm only going to use examples that seem to easily show what I'm looking to do.Lets say I'm trying to display types of fruit. This would be useful if, say, you had an unknown fruit and were trying to determine what it actually was, along with a recipe to cook your fruit. In the database, you'd obviously have listed all types of fruit, and all the ways you'd like people to be able to look for their fruit.The idea I have is having a dropdown menu with different variables to search for (color, shape, size, etc.), then a text feild next to the dropdown menu in which someone could submit a keyword to search by. So, say you had a red fruit. In the dropdown menu, you could set it to "Color" and then in the search field, type in the word "Red" then hit submit. At this point it would go and look for all matches in the database that would match the Color=Red search, then display it back on your page in a nice table, with each matching result's Name, Recipe, Color, etc.Now that's the idea ~ how do I turn the idea into a functioning page? And is there a fairly simple way for me to lay all this information out without having to enter line after line of mySQL commands to get the data into the tables I'd need, such as a GUI interface?

[oncombeureum 0]

1. u want to display types of fruits2. u have a table for receipt of your fruiti still can't get what u want to get ? the information given is rather blur.>The idea I have is having a dropdown menu with different variables to search for (color, shape, size, etc.), then a text feild next to the dropdown menu in which someone could submit a keyword to search by. So, say you had a red fruit. In the dropdown menu, you could set it to "Color" and then in the search field, type in the word "Red" then hit submit. At this point it would go and look for all matches in the database that would match the Color=Red search, then display it back on your page in a nice table, with each matching result's Name, Recipe, Color, etc.>Now that's the idea ~ how do I turn the idea into a functioning page? And is there a fairly simple way for me to lay all this information out without having to enter line after line of mySQL commands to get the data into the tables I'd need, such as a GUI interface?i think u should create something like this:1- the search page (could be php or htm)2- the post page (this should be php)3. the display page (inserted by post page)in the search page you can each dropdown will have it own id and valuesuch as id=color value=color, id=shape value=shape .. etcduring the post pageu have to insert the value into sql query ============================ex:$wheris = $_post("selectwhere");$wheris2 = $_post("theselect");if (whereis2 == blank) { $wherenya = "";} else { $wherenya = "where " . $wheris . " = " . $wheris2;}$sql = "select * from fruit $wherenya";go on ==============================that's maybe the solution u may need.danke Oncom BeureumThe Best Place in The City

[nachtgeist03 0]

This might explain it better. Lets say you have a green fruit in your hand, and you want to know what in my database will match the color green. You change the pull down menu to "Color" and enter "Green" into the search field:

 

 

How do I go about setting the table up in mySQL to hold all the information, then use a search function like this to find what someone is looking for, then display it onto a page?

[nachtgeist03 0]

Is my description still unclear where nobody can figure out what it is I am wanting to do? This has sat here for over a week without reply... c'mon, someone has to know how to do this or know a reference that does.... <.<

[wykked_dreamz 0]

I understand what you want to do, but I'm not sure how you're going to do it. You may want to do a yahoo or a google search on it. I can tell you right now that you will have to have the dtatbase as a seperate file. I'm sorry that I can't be much of help, but I have only taken a course of a programming language called scheme. I can't do much in javascript, perl, php, cgi, or anything else. when you search though, make sure you put "dtabase search script" in quotation marks to improve your results. hope I helped in some way.

[vhortex 1]

Is my description still unclear where nobody can figure out what it is I am wanting to do? This has sat here for over a week without reply... c'mon, someone has to know how to do this or know a reference that does.... <.<

<{POST_SNAPBACK}>

you want this:

 

COLOR  <-- column namegreenyellowblackNAME   <-- column nameAppleMangoBerries

Then you will put a drop down which selects the column name and check what is it to be search..

 

Example: You can either choose COLOR or NAME and type the desired string to search..

 

 

You can build the query like this..

query = "SELECT * FROM " + $table_name + " WHERE `" + $column_name + "` like  \"" + $search_text +"\"";

$table_name = the database table name that you are going to use

$column_name = the database column that you are going to search for and the value is COLOR or NAME..

$search_text is the text that they input in your search box..

 

 

If I do help you, good increase in my reputation rating will be appreciated...

[signatureimage 0]

Dear nachgeist03,

 

Before tackling the web-interface, let's first do a definition of the data-base structure that you need.

 

You need a data-base table that contains a row for every fruit that you want to describe.

The columns of that "fruits" data-base table would be:

 

(1) id (identification) - type=integer - autoincrement (means that the data-base engine will generate a value for this field, automatically, when the row is inserted into the data-base table "fruits".

(2) name (the fruit's name) - type=text

(3) color_id - type-integer - this field will contain the identification number of the row in the "colors" data-base table that contains the information about fruit colors.

 

 

Because we talked about a new data-base table, "colors", let's describe this tables.

The columns of the "colors" data-base table would be:

 

(1) id (identification) - type=integer - autoincrement (means that the data-base engine will generate a value for this field, automatically, when the row is inserted into the data-base table "colors".

(2) name (the color's name) - type=text

(3) description (the color's long description) - type=varchar

 

We will need a web-dialog that allows us the enter the different colors, and their long description.

 

 

The columns of the "recipes" data-base table would be:

 

(1) id (identification) - type=integer - autoincrement (means that the data-base engine will generate a value for this field, automatically, when the row is inserted into the data-base table "recipes".

(2) name (the recipe's name) - type=text

(3) description (the recipe's long description) - type=varchar

 

We will need a web-dialog that allows us the enter the different recipes, and their long description.

 

 

And yet another data-base table is needed.

This data-base table will be the link between the "fruits" and the "recipes" data-base tables.

 

The columns of the "fruits_recipes" data-base table would be:

 

(1) id (identification) - type=integer - autoincrement (means that the data-base engine will generate a value for this field, automatically, when the row is inserted into the data-base table "fruits_recipes".

(2) fruit_id - type=integer - this field will contain the identification number of the row in the "fruits" data-base table that contains the information about fuits.

(3) recipe_id - type=integer - this field will contain the identification number of the row in the "recipes" data-base table that contains the detailed information about fuit cooking recipes.

 

 

 

 

Why did we separate our information into 4 data-base tables?

This was done because of the rules of relational data-base design:

when an information occurs more than once, it must be moved into a separate data-base table.

This is know as "normalizing".

 

Indeed, more than one fruit may be "red", and more than one fruit may be used in the same recipe, for instance "apple pie with raspberry topping".

 

 

The "fruits" data-base table would be:

id=1 - name=orange - color_id=4

id=2 - name=raspberry - color_id=6

id=3 - name=apple - color_id=5

id=4 - name=strawberry - color_id=6

 

 

The "colors" data-base table would be:

id=1 - name=black - description=blabla...

id=2 - name=white - description=blabla...

id=3 - name=blue - description=blabla...

id=4 - name=orange - description=blabla...

id=5 - name=green - description=blabla...

id=6 - name=red - description=blabla...

 

 

The "recipes" data-base table would be:

id=1 - name=Apple pie with raspberry topping - description=The recipe is as follows:.......

 

 

The "fruits_recipes" data-base table would be:

id=1 - fruit_id=2 - recipe_id=1

id=2 - fruit_id=3 - recipe_id=1

 

 

Now that the information is stored into separate tables, it has become very easy to use PHP to generate the drop-down boxes for the web-interface.

We simply query the data-base table colors and obtain all valid colors, alfabetically.

select id,name from colors order by name;

 

(The same would be true for supplemental information, like the month that the fruit is available. Just create a separate data-base table, and insert a new column in the "fruits" data-base table.)

 

 

The generated HTML would be:

<SELECT NAME="mycolor">
<OPTION VALUE="1">black
<OPTION VALUE="2">white
<OPTION VALUE="3">blue
<OPTION VALUE="4">orange
<OPTION VALUE="5">green
<OPTION VALUE="6">red
</SELECT>

 

 

And the PHP that would be called could do the following SQL:

select name from fruits where color_id = $mycolor;

This would result in all fruits with the color that the surfer had selected in the dropdown box.

 

Moreover, the PHP could do the following SQL:

select recipes.name, recipes.description 
from recipes,fruits,fruits_recipes where fruits.color_id = $mycolor and fruits_recipes.fruits_id = fruits.id and fruits_recipes.recipes_id = recipes.id;

This would result in all recipes with fruits that have the color that the surfer had selected in the dropdown box.

 

 

From here on, you can start designing your web-application...

[vhortex 1]

Dear nachgeist03,

 

Before tackling the web-interface, let's first do a definition of the data-base structure that you need.

i do highly agree with this one since the database design is the most thing of concern here. Good database designers knows a rule of the thumb, malformed database design will bog down any system that it runs on..

 

Maybe the concept of fragmentation that signatureimage introduce is foreign to you but it is a design technique that you need to consider.. the purpose of a database is to compact the data and minimize repeatitions.

 

And sometimes we fragment data into several tables to speed up searching..

 

the smaller the record to be search the faster the response will be.

 

Corrections:

select id,name from colors order by name;

will produce two column results, column1 is id and column2 is name however this wont always result in an alphabetical order of listing..

 

it depends on the backend engine, even MYSQL have lots of database engine designs..

 

the complete query is

 

select id,name from colors order by name ASC;

notice the ASC at the end, it will force the database to sort all the return matches in an alpahbetical order from a - z.

 

The dropdown method is good but i guess he wants the user to type what to search and what values need to be search..

 

the more appropriate SQL query is like this

 

select f.name from fruits as f, color as c where f.color_id = c.id and c.name like "%$search_text%"

why do we use the keyword LIKE?

 

it is for one good reason.. searching means that sometimes or most of the time, people will just input a portion of a word or phrase and hope that everything will be fine. people sometimes forget the exact words to search for..

 

the LIKE keyword will search every entry in color with names having the $search_text string in any part of the name..

 

a $search_string of "re" will display fruits with "green" and "red" colors.. now we really created a group of lazy people are but that is how searches should work..

 

now what are the f. and c. letters in the query?

 

well they are called alias qualifier, we called fruits as the plain letter f and color as the plain letter c.. cool eh?

 

this is in regards with the above database design given by signatureimage.

 

now what if we want ot search the fruits name?

 

select f.name from fruits as f, color as c where f.color_id = c.id and f.name like "%$search_text%"

just replace the letter c at the end with a letter f (and f.name like "%$search_text%")... and it is done

 

now you onle need to check the search criteria and switch the right query..

 

of course there are far more better way in doing this but they require some adjustment in the database tables but i guess we have both [signatureimage] laid out the path for you..

 

signatureimage last query is also cool and usefull, I dot use those kinds of queries.. and as on my point of view it can also be compacted..

 

the only limit that you have here is your imagination so use it wisely..

 

ciao!!!