Populate HTML dropdown list using value from mysql

[twiggy145]

I'm trying to create a dropdown list in a HTML form populated by results for a mysql query. However it is not loading and i am just given a white screen.

$query = mysql_query("SELECT eventName from sgt")or die(mysql_error());
            while($row = mysql_fetch_array($query)) 
            {
                echo "<option value=\"$row['eventName']\">" $row['eventName']"</option>";
            }

Any help would be much appreciated.

[Tpojka]

Wrap your while loop with <select></select> tags.

[twiggy145]

It is already sorry didn't put that part of the code in

echo'<select name="events">';
            $query = mysql_query("SELECT eventName from sgt")or die(mysql_error());
            while($row = mysql_fetch_array($query)) 
            {
                echo "<option value=\"$row['eventName']\">" $row['eventName']"</option>";
            }
            echo'</select><br><br>';

[phorce]

First off, right click. Is the HTML code being populated with "eventName" if this is a no, then you should attempt to look at the querying of the database (I'm guessing that you have mysqladmin or w.e.) try and run this query within that client and see what results show up.

[twiggy145]

The query is working fine. just checked in phpmyadmin. i can't check if they're being loaded because i am greeted by a white screen. There is a problem with the while loop because if i comment out the entire loop the page loads

[phorce]

Then your problem is this line:

echo "<option value=\"$row['eventName']\">" $row['eventName']"</option>";

Should be:

  echo '<option value=' . $row['eventName'] . '>' . $row['eventName'] . '</option>';

If your going to code in PHP, please turn on the error handling in your .ini file, or set it. Then you'd actually be able to see these erros been given.

Peace

[twiggy145]

The page now loads without anything in the dropdown menu. I cannot turn that option on because i'm using a server run by my university. However the cause of the issue is the apache server denying access. Even though my access details are correct as other items on the page are loaded using php.

Message from inspect element in Google Chrome

<select name="events">Access denied for user 'apache'@'localhost' (using password: NO)</select>

[phorce]

Are your connection details right? You should be able to access the database. If you're trying to run this script from your own server (i.e. on your machine) and connecting to the Universties then no, this would be a problem. Looking at your previous script though (in your other question) This was not the issue. So surely, if you can UPDATE then you can SELECT mhm. Strange. Post the full script, removing any passwords associated to your account.

Peace

[twiggy145]

All connection information is stored in another file which all my pages on this site use to connected to the database so i know these detail are correct. However here is the code for the entire page

<?php
require('includes/conn.inc.php');
require("common.php");
$username = $_SESSION['user']['username'];

if(!empty($_POST))
{

    //ensure the user has entered an non empty team name
    if(empty($_POST['teamName']))
    {
        die("Please enter a teamName");
    }
    if(empty($_POST['events']))
    {
        die("Please enter a valid event");
    }

    $query = "SELECT 1 FROM teams  WHERE teamName = :teamName";
    $query_params = array(':teamName' => $_POST['teamName']);

    try
    {
    $stmt = $db->prepare($query);
    $result = $stmt->execute($query_params);
    }
    catch(PDOException $ex)
    {
    die("Failed to run query1: " . $ex->getMessage());
    }

    $row = $stmt->fetch();
    //if a row is returned we know there is a matching team name in the database.
    if($row)
    {
        die("This team name is already in use try again");
    }

    $query = "SELECT 1 FROM teams WHERE events = :events";
    $query_params = array(':events' => $_POST['event']);
    try
    {
    $stmt = $db->prepare($query);
    $result = $stmt->execute($query_params);
    }
    catch(PDOException $ex)
    {
    die("Failed to run query2: " . $ex->getMessage());
    }

    $query = "SELECT 1 FROM teams WHERE teamImage = :teamImage";
    $query_params = array(':teamImage' => $_POST['teamImage']);
    try
    {
    $stmt = $db->prepare($query);
    $result = $stmt->execute($query_params);
    }
    catch(PDOException $ex)
    {
    die("Failed to run query3: " . $ex->getMessage());
    }

    $query = 
    "INSERT INTO teams(teamName, username, events, teamImage) 
    VALUES (:teamName, :username, :events, :teamImage) ";

    $query_params = array(
    ':teamName' => $_POST['teamName'],
    ':username' => $username,
    ':events' => $_POST['events'],
    ':teamImage' => $_POST['teamImage']
    );

    try
    {
    $stmt = $db->prepare($query);
    $result = $stmt->execute($query_params);
    }
    catch(PDOException $ex) 
    {
    die("Failed to run query4: " . $ex->getMessage());
    }
}
?>
<head>
    <title>Sheffield Gaming Tournaments</title>
    <link href="styles/style.css" type="text/css" rel="stylesheet">
</head>
<body>
    <div id="container">
        <header>
<img src="images/sgtcover.png" width=950px height=250px alt="Sheffield Gaming Tounements">
            <br>
            <menu>
                <ul id="nav">
                    <li><a href="Index.php">Home</a></li>
                    <li><a href="Teams.php">Teams</a></li>
                    <li class="current"><a href="getinvolved.php">Get Involved</a></li>
                    <?php
                    if(empty($_SESSION['user']))
                    {
                    echo '<li><a href="login.php">Login</li></a>';
                    }
                    else
                    {
                    echo "</a>";
                    echo "</li>";
                    echo '<li><a href="myteams.php">My Teams</a></li>';
                    echo '<li class="loggedin"><a href="private.php"> Logged in as ';
                    echo htmlentities($_SESSION['user']['username'], ENT_QUOTES, 'UTF-8');
                    echo '<li><a href="logout.php">Logout</a></li>';
                    }
                    ?>
                </ul>
            </menu>
        </header>
        <div id="page">
            <div id="content">
            <?php
            if(empty($_SESSION['user']))
            {
            echo ' On this page you can register a team. In order to register a team you must be signed in';
            echo '<p><a href="login.php">Login</a> or <a href="register.php">Create an account</a></p>';
            }
            else
            {
            echo'<p><h2><u>Sign up for an event</u></h2></p>';
            echo'<form action="getinvolved.php" method="post">';
            echo'Teamname:<br>';
            echo'<input type="text" name="teamName" value=""><br><br>';
            echo'Event:<br>';
            echo'<select name="events">';
            $query = mysql_query("SELECT eventName from sgt")or die(mysql_error());
            while($row = mysql_fetch_array($query)) 
            {
                echo '<option value=' . $row['eventName'] . '>' . $row['eventName'] . '</option>';
            }
            echo'</select><br><br>';
            echo'Team Image Link (Please use external image host. Leave empty for default.)<br>';
            echo'<input type="text" name="teamImage" value="images/default.gif"><br><br>';
            echo'<input type="submit" value="Submit">';
            echo '</form>';
            }
            ?>
            </div>
        </div>
            <footer>
                &copy Copyright 2014
            </footer>
    </div>

</body>
</html>

[twiggy145]

Also all files are stored on the univerities web server hence localhost

[phorce]

I don't get what's going on here, dude. First off Sheffield, really? ;)

Anyway.. Back on topic: It looks like you're mixing up PDO with general mysql_* first off, mysql_* is being deprciated, hence the use of PDO

For example, you use PDO and then suddenly reach this:

$query = mysql_query("SELECT eventName from sgt")or die(mysql_error());
            while($row = mysql_fetch_array($query)) 
            {
                echo '<option value=' . $row['eventName'] . '>' . $row['eventName'] . '</option>';
            }

But surely, it would follow suite from everything else. The coding looks OK now. We sorted out the error that you are having, I personally think the fact it's not allowing you to connect to the database (on this piece of code) is because you're using PDO in the script and it does not know now which connection this referes to.

I personally think you've chosen this, as you've either ripped it off a book or the internet because you are unsure of how to display rows PDO style. I would therefore suggest trying:

$sql = 'SELECT eventName from sgt'; 
foreach($db->query($sql) as $row) {

    print $row['eventName'] . "\n";

}

Does this print the EventName? If so, then there is your problem!

Peace </3

[twiggy145]

That worked a treat. Thank you so much. I was starting to stree out

[phorce]

No problem, mark this thread as solved and give rep!! Good luck with this project!

Also, learn the difference between require and require_once

And what is the use of this: $username = $_SESSION['user']['username'];?

Peace

[twiggy145]

Yes that works thank you. However when it goes to store the information on the database it only stores the first word. for example i selected "Park Student Village" and it inserted park into the database. how can i fix this?

[phorce]

Well.. When you print eventName what is being displayed? I don't get what you mean..

[twiggy145]

In the dropdown menu the events are shown as their full names with spaces. However when this gets posted to the database only the text upto the first space is put into the database

Example

Park Student Village

becomes
Park

[phorce]

Okay! Try this:

echo "<option value=\"" . $row['eventName'] . "\">" . $row['eventName'] . "</option>"; 

If this doesn't work, right click on the page -> view source and post what the HTML form is saying in the <option value=>

[twiggy145]

They all display correctly and show correctly in the HTML

<select name="events"><option value="Park Student Village">Park Student Village</option><option value="The Pinnacles">The Pinnacles</option><option value="The Graduate">The Graduate</option><option value="The Forge">The Forge</option><option value="The Common Room">The Common Room</option></select>

[phorce]

Urgh,

I'm guessing on your submit page, you have something like this:

$eve = $_POST['events']; If so, can you just var_dump($eve) where $eve is the variable name. I think this is a PDO issue (I don't code in PHP no more, haven't really taken PDO on) but I believe that it's because when you're inserting, it only inserts the first word.

If the var_dump shows corretly, post the INSERT sql

[twiggy145]

It wasn't like that but i have now changed it to

$event = $_POST['events'];

and changed the query_params accordingly. now it's working

Thanks :D