John Davidson

I am working on a project in my PHP course where I have to create a database using SQLite for museums and countries they reside in, connect to that database from a webpage (using PHP/HTML) and generate a list of museums based on country chosen from a drop down list. The issue I am having is that I don't seem to be able to return data from country values with multiple words (United States, United Kingdom, etc.). It is working perfectly for single word countries. There is a pre-selection list generated that has the correct information, but the selection list is not generating correctly.

Here is a database sample (SQLite):

CREATE TABLE museums (

    museum_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

    museum_name TEXT NOT NULL,

    country TEXT NOT NULL

);



INSERT INTO museums (museum_name, country) VALUES



('Louvre'  ,  'France'),

('State Hermitage Museum'  ,  'Russia'),

('National Museum of China'  ,  'China'),

('Metropolitan Museum of Art'  ,  'United States'),

('Vatican Museums'  ,  'Vatican City'),

('Tokyo National Museum'  ,  'Japan'),

('National Museum of Anthropology'  ,  'Mexico'),

('Victoria and Albert Museum'  ,  'United Kingdom'),

('National Museum of Korea'  ,  'South Korea'),

('Art Institute of Chicago'  ,  'United States');

Here is the PHP/HTML Code:

<?php



    $museumDb = new PDO('sqlite:museum.sqlite');

    $query = $museumDb->prepare('SELECT * FROM museums');

    $query->execute();

    $museums = $query->fetchAll(PDO::FETCH_ASSOC);

    

    $country = $museumDb->prepare('SELECT DISTINCT country FROM museums ORDER BY country ASC');

    $country->execute();

    $countryLists = $country->fetchAll(PDO::FETCH_ASSOC);



?>



 <form method="POST"> //selection form

   <fieldset>

      <legend><strong>Select from the drop down list below to narrow your search results by country</strong></legend>



      

        <select name="country" id="country">



           <?php foreach($countryLists as $countryList): ?>



           <!--Generates drop down list of countries from DB-issue may be here?-->

           <option value=<?php print $countryList['country']; ?>><?php print $countryList['country']; ?></option>     

           <?php endforeach; ?>

    

        </select>

       

            <input type="submit" name="submit" value="Submit">

            

        </fieldset>

    </form>

    



<?php if (isset($_POST) && !empty($_POST)) {

        $countryName = htmlspecialchars($_POST['country']); //retrieves POST data from form selection

        print $countryName; //testing



        $new = $museumDb->prepare("SELECT country, museum_name FROM museums WHERE country = '$countryName'"); //sqlite query setting country = selected country from form

        $new->execute();

        $newLists = $new->fetchAll(PDO::FETCH_ASSOC);





        print '<pre><code>';

        while ($row = $new->fetch(PDO::FETCH_ASSOC)) {//testing

            var_dump($row);

        }

        



        var_dump($newLists); //testing query

        var_dump($new); //testing

        print '</code></pre>';

        ?>





<table><!--table not generating for countries with two words in name-->

     <tr>

          <th>Museum Name</th>

          <th>Country</th>

     </tr>

    

     <?php foreach ($newLists as $newList): ?>

            

          <tr>

              <td><?php print $newList['museum_name']; ?></td>

              <td><?php print $newList['country']; ?></td>

          </tr>

            

      <?php endforeach; ?>



</table>

';

        while ($row = $new->fetch(PDO::FETCH_ASSOC)) {//testing

            var_dump($row);

        }

        



        var_dump($newLists); //testing query

        var_dump($new); //testing

        print '