pulling from database

pheidole · November 4, 2018

Im trying to find some examples on how to pull results from a mysql database. I have different ants species stored in the database for each state. I would like to fetch all the ants found in the database from the selected state and echo them to the page...

here is the html

<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" type="text/css" href="view.css" media="all">
<script type="text/javascript" src="view.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<title></title>
<style>
.hover_bkgr_fricc{
    background:rgba(0,0,0,.4);
    cursor:pointer;
    display:none;
    height:100%;
    position:fixed;
    text-align:center;
    top:0;
    width:100%;
    z-index:10000;
}
.hover_bkgr_fricc .helper{
    display:inline-block;
    height:100%;
    vertical-align:middle;
}
.hover_bkgr_fricc > div {
    background-color: #fff;
    box-shadow: 10px 10px 60px #555;
    display: inline-block;
    height: auto;
    max-width: 551px;
    min-height: 100px;
    vertical-align: middle;
    width: 60%;
    position: relative;
    border-radius: 8px;
    padding: 15px 5%;
}
.popupCloseButton {
    background-color: #fff;
    border: 3px solid #999;
    border-radius: 50px;
    cursor: pointer;
    display: inline-block;
    font-family: arial;
    font-weight: bold;
    position: absolute;
    top: -20px;
    right: -20px;
    font-size: 25px;
    line-height: 30px;
    width: 30px;
    height: 30px;
    text-align: center;
}
.popupCloseButton:hover {
    background-color: #ccc;
}
.trigger_popup_fricc {
    cursor: pointer;
    font-size: 20px;
    margin: 20px;
    display: inline-block;
    font-weight: bold;
}


</style>
</head>
<body>

<script>
$(document).ready(function() {
    $(".trigger_popup_fricc").click(function(){
       $('.hover_bkgr_fricc').show();
    });
    $('.hover_bkgr_fricc').click(function(){
        $('.hover_bkgr_fricc').hide();
    });
    $('.popupCloseButton').click(function(){
        $('.hover_bkgr_fricc').hide();
    });
});

</script>
<a class="trigger_popup_fricc">popup</a>

<div class="hover_bkgr_fricc">
    <span class="helper"></span>
    <div>
        <div class="popupCloseButton">X</div>
        <img id="top" src="top.png" alt="">
    <div id="form_container">
    
        <h1><a>Queen Ant Databank</a></h1>
        <form id="form_34089" class="appnitro"  method="post" action="">
                    <div class="form_description">
            <h2>Queen Search</h2>
            <p>Make Sure Sure All Your Data Is Correct!</p>
        </div>                        
            <ul >
            
                    <li id="li_1" >
        <label class="description" for="element_1">Size of Queen </label>
        <span>
            <input id="element_1_1" name="element_1" class="element radio" type="radio" value="1" />
<label class="choice" for="element_1_1">1-2 mm</label>
<input id="element_1_2" name="element_1" class="element radio" type="radio" value="2" />
<label class="choice" for="element_1_2">2-4 mm</label>
<input id="element_1_3" name="element_1" class="element radio" type="radio" value="3" />
<label class="choice" for="element_1_3">4-6 mm</label>
<input id="element_1_4" name="element_1" class="element radio" type="radio" value="4" />
<label class="choice" for="element_1_4">6-8 mm</label>
<input id="element_1_5" name="element_1" class="element radio" type="radio" value="5" />
<label class="choice" for="element_1_5">8 + mm</label>

        </span>
        </li>        <li id="li_2" >
        <label class="description" for="element_2">State Found </label>
        <div>
        <select class="element select medium" id="element_2" name="element_2">
            <option value="1" selected="selected">All States</option>
<option value="2" >Alaska</option>
<option value="3" >Alabama</option>
<option value="4" >Arizona</option>
<option value="5" >Arkansas</option>
<option value="6" >California</option>
<option value="7" >Colorado</option>
<option value="8" >Connecticut</option>
<option value="9" >Delaware</option>
<option value="10" >Florida</option>
<option value="11" >Georgia</option>
<option value="12" >Hawaii</option>
<option value="13" >Idaho</option>
<option value="14" >Illinois</option>
<option value="15" >Indiana</option>
<option value="16" >Iowa</option>
<option value="17" >Kansas</option>
<option value="18" >Kentucky</option>
<option value="19" >Louisiana</option>
<option value="20" >Maine</option>
<option value="21" >Maryland</option>
<option value="22" >Massachusetts</option>
<option value="23" >Michigan</option>
<option value="24" >Minnesota</option>
<option value="25" >Mississippi</option>
<option value="26" >Missouri</option>
<option value="27" >Montana</option>
<option value="28" >Nebraska</option>
<option value="29" >Nevada</option>
<option value="30" >New Hampshire</option>
<option value="31" >New Jersey</option>
<option value="32" >New Mexico</option>
<option value="33" >New York</option>
<option value="34" >North Carolina</option>
<option value="35" >North Dakota</option>
<option value="36" >Ohio</option>
<option value="37" >Oklahoma</option>
<option value="38" >Oregon</option>
<option value="39" >Pennsylvania</option>
<option value="40" >Rhode Island</option>
<option value="41" >South Carolina</option>
<option value="42" >South Dakota</option>
<option value="43" >Tennessee</option>
<option value="44" >Texas</option>
<option value="45" >Utah</option>
<option value="46" >Vermont</option>
<option value="47" >Virginia</option>
<option value="48" >Washington</option>
<option value="49" >West Virginia</option>
<option value="50" >Wisconsin</option>
<option value="51" >Wyoming</option>

        </select>
        </div>
        </li>
            
                    <li class="buttons">
                <input type="hidden" name="form_id" value="34089" />
                
                <input id="saveForm" class="button_text" type="submit" name="submit" value="Submit" />
        </li>
            </ul>
        </form>    
        
    </div>
    <img id="bottom" src="bottom.png" alt="" >
    </div>
</div>

</body>
</html>

maxxd · November 4, 2018

In all the unformatted code you just posted (use the "<>" button next time), there's no PHP.

Barand · November 4, 2018

Plus we can give you more help with database queries if we know the structure of your tables.

Barand · November 4, 2018

So you have ants and locations. Let us assume that a species of ant can exist in more than one location, and a location can contain more then one species of ant. This means you have a many-to-many relationship and therefor requires a third table to assign ants to location.

Also note that names, like location names, should occur only once (in their respective table. Links between records are maintained using the keys (ids).

So we now have (note my extensive knowledge of ant species)

 TABLE : ant                                                    TABLE : loc
+--------+------------+                                         +--------+----------+                        
| ant_id | species    |                                         | loc_id | state    |                        
+--------+------------+                                         +--------+----------+                        
|      1 | Big ant    |                                         |      1 | Alabama  |                        
|      2 | Little ant |                                         |      2 | Delaware |                        
|      3 | Giant ant  |                                         |      3 | Kentucky |                        
|      4 | Red ant    |                                         |      4 | Missouri |                        
|      5 | Black ant  |                                         |      5 | Hawaii   |                        
+--------+------------+                                         +--------+----------+                        
     |                                                               |
     +----------------------------------+        +-------------------+
                                        |        |
             TABLE: ant_location   +--------+--------+
                                   | ant_id | loc_id |
                                   +--------+--------+
                                   |      1 |      1 |
                                   |      2 |      4 |
                                   |      2 |      5 |
                                   |      3 |      2 |
                                   |      3 |      3 |
                                   |      4 |      3 |
                                   |      5 |      1 |
                                   |      5 |      4 |
                                   +--------+--------+

To get the species in a particular location

SELECT l.state
     , a.species
FROM loc l 
        INNER JOIN
     ant_location al USING (loc_id)
        INNER JOIN 
     ant a  USING (ant_id)
WHERE l.state = 'Missouri'
ORDER BY species;

+----------+------------+
| state    | species    |
+----------+------------+
| Missouri | Black ant  |
| Missouri | Little ant |
+----------+------------+

Sign In

pulling from database

Recommended Posts

pheidole

Link to comment

Share on other sites

maxxd

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information