Help with checking if data exists in database

[TheMonk]

Hi,

I'm trying to do something which should be pretty simple. I'm creating a login system and I'm currently working on the admin pages. The page I'm working allows the admin to enter information about the client, when submitted it needs to check the DB and see if the client email and company name already exist.

That's where I'm running into problem and I'm just banging my head against the wall....

Here's my code. (Bear in mind this is my first PHP project alone so be nice )

	$emailExists = "SELECT email FROM access_table WHERE email=" . $_POST['email'] . "";
	$emailResult = @mysql_query($emailExists, $conn) or die('Could not look up information; ' . mysql_error());
	
	$clientExists = "SELECT ct.company_name FROM company_table WHERE company_name='" . $_POST['company_name'] . "'";
	$clientResult = mysql_query($clientExists, $conn) or die('Could not look up information; ' . mysql_error());
	
	if ((!mysql_num_rows($clientResult)) && (!mysql_num_rows($emailResult))){

Now if the client info doesn't exist insert the client info into the dbs...

alright php gurus lay into me!

[TheMonk]

Btw here's the error I get:

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in C:\Program Files\xampp\htdocs\FaQtory_clients\add_client.php on line 29

Line 29 is this one:
$emailResult = mysql_query($emailExists, $conn) or die('Could not look up information; ' . mysql_error());

Thanks

[PragyaRatna]

Try the following query

SELECT	
CASE WHEN 
(SELECT COUNT(*) FROM access_table WHERE 
email = '$email' > 0 
THEN 'ERROR'
ELSE 'OK'
END AS email_status,

CASE WHEN 
(SELECT COUNT(*) FROM company_table WHERE 
company_name = '$companyname') > 0 
THEN 'ERROR'
ELSE 'OK'

END AS company_status

If the query results in 'ERROR' , show that there is already another record with the inputted data.

[TheMonk]

Hmm I'm not sure if I'm doing something wrong. I still get the same error:
Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in C:\Program Files\xampp\htdocs\FaQtory_clients\add_client.php on line 30

Line 30 is the line that contains the actualy mysql_query.

Here's my code with the modifications you suggested:

$email = $_POST['email'];
	//$emailExists = "SELECT client_id FROM access_table WHERE email='$test'";
	$emailExists = "SELECT CASE WHEN (SELECT COUNT(*)) FROM access_table WHERE email = '$email' > 0 THEN 'ERROR' ELSE 'OK' END AS email_status";
	$emailResult = mysql_query($emailExists, $conn) or die('Could not look up information; ' . mysql_error());

I definetly have alot to learn when it comes to MySql queries.

[PragyaRatna]

Is the $conn variable defined on this page? The problem could have been caused due to connection problems with the DB.

[TheMonk]

I have it included as a require once include at the top of the page:

<?php

define('SQL_HOST','localhost');
define('SQL_USER','root');
define('SQL_PASS','');
define('SQL_DB','comp_client');

$conn = mysql_connect(SQL_HOST, SQL_USER, SQL_PASS) or die('Could not connect to the database; ' . mysql_error());

mysql_select_db(SQL_DB, $conn) or die('Could not select database; ' . mysql_error());

?>

The login page uses this same include and it's working fine...

[TheMonk]

I'm making these database calls from within a custom function, it's my understanding that they should work fine whether they are in a function or not. But I figured I'd mention it in case that could cause problems.

[PragyaRatna]

What does the following return?

 var_dump($conn); die(); 

[TheMonk]

It returns null...

if I add:

global $conn;

Before adding that line you told me I get: resource(3) of type (mysql link)

I'm acutally considering breaking this code into multiple pages. I was trying to get this process working on one page. I know it can be done but I think for my level of experience it might make things easier to break it down.

If you have any other ideas let me know I might still try them. For now it's getting late on my little part of the planet and I must sleep.

Thanks for your help! Even if I didnt fix my problem (yet) I've learnt some more.

[PragyaRatna]

Also if you are using a custom function to connect to the database, you could verify where or not this function returns a proper mysql link to the database.

<?php
    define('SQL_HOST','localhost:3306');
    define('SQL_USER','root');
    define('SQL_PASS','password');
    define('SQL_DB','DB');
    
 function connect()  {

  $link   = mysql_connect(SQL_HOST, SQL_USER, SQL_PASS);
  $conn = mysql_select_db(SQL_DB,$link);
  return $link;
  }
  
  $connection = connect();
  var_dump($connection);
  
  $sel = "select FIELDNAMES from TABLE";
  $rs = mysql_query($sel,$connection) or die(mysql_error());
  
  var_dump($rs);
?>

[Cups]

Not taking away from these ppl trying to help you with your connection problem but did you know you can force the database to enforce a unique rule?

So it will not allow you to enter an email address which is already in there.

Make the email column UNIQUE and mysql will return a 1062 error if you try and submit an email which is already stored in there.

heres how to add a unique index:
http://www.sitepoint.com/article/opt...ql-application

this article shows how to deal with mysql errors nicely
http://www.onlamp.com/pub/a/php/2004...undations.html

I know this may not be the solution for you, but depending on your POV, knowing when to use Mysql to do the work is always a parallel learning process!

HTH