SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Guru tombempty's Avatar
    Join Date
    Oct 2001
    Location
    New York
    Posts
    688
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    OK, what am I missing here....

    Hello All:

    I have a DB collecting information from a form fine. However when no one enters an email nothing goes into the DB.

    I thought i found the issue when I had the query for email set to unique, because then if another blank one would try to go in, it would not, made sense right.....

    Nope, I set the NULL to YES and took out the UNIQUE requirement but it still will not go in if that field is blank or if the email is already in the DB.

    Is there something that i am missing or that I could look for.

    I am running MySQL - 4.1.20

    Here is the php code for that query:

    Code:
    $today = date("F j, Y, g:i a"); // Date Function
            //Define the Query
            $queryx = "SELECT email FROM customer_table WHERE email='$email' LIMIT 1"; // Check if the email exists in the table
            $resultx = mysql_query($queryx); 
            if (mysql_num_rows($resultx) > 0) { 
                $message =  '<p><font color="red">Please use a different email address.</font></p>'; 
            } else { 
                $query = "INSERT INTO customer_table "; // Insert into table 'customer_table'
                $query .= "(First_Name, Last_Name, Company_Name, Address_Line_1, City, State, Postal_Code, Country, Custom_Field_1, Custom_Field_2, Custom_Field_3, Optin, email, List_Array, Parent, Date) ";
                $query .= "VALUES ('";
                $query .= mysql_escape_string($First_Name) . "', '";
                $query .= mysql_escape_string($Last_Name) . "', '"; // Last_Name is a unique field; the same email address will not be written to the table
                $query .= mysql_escape_string($Company_Name) . "', '";
                $query .= mysql_escape_string($Address_Line_1) . "', '";
                $query .= mysql_escape_string($City) . "', '";
                $query .= mysql_escape_string($State) . "', '";
                $query .= mysql_escape_string($Postal_Code) . "', '";
                $query .= mysql_escape_string($Country) . "', '";
                $query .= mysql_escape_string($Custom_Field_1) . "', '";
                $query .= mysql_escape_string($Custom_Field_2) . "', '";
                $query .= mysql_escape_string($Custom_Field_3) . "', '";
                if ($Optin) { 
                    $query .= "1', '"; // Write a 1 if opted in
                } else { 
                    $query .= "0', '"; // Write a 0 if opted out
                }
                $query .= mysql_escape_string($email) . "', '"; // email is a unique field; the same email address will not be written to the table
                $query .= mysql_escape_string($trial) . "', '";
                $query .= mysql_escape_string($parentsite) . "', '";
                $query .= mysql_escape_string($today) . "');";
                
                //Execute the Query
                $result = mysql_query($query) or die(mysql_error()); // Run the query.
    }
    Last edited by tombempty; Apr 15, 2008 at 10:34. Reason: Forgot to add something...
    ********
    Regards,

    P

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    are you getting an error? because even when you had the UNIQUE constraint on the email column, it should have returned an error when you tried to insert a duplicate row.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SitePoint Guru tombempty's Avatar
    Join Date
    Oct 2001
    Location
    New York
    Posts
    688
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nope, no errors.

    I tried to echo it on the page to see what it returned but I would get an undefined error?

    That is impossible being that I have $query like 12 times on that page.
    ********
    Regards,

    P

  4. #4
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    365
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Add a echo $query on the line before mysql_query, and post the output

  5. #5
    SitePoint Guru tombempty's Avatar
    Join Date
    Oct 2001
    Location
    New York
    Posts
    688
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    when I put in NO email I get NO output, however when I put an email in the form field I get

    INSERT INTO customer_table (First_Name, Last_Name, Company_Name, Address_Line_1, City, State, Postal_Code, Country, Custom_Field_1, Custom_Field_2, Custom_Field_3, Optin, email, List_Array, Parent, Date) VALUES ('Jack', 'Hayford', '', '345 Chocolate Way', 'Hershey', 'PA', '56877', 'US', 'NETM08', '', 'None', '1', 'jack@hayford.com', 'Playing Cards, Custom, ', 'CustomPlayingCards', 'April 15, 2008, 4:44 pm');
    ********
    Regards,

    P

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    what do you get when you run this query?
    Code:
    select count(*) from customer_table where email = ''
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  7. #7
    SitePoint Zealot
    Join Date
    Apr 2008
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    did u tried running it on another server?
    Last edited by web67; Apr 15, 2008 at 19:12.
    Keep Up to date about everthing Website Flipping
    Find out how to same thousands on your Mortgage Broker fees.

  8. #8
    SitePoint Guru tombempty's Avatar
    Join Date
    Oct 2001
    Location
    New York
    Posts
    688
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    what do you get when you run this query?
    Code:
    select count(*) from customer_table where email = ''

    I get the following in PHPMyADMIN

    SQL query SQL query: SELECT count( * )
    FROM customer_table
    WHERE email = '' [ Edit ] [ Explain SQL ] [ Create PHP Code ] [ Refresh ]




    count(*) 1
    ********
    Regards,

    P

  9. #9
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    365
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not sure if i understand your question, Did you want a entry to be made even if there is no email entered?

    if so change to
    Code:
     $queryx = "SELECT email FROM customer_table WHERE email='" . mysql_real_escape_string($email) .  "' LIMIT 1"; // Check if the email exists in the table
            $resultx = mysql_query($queryx); 
            if (mysql_num_rows($resultx) > 0 || trim($email) == '') { 
                $message =  '<p><font color="red">Please use a different email address.</font></p>'; 
            } else {

  10. #10
    SitePoint Guru tombempty's Avatar
    Join Date
    Oct 2001
    Location
    New York
    Posts
    688
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by taliesinnz View Post
    Not sure if i understand your question, Did you want a entry to be made even if there is no email entered?

    if so change to
    Code:
     $queryx = "SELECT email FROM customer_table WHERE email='" . mysql_real_escape_string($email) .  "' LIMIT 1"; // Check if the email exists in the table
            $resultx = mysql_query($queryx); 
            if (mysql_num_rows($resultx) > 0 || trim($email) == '') { 
                $message =  '<p><font color="red">Please use a different email address.</font></p>'; 
            } else {
    YES! that is exactly what I want. I want the db to record the entry if the email field is blank or if it is filled in.

    For some reason though the DB will not record anything if that field is filled in.

    I tried your above code and changed mine accordingly but it still did not seem to insert it still.

    Anythoughts on this?
    ********
    Regards,

    P

  11. #11
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you code takes the email address supplied by the user, then stops if the email address is already in the database. if the user doesn't supply an email address, it's still gonna stop because there's already one address in the database with a zero-length string as an email address. a zero-length string is not the same as NULL.

    do you see that now?

    the correct way to handle this would be to first modify the table and fix the data, then fix your code.

    modify the table:
    Code:
    alter table customer_table
    modify column email varchar(255) null default null
    , add unique index (email)
    fix your data:
    Code:
    update customer_table
    set email = NULL
    where email = ''
    then rewrite your PHP code as follows:
    Code:
    1) build the insert query such that if $email = '' that you specify NULL instead.
    2) run the query.
    3) if you get error # 1062 from the query, then the email address is already in the database
    this code relies on the behavior that you can have as many NULL values as you want in a UNIQUE index.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  12. #12
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    365
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tombempty View Post
    YES! that is exactly what I want. I want the db to record the entry if the email field is blank or if it is filled in.

    For some reason though the DB will not record anything if that field is filled in.

    I tried your above code and changed mine accordingly but it still did not seem to insert it still.

    Anythoughts on this?
    Sorry I had my code wrong should be
    Code:
    if (mysql_num_rows($resultx) > 0 && trim($email) != '') {

  13. #13
    SitePoint Guru tombempty's Avatar
    Join Date
    Oct 2001
    Location
    New York
    Posts
    688
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok,

    thanks guys for all yoru help with this.

    I discovered a few bugs I have to work out first before I can get back on this.

    Thanks again,

    Paul
    ********
    Regards,

    P


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
  •