Page 1 of 1

Check for existing record

Posted: Tue Apr 23, 2002 3:29 pm
by abionifade
Hi Guys,

how would i modify my code to check if there is already an existing record of the same coloumn name (field = option_name) in the table?

Thanks

-Abi

Code: Select all

<?php include("dbconnect.php"); ?>
<?php


        $query = "insert into option_table"
                ." (option_name, course_id, module_id) values "
                ."('$option_name', '$course_id', '$module_id')"
        ;
       $result = mysql_query($query);
		
		if($result) &#123;
			echo "submission succesful"; &#125;
		else &#123;
			echo "error"; 
			&#125;

?>

Posted: Tue Apr 23, 2002 4:21 pm
by jason
When you make the MySQL table, make the column a Unique column, that way, MySQL won't accept a value that already exists.

Posted: Tue Apr 23, 2002 4:27 pm
by abionifade
oops,

i should have included that in my first post. I have set the field as unique, but would like to incorporate in my code some feedback.

Thanks in advance

Posted: Tue Apr 23, 2002 6:00 pm
by mydimension
i've used the unique setting before but it didn't give me what i wanted. here is a function that i use periodically.

Code: Select all

function nickname_exists($nickname) &#123;
	$query = "SELECT * FROM database WHERE field='$variable'";
	$result = mysql_query($query);
	$row = mysql_fetch_row($result);
	if ($row&#1111;0] == $variable) return true;
	else return false;
&#125;
it has been working for me for a while. hope it works for you

Posted: Tue Apr 23, 2002 6:01 pm
by mydimension
oops, forgot to change the function name and argument but i think you get the idea

Posted: Wed Apr 24, 2002 11:52 pm
by Ruiser
This looks like the same project you were working on in another thread.
In this case, you wouldn't want any column to be a unique column. that would cause problems where a module could only have one course or a course could only have one module. I may be missunderstanding what you're trying to do, but let me take a shot at it.

You could have three tables:

Modules
----------
module_id
module_name

Course
----------
course_id
course_name

CourseModules (none of the fields below should be unique or a primary key) you could index them for speed though.
----------
module_id
course_id

then if you wanted to have course #2 have a module #3, you would do an
insert into CourseModules (module_id, course_id) values (3,2);

then if you wanted to check and see if module #3 was already associated with course #2, you would do a select on it first.

$result=mysql_query("select * from CourseModules where module_id=3 and course_id=2;");
if (mysql_num_rows($result) > 0)
{
echo "course 2 is linked to module 3";
// you could even do and update here if you wish.
}
else
{
// if it's not found, you can go ahead and add it with an insert if you wish.
insert into CourseModules (module_id, course_id) values (3,2);
}

does this help at all?

Posted: Sun Apr 28, 2002 6:10 am
by abionifade
Hi Ruiser,

thanks for getting back to me on this. I'll be taking a look into this cos it looks really good.

When i get back home, I will give you some feedback as to how i got on.

Once again, thank you!

-Abi :D