[SOLVED] Checking to see if information already exists in a database

[leest]

HI,

 

I want to run a query to see if information already exsits in my databse.  If it does I want to add to the entry and if it doesn't i want to add the information to the database.

 

I know how to up date the database and insert the data but i am not to sure how to check the database to see if the data already exists.

 

I read a previous posting and used the code from that but it didn't work and was wondering if somebody could push me in the right direction, the code i used from the previous posting is below along with the error.  Thanks

 

$query = "SELECT data FROM table1 WHERE answer = '$answer'";
$result = mysql_query($query);
$num = mysq_num_rows($result); //will be either 0 if its not there or more than zero if it is


if($num > 0) { //checks for an entry  

 

Below is the error, line 458 referes to the  result = mysql line.

 

"Fatal error: Call to undefined function: mysq_num_rows() in /home/sites/harrier-recruitment.com/public_html/search/mail_reg_demo.php on line 458"

 

any help would be appreciated

[teng84]

mysq_num_rows  should be mysql_num_rows

[leest]

Hi Thanks just made that change but it still produces this error,

 

"Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/sites/harrier-recruitment.com/public_html/search/mail_reg_demo.php on line 458"

[teng84]

that means your query failed that is why you're not passint the right resource .do check you query ...

[pocobueno1388]

Try to catch the error by changing the query line to this

$result = mysql_query($query)or die(mysql_error());

[leest]

Thanks for that by changing that code around it highlighted that i had placed an s on the end of my table name, once i removed that it worked fine.

 

Thanks for your help

[leest]

Hi thanks for your help, a little question again, this runs perfect now though it doesn't run when i tried to make the database check two fields of the database, this is the code as i am using it and the error I get, again any advice is appreciated.

 

$user = $_SESSION['user'];
$SID = $_SESSION['SID'];
$query = "SELECT username, site FROM table WHERE user = '$user AND site ='$site'";
$result = mysql_query($query);
$num = mysql_num_rows($result); //will be either 0 if its not there or more than zero if it is
if($num > 0) { //checks for an entry

 

 

Error Message is as follows:

 

"Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/sites/harrier-recruitment.com/public_html/search/mail_reg_demo.php on line 460

[pocobueno1388]

I do see an error in your query, but also you need to always use mysql_error()

 

$query = "SELECT username, site FROM table WHERE user = '$user' AND site ='$site'";
$result = mysql_query($query)or die(mysql_error());

[leest]

Hi, thanks I just noticed the error i have added the line of code that you mentioned and it is all working now, thanks

[leest]

Ok problems again, this script works perfect providing that i am only trying to check one field within the database say User, when i add another field like site, the code just skips to the second part of the if else statement.

 

Below are the two versions of code the first works and returns the value i need the second just skips to the second part of the if else statement.

 

I was wondering if it is because the

 if[$num > 0) { 

is aimed at only one field and if i need to change this two give results on two fields from the table. 

 

Code one this works perfectly

 

$user = $_SESSION['user'];

$query = "SELECT username FROM candidate WHERE user ='$user'";

$result = mysql_query($query);
$num = mysql_num_rows($result); //will be either 0 if its not there or more than zero if it is


if($num > 0) { //checks for an entry

echo "$num";

} 

else 
{ 

Thank You your Application has been submitted you can track your application through your account

 } 

 

 

The second code is as follows, it doesn't return any errors it just skips to the second part of the if else statement, even when it shouldn't.

 

$user = $_SESSION['user'];
$SID = $_SESSION['SID'];

$query = "SELECT username FROM candidate WHERE user ='$user' AND site ='$SID'";

$result = mysql_query($query);
$num = mysql_num_rows($result); //will be either 0 if its not there or more than zero if it is


if($num > 0) { //checks for an entry

echo "$num";

} 

else 
{ 

  Thank You your Application has been submitted you can track your application through your account

 } 

 

Again any advice or direction would be appreciated.

 

Thanks

 

 

[pocobueno1388]

Try this

 

<?php

$user = $_SESSION['user'];
$SID = $_SESSION['SID'];

$query = "SELECT username FROM candidate WHERE user ='$user' AND site ='$SID'";

$result = mysql_query($query)or die(mysql_error());
$num = mysql_num_rows($result); //will be either 0 if its not there or more than zero if it is


if($num > 0) { //checks for an entry

   echo "There are $num results";

} else { 

   echo "Thank You your Application has been submitted you can track your application through your account";

} 

?>

 

Tell us what it says.

[leest]

Hi, Thanks

 

I run the script and it showed that the information was already in the database, i'm gonna have a look at the code now and see the difference and then try to add a query to insert info if it isn't there.

 

cheers

 

Lee

[leest]

Ok, I have managed to get the script working, but as true to form for me when i try the next step i break it.  In the code below you will see i have added a query instead of  displaying the result of the

 echo "$num"; 

 

Now I have done this the query doesn't seem to work. I am wondering if i should have a longer else if statement, as this just doesn't seem right as it leads from one query straight in to another, no errors are generated it just seems to run through the query and do nothing.  I have double checked my databases and the information i am trying to retrieve is there and so i am a bit lost.

 

My code is:

 

 
$username = $_SESSION['username'];
$SID = $_SESSION['SID'];

$query = "SELECT username FROM candidate WHERE username ='$username' AND site_id ='$SID'";
$result = mysql_query($query)or die(mysql_error());
$num = mysql_num_rows($result); //will be either 0 if its not there or more than zero if it is
if($num > 0) { //checks for an entry

include '../table1/folder/test.php';
$username = $_SESSION['username'];

$query = "SELECT username FROM candidate WHERE username ='$username'"; 

$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result) or die(mysql_error());

echo $row['candidate_id'];


} else { 

   echo "Thank You your Application has been submitted you can track your application through your account";

} 

 

 

[fenway]

Just joining in now... which part doesn't work?

[leest]

Hi, Thanks the first part works perfectly, but when it gets past the

 

 if($num > 0) { //checks for an entry 

 

It doesn't run the second query or it doesn't return the value of the candidate_id.

 

I have checked the database and the value is there, and no errors are returned.

 

The script also works that if the information isn't there it skips to the else part and works perfectly.

 

 

 

 

[fenway]

It can't not run the 2nd query... you sure?

[leest]

Hi,

 

YEah I know that's what didn't make sense in the end i gave up trying to find out why, re wrote it and now it's working, so i think i will leave it alone,

 

Cheers

 

Lee