Getting the data from a drop down box

[Ugluth]

Hello there, i have a pretty simple problem, but i cant seem to get over it hence this post. I have a very simple form with 2 text boxes and a dropdown box. The user is supposed to insert an actor to the database using that form, but the thing is that i can't get the value from my dropdown box for some reason.

Anyway here's the form.

 

<div id="content">
<?php
if (isset($_POST["actorID"]) && isset($_POST["actorName"]) && isset($_POST["nation"])) {
    if ($_POST["actorID"] != "" && $_POST["actorName"] != "") {
        $link = mysql_connect('localhost', 'Ugluth', 'dracul');
        if (!$link) {
            die('Could not connect: ' . mysql_error());
        }
        mysql_select_db("videoclub", $link);
        $sql="INSERT INTO actors (Actor_ID, Actor_Name, Actor_Nation) VALUES ('$_POST[actorID]','$_POST[actorName]','$_POST[nation]')";
        if (!mysql_query($sql,$link))  {
          die('Error: ' . mysql_error());
        } else {
            echo "1 record added";
            mysql_close($link);
        }
    } else {
        echo "All fields need to be completed to add the record.";
    }
}
$link = mysql_connect('localhost', 'turu', 'tururu');
        if (!$link) {
            die('Could not connect: ' . mysql_error());
        }
mysql_select_db("videoclub", $link);
$result_nation = mysql_query("SELECT * FROM nationalities");
$row = mysql_fetch_array($result_nation);
mysql_close($link);
?>
<form action="actor_add.php" method="post">
<table>
    <tr>
        <td>
            Actor ID:
        </td>
        <td>
            <input type="text" name="actorID">
        </td>
    </tr>
    <tr>
        <td>
            Actor Name:
        </td>
        <td>
            <input type="text" name="actorName">
        </td>
    </tr>
    <tr>
        <td>
            Actor Nationality ID:
            <?php  ?>
        </td>
        <td>
            <select name="nation">
                <?php 
                while($row = mysql_fetch_array($result_nation)) { ?>
                  <option value="<?php $row[0]; ?>"><?php echo $row[1]; ?></option>
              <?php
              }
            ?>
            </select>
            </td>
    </tr>
</table>
<?php     
            mysql_free_result($result_nation);
?>
<input type="submit" value="Submit" />
</form>

</div> <!-- end #content -->

 

I'm trying to enter the value to my query using '$_POST[nation]' on the $sql string i use for query. I have also tried just displaying it on the screen to see what value it holds, but it just wont show anything. Also i don't really need this answered for this particular problem, but in general its good to know, how can i select the value from the text of each option on the drop down box? The $row[0] holds the id while $row[1] holds the name.

 

Thank you in advance and if u need any more information just let me know

[DavidAM]

I see a couple of issues here.

 

1) If those are your actual database login and password, you should change them NOW since you have just posted them on the internet.

 

2) There is no real need to connect to the database twice; although it appears you are using different credintials.  If you are using the same login and password, I personally would connect before the first IF statement and just use the same connection for all database access. If you are using different credintials, then the code is fine.

 

3) This statement needs to be revised:

$sql="INSERT INTO actors (Actor_ID, Actor_Name, Actor_Nation) 
VALUES ('$_POST[actorID]','$_POST[actorName]','$_POST[nation]')";

when you use an array element inside of double-quotes, you need to use curley braces:

$sql="INSERT INTO actors (Actor_ID, Actor_Name, Actor_Nation) 
VALUES ('{$_POST['actorID']}','{$_POST['actorName']}','{$_POST['nation']}')";

also, you need to put the element name ( actorID ) in quotes just as you do when referencing the element anywhere else in the code.

 

NOTE: You absolutely need to sanitize any input coming from the user that is going to the database. Use mysql_real_escape_string() to protect against SQL injections. I would, personally, rewrite the INSERT statement as:

$sql= sprintf("INSERT INTO actors (Actor_ID, Actor_Name, Actor_Nation) VALUES ('%s', '%s', '%s')", 
mysql_real_escape_string($_POST['actorID']),
mysql_real_escape_string($_POST['actorName']),
mysql_real_escape_string($_POST['nation']));

 

4) This code is problematic (see comments):

$result_nation = mysql_query("SELECT * FROM nationalities");
$row = mysql_fetch_array($result_nation); // RETRIEVING THE FIRST ROW
mysql_close($link); // CLOSING THE CONNECTION
?>

a) You are retrieving the first row from the result, but you are never using it. You have a WHILE loop later that starts with retrieving the next row -- REMOVE this line entirely

b) You are closing the connection to the database, but you have a while loop later trying to retrieve the data, I don't think that will work. -- MOVE this line below the free_result() later in the code

 

5) This code is not outputting anything for the VALUE of the OPTION tag. I suspect the WHILE is failing because you closed the database connection earlier. But if it is not, you are not echoing the value, so nothing is being put in the tag. The way I see it, with the code you have, there should be NO OPTIONS in the SELECT to choose from.

            <select name="nation">
                <?php 
                while($row = mysql_fetch_array($result_nation)) { ?>
                  <option value="<?php $row[0]; ?>"><?php echo $row[1]; ?></option>
              <?php
              }
            ?>
            </select>

change it to

            <select name="nation">
                <?php 
                while($row = mysql_fetch_array($result_nation)) { ?>
                  <option value="<?php echo $row[0]; ?>"><?php echo $row[1]; ?></option>
              <?php
              }
            ?>
            </select>

 

The code you have (with these corrections) should POST the value from the selected OPTION in $_POST['nation'].  This is the value from the first column in your SELECT statement, it is NOT the value displayed as the text of the OPTION.

[Ugluth]

First of all thank you for your reply, and I'm rather stupid for posting the db connect info and thank you for pointing out. Actually another problem came up to me rather irrelevant with the drop down box. I just tried to open phpmyadmin and for some reason it tells me:

#1045 - Access denied for user 'root'@'localhost' (using password: NO)

 

I can open the console it asks for password and i supply it and its fine. But how can i make phpmyadmin get that password too so i don't get that error?

 

About the dropdown box i see my mistake now, i was recently studying some vb.net and got a bit confused with the datasets, for some reason thought php worked that way too, but thank you very much.

 

If you could help me with getting phpmyadmin to work (and change the password as well) i would be really grateful.

Thanks in advance!

[Ugluth]

Found the problem with phpmyadmin got it to work after all. I've tried what you suggested, even though now my drop down doesn't even get data. Here's the code i used:

<?php
include 'includes/dbconn.php';
if (isset($_POST['actorID']) && isset($_POST['actorName']) && isset($_POST['nation'])) {
    if ($_POST['actorID'] != "" && $_POST['actorName'] != "") {
        $sql= sprintf("INSERT INTO actors (Actor_ID, Actor_Name, Actor_Nation) VALUES ('%s', '%s', '%s')", 
                    mysql_real_escape_string($_POST['actorID']),
                    mysql_real_escape_string($_POST['actorName']),
                    mysql_real_escape_string($_POST['nation']));
        dbconnect();
        if (!mysql_query($sql, $link))  {
          die('Error: ' . mysql_error());
        } else {
            echo "1 record added";
            mysql_close($link);
        }
    } else {
        echo "All fields need to be completed to add the record.";
    }
}
?>
<form action="actor_add.php" method="post">
<table>
    <tr>
        <td>
            Actor ID:
        </td>
        <td>
            <input type="text" name="actorID">
        </td>
    </tr>
    <tr>
        <td>
            Actor Name:
        </td>
        <td>
            <input type="text" name="actorName">
        </td>
    </tr>
    <tr>
        <td>
            Actor Nationality ID:
      </td>
        <td>
            <select name="nation">
                <?php 
                dbconnect();
                $result_nation = mysql_query("SELECT * FROM nationalities");
                while($row = mysql_fetch_array($result_nation)) { ?>
                  <option value="<?php echo $row[0]; ?>"><?php echo $row[1]; ?></option>
              <?php
              }
              mysql_close($link);
            ?>
            </select>
            </td>
    </tr>
</table>
<?php     
            mysql_free_result($result_nation);
?>
<input type="submit" value="Submit" />
</form>

 

I also made a function for connection with the database, gonna paste it here in case something is wrong with it.

 

<?php  
function dbconnect() {
  // Connect to the database
  $link = mysql_connect("localhost", "User-name", "Password")
  or die ("Failed to connect to the database");
  $db_select = mysql_select_db("videoclub", $db_connect)
  or die ("Failed to select the database");
}
?>  

 

What is wrong with my code and i don't get any data on my drop down?

[DavidAM]

First, you should turn on error reporting and display during development.  You should be getting a couple of errors with that code, and it might help determine the problem. At the beginning of the script add:

error_reporting(E_ALL);
ini_set('display_errors', 1);

 

Second, you are referring to $link outside of your database connection function, but it does not exist outside of the function.  I don't think that is the problem with the SELECT box, but it will be throwing errors.  Add a line to the end of the function to return the link:

return $link;

and then capture it when you call the function (see below)

 

I don't really see a problem with this code, which means the query is probably failing. Since the query is being executed inside of the SELECT tag, the browser may be hiding any error messages. After you add the error reporting (above), use the option in the browser to view the source of the page.  See if there are any error messages inside of the SELECT tag. You might also test the result of the query execution:

            <select name="nation">
                <?php 
                $link = dbconnect(); // CAPTURE THE DATABASE CONNECTION
                $result_nation = mysql_query("SELECT * FROM nationalities");
                // CHECK TO SEE IF THE QUERY SUCCEEDED
                // THIS IS NOT THE BEST WAY TO REPORT AN ERROR, BUT FOR TESTING IT WILL DO
                if (! $result_nation) {
                   echo 'Query Failed: ' . mysql_error();
                }
                while($row = mysql_fetch_array($result_nation)) { ?>
                  <option value="<?php echo $row[0]; ?>"><?php echo $row[1]; ?></option>
              <?php
              }
              mysql_close($link);
            ?>
            </select>