[RESOLVED] Check if exist

[SoulAssassin]

I have a form that adds a room to the database.
This form inserts the site_id and room_number.

But before it inserts, I need to check if that room number already exist in that site.
I tried this code below, but it can only check room_number of all sites.
It needs to check that a specific site, doesn't have duplicate room_number.

<? 
session_start();
include "config.php";

if ($add)
{
$self = $_SERVER['php_self'];
$room_id = $_POST['room_id'];
$site_id = $_POST['site_id'];
$room_number = $_POST['room_number'];

// Check if room exist
$check = mysql_query("Select * from room",$con);
$checking = mysql_num_rows($check);

if ($row['room_number'] == '$room_number' AND )
{
echo "Already a room '$room_number'";
}
else{
// Check ends

// Insert New Room
$rooms = mysql_query("INSERT into room (site_id, room_number)
values ('$site', '$room_number')",$con);
if ($rooms)
{
echo "Room Added";
}
else 
{
echo "Failed";
}
}
?>

Any suggestions on a better way to check this?

[esukf]

try

/ Check if room exist
$sql = "Select * from room WHERE site_id=%d AND room_number=%d";
$sql = sprintf($sql, 
            mysql_real_escape_string($site_id),
            mysql_real_esacpe_string($room_number)
          );
$check = mysql_query($sql,$con);
$num_rows = mysql_num_rows($check); 

if($num_rows > 0)
{
  echo "Already a room '$room_number'"; 
} else {
  //.........
}

[Piranha]

You should do that in the database rather than in PHP code. Using the database to do this check is both safer since you will never forget the check and safer since there is no way it could add two rooms with the same room number in the same site. It could easily be that you check if the room exists, get the result that it doesn't and then add it. But between your check and the actual insert of a room it could have been added by another process.

Instead use UNIQUE (site_id, room_number) in the database, then you will get an error when trying to insert where it already exists.

More than that I have to say that I don't understand how you code. I find the following strange things in your code:

1. You don't use [man]mysql_real_escape_string[/man] to make sure that the code is safe for database injection.

2. You have an if-statement that ends with AND. Very strange since there is nothing to evaluate after the AND.

3. In the if-statement you have "$row['room_number']". But $row is not set anywhere.

4. You insert the variable $site into the database, but it haven't been set yet.

5. You use the variable $checking to see how many rooms there are in the database. But you never use the variable for anything.

[SoulAssassin]

Thanks for the replies.
Piranha
I didn't post the form which gives the $site and took out lots of other pieces like the:

while($row = mysql_fetch_array($check, MYSQL_ASSOC)){

The "AND" was also posted incorrectly as I tried:

 AND $row['site_id'] == '$site')

and didn't remove it properly from the code before posting.
I can understand how my code looks a bit screwed.
Appologies for not posting it correctly.

I can have multiple site_id and multiple room_id in the database,
But not entries with the same site_id AND room_id.
like this is correct:
Site_id: Room_id:
1 1
2 1
3 2
2 3

this is incorrect:
Site_id: Room_id:
1 1
2 1
3 2
2 1

I don't understand how "UNIQUE" can do it in the Database, as it has to check more than one field. I will try the method that "esukf" suggested.

Sorry for the messy code.

[Piranha]

Then I understand the messy code.

You can of course do it as esukf suggests, and it will work relatively good. But there is room for error with that approach. I was going to write something like "if you don't understand it then read up on it", but since I didn't find anything with a quick search I guess that the manual doesn't have enough information on it. So here is a crash course to UNIQUE:

The command UNIQUE makes sure that everything inside the paranthesis is unique together. Let's say that you have a table with id, username and mail and that id is the primary key. You decide that you don't want duplicate usernames, so you add the following:

UNIQUE (username)

That makes it impossible for two users to have the same username. If you don't want the users to be able to have the same username or mail then you would add:

UNIQUE (username),
UNIQUE (mail)

Then it checks that the username doesn't exist before and that the mail address doesn't exist before. If you instead want to make sure that noone have the same combination of username and mail you do this:

UNIQUE (username, mail)

After a while you have the following table:

id    username    mail
1     Piranha     [e-mail]
2     SoulAssassin  [e-mail]
3     esukf       [e-mail]
4     TheOnlyPiranha  [e-mail]

Then I try to join. I first try to use Piranha and [e-mail], and get an error message since it is unique. Then I try to use Piranha and [e-mail], and it works since it is the combination that is unique. I could even have used Piranha and [e-mail]. Both values are already taken, but because there is not one row with both values it is possible.

I suggest that you try it out with a few quick examples, you will understand it pretty quick.

[SoulAssassin]

Thanks esukf
That was exactly what I was looking for.
I obviously over complicated it for myself.

Once again sorry for my messy code, I just thought I'd make the read shorter.

Resolved

[SoulAssassin]

Piranha
Thanks for that, because I didn't know it.
I will try it out, and see how it works.
How would the error then get echo for the user, if it's a sql error?

I prefer to rather write it in PHP, than in MYSQL (bacause my DB knowledge is not good), is that the wrong approach?
I understand that it's saver to make the DB reject the insert.

But thanks for the tip, I will surely try it out.

[Piranha]

It is safer to do in the DB since there is a chance that in the milliseconds between the check and the insert someone else have already inserted it. Now you might say that it is not a big risk that it is going to happen. True, but Murphy's law always work against programmers: "If anything can go wrong, it will". If it happens, how do you handle it? It can of course be handled, but it is much more complicated to write the code to handle it than it is to check it in the database.

Let's say that you have prevented the problem above in PHP. Then another problem is what happens in 3 years when the code should be updated or the database should be kept but the site should move to Java or another language. It may even be a new programmer that should do this. And of course he will forget to add a couple of those little things (I assume that if you work this way you have lots of other workarounds as well) and there will be a problem in the code. With it in the database the programmer would probably have a general database error handling that takes care of errors that is not thought of, and that will jump in when these kind of things occur.