Select in MYSQL

[princeofpersia]

Hi guys I have a two tables in my mysql 

 

branch:

 

id      branchname    postcode

 

 

then a user table

 

id    username    password  branch1    branch2

 

 

so far I have a select form as below which populates the select form from mysql

 

    branch 1<select name='branch1'><p />
    
<?php
$branchdropdown=mysql_query("SELECT id ,branchname, postcode FROM branch");

while($row = mysql_fetch_array($branchdropdown))
{
// echo '<option value="' .$row['stationname']. '"></option>'  ;
echo "<option value=\"".$row['id']."\">".$row['branchname']."\n  ";
$postcodeone=$row['postcode'];

}


?>

 

and then this will be inserted in mysql as below 

 

$submit = mysql_query("INSERT INTO users (branch1, branch2) VALUES ($postcodeone, $postcodetwo)");
     echo "This entry has been added to our database";

    </select>

 

but it only inserts the branch id and not the password, can you please tell me what im doing wrong?

[litebearer]

for starters, your VALUE variables need to have single quotes around them

ie

'$postcodeone'

[princeofpersia]

I have done this but still the same

[ManiacDan]

Have you tried printing the SQL and looking at it?  Have you tried running it by hand?  What's the output of the mysql_error() function after this query fails?  Are you sure the database table is set up properly?

 

-Dan

[princeofpersia]

yeah when i echo the $postcodeone in while statement mentioned, it shows the postcode in the same dropdown next to station name, but  i need only the postcode to be added to database

[princeofpersia]

btw i dont get any errors

[ManiacDan]

You've answered 1 of the 4 questions.

[princeofpersia]

Dan

 

Have you tried printing the SQL and looking at it?  yeah, it gives me the postcode

 

Have you tried running it by hand? I am a newbie, i dont understand what do you mean

 

What's the output of the mysql_error() function after this query fails? No errors, it adds the rest of the entries, it wont update the branch1 field

 

Are you sure the database table is set up properly? Yes, I have other fields in my database which work fine except branch1 and branch 2 which both using the same drop down, in branch2 I have not have the variable $postcodetwo, i changes it to $branch2 to test and it only add the id of branch and not its postcode or name

 

thanks for you time

 

[JakeTheSnake3.0]

Have you tried printing the SQL and looking at it?  yeah, it gives me the postcode

- He asked you to print the SQL query, not the $postcode variable....for troubleshooting purposes it's best to store the query in a separate variable instead of putting it directly inside the mysql_query() function.  That way you can echo() it to see if it contains any errors.

 

Have you tried running it by hand?

- Log into phpmyadmin and select the table.  You should then see an "SQL" tab in which you can directly enter the SQL query and run it.  It will show you errors in your syntax if you have any.

[ManiacDan]

Adding to what Jake said:

Have you tried printing the SQL and looking at it?  yeah, it gives me the postcode

Like Jake said: Print the QUERY, not the variables.  The query should appear well-formed.

 

Have you tried running it by hand? I am a newbie, i dont understand what do you mean

Like Jake said again: Run the query in the database manager directly, see if it produces any errors or warnings.

 

What's the output of the mysql_error() function after this query fails? No errors, it adds the rest of the entries, it wont update the branch1 field

What value is inside the branch1 field when you're done?  Empty string?  Null?  Zero?

 

Are you sure the database table is set up properly? Yes, I have other fields in my database which work fine except branch1 and branch 2 which both using the same drop down, in branch2 I have not have the variable $postcodetwo, i changes it to $branch2 to test and it only add the id of branch and not its postcode or name

Drop-downs have nothing to do with database tables.  It's possible you have the database column set up as a NUMBER, but you're trying to insert a STRING, and all you're getting is a zero.

[princeofpersia]

No, i have tried them all and no changes,

 

could it be because I have the php tag in form?

[JakeTheSnake3.0]

Could you please post the sql query that you see?

[litebearer]

perhaps we should start from the beginning and see ALL of your most recent code

[ManiacDan]

Yeah, re-reading your original post shows that your code doesn't match your description of the problem. 

 

 

[princeofpersia]

Hi, you are right what i meant by "but it only inserts the branch id and not the password" is that it add the branch id not the postcode

[ManiacDan]

You're getting a duplicate key error because you have a key on the table that's blocking the insert.

 

The original insert is failing, and we can't tell why, because you STILL haven't printed out the query, run the query by hand, or shown us the table definition. 

 

echo the query (the QUERY) and print it here.

 

Copy and paste the query into a query browser (PHPMyAdmin, Navicat, whatever you used to make the tables).  See what it says, if anything.

 

Delete all rows from the table and try again.

 

Do a "SHOW CREATE TABLE branch" (or...users, you have two conflicting tablenames in your post).  Show us the output.

 

-Dan