[alwa18]

I never hard coded anything before in my life (I use site spinner), and about three weeks ago was the first time that I heard about MySQL... but I think I found a new challenge/hobby.
I was able to copy, paste and modify a reasonably working interactive website together.
However, one of the forms does not work the way I want it to. It is supposed to update a table in the MySQL database and consists of two drop-down boxes and three text fields. The first drop down boxes is populated by a MySQL query (which works fine) and the other drop-down box has a choice of three values.
If I don't touch the first drop-down box, everything will update correctly in the first choice. But if I choose a different selection (the entry in the second row, etc.) I still will get the message "Record Updated" but nothing will actually happen to the table?
After googling for the past 4 to 5 days to find a solution, reading up on hundreds of articles in order to make sense of it all and countless trial and error tests, I have to admit defeat and beg for help!
I would highly appreciate if somebody could have a look at my code and tell me what I'm doing wrong in layman's terms, S. all in what I have to work with is common sense, logic but unfortunately no knowledge.

Here is the code for the form:

<form action="update_sh.php" method="post">

<p>
 <th ALIGN=left><font size='2'>Select Property</th>
<br>

<?php
 $con = mysql_connect("localhost","username","password");
 if (!$con)
   {
   die('Could not connect: ' . mysql_error());
   }
 
mysql_select_db("dbname", $con);
 
$result = mysql_query("SELECT * FROM tablename");
 
echo "<select name='property'>";while ($row = mysql_fetch_array($result)) {    echo "<option value='" . $row['prop'] . "'>" . $row['prop'] . "</option>";}echo
"</select>";?>
</p>

<p>
What is your Position?
<br>
<select name="position">
  <option value="">Select...</option>
     <option value="Executive Chef">Executive Chef</option>
     <option value="Exec. Sous Chef">Exec. Sous Chef</option>
     <option value="Pastry Chef">Pastry Chef</option>
</select>
</p>


Your Full Name: <br>
<input type="text" name="chef" size="45"><br>
E-mail: <br>
<input type="text" name="chefe" size="45"><br>
Phone: <br>
<input type="text" name="chefn" size="45"><br>
<input type="Submit">
</form>
[code/]

And here is the PHP code for the update.php file:

[code]
<?php
 
$con = mysql_connect("localhost","username","password");
 if (!$con)
   {
   die('Could not connect: ' . mysql_error());
   }
 
mysql_select_db("dbname", $con);
$ud_prop=$_REQUEST['property'];
$ud_position=$_REQUEST['position'];
$ud_chef=$_POST['chef'];
$ud_chefe=$_POST['chefe'];
$ud_chefn=$_POST['chefn'];
$ud_sous=$_POST['chef'];
$ud_souse=$_POST['chefe'];
$ud_sousn=$_POST['chefn'];
$ud_patt=$_POST['chef'];
$ud_patte=$_POST['chefe'];
$ud_pattn=$_POST['chefn'];

if ($ud_position=='Executive Chef')
   {
$query="UPDATE tablename SET id='Null', posie='".$ud_position."', chef='$ud_chef', chefe='$ud_chefe', chefn='$ud_chefn' WHERE prop='$ud_prop'";
mysql_query($query);
echo "Record Updated";
mysql_close($con);
    }

elseif ($ud_position=='Exec. Sous Chef')
   {
$query="UPDATE tablename SET id='Null', posis='$ud_position', sous='$ud_chef', souse='$ud_chefe', sousn='$ud_chefn' WHERE prop='$ud_prop'";
mysql_query($query);
echo "Record Updated";
mysql_close($con);
    }

elseif ($ud_position=='Pastry Chef')
   {
$query="UPDATE tablename SET id='Null', posip='$ud_position', patt='$ud_chef', patte='$ud_chefe', pattn='$ud_chefn' WHERE prop='$ud_prop'";
mysql_query($query);
echo "Record Updated";
mysql_close($con);
    }

elseif ($ud_position=='Select...')
   {
echo "Please click your browsers back button, select your position and try again -- ";
    }

 ?> 

Thank you in advance!

[RudiVisser]

Okay:

1: Consider using MySQLi.
2: Use mysql_real_escape_string().
3: Why do you have an if() statement saying if $ud_position differs, do the exact same thing?
4: Why are you, in some cases using '".$ud_position."' and in others '$ud_position'? They do the same thing so just ignore it.
5: Basic debugging - Check your POST data, a simple var_dump($_POST) will tell you what's coming through.
6: Don't use mysql_close(), there's no need.
7 AND IMPORTANT: You can check why your queries are failing by changing the mysql_query($query); lines to mysql_query($query) or die(mysql_error());.

Make sure you read all of the points and not just the 7th, but definitely do the 7th as that is how you fix your error.

[Atli]

Hey.

I have two points I'd like to make.

First:
I would suggest reconsidering the names you use in your MySQL design. Using vague or unspecific acronyms can get very confusing, both for others who may have to read you code, and for you if you ever have to come back to it.

A table named tablename is obviously not a good idea. It tells people nothing about what is stored there.

And instead of doing chef, chefe and chefn for a full name, email and number, name them just that: full_name, email and phone.

Second:
Consider these lines from your code:

$result = mysql_query("SELECT * FROM tablename");
 
echo "<select name='property'>";while ($row = mysql_fetch_array($result)) {    echo "<option value='" . $row['prop'] . "'>" . $row['prop'] . "</option>";}echo
"</select>";?>

There you are selecting ALL the data in the table (every field of every row), but you only use one column. This is a horrible waste of resources. You should always aim to select only what you need from a SQL query, and no more. As such, using the wild-card char, *, in a select statement is very rarely a good idea.

In this specific case, all you need is this: SELECT prop FROM tablename.
This would be much much more efficient.

[alwa18]

Hi Rudi, thank you for your prompt reply. In regards to the If statements, they all look the same but depending on the selection of petition, they should post in a different column.
About the basic debugging "var_dump($_POST)", please don't forget that I am a complete beginner and need some guidance were I should insert this into the code -- sorry for that :-)
I did remove the closing of the database connection.
After I extended the query (mysql_query($query) or die(mysql_error()) I get the following message: "Duplicate entry '0' for key 1" which only displays if I make a change to the first drop-down box.
Thank you for your help but I still don't know how to fix it.
Alexander

Hello Atli, thank you for your suggestions. I limited the selection to one column. The name of the table is not tablename. I just changed it to look more generic...

[RudiVisser]

Thank you for more info

Let's leave the var_dump out for now and skip straight to your error.

Basically what it's saying is that it can't insert a record because one with the same key (either PRIMARY or UNIQUE) already exists. Now I'm assuming this is coming from your primary key column, id.

You need to give this the AUTO_INCREMENT functionality.

We can confirm that this is the issue by seeing a dump of your create table query, which you can get on the MySQL Command line by doing:
SHOW CREATE TABLE `tablename`;
and posting it here

[alwa18]

Hello Rudi,
I get the following result for the show create table command:

shanghai CREATE TABLE `shanghai` (
`id` int(11) NOT NULL auto_increment,
`fivestar` text NOT NULL,
`posie` text NOT NULL,
`chef` text NOT NULL,
`chefe` text NOT NULL,
`chefn` text NOT NULL,
`posis` text NOT NULL,
`sous` text NOT NULL,
`souse` text NOT NULL,
`sousn` text NOT NULL,
`posip` text NOT NULL,
`patt` text NOT NULL,
`patte` text NOT NULL,
`pattn` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=125 DEFAULT CHARSET=utf8

my table name is shanghai...

[RudiVisser]

Oh, I missed it.

I think this may be the issue: id='Null', either remove it entirely, or change it to id=NULL, but I would remove it entirely.

That should help, btw your create table is fine

[alwa18]

Rudi, thank you so much!

I removed the id='null' and everything is working!

My next challange will be how and where to add the mysql_real_escape_string. Now if I run into problems I know where to look for help.

Thank you, Alexander

[RudiVisser]

mysql_real_escape_string would be used here, for example: $ud_chef=$_POST['chef'];

Everything that gets inserted into the database should be escaped using this function, so the above line of code would change to: $ud_chef = mysql_real_escape_string($_POST['chef']);

BTW, if you read my link about using PDO/MySQLi and decided to do so, you could use prepared statements, which are much better as you don't need to escape (and a lot more reasons!).

Oh and, you're welcome! Glad you got it sorted.