code will not work

smgtech

I am trying to update the database without making duplicate rows.

<?php
$con = mysql_connect(*****************","***","***");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
// insert information into table within database
mysql_select_db("offadmi_staff", $con);

$sql="INSERT INTO Director (Position, FirstName, MiddleName, LastName, BirthDay, Height, Weight, Status, Hobbies, FavFood, FavDrink, FavMovie, FavSports, SuperPower, Quote)
VALUES ('{$_POST['position']}','{$_POST['firstname']}','{$_POST['middlename']}','{$_POST['lastname']}','{$_POST['birthday']}','{$_POST['height']}','{$_POST['weight']}','{$_POST['status']}','{$_POST['hobbies']}','{$_POST['favfood']}','{$_POST['favdrink']}','{$_POST['favmovie']}','{$_POST['favsports']}','{$_POST['superpower']}','{$_POST['quote']}')";
$upd="UPDATE `FirstName` = '{$_POST['firstname']}', `MiddleName` = '{$_POST['middlename']}', `LastName` = '{$_POST['lastname']}', `BirthDay` = '{$_POST['birthday']}', `Height` = '{$_POST['height']}', `Weight` = '{$_POST['weight']}', `Status` = '{$_POST['status']}', `Hobbies` = '{$_POST['hobbies']}', `FavFood` = '{$_POST['favfood']}', `FavDrink` = '{$_POST['favdrink']}', `FavMovie` = '{$_POST['favmovie']}', `FavSports` = '{$_POST['favsports']}', `SuperPower` = '{$_POST['superpower']}', `Quote` = '{$_POST['quote']}'";
$check="$_POST["position"]";
$datacheck= mysql_query("SELECT position FROM director");
if ($check = $datacheck)
then (!mysql_query($upd,$con))
else (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Information Saved";

mysql_close($con);
?>

thorpe

And?

smgtech

<?php
$con = mysql_connect("***");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
// insert information into table within database
mysql_select_db("offadmi_staff", $con);

$sql="INSERT INTO Director (Position, FirstName, MiddleName, LastName, BirthDay, Height, Weight, Status, Hobbies, FavFood, FavDrink, FavMovie, FavSports, SuperPower, Quote)
VALUES ('{$_POST['position']}','{$_POST['firstname']}','{$_POST['middlename']}','{$_POST['lastname']}','{$_POST['birthday']}','{$_POST['height']}','{$_POST['weight']}','{$_POST['status']}','{$_POST['hobbies']}','{$_POST['favfood']}','{$_POST['favdrink']}','{$_POST['favmovie']}','{$_POST['favsports']}','{$_POST['superpower']}','{$_POST['quote']}')";
$upd="UPDATE Director SET `FirstName` = '{$_POST['firstname']}', `MiddleName` = '{$_POST['middlename']}', `LastName` = '{$_POST['lastname']}', `BirthDay` = '{$_POST['birthday']}', `Height` = '{$_POST['height']}', `Weight` = '{$_POST['weight']}', `Status` = '{$_POST['status']}', `Hobbies` = '{$_POST['hobbies']}', `FavFood` = '{$_POST['favfood']}', `FavDrink` = '{$_POST['favdrink']}', `FavMovie` = '{$_POST['favmovie']}', `FavSports` = '{$_POST['favsports']}', `SuperPower` = '{$_POST['superpower']}', `Quote` = '{$_POST['quote']}'
WHERE Position = '{$_POST['position']}'");
$check="'{$_POST["position"]}'";
$result = mysql_query("SELECT * FROM director
WHERE Position='{$_POST['position']}'");";
if ($check = $result)
  (!mysql_query($upd,$con))
else (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Information Saved";

mysql_close($con);
?>

gives the:
Parse error: parse error, unexpected ')' in /insert.php on line 20

smgtech

actually I resolved the other problem, stupidy, but now I have the error:

Parse error: parse error, unexpected T_STRING in /insert.php on line 29

bradgrafelman

Well, if you post your code with the [ PHP ][ /PHP ] tags, OR if you use a decent editor that does some syntax highlighting, you'll notice a problem with the end if your SQL query:

<?php
$con = mysql_connect("***");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
// insert information into table within database
mysql_select_db("offadmi_staff", $con);

$sql="INSERT INTO Director (Position, FirstName, MiddleName, LastName, BirthDay, Height, Weight, Status, Hobbies, FavFood, FavDrink, FavMovie, FavSports, SuperPower, Quote)
VALUES ('{$_POST['position']}','{$_POST['firstname']}','{$_POST['middlename']}','{$_POST['lastname']}','{$_POST['birthday']}','{$_POST['height']}','{$_POST['weight']}','{$_POST['status']}','{$_POST['hobbies']}','{$_POST['favfood']}','{$_POST['favdrink']}','{$_POST['favmovie']}','{$_POST['favsports']}','{$_POST['superpower']}','{$_POST['quote']}')";
$upd="UPDATE Director SET `FirstName` = '{$_POST['firstname']}', `MiddleName` = '{$_POST['middlename']}', `LastName` = '{$_POST['lastname']}', `BirthDay` = '{$_POST['birthday']}', `Height` = '{$_POST['height']}', `Weight` = '{$_POST['weight']}', `Status` = '{$_POST['status']}', `Hobbies` = '{$_POST['hobbies']}', `FavFood` = '{$_POST['favfood']}', `FavDrink` = '{$_POST['favdrink']}', `FavMovie` = '{$_POST['favmovie']}', `FavSports` = '{$_POST['favsports']}', `SuperPower` = '{$_POST['superpower']}', `Quote` = '{$_POST['quote']}'
WHERE Position = '{$_POST['position']}'");
$check="'{$_POST["position"]}'";
$result = mysql_query("SELECT * FROM director
WHERE Position='{$_POST['position']}'");";
if ($check = $result)
(!mysql_query($upd,$con))
else (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "Information Saved";

mysql_close($con);
?>

More specifically, an extraneous "; outside of the $result SQL query.

Also, what in the world are you trying to do with the SELECT * query and then if($check = $result) stuff? Even if you had used the correct operator in that IF statement (which you didn't)... it still wouldn't make sense.

smgtech

Well the previous help that you gave me on the duplicate key, did not work with words, but with numbers, because the only way that it could work is if you added a +1 after the post, and instead of putting the words in the mysql table it put the number 1. So I had to adopt something different, so I ventured to write my own code, and as you can see it doesn't makes sense, but I'm trying. So on the code: if($check = $result) then it will update everything accept the position field, else it will run the insert ($sql) and make a brand new row. I tried it with the duplicate key and it would either insert a new row or it would not post the position at all. Your help is greatly appreciated. Thank You.

smgtech

Although I have tryed to do some changes to the code it still does not work:

// insert information into table within database
mysql_select_db("offadmi_staff", $con);

$sql="INSERT INTO Director (Position, FirstName, MiddleName, LastName, BirthDay, Height, Weight, Status, Hobbies, FavFood, FavDrink, FavMovie, FavSports, SuperPower, Quote)
VALUES ('{$_POST['position']}','{$_POST['firstname']}','{$_POST['middlename']}','{$_POST['lastname']}','{$_POST['birthday']}','{$_POST['height']}','{$_POST['weight']}','{$_POST['status']}','{$_POST['hobbies']}','{$_POST['favfood']}','{$_POST['favdrink']}','{$_POST['favmovie']}','{$_POST['favsports']}','{$_POST['superpower']}','{$_POST['quote']}')";
$check="'{$_POST["position"]}'";
$result = mysql_query("SELECT * FROM director WHERE Position='{$_POST['position']}'");
while ($check = $result)
  {!mysql_query(UPDATE Director SET `FirstName` = '{$_POST['firstname']}', `MiddleName` = '{$_POST['middlename']}', `LastName` = '{$_POST['lastname']}', `BirthDay` = '{$_POST['birthday']}', `Height` = '{$_POST['height']}', `Weight` = '{$_POST['weight']}', `Status` = '{$_POST['status']}', `Hobbies` = '{$_POST['hobbies']}', `FavFood` = '{$_POST['favfood']}', `FavDrink` = '{$_POST['favdrink']}', `FavMovie` = '{$_POST['favmovie']}', `FavSports` = '{$_POST['favsports']}', `SuperPower` = '{$_POST['superpower']}', `Quote` = '{$_POST['quote']}' WHERE Position = '{$_POST['position']}')};
else (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Information Saved";

mysql_close($con);
?>

it gives the:

Parse error: parse error, unexpected T_STRING in /insert.php on line 22

which means that the ($check = $result) thing I tryed is not working?

smgtech

basically I want to query the database and see if the position value has already been taken if not then insert the new values, if so then update the values, ive searched the forums and people are asking the same quesion but not getting results. Ive got the right Idea but the sql manual doesn't tell you step by step how to do this. Has anyone ever done this before?

Ausx

First it would be a good idea to check those $_POSTs to make sure they're nothing harmful. But basically, this should work.

$sql = "SELECT * FROM `Director` WHERE Position = ".$_POST['position']."";
$qry = mysql_query($sql);
if (!$qry) { die("There was an error!"); }
if (mysql_num_rows($qry) > 0) {
   $sql = "UPDATE `Director` WHERE position = ".$_POST['position']." SET Position = ".$_POST['position'].", FirstName = ".$_POST['firstname'].",  MiddleName = ".$_POST['middlename'].", LastName = ".$_POST['lastname'].",Birthday = ".$_POST['birthday'].", Height = ".$_POST['height'].", Weight = ".$_POST['weight'].", Status = ".$_POST['status'].", Hobbies = ".$_POST['hobbies'].", FavFood = ".$_POST['favfood'].", FavDrink = ".$_POST['favdrink'].", FavMovie = ".$_POST['favmovie'].", FavSports = ".$_POST['favsports'].", Superpower = ".$_POST['superpower'].", Quote = ".$_POST['quote']."";
$qry = mysql_query($sql);
if (!$qry) {
die("There was an error!"); 
} else {
echo 'Your data has been added!';
}
} else {
$sql = "INSERT INTO `Director` WHERE (Position, FirstName, MiddleName, LastName, BirthDay, Height, Weight, Status, Hobbies, FavFood, FavDrink, FavMovie, FavSports, SuperPower, Quote) VALUES (".$_POST['position'].", ".$_POST['firstname'].", ".$_POST['middlename'].", ".$_POST['lastname'].", ".$_POST['birthday'].", ".$_POST['height'].", ".$_POST['weight'].", ".$_POST['status'].", ".$_POST['hobbies'].", ".$_POST['favfood'].", ".$_POST['favdrink'].", ".$_POST['favmovie'].", ".$_POST['favsports'].", ".$_POST['superpower'].", ".$_POST['quote'].")";
$qry = mysql_query($sql);
if (!$qry) {
die("There was an error!"); 
} else {
echo 'Your data has been added!';
}
}

That should give you what your looking for.

EDIT: Ugh, it's too early for reading entire posts >_>

Weedpacket

Wouldn't it be simpler to simply enforce the uniqueness requirement in the database? Pick some fields that, together, have to be unique (FirstName, MiddleName, LastName looks like a possibility - depending on what "Position" is...) and then make that a Primary key (or at least a Unique key). Then the database will refuse to insert a record that has the same values for those fields as a record that's already in the database.

smgtech

Thanks Ausx, I tried the code and it gave there was an error, so I went back and changed each error display text to be specific to the error, and the error occurred after the select.

So I am guessing that it is the .$_POST['position'].

If so, what would could cause the POST to not work when it is in my form, what should I check.

Weedpacket I did try the primary key with position (position is the position of the staff director) using the ON DUPLICATE KEY UPDATE syntax after an INSERT INTO. There is a great possibility that I did something wrong, could you show me a working example of something small? Or are you saying that if it is a unique key then the database will automatically give an error?

Ausx

If the first query is failing then make sure that 1, you're definatly connected to your server, 2, the table name is "Director", the field name is "Position" and the name of the field from the form that the data on this page is from is called "position".

smgtech

Well all of that checks out ok, here is a look of what I got so far:

Create.php makes the tables, sif.html is the form, and Insert.php does the inert into the database.

<?php
$con = mysql_connect("***","***","***");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
// Create table in database
mysql_select_db("offadmi_staff", $con);
$sql = "CREATE TABLE Director
(
Position varchar(30),
PRIMARY KEY(Position),
FirstName varchar(30),
MiddleName varchar(30),
LastName varchar(30),
BirthDay varchar(20),
Height varchar(16),
Weight int(3),
Status tinytext,
Hobbies tinytext,
FavFood tinytext,
FavDrink varchar(30),
FavMovie varchar(50),
FavSports varchar(30),
SuperPower tinytext,
Quote tinytext
)";
mysql_query($sql,$con);

mysql_close($con);
?>

<form action="insert.php" method="post">
<p>
Position.<br /><input type="text" name="position" /><br />
First Name.<br /><input type="text" name="firstname" /><br />
Middle Name.<br /><input type="text" name="middlename" /><br />
Last Name.<br /><input type="text" name="lastname" /><br />
Birthday.<br /><input type="text" name="birthday" size="10" maxlength="10" value="dd/mm/yyyy" /><br />
Height.<br /><input type="text" name="height" size="13" maxlength="16" value="# feet ## inches" /><br />
Weight.<br /><input type="text" name="weight" size="1" maxlength="3" value="###" />lb.<br />
Status.<br /><input type="textarea" name="status" size="80" /><br />
Hobbies.<br /><input type="textarea" name="hobbies" size="80" /><br />
Favorite Food.<br /><input type="textarea" name="favfood" size="80" /><br />
Favorite Drink.<br /><input type="text" name="favdrink" /><br />
Favorite Movie.<br /><input type="text" name="favmovie" size="40" /><br />
Favorite Sports.<br /><input type="textarea" name="favsports" size="80" /><br />
If God gave you a Super Power for one day what would it be.<br /><input type="text" name="superpower" size="80" /><br />
Favorite Quote.<br /><input type="textarea" name="quote" size="80" /><br /><br />
<input type="submit" value="Save All" />
</p>
</form>

<?php
$con = mysql_connect("***","***","***");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
// insert information into table within database
mysql_select_db("offadmi_staff", $con);

$sql = "SELECT * FROM `Director` WHERE Position = ".$_POST['position']."";
$qry = mysql_query($sql);
if (!$qry) { die("Could not select!"); }
if (mysql_num_rows($qry) > 0) { 
   $sql = "UPDATE `Director` WHERE position = ".$_POST['position']." SET Position = ".$_POST['position'].", FirstName = ".$_POST['firstname'].",  MiddleName = ".$_POST['middlename'].", LastName = ".$_POST['lastname'].",Birthday = ".$_POST['birthday'].", Height = ".$_POST['height'].", Weight = ".$_POST['weight'].", Status = ".$_POST['status'].", Hobbies = ".$_POST['hobbies'].", FavFood = ".$_POST['favfood'].", FavDrink = ".$_POST['favdrink'].", FavMovie = ".$_POST['favmovie'].", FavSports = ".$_POST['favsports'].", Superpower = ".$_POST['superpower'].", Quote = ".$_POST['quote']."";
$qry = mysql_query($sql);
if (!$qry) {
die("Could not update!");
} else {
echo 'Your data has been updated!';
}
} else {
$sql = "INSERT INTO `Director` WHERE (Position, FirstName, MiddleName, LastName, BirthDay, Height, Weight, Status, Hobbies, FavFood, FavDrink, FavMovie, FavSports, SuperPower, Quote) VALUES (".$_POST['position'].", ".$_POST['firstname'].", ".$_POST['middlename'].", ".$_POST['lastname'].", ".$_POST['birthday'].", ".$_POST['height'].", ".$_POST['weight'].", ".$_POST['status'].", ".$_POST['hobbies'].", ".$_POST['favfood'].", ".$_POST['favdrink'].", ".$_POST['favmovie'].", ".$_POST['favsports'].", ".$_POST['superpower'].", ".$_POST['quote'].")";
$qry = mysql_query($sql);
if (!$qry) {
die("Could not insert!");
} else {
echo 'Your data has been added!';
}
}

mysql_close($con);
?>

Ausx

Sorry, my bad. Here's the code, I've tested it so it does work this time 😉

Remember, without cleaning out those $_POSTs though you really are leaving yourself wide open to hacking.

$sql = "SELECT * FROM `Director` WHERE Position = '".$_POST['position']."'";
$qry = mysql_query($sql);
if (!$qry) { die("Could not select!"); }
if (mysql_num_rows($qry) > 0) {


 $sql = "UPDATE `Director` SET FirstName = '".$_POST['firstname']."',  MiddleName = '".$_POST['middlename']."', LastName = '".$_POST['lastname']."', Birthday = '".$_POST['birthday']."', Height = '".$_POST['height']."', Weight = '".$_POST['weight']."', Status = '".$_POST['status']."', Hobbies = '".$_POST['hobbies']."', FavFood = '".$_POST['favfood']."', FavDrink = '".$_POST['favdrink']."', FavMovie = '".$_POST['favmovie']."', FavSports = '".$_POST['favsports']."', Superpower = '".$_POST['superpower']."', Quote = '".$_POST['quote']."' WHERE Position = '".$_POST['position']."'";

$qry = mysql_query($sql);
if (!$qry) {
die("Could not update!".mysql_error());
} else {
echo 'Your data has been updated!';
}
} else {
$sql = "INSERT INTO `Director` (Position, FirstName, MiddleName, LastName, BirthDay, Height, Weight, Status, Hobbies, FavFood, FavDrink, FavMovie, FavSports, SuperPower, Quote) VALUES ('".$_POST['position']."', '".$_POST['firstname']."', '".$_POST['middlename']."', '".$_POST['lastname']."', '".$_POST['birthday']."', '".$_POST['height']."', '".$_POST['weight']."', '".$_POST['status']."', '".$_POST['hobbies']."', '".$_POST['favfood']."', '".$_POST['favdrink']."', '".$_POST['favmovie']."', '".$_POST['favsports']."', '".$_POST['superpower']."', '".$_POST['quote']."')";
$qry = mysql_query($sql);
if (!$qry) {
die("Could not insert!".mysql_error());
} else {
echo 'Your data has been added!';
}
}

smgtech

Well I am pretty new to php, I don't exactly know any other ways to clear them out. Do you know of any resources where I could learn how to do this.

Im checking the code out now. Thank you in advance.

smgtech

maybe I have some sort of database problem, becuase its giving "could not select!" but from reading the code it looks like Im telling it to die before it even gets a chance to check if there is less than 0 in the database.