Evening everyone,

 

I wonder if someone can help.

 

My database has records with 4 fields, one named 'active'.

 

How do I add a record, that first checks the database to see if any of the fields named 'active' have a value of '1' and if it does, does not insert the record, but if none of the records have a value of '1' it will insert it.

 

Does any of the above make sense? If so, can anybody help?

 

Thanks

Donkeyfourthumbs

You can use this code, but modify table_name to the name of your table.

The first query checks if any record has an active value of 1, as soon as 1 result is found the query ends.

You then check to see if any rows were returned. If 0 rows (no rows have active=1) then you can perform the insert, otherwise you ca display an error message or something.

 

//select rows from the table which have active value of 1
$qry = "SELECT * FROM table_name WHERE active='1' LIMIT 1;
$res = mysql_query($qry);
if(mysql_num_rows($res)==0) {
 //perform your insert here
}
else {
 //print an error or something?
}

Thank you MrBrightside.

 

Too much time in front of a computer today, will try first thing tomorrow, but it does make sense and looks like it will work.

 

Thanks

Donkeyfourthumbs

Once again, thanks MrBrightside, it does work and does do what I asked for, but i just thought of something!

 

For example, if the database has the following 2 records.

 

Company1 Contact1 "Some Nice Words" 'active=1'

 

Company2 Contact2 "Very Nice Words" 'active=0'

 

If i want to add a new record, I want it to check the 'active' value I am submitting. If it is 0 then the record can be added, if it is 1 then I want to return a message.

 

At the moment, if any of the records return 1, then it won't Insert.

 

Does that make sense?

 

Donkeyfourthumbs

I'm not sure if I get what you mean...

At the moment it checks if anything in the DB is active, if true then it won't insert.

Do you still want that database check, or are you just wanting to check the active value of the record you're submitting?

It needs to do both thinking about it.

 

If the record being submitted is active and a record already inserted is active, then it needs to return with an error.

 

If the record being submitted is false then it should insert even if a record already submitted is true.

 

Even I'm getting confused!!!

 

Does the above make sense?

 

DFT

I kind of get what you mean now, basically:

an active row can be added if no active rows already exist

an inactive row can be added at any time

 

So on your insert form, say you have a dropdown called active, with Yes as value=1, and No as value=0

$caninsert = false; // this a flag which keeps track of whether the insert can be made or not.
//if inserting an active row, make sure no active records already exist.
if($_POST['active']==1) {
$qry = "SELECT * FROM table_name WHERE active='1' LIMIT 1;
$res = mysql_query($qry);
if(mysql_num_rows($res)==0) {
	$caninsert = true;
} 
}
//active value for new record is false so we can insert.
else {
 $caninsert = true;
}

//see if we can insert, if so then do it, otherwise print an error or something
if($caninsert) {
 //perform your insert here
}
else {
 //output any errors that you want here
}

Perfect, works like a dream.

 

$qry = "SELECT * FROM table_name WHERE active='1' LIMIT 1;

 

the above just needed an " after LIMIT 1, but apart from that, does what I need it to do.

 

Thanks for your help.

 

DFT

No problem! Good luck with your website