checking against database

smgtech

Hello, I am trying to create an insert form that will insert information into a database as long as it is not already there, if it is then update it. "Only if there was a coding language out there that understood english..."

This is the php that I got so far:

<?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
')";
if EXISTS (SELECT * FROM Director WHERE position = 'positiontypedinform')
else (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "Information Saved";

mysql_close($con);
?>

bradgrafelman

So in other words, you want only ONE unique row for every "Position" value?

I would make a UNIQUE (or PRIMARY) key for the Position column, and then use the INSERT ... ON DUPLICATE KEY UPDATE syntax (here's a link to the MySQL manual for this syntax) like so:

$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']}')
ON DUPLICATE KEY UPDATE `FirstName` = '{$_POST['firstname'], `MiddleName` = '{$_POST['middlename']}'";
// You'll have to finish the query, I only did the first 2 fields

EDIT: Also, here's an explanation of a couple of things I changed in your code..

Array indeces in your case should be strings. $row[name] does not have a string as an index, but a constant. PHP will detect this (assuming there actually isn't a constant named 'name') and throw an E_WARNING and then assume it is a string. So, use quotes: $row['name'].
Arrays, more specifically items in arrays referenced by indeces, should not simply appear inside a double-quoted string. If they do, they should be wrapped in braces ( {} ).

smgtech

Thank you for that code, that is a lot easyer than what I have been reading. Also, does the primary key need to be null or not null?

bradgrafelman

I do believe PRIMARY keys need to be not null.

smgtech

I read the manual on that syntax and Im not understanding the update fully. Once all of these are written in, if the Form was submited with the same Position again, it will only update these values "FirstName, MiddleName, etc..."?

bradgrafelman

Correct. If the form is submitted and you try to INSERT a row with a duplicate position name, it will UPDATE the existing row instead of inserting a new row.

The only fields it updates are those that you define after the "ON DUPLICATE KEY UPDATE ..." syntax.

smgtech

Thank you for all your help. Although I was wondering if there was a way to put all the names and the values in seperate fields instead of doing them one by one.

bradgrafelman

I'm afraid I don't understand your question ... ?

smgtech

Instead of writing first name = post first name, can I write firstname, middlename, etc... = post firstname, post middlename, post etc...

bradgrafelman

Ah. AFAIK, no, you have to list the fields individually. I'm no SQL guru, however... just having found any other mention in the manual and this is how I've always done it.

smgtech

I am coming accross a problem, if one value is different it still makes a new row, and when inserting for the first time, it leaves out the position.

<?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']}')
ON DUPLICATE KEY 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']}'";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "Information Saved";

mysql_close($con);
?>

smgtech

Ive tryed it over and I just can't seem to get it to work, it looks as if this is used mostly for numbers as the mysql manual gives examples with numers only.

smgtech

Does anyone know how I can intergrate this script into my script.
// checks if the username is in use
if (!get_magic_quotes_gpc()) {
$POST['username'] = addslashes($POST['username']);
}
$usercheck = $_POST['username'];
$check = mysql_query("SELECT username FROM users WHERE username = '$usercheck'")
or die(mysql_error());
$check2 = mysql_num_rows($check);

//if the name exists it gives an error
if ($check2 != 0) {
die('Sorry, the username '.$_POST['username'].' is already in use.');
}