SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Zealot TheMonk's Avatar
    Join Date
    Oct 2005
    Location
    Canada
    Posts
    143
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with checking if data exists in database

    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 )

    Code:
    	$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!

  2. #2
    SitePoint Zealot TheMonk's Avatar
    Join Date
    Oct 2005
    Location
    Canada
    Posts
    143
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    SitePoint Zealot
    Join Date
    Mar 2006
    Posts
    139
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try the following query

    Code:
    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.

  4. #4
    SitePoint Zealot TheMonk's Avatar
    Join Date
    Oct 2005
    Location
    Canada
    Posts
    143
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    $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.

  5. #5
    SitePoint Zealot
    Join Date
    Mar 2006
    Posts
    139
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is the $conn variable defined on this page? The problem could have been caused due to connection problems with the DB.

  6. #6
    SitePoint Zealot TheMonk's Avatar
    Join Date
    Oct 2005
    Location
    Canada
    Posts
    143
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have it included as a require once include at the top of the page:

    Code:
    <?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...

  7. #7
    SitePoint Zealot TheMonk's Avatar
    Join Date
    Oct 2005
    Location
    Canada
    Posts
    143
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    SitePoint Zealot
    Join Date
    Mar 2006
    Posts
    139
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What does the following return?
    PHP Code:
     var_dump($conn); die(); 

  9. #9
    SitePoint Zealot TheMonk's Avatar
    Join Date
    Oct 2005
    Location
    Canada
    Posts
    143
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It returns null...

    if I add:
    Code:
    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.

  10. #10
    SitePoint Zealot
    Join Date
    Mar 2006
    Posts
    139
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 Code:
    <?php
        define
    ('SQL_HOST','localhost:3306');
        
    define('SQL_USER','root');
        
    define('SQL_PASS','password');
        
    define('SQL_DB','DB');
        
     function 
    connect()  {

      
    $link   mysql_connect(SQL_HOSTSQL_USERSQL_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);
    ?>

  11. #11
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •