drop down and insert into mysql

[packets]

I'm a newbie on php. I'm really a system administrator and I was just task to do this simple task. For me its hard but I'm sure for a programmer this is very simple. My agenda is to pull out data on one of my column in mysql, select it and dump it on mysql. Here is the php for retrieving mysql data

 

<?php
function database_connect($users)
{
    $resource_link = mysql_connect("localhost", "root", "root");
    if (mysql_select_db($users, $resource_link)) {
        return $resource_link;
    } else {
        echo "Cannot connect to DB";
        return false;
    }
}

function print_dropdown($query, $link){
    $queried = mysql_query($query, $link);
    $menu = '<select username="username">';
    while ($result = mysql_fetch_array($queried)) {
        $menu .= '
      <option value="' . $result['id'] . '">' . $result['username'] . '</option>';
    }
    $menu .= '</select>';
    return $menu;
}

//Some other form elements, or just start a form.
echo '<form method="post" action="create2.php">';

//The important bit
echo print_dropdown("SELECT username FROM mailbox;", database_connect("users"));

//Some other form elements, or just end the form.
echo '<input type="submit" name="submit" value="submit"/></form>';

 

Here is the content of my create2.php. This is the php page who do the insert on my mysql.

 

<?php
// open the connection
$conn = mysql_connect("localhost", "root", "root");
// pick the database to use
mysql_select_db("users",$conn);
// create the SQL statement
$sql2 = "INSERT INTO mailbox values ('','locked','','$_POST[username]','',NOW(),'','locked','')";
// for troubleshooting
$result = mysql_query($sql2, $conn) or die(mysql_error());
// execute the SQL statement
//if (mysql_query($sql2, $conn)) {
//    echo "Success";
//} else {
//   echo "Fail";
//}
}
?>

 

When I click the submit button, I don't see any record being inserted on my table. I'm using the create2.php on my other page though it is only an insert/fill up form not like this one that I need to pull up the date, select and insert to mysql.

[Buddski]

You do not have a name attribute on your select box..

You have <select username="username">

If you change that to <select name="username"> you may have some success.

 

Also, when entering data into MySQL you should always escape it.

$sql2 = "INSERT INTO mailbox values ('','locked','','".mysql_real_escape_string($_POST['username'])."','',NOW(),'','locked','')";

[Ivan Ivković]

If you're building an international site, NOW() could be replaced with nicely formatted gmtdate().

But let's leave it with that.

 

It's good to make {} braces fir variables in your queries too.

 

$username = mysql_real_escape_string($_POST['username']);

$sql2 = "INSERT INTO mailbox values ('','locked','','{$username}','',NOW(),'','locked','')";

[packets]

@Buddski

 

I change username to name but nothing happens

 

function print_dropdown($query, $link){
    $queried = mysql_query($query, $link);
    $menu = '<select name="username">';
    while ($result = mysql_fetch_array($queried)) {
        $menu .= '
      <option value="' . $result['id'] . '">' . $result['username'] . '</option>';
    }
    $menu .= '</select>';
    return $menu;
}

 

@Ivan Ivković

 

Thanks for the recommendation and the variables/curly bracket.

 

[awjudd]

Does your $query variable's query return any results?  i.e. if you throw it into phpmyadmin then do you see the user id and usernames?

 

I disagree with Ivan's suggestion of using the {} I personally prefer if I'm doing it that way either string concatenation or using sprintf.

 

$sql2 = sprintf ( "INSERT INTO mailbox VALUES ( '', 'locked', '', '%s', '', NOW(), '', 'locked', '')", mysql_real_escape_string ( $_POST [ 'username' ] ) );

 

You are adding in lots of empty strings could those not be NULL?

 

~juddster

[packets]

Does your $query variable's query return any results?

 

I think so because when I check the page, I saw all the infos on column username on my dropdown.

 

You are adding in lots of empty strings could those not be NULL?

 

Some of them has default data.

 

 

I'm very sorry guys. I'm so noob on this part. I just studied php mysql yesterday and I'm stuck.

[Muddy_Funster]

ok, fistly, there is no need at all to use the {} for flat variables, only array key values.  Secondly, if you are not inserting a value to a field it should not be included in the insert statement.  Insert only to the fields that you are changing the data with your input.

$sql2 = "INSERT INTO mailbox (fieldName2, fieldName4, fieldName6, filedName8) VALUES ('locked', '$username', <see below>, 'locked')";

 

for the field that you aere inserting NOW() into, if it is a timestamp field you would be better using CURRENT_TIMESTAMP() instead.

[packets]

I'm now able to insert to mysql. However, username field was not inserted in mysql. It was NULL.

 

test.php

 

<?php
function database_connect($select_database)
{
    $resource_link = mysql_connect("localhost", "root", "root");
    if (mysql_select_db($select_database, $resource_link)) {
        return $resource_link;
    } else {
        echo "Cannot connect to DB";
        return false;
    }
}

function print_dropdown($query, $link){
    $queried = mysql_query($query, $link);
    $menu = '<select name="username">';
    while ($result = mysql_fetch_array($queried)) {
        $menu .= '
      <option value="' . $result['id'] . '">' . $result['username'] . '</option>';
    }
    $menu .= '</select>';
    return $menu;
}

//Some other form elements, or just start a form.
echo '<form method="post" action="lock.php">';

//The important bit
echo print_dropdown("SELECT username FROM mailbox where status = 'active'", database_connect("users"));

//Some other form elements, or just end the form.
echo '<input type="submit" name="submit" value="submit"/>
    </form>';

 

lock.php

 

<?php
// open the connection
$conn = mysql_connect("localhost", "root", "root");
// pick the database to use
mysql_select_db("users",$conn);
// create the SQL statement
$sql1 = "SELECT username from mailbox where username = '$_POST[username]'";
$result1 = mysql_query($sql1, $conn) or die(mysql_error());
$number_of_rows = mysql_num_rows($result1);
if ( $number_of_rows != 0 ){
        print "Account already exists";
        $page = "index.html";
        header("Refresh: 5; URL=\"" . $page . "\"");
        }
if ( $number_of_rows == 0 ){
$username = mysql_real_escape_string($_POST['username']);
$sql2 = "INSERT INTO mailbox values ('','lockuser','','{$username}','',CURRENT_TIMESTAMP(),'','active','')";
// for troubleshooting
//$result = mysql_query($sql2, $conn) or die(mysql_error());
// execute the SQL statement
if (mysql_query($sql2, $conn)) {
    echo "Successfully created mailbox";
    $page = "index.html";
    header("Refresh: 5; URL=\"" . $page . "\"");
} else {
    echo "Failed creating mailbox";
    $page = "index.html";
    header("Refresh: 5; URL=\"" . $page . "\"");
}
}
?>

 

Anyone can give a hint? 

[Muddy_Funster]

how about reading my last post?

 

I mentioned not using {} arround $username, as well as not entering empty values and the use of the CURRENT_DATETIME()

 

One out of three aitn bad...

 

Anyway, ignoring the fact that you ignored most of my last suggestions to improve your code, lets focus on the fix.  Find this piece of your code:

 while ($result = mysql_fetch_array($queried)) {
        $menu .= '
      <option value="' . $result['id'] . '">' . $result['username'] . '</option>';
    }

replace with:

 while ($result = mysql_fetch_array($queried)) {
        $menu .= "<option value=\"{$result['username']}\">{$result['username']}</option>";
    }

Next Find:

$sql1 = "SELECT username from mailbox where username = '$_POST[username]'";

And change to:

$sql1 = "SELECT username from mailbox where username = '{$_POST['username']}'";

 

I'm not even going back to what's wrong with your $sql2 line.

 

Interested how this is ever supposed to make a new mailbox if the only choices on input are from a dropdown list of already existing mailboxes...

[packets]

@Muddy_Funster

 

Thanks for all the help. I was now able to insert data to mysql. Also, all your recommendation has been implemented and noted. I would not be able to finish and check all the errors without your help. I've also corrected the $sql2 syntax.

 

Interested how this is ever supposed to make a new mailbox if the only choices on input are from a dropdown list of already existing mailboxes...

 

 

[jcbones]

for the field that you aere inserting NOW() into, if it is a timestamp field you would be better using CURRENT_TIMESTAMP() instead.

 

Ummm, from the MySQL manual.

CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP Synonyms for NOW()