Member Avatar for websurfer

Hello, all:

I have a simple chart, like...
ID ACCOUNT YEAR JAN FEB
1 Utilities 2007 $20 $20
2 Utilities 2008 $25 $25

Let's say, customer wants to add a "year" like 2009, how can I check that this "2009" is NOT already in the YEAR column?? so that if it's NOT, then go ahead and insert new year, and if it was, then DO NOT insert. and instead echo something like "year already exists!"

I am kind of a newbie, and I've tried several ways, but cant seem to figure it out!

Aprpeciate the help! thanks...

  • 3 Contributors
  • 3 Replies
  • 366 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by websurfer

Dani AI

Generated

Two practical, reliable approaches:

  • Let the application check first (good for clarity) but always use parameterized queries to avoid SQL injection and trim/validate the input. Checking with SELECT COUNT(*) and then inserting is fine for simple use, but it can produce duplicates under concurrent writes.
  • Enforce uniqueness at the database level (recommended). Add a UNIQUE index on the year column and either use INSERT IGNORE / ON DUPLICATE KEY UPDATE or catch the duplicate-key error. That prevents race conditions and keeps data integrity regardless of application logic.

Example using PDO (existence check then insert):

<?php
$year = (int) trim($_POST['year']); // validate as integer
$pdo = new PDO('mysql:host=localhost;dbname=mydb;charset=utf8mb4','user','pass',[
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);

$stmt = $pdo->prepare('SELECT COUNT(*) FROM example WHERE `year` = ?');
$stmt->execute([$year]);
if ($stmt->fetchColumn() == 0) {
  $pdo->prepare('INSERT INTO example (`year`) VALUES (?)')->execute([$year]);
  echo "Year $year added.";
} else {
  echo "Year $year already exists.";
}
?>

Alternative (robust) — create a DB constraint and handle duplicates:

ALTER TABLE example ADD UNIQUE KEY unique_year (`year`);

Then either use INSERT IGNORE and check affected rows, or run a normal INSERT inside a try/catch and treat SQLSTATE 23000 as “duplicate key”.

Quick troubleshooting notes: avoid echoing inside a loop (that causes repeated messages), prefer an INT or YEAR column type to avoid string/whitespace mismatches, trim input, and stop using old mysql_* calls (use PDO or mysqli). ’s basic SELECT/count idea is sound, was correct about the comparison, and ’s repeated-message issue came from checking rows in a loop rather than checking the result count once.

Recommended Answers

All 3 Replies

Member Avatar for jakx12

$userinput = $_POST[example];
$result = mysql_query("SELECT * FROM example WHERE year='$userinput'")
or die(mysql_error());
$total = mysql_num_rows( $result );
if ($total == 0){
echo "The year " .$userinput ." already exists!";
}
else
{

mysql_query("INSERT INTO example
(year) VALUES('$userinput') ")
or die(mysql_error());
echo "The year: " .$userinput ." has been added successfully";
}

Member Avatar for death_oclock

Hmmm, so you look for records with the user's selected year in it, thats where I would have started. But if we don't find any such rows, then it must already exist? I think not. Try $total > 0 .

Member Avatar for websurfer

Thanks guys...

I see now how I should have simply matched the POST entry vs. the DB!! I seemed to have been doing it all in-reverse, like first SELECTING the records, and then comparing the POST against each row with a "while" or with "in_array", which would then repeat the message repeatedly... what a mess I was doing! I definitely need to be more creative and logical with php... such a simple solution and almost had a brain-meltdown!

Aprpeciate the help!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.