Beginner looking for advice on creating a multi variable web search function.

[Snot]

Greetings

I am trying to get a search able database for my site I hope I am posting this in the right place, if not please feel free to more or delete it and I'll know that I was wrong. Well this is my project I have been trying deferent search engines from here for my Tarantula website and I haven't really come up with anything pre made for the search function it just needs to allow users search by all or any of these options.

1. Scientific Names = genus + species-----------------Will be added over time
2. Genus = 100+ different tarantulas------------------Can be added upfront but may need to change or add to over time
3. Species = 800+ different species-------------------Will be added over time
4. color = The most common colors---------------------Will be added over time
5. Gender = Male, Female------------------------------Fixed
6. Humidity = Low 50% to 60%, Medium 60% to 70%, High 70% to 85%-----------Fixed
7. Speed = Slow, Medium, Fast-------------------------Fixed
8. Temperature = Cool 50°F to 80°F, Warm 60°F to 90°F, Hot 75°F to 90°F----Fixed
9. Max Size = 1" to 3", 4" to 6", 7" to 8, 9"+--------Fixed
10. Location = New World, Old World-------------------Fixed
11. Housing = Arboreal, Terrestrial, Fossorial--------Fixed

There are 800+ different types of Tarantulas and 99% of the time, you decided you want a big one or a small one or a blue one or a extra harry one before you find out what Tarantulas actually have those traits. I would like my site to be a place where you can search for blue and find all the Tarantulas that are tagged as blue. You could mix and match and search to find all the blue Tarantulas that are Old World and so on.

I also need a system that will allow users to submit Tarantulas to my website using their scientific names along with any other information or pictures they have. There also must be a way for me/admin to approve/deny/edit all the submission before they are posted into the site or made search able. So there will need to be some type of admin login panel that will allow me/admin to be the only ones able to approve/deny/edit the submission.

There must also be some way to upload images and data about the Tarantulas that are already existing in the site. I also need to be able to have a good looking results page.


Well you've got to start some where right? Grin Please forgive me for the following vague questions but being a beginner its really be a challenge to explain everything I need the search function to do.

I would really appreciate it if you guys would be so kind as to help me figure out what sets of code/database or program will allow me to accomplish this?

My most important question is how a beginner should go about starting a project like this?

I was advised to check out regex, php, mysql, and ASP (I think it only works on windows server and mine is on a Linux) Huh

This is a link to my site. The only thing I care to keep is the design the form's not connected to anything and for all I know I'll never need them but I made them just to give myself an idea of what I thought it would look like.

Tarantula Database Search Form

Thank you all so much for trying to get me started on the right path.

[sde]

hi snot, welcome to the site. you need to start simple.

does your web host support php and mysql? if so, start with creating a mysql database, and then a tarantula table with just one field. add a few records to the database so you have something to play with in the next step.

next step is to learn how to connect and query the database with your server-side language (php). since we only have 1 field, we can do something simple like

PHP Code:

$result = mysql_query("select * from tarantula where scientific_name = 'some smart name';"); 


once you are comfortable with how that works, the next step is to understand how PHP submits forms. you can use your current form, but only use the Scientific name for learning purposes. the code to process the form at this point may look something like this

PHP Code:

<?php
$name = $_POST['scientific_name'];

$result = mysql_query("select * from tarantulas where scientific_name='{$name}';");
$rows_found = mysql_num_rows($result);

echo $rows_found . ' rows found.';
?>

keep in mind, this is not tested code, i'm just giving you brief examples.

once you are able to query one field ok, then you need to learn a little more about sql queries and how to check for multiple fields. i.e.

select * from tarantula where scientific_name='my spider' AND genus='Acanthopelma' AND species='...';

you will have to dynamically build your query string based on what the user submits.

having a project is the best way to learn. just start simple and don't get fusterated.

[Snot]

Thanks so much for helping me

Testing Tarantula Database

This is kinda what I have been playing around with If this is working I can see how it works a little.

I haven't been able to get connected to the database I did find out that to make a table I go into my cpanel then into phpmyadmin. I made a table with 1 feild but I don't understand what format I need to use.

Will i have 1 table for each spider or will i have one table called Tarantula with 1 field called Housing and so one like this.

Table called Tarantula

>Housing
>>>>>>>>Arboreal
>>>>>>>>Terrestrial
>>>>>>>>Fossorial
>Location
>>>>>>>>New World
>>>>>>>>Old World
>Speed
>>>>>>>>Slow
>>>>>>>>Medium
>>>>>>>>Fast

Is this right at all?
I am confused about the things that I dont know like genes species and color.

[sde]

yeah, 1 table. each 'field' within the single table is a piece of information about tarantulas.

try naming your fields lower case with underscores to separate words. that's pretty standard.

for short amounts of text, use varchar field types. you can specify a length of up to 255 characters. the length is just the max number of characters in the text... it can be less.

for paragraphs of information, let's say you had a column named "history" and it could possibly have more than 255 charcters, then use the 'text' field type.

for whole numbers, use 'integer'

usually you will want to setup an field that automatically assigns a unique id, or number, to each tarantula row. just create an 'integer' field named 'id' and set it as the primary key and auto increment. you will notice that every time you enter a new record, that number will increase.

here's an old tutorial on how to connect to mysql through php: http://php.codenewbie.com/articles/p...QL-Page_1.html

[Snot]

Thank you so much for helping me with this I know i'm a total noob but I skiped the id field becuase I keep getting this error and the only thing I am not seeing is where I select integer

Error

SQL query:

ALTER TABLE `tarantula` ADD `id` VARCHAR( 255 ) NOT NULL AUTO_INCREMENT FIRST

MySQL said: Documentation
#1063 - Incorrect column specifier for column 'id'

But I went ahead and made as much as I could and I think i got the hang of how to create the table... I think

This is my table

http://www.tarantuladatabase.com/table.JPG

and these are the values

http://www.tarantuladatabase.com/value.JPG

I couldn't figure out how to leave a field value black so I just put None in as a place holder.

How does that look?

[sde]

Field 'Type' is what you need to change from Varchar to Integer. Sometimes you will have problem doing that if there is already data in it, so if it still gives you trouble, just delete the field and add a new one.

[sde]

as far as your data goes, remove 'Search All' and 'None' since those are not tarantulas. You will need to use PHP to check that form field to see if they selected 'search all' and modify your query appropriately.

[Snot]

Could you give me an example of how I would put in my last line?

housing, humidity, and speed have 3 lines

size has 4 lines

and location only has 2

Could you show me out I enter a new line for size with out entering a new line for the rest of them?

[Snot]

never mind I fixed the id thing and got rid of the search all and none values.


http://www.tarantuladatabase.com/gotit.JPG


now to try and connect it to the database *Gulp*

[Snot]

This is my first time working with php and databases and this is my database table fields

http://www.tarantuladatabase.com/tf.jpg

This is my field values

http://www.tarantuladatabase.com/fv.jpg

I am using two php files one of them is called connect.php and it only has the information to connect to my database. The other files is called echo.php. The form is at

Tarantula Database Search Form

When I try and connect I am getting this error message

Warning: include(includes/connect.php) [function.include]: failed to open stream: No such file or directory in /home/e0120590/public_html/tarantuladatabase.com/assets/php/echo.php on line 38

Warning: include() [function.include]: Failed opening 'includes/connect.php' for inclusion (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/e0120590/public_html/tarantuladatabase.com/assets/php/echo.php on line 38

Warning: mysql_query() [function.mysql-query]: Access denied for user 'e0120590'@'localhost' (using password: NO) in /home/e0120590/public_html/tarantuladatabase.com/assets/php/echo.php on line 40

Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in /home/e0120590/public_html/tarantuladatabase.com/assets/php/echo.php on line 40

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/e0120590/public_html/tarantuladatabase.com/assets/php/echo.php on line 43

Warning: mysql_close(): no MySQL-Link resource supplied in /home/e0120590/public_html/tarantuladatabase.com/assets/php/echo.php on line 48

I am kinda stuck right here so any advice would be greatly appreciated.

Here are the three files I'm working with http://www.tarantuladatabase.com/3_Files.zip

[Belisarius]

Take a look at the first Warning; your page can't find "include/connect.php".

[Snot]

Thanks so much I got that changed and also changed results to request.

Now I am at this error

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/e0120590/public_html/tarantuladatabase.com/assets/php/echo.php on line 43

I haven't really done anything with this file so I'm sure I need to some how match it up with the search form.

[Snot]

Thanks so much for the help

This phase of my tarantula database project has really slowed everything down. Learning PHP code is way more difficult than setting up the database.

I am trying to match my php code up with the form found here. Tarantula Database Search Form The big problem is that I've done so much guess work with the database that I'm not sure if its even set up like I need it to be.

But aside from that I have a php file called connect.php that is working and connecting to my database after my file path issue got in order. I also have a file called echo.php that contains my non working php code.

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/e0120590/public_html/tarantuladatabase.com/assets/php/echo.php on line 43

This is my php code

<?
include("connect.php");
// now you are connected and can query the database
$request = mysql_query("select field1,field2 from table");

// loop through the results with mysql_fetch_array()
while($row = mysql_fetch_array($request)){
echo $row[0]." / ".$row[1]."<br>\n";
}

// don't forget to close the mysql connection
mysql_close();
?>

Its just a copy and paste gig I got off of some php link I was reading. All i changed was the path to my connect.php file and the $results to $request so I expected the errors and now..

I'm just trying to find out if my database has enough information in it to even be search able? http://www.tarantuladatabase.com/fv.jpg and what I need to do to get my php file talking to it?

Any advice or links or anything to point me on the right track would be great. I have been reading and trying to learn php but its really confusing me hehe

[Snot]

I think it got it?

Tarantula Database Search Form

[sde]

good job

when you get an error on mysql_fetch_array, it usually means you have a bad query. test out your queries in phpmyadmin or whatever mysql manager you have when you run into those problems.

now you can work on creating a dynamic sql string. here's a quick example you would have to modify to make work.

PHP Code:

<?php
// check if they submitted a search
if ($_POST) {
  // make an array to hold conditions
  $query = array();

  // check if they are searching for habitat
  if ($_POST['habitat']) {
    // append array if they selected something for habitat
    $query[] = "habitat='". mysql_real_escape_string($_POST['habitat']) . '";
  }

  if ($_POST['speed']) {
    // append array if they selected something for speed
    $query[] = "speed='". mysql_real_escape_string($_POST['speed']) . "'";
  }

  // now build your query.  we're imploding our array and putting the word 'AND' in between each element
  $sql = "SELECT * FROM trantulas WHERE ". implode(" AND ", $query);

  // just so you can see, print your sql here
  echo $sql;

  // now run the query
  $result = mysql_query($sql);

  // now loop through your results like you normally would
  while($row = mysql_fetch_assoc($result)) {
    echo "id: {$row['id']}, species: {$row['species']}, speed: {$row['speed']} <br />";
  }
}
?>

notice mysql_fetch_assoc($result)

this lets you access your results by the field name in the database. it's probably easier and makes your code more readable to use: $row['id'] instead of $row[0] .. or however you are using it.