Return an error if no suitable db entry found

[DevCore]

Hi,

I have a page where my users have to enter a day, a time, and some other info to be entered in to my database.. this bit is simple.

What I actually want to do is before the data is entered, check the database to make sure that the day and time submited is not already taken.

I know how to connect and gather info if the day and time rows are complete... but what if there's no match?

Any help would be great, thanks.

[DevCore]

Ok, I think i've worked it out.. well.. it works anyway, whether or not it's the correct way i'll leave for you to decide 😉

Here's what I did in the end...

$result = mysql_query("SELECT * FROM hardcore WHERE doc = '$day' AND toc = '$showtime'")
		or die(mysql_error());

	if(mysql_num_rows($result) == 1)

     echo "Sorry this slot is already taken";

 else 

mysql_query("INSERT INTO hardcore 
(djid, djname, doc, toc, title, description) 
	VALUES('$djid', '$djname', '$day', '$showtime', '$title', '$description' ) ") 
or die(mysql_error());

Like i say, it works... but is it right?

Any feedback is most welcome!

Thanks

[djjjozsi]

When you made the query, lets ask how many rows selected ( mysql_num_rows() )

i have this little function to make sql query from table name, and listed field names (with an added parameter if it is an integer, or a string, or a password)

$sql=sql_check("table_name","field1,field2+int,field3");
print $sql;  //just for testing lets print the sql query
$res=mysql_query($sql);
$num=mysql_num_rows($res);   //you store the rows number in $num.
if($num==0)
{
  echo "No match!";   //if there is no match
}
else
{
  echo $num." data found<br />";   //lets print the results.
}

function sql_check($table,$fields)
{
$sql="SELECT * FROM $table WHERE ";
$fields=explode(",",$fields); 
foreach($fields AS $var)
{
	$sl=explode("+",$var);
	$one_part=trim($sl[0]);
	$sec_part=trim($sl[1]);
		switch ($sec_part)
		{
			case "pass":
				$sql.="AND `$one_part`='".md5($_POST["$one_part"])."'";
			break;

		case "int":
			$sql.="AND `$one_part`='".(int)($_POST["$one_part"])."'";
		break;

		case "":
			$sql.="AND `$one_part`='".mysql_real_escape_string($_POST["$one_part"])."'";
		break;
	}
}
$sql=str_replace("WHERE AND","WHERE",$sql);
return($sql);
}

[NogDog]

Another option is to define a unique index for that table across the two fields ('doc' and 'toc'), then just do your insert query. If the insert query fails, check to see if it's a duplicate entry error (in MySQL check for mysql_errno() == 1062). This way you eliminate a query, plus you avoid possible timing issues between two separate but concurrent request for the same doc/toc.

[djjjozsi]

   if(mysql_num_rows($result) >0)   

//your results could be bigger then 1.

[DevCore]

Sorry im still very new to php/mysql on a whole.. can you enlighten me a little more please?

[DevCore]

djjjozsi;10898190 wrote:

//your results could be bigger then 1.

The result cant be bigger than one, as there is only one entry with that doc/toc.

[djjjozsi]

DevCore;10898192 wrote:

The result cant be bigger than one, as there is only one entry with that doc/toc.

if you use LIMIT in your query, or set 2 or more fields as UNIQUE , in these case only be sure the results won't bigger then 1.

as NogDog suggested set these 2 fields as UNIQUE (in pairs)

then check the mysql_errno() (but do not use die(mysql_error()))

[NogDog]

SQL:

ALTER TABLE hardcore ADD UNIQUE (doc, toc)

PHP:

$sql = "INSERT INTO hardcore (djid, djname, doc, toc, title, description)
        VALUES('$djid', '$djname', '$day', '$showtime', '$title', '$description' ) ";
$result = mysql_query($sql);
if($result == false)
{
   if(mysql_errno() == 1062)
   {
      echo "Sorry, this time is already taken.";
   }
   else
   {
      error_log(mysql_error());
      echo "Sorry, we could not complete your request. An error has been logged.";
      exit;
   }
}
else
{
   echo "Success message here....";
}

[djjjozsi]

@

is this duplicate check causes notices/warnings ?

[NogDog]

djjjozsi;10898198 wrote:

@

is this duplicate check causes notices/warnings ?

No.

(Not that it should matter, since you don't have display_errors enabled in your production version, right? 😉 )

[DevCore]

Thanks for your help guys, gave me a bit to get on with.

Pleased your dog is doing well NogDog, keep up the good work 😃

[djjjozsi]

NogDog;10898200 wrote:

No.

(Not that it should matter, since you don't have display_errors enabled in your production version, right? 😉 )

i keep it off. but lots of peaople might came into this (warning) problem. 🙂