OK, what am I missing here....

[tombempty]

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:

$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.
}

[longneck]

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.

[tombempty]

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.

[taliesinnz]

Add a echo $query on the line before mysql_query, and post the output

[tombempty]

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');

[longneck]

what do you get when you run this query?

select count(*) from customer_table where email = ''

[web67]

did u tried running it on another server?

[tombempty]

what do you get when you run this query?

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

[taliesinnz]

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

 $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 {

[tombempty]

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

 $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?

[longneck]

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:

alter table customer_table
modify column email varchar(255) null default null
, add unique index (email)

fix your data:

update customer_table
set email = NULL
where email = ''

then rewrite your PHP code as follows:

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.

[taliesinnz]

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

if (mysql_num_rows($resultx) > 0 && trim($email) != '') {

[tombempty]

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