8 Replies - 1067 Views - Last Post: 12 August 2011 - 06:31 AM Rate Topic: -----

#1 alwa18  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 12-August 11

Form to update MySQL database is not working correctly

Posted 12 August 2011 - 02:22 AM

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!

Is This A Good Question/Topic? 0
  • +

Replies To: Form to update MySQL database is not working correctly

#2 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1004
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: Form to update MySQL database is not working correctly

Posted 12 August 2011 - 02:42 AM

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.
Was This Post Helpful? 2
  • +
  • -

#3 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,991
  • Joined: 08-June 10

Re: Form to update MySQL database is not working correctly

Posted 12 August 2011 - 03:11 AM

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.
Was This Post Helpful? 3
  • +
  • -

#4 alwa18  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 12-August 11

Re: Form to update MySQL database is not working correctly

Posted 12 August 2011 - 04:19 AM

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...
Was This Post Helpful? 0
  • +
  • -

#5 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1004
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: Form to update MySQL database is not working correctly

Posted 12 August 2011 - 04:34 AM

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 :)
Was This Post Helpful? 0
  • +
  • -

#6 alwa18  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 12-August 11

Re: Form to update MySQL database is not working correctly

Posted 12 August 2011 - 05:21 AM

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...
Was This Post Helpful? 0
  • +
  • -

#7 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1004
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: Form to update MySQL database is not working correctly

Posted 12 August 2011 - 05:42 AM

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 :)
Was This Post Helpful? 2
  • +
  • -

#8 alwa18  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 12-August 11

Re: Form to update MySQL database is not working correctly

Posted 12 August 2011 - 06:16 AM

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
Was This Post Helpful? 0
  • +
  • -

#9 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1004
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: Form to update MySQL database is not working correctly

Posted 12 August 2011 - 06:31 AM

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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1