Check form value against database values

PHP
1

Hi all,

I have a question to see if its possible before I go ahead and try anything.

I have a form and the very first option is to input the contract number.

I have been asked to put a little button to the right of this text area which before they continue to input the rest of the data, they wont to be able to click that button and it only checks the value of that field against all the other same values in the database, to see if it has been used before.

I cant put a form within a form can I, so is there some other way of doing this.

Cheers

2

Why not 2 forms?
One for the contract number. Submit the form to check if it has been used before.
If it has, show error message. If not, show the 2nd form with the other fields from the database.

Seems simple.

3

Ye good idea that…

Just needed to bounce the idea of someone first.

Cheers

4

Hi, Ive nearly got that workign, but just want to add some code now to check what I need.

I found this on the web, and need it to work with mssql, can somebody let me kow what bits need to change.


$result = mysql_query("SELECT * FROM links WHERE url = 'http://www.example.com/'", $conn);
if (!$result)
{
die('There was a problem executing the query');
}
$number_of_rows = mysql_num_rows($result);
if ($number_of_rows > 0)
{
die('This URL already exists in the database');
}
5

the query is fine, it will work in pretty much any database

because you’re only looking for existence of a row, you should replace the asterisk with a constant like 1 or NULL

6

Hi r937.

it was this line that i was most concerned with:


$number_of_rows = mysql_num_rows($result);
7

I changed it to what I think it should be:


$result = "SELECT 1 FROM Intranet WHERE Contract_Number=$cNumberCheck";
	if (!$result)
	{
		die('There was a problem executing the query');
	}
		$number_of_rows = sqlsrv_num_rows($result);
	if ($number_of_rows > 0)
	{
		die('This URL already exists in the database');
	}

Ran it and it came back with an error.

Warning: sqlsrv_num_rows() expects parameter 1 to be resource, string given in

8

perhaps what’s causing the error (i’m not sure, as i don’t do php) is that when the searched value isn’t in the table, nothing comes back, and sqlsrv_num_rows doesn’t return 0 like you’d expect it to

a workaround is to change the query to –

SELECT COUNT(*) AS rows FROM ...

and then instead of using the sqlsrv_num_rows function, just inspect the “rows” column of the result set (which will always exist)

9

OK, I changed tact and although the error gone, its still not working. I’m getting a bit of feedback though in the form of ‘Array’, even though I’m not asking for it.

here is my new code:


$strSQL = "SELECT * FROM Intranet WHERE Contract_Number = ".$cNumberCheck." ";


$objQuery = sqlsrv_query($conn, $strSQL) or die(sqlsrv_errors());;
$objResult = sqlsrv_fetch_array($objQuery);
if($objResult){
echo "CustomerID already exists.";
	} else {
echo "nothing";	
}
10

Na cant get anything to work at the moment.

Here is where I have got too, and seem to be going around in circles, getting the same error.


if(isset($_POST['btncheck']))
{
	echo "It works!";
	$cNumberCheck=$_POST['contractNumberCheck'];
	echo $cNumberCheck;	
	
$strSQL = "SELECT * FROM Intranet WHERE Contract_Number=$cNumberCheck ";
$objQuery = sqlsrv_query($conn, $strSQL);
$objResult = sqlsrv_fetch_array($objQuery);

if($objResult){
echo "CustomerID already exists.";
	} else {
echo "nothing";	
}
}

Error: Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in…

Have tried what cpradio has helped me with before, as before it was that I had a data type set to ‘Text’ but this time the data type is set to nvarchar(8)

11

Going back to your original thought

	$result = "SELECT 1 FROM Intranet WHERE Contract_Number=$cNumberCheck";
	if (!$result) 
	{     
		die('There was a problem executing the query'); 
	}  
		$number_of_rows = sqlsrv_num_rows($result);  
	if ($number_of_rows > 0) 
	{     
		die('This URL already exists in the database'); 
	}

In short, your error is because you never called sqlsrv_query()

	$result = sqlsrv_query($conn, "SELECT 1 FROM Intranet WHERE Contract_Number=$cNumberCheck");
	if (!$result) 
	{     
		die('There was a problem executing the query'); 
	}  
		$number_of_rows = sqlsrv_num_rows($result);  
	if ($number_of_rows > 0) 
	{     
		die('This URL already exists in the database'); 
	}

Also, make sure you run Contract_Number through intval() or something similar to ensure it is numeric and to prevent a SQL Injection

12

Morning cpradio,

Nice to hear from you again.

I put your code in:


$result = sqlsrv_query($conn, "SELECT 1 FROM Intranet WHERE Contract_Number=$cNumberCheck");
    if (!$result)
    {
        echo "Contract Number does not already exist.";
    }
        $number_of_rows = sqlsrv_num_rows($result);
		echo $number_of_rows."number of rows";
    if ($number_of_rows > 0)
    {
        echo "Contract Number already exists.";
    }
}

And I’m still getting this error.

Warning: sqlsrv_num_rows() expects parameter 1 to be resource, boolean given in

And whatever contract number I put in, whether its already there or not i get the same result.

Contract Number does not already exist

When you also said to make Contract_Number numeric, it isnt only numeric, as they all have 8 characters od letters and numbers.

I’m nearly there, just a tweek or two I reckon, but will crack on this morning, and see if I can get to the bottom of it.

Thanks again

13

I added single quotes around $cNumberCheck, which took away the error, but its not coming back with any result then of either exists or doesnt exist.


$result = sqlsrv_query($conn, "SELECT 1 FROM Intranet WHERE Contract_Number='$cNumberCheck'");
14

Can you show us the result of

var_dump("SELECT 1 FROM Intranet WHERE Contract_Number='$cNumberCheck'");

by placing that line directly above

$result = sqlsrv_query($conn, "SELECT 1 FROM Intranet WHERE Contract_Number='$cNumberCheck'");
15

Hi,

Yes i put a contract number that I know doesnt exist, which probably doesnt matter to you I suppose, but this is what I got back from it;

string(55) “SELECT 1 FROM Intranet WHERE Contract_Number=‘12345678’”

16

Okay, now put one that does exist and show me the output.

17

MMM.

string(55) “SELECT 1 FROM Intranet WHERE Contract_Number=‘DOM00076’”

There no change from one ot the other by the seems.

18

Okay, duh moment just occured


    $params = array();
    $options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
    $result = sqlsrv_query($conn, "SELECT 1 FROM Intranet WHERE Contract_Number='$cNumberCheck'", $params, $options);
    if (!$result) 
    {     
        die('There was a problem executing the query'); 
    }  
  
    $number_of_rows = sqlsrv_num_rows($result);  
    if ($number_of_rows > 0) 
    {     
        echo "Contract Number already exists."; 
    }
    else
    {     
        echo "Contract Number does not exist."; 
    }

Based on the PHP manual, the default cursor operation for SQL Server returns false for sqlsrv_num_rows instead of the value, so you have to change the cursor to SQLSRV_CURSOR_KEYSET
http://us2.php.net/manual/en/function.sqlsrv-num-rows.php

19

Oh thank you cpradio,

That worked brilliantly, and will go through it again and try and work out what you have done to get it working.

The scary thing is though once again is that I think I’m getting somewhere and then i get a problem, you work it out and I realise I’m still way off.

But thank you once again cpradio, you been fantastic.

20

You were close, in fact, very close, the key was the CURSOR and that the default CURSOR used against SQL Server will ALWAYS return 0/false for sqlsrv_num_rows. Once I updated your code to use a cursor that WOULD return the number of rows, your code worked fine :wink: