How to add more than one value to a mysql database field

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Snot
Forum Newbie
Posts: 12
Joined: Wed Jun 11, 2008 2:33 pm

How to add more than one value to a mysql database field

Post by Snot »

Ok guys no more error messages! ;D

You try it out here

I think its connecting with the database even though its not valid code but I'll work on that :D So this is my new code in echo.php

Code: Select all

<div class="c1">
    <table cellspacing="20">
<tr>
            <th>Habitat</th>
            <th>Humidity</th>
            <th>Speed</th>
            <th>Size</th>
            <th>Location</th>
            <th>Aggression</th>
        </tr>
        
    <?
include("connect.php");
// now you are connected and can query the database
$request = mysql_query("SELECT * FROM common_traits 
    WHERE habitat = \"".$_REQUEST['habitat']."\" 
    AND humidity = \"".$_REQUEST['humidity']."\"
    AND speed = \"".$_REQUEST['speed']."\"
    AND size = \"".$_REQUEST['size']."\"
    AND location = \"".$_REQUEST['location']."\",
    AND aggression = \"".$_REQUEST['aggression']."\"");
 
// loop through the results with mysql_fetch_array()
if ( @mysql_num_rows($row) > 0 ) {
    while($row = mysql_fetch_array($result)){
      echo "
        <tr>
                <td>".$row['habitat']."</td>
                <td>".$row['humidity']."</td>
                <td>".$row['speed']."</td>
                <td>".$row['size']."</td>
                <td>".$row['location']."</td>
                <td>".$row['aggression']."</td>
            </tr>
      ";
    }
 
} else {
 
    echo "<tr><td colspan = 6></td></tr>";
 
}
 
// don't forget to close the mysql connection
mysql_close();
?>   
</table>
 
<a href="http://www.tarantuladatabase.com/assets/pages/search.html"><input name="Back" type="button" value="Back" /></a></div>
  </div>
Please correct me if i'm wrong but i think my next step in the project is to create a form that will submit data to my database so that I have something to search?

I need to add all of the database field values for genus there are a lot of them. I think about 113 give or take

in this pic I have 4 of them added already.

Image

I know there is a way to past them into my sql and submit them all at one time but I don't know the format. What I would like to do is create the file in a .txt file and then copy and past it to the database once its all compiled.

Is there any way you can give me any example of the code I would need to submit that to the database?

I have also added a species field that will be typed in by my users when they submit a new species into the database. but before I start doing all this I just wanted to check and make sure it was necessary.

Just to explain how the submission form will work you select a genus from the drop down then type in the species name in the text box. Click one of each of the bullet points and click the tarantula submission button.

This is the submission form

I want that to create a new tarantula in my database under the selected genus. Is this adding up?

Thanks so much for all the help and advice.
noctorum
Forum Commoner
Posts: 31
Joined: Fri Jun 13, 2008 10:46 am

Re: How to add more than one value to a mysql database field

Post by noctorum »

I don't have too much time but I thought I'd chime in.

The form entry to build the database is pretty easy, take the variables from the form that are passed to your input page (can be the same page if you want) and insert them into the MySQL query, more info here;

http://w3schools.com/sql/sql_insert.asp

So if you have $genus, $species and $habitat from your form, something like this for the query..

Code: Select all

 
INSERT INTO common_traits (genus, species, habitat)
VALUES ($genus, $species, $habitat)
 
Now if you already have a large collection of data and just want to move that into the DB quickly, yes, you can build an SQL query for it, but thats the long way. If you have it in excel for example, save the file as CSV (comma seperated values) and then import that through PhpMyAdmin.
Snot
Forum Newbie
Posts: 12
Joined: Wed Jun 11, 2008 2:33 pm

Re: How to add more than one value to a mysql database field

Post by Snot »

I am going to try this to upload all the genus?

Code: Select all

<body>
<?php
include("connect.php");
$genus = array (
'Aphonopelma',
'Augacephalus',
'Avicularia',
'Batesiella',
'Bonnetina',
'Brachionopus',
'Brachypelma',
'Cardiopelma',
'Catumiri',
'Cerato*****',
'Chaetopelma',
'Chilobrachys',
'Chromatopelma',
'Citharacanthus',
'Citharischius',
'Citharognathus',
'Clavopelma',
'Coremiocnemis',
'Crassicrus',
'Cratorrhagus',
'Cyclosternum',
'Cyriocosmus',
'Cyriopagopus',
'Cyrtopholis',
'Davus',
'Encyocratella',
'Encyocrates',
'Ephebopus',
'Euathlus',
'Eucratoscelus',
'Eumenophorus',
'Eupalaestrus',
'Euphrictus',
'Grammostola',
'Hapalopus',
'Hapalotremus',
'Haploclastus',
'Haplocosmia',
'Haplopelma',
'Harpactira',
'Harpactirella',
'Hemiercus',
'Hemirrhagus',
'Heteroscodra',
'Heterothele',
'Holothele',
'Homoeomma',
'Hysterocrates',
'Idiothele',
'Iracema',
'Iridopelma',
'Ischnocolus',
'Lampropelma',
'Lasiodora',
'Lasiodorides',
'Loxomphalia',
'Loxoptygus',
'Lyrognathus',
'Mascaraneus',
'Megaphobema',
'Melloina',
'Melloleitaoina',
'Metriopelma',
'Monocentropus',
'Myostola',
'Neostenotarsus',
'Nesiergus',
'Nesipelma',
'Nhandu',
'Oligoxystre',
'Ornithoctonus',
'Orphnaecus',
'Ozopactus',
'Pachistopelma',
'Pamphobeteus',
'Paraphysa',
'Phlogiellus',
'Phoneyusa',
'Phormictopus',
'Phormingochilus',
'Plesiopelma',
'Plesiophrictus',
'Poecilotheria',
'Proshapalopus',
'Psalmopoeus',
'Pseudhapalopus',
'Pseudoligoxystre',
'Pterinochilus',
'Reversopelma',
'Schismatothele',
'Schizopelma',
'Selenobrachys',
'Selenocosmia',
'Seleno*****',
'Selenotholus',
'Selenotypus',
'Sericopelma',
'Sickius',
'Sphaerobothria',
'Stichoplastoris',
'Stromatopelma',
'Tapinauchenius',
'Theraphosa',
'Thrigmopoeus',
'Thrixopelma',
'Tmesiphantes',
'Trichognathella',
'Vitalius',
'Xenesthis',
'Yamia',
);
 
// $sql = "INSERT INTO tablename (genus) VALUES ('".$genus[$i]."')";
 
 
//then make you a while loop that will loop through your Insert into query code 110 times -- Edit lol?
 
/*
while($i<=110)
{
mysql_query($sql,$con);
$i++;
}*/
 
for ($x=0;$x<count($genus);$x++) {
  $sql = "INSERT INTO common_traits(genus) VALUES ('".$genus[$x]."')";
  echo "$sql <br>";  
  //mysql_query($sql);  // Uncomment when your ready for the real deal
}
?> 
</body>
 
I'll need to do some more reseach before I figure out how to work the form though
noctorum
Forum Commoner
Posts: 31
Joined: Fri Jun 13, 2008 10:46 am

Re: How to add more than one value to a mysql database field

Post by noctorum »

Like I said, if you already have the geni assembled in CSV, just use PhpMyAdmin's import function.
Snot
Forum Newbie
Posts: 12
Joined: Wed Jun 11, 2008 2:33 pm

Re: How to add more than one value to a mysql database field

Post by Snot »

I don't know what a csv is :oops:

I'll try to figer it out if this doesnt work
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: How to add more than one value to a mysql database field

Post by califdon »

Snot wrote:I don't know what a csv is :oops:

I'll try to figer it out if this doesnt work
csv = Comma Separated Values

This is a common format for text information, and is indeed the basis for spreadsheet files. It just means that fields are separated with commas, and rows are ended with newline characters or whatever else you might specify:

Code: Select all

1342,Jones,Mary,312-446-1002
1343,Smith,Joe,922-311-0688
1344,Berger,Ed,773-209-1234
That would be 3 records, each with 4 fields (an id, lastname, firstname, phone).

If you are using phpMyAdmin database interface, you can import such files fairly easily. If you are not, you can use MySQL's LOAD DATA LOCAL INFILE ... command.
Post Reply