Dynamically loading a listbox from MySql using PHP

Hi folks,

 I have the need to load a listbox dynamically with a table from MySQL using PHP.
I have verified that the select statement works correctly.
When I execute the page, the only option value that shows is "Choose One".
The includes have the db info etc and I know the includes are good since they fetch my data on other pages.
Can anyone point out my errors in my coding? I have posted the coding from page below. The two column names are RecId and Country. Recid is a integer and a primary key - could this be the problem? Country is alpha.
Thank you so much.
<?php
    require_once ("Includes/simplecms-config.php");
    require_once ("Includes/connectDB.php");
    include("Includes/session.php");
 
?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 <html>
   
   <head>
     <title>Country Listings</title>
     <link href="Styles/StyleSheet.css" rel="stylesheet" type="text/css" />
    </head>
     
    <body class="bodybg">


        <table align="center">
           <tr><td>Select any of the following tags for searching later:</td></tr>
            <tr><td align="right">Country:</td>
                             <td align="left">
                                <select name="country">
                                    <option value="0">Choose One</option>
                      <?
                         $query = "SELECT * FROM countries";
                         $result = $databaseConnection->query($query);
                         while($row = $result->fetch_assoc())
                              {
                            ?>
                                    <option value="<?=$row["RecId"];?>"><?=$row["Country"];?></option>

                             <?  }  ?>
                               </select>
                                 <? mysqli_close($databaseConnection);?>
           
                                     </select>
            </td></tr>
         </table>

    </body>
</html>
OverthereAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
I find nothing wrong with your logic.  I had to modify your code to use 'long opening tags' since my install of PHP does not support short open tags.  But this is what I have and it works perfectly.  You can put your own details back in there and it should work for you.
        <table align="center">
           <tr><td>Select any of the following tags for searching later:</td></tr>
            <tr><td align="right">Country:</td>
                             <td align="left">
                                <select name="country">
                                    <option value="0">Choose One</option>
                      <?php
                         $query = "SELECT * FROM websitelist LIMIT 10";
                         $result = $databaseConnection->query($query);
                         while($row = $result->fetch_assoc())
                              {
                            ?>
                                    <option value="<?php echo $row["ent_num"];?>"><?php echo $row["DisplayName"];?></option>

                             <?php  }  ?>
                               </select>
                                 <?php mysqli_close($databaseConnection);?>
           
                                     </select>
            </td></tr>
         </table>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
There are a couple of things that could be wrong here.  I want to focus on two statements that, in my experience, are often misunderstood.  Here they are:

$result = $databaseConnection->query($query);
while($row = $result->fetch_assoc())

When I see those two statements together in a script, it is a recognizable code smell.  Here are the things that get my attention.

1. What is the value of $result after calling the query() method on the $databaseConnection object?  As a general rule database queries return values of some sort - usually a resource or object if the query succeeds or FALSE if the query fails.  Your PHP script can and must test for the query failure because queries can and will fail for reasons that are not under your control.  When these failures occur, your script should raise a message of some sort (the database engine will not do this for you).  You can learn how to test for query success or failure in this article.
http://www.experts-exchange.com/articles/11177/PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

2. Without testing the value of $result you cannot know whether it's appropriate to use that value in the while() iterator.

3. Even if the query succeeded and $result contains something that can be used in the while() iterator, you still do not know whether the query found any data or how many rows it returned.  Most database extensions have something like a "num_rows()" method that you can test to see if there is any data in the results set.

Going forward, you may want to learn how to isolate these data-related issues and get a quick answer to the essential questions like, "Did my query work?" and "Did it find any data?"  Here is how you might do that.  Once you know the data access layer is working, then you can add in the HTML to make a nicely formatted view of the data.  In this example I am assuming that you're using MySQLi.  Obviously I cannot test it because I do not have your database, but it should be correct in principle.
<?php // demo/temp_overthere.php

/**
 * http://www.experts-exchange.com/questions/28693509/Dynamically-loading-a-listbox-from-MySql-using-PHP.html
 */
error_reporting(E_ALL);

require_once ("Includes/simplecms-config.php");
require_once ("Includes/connectDB.php");
include("Includes/session.php");

// CREATE THE QUERY STRING (MAYBE ADD "ORDER BY" OR "LIMIT" CLAUSES?)
$query = "SELECT * FROM countries";

// TRY TO RUN THE QUERY
$result = $databaseConnection->query($query);

// TEST FOR SUCCESS OR FAILURE
if (!$result)
{
    $err
    = "QUERY FAIL: "
    . $query
    . ' ERRNO: '
    . $databaseConnection->errno
    . ' ERROR: '
    . $databaseConnection->error
    ;
    trigger_error($err, E_USER_WARNING);
}

// ITERATE OVER THE RESULTS SET
while($row = $result->fetch_object())
{
    // DUMP EACH ROW SO YOU CAN INSPECT THE DATA
    var_dump($row);
}

Open in new window

HTH, ~Ray
0
OverthereAuthor Commented:
Thank you both for responding. I copied your coding David, substituting my values and all is well. I realized you wrote something that made me think - long tags. Thank you!
Ray - the snippet of coding and comments that  you provided was very good as it helped me to see that my data was good and I am keeping it for future use.
I did laugh at myself and at the phase "code smell" - which I never heard of before but I know the term now!
Thanks to both of you and I am splitting the points, the majority going to David for the actual resolution (long tags) and to Ray for your coding for verifying data and comments were helpful too. If this is not acceptable , please let me know. Again, my thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.