PHP/MySQL won't INSERT and echo as it should?

supraicer69 · March 15, 2011, 4:46pm

What the following script does (or should do):

Connects to DB
Includes a function for creating a 5 digit code
User enters their email address
Checks if it’s a valid email
Inserts the email into the ‘email’ column
Checks if email already exists, if so, let the user know and break script
Runs the funtion for creating a 5 digit code
Checks the ‘unique_code’ column if it already exists, if so, loop from 5 digit code creation function
If all is valid, hide the form and display the (ajax from a seperate JS) thank you div
Display the unique code to the user

Everything runs, however the unique_code is not inserted into the DB and is not displayed when it does “Thank you! <?php echo $unique_code;?>”.

What am I doing wrong and what needs to be modified?

Thank you!

Code

       <?php
        
        require "includes/connect.php";
        
        function generateCode($length = 5) {
    
        $characters = 'bcdfghjkmnpqrstvwxyz';
    
        $string = '';
        for ($i = 0; $i < $length; $i++) {
            $string .= $characters[rand(0, strlen($characters) - 1)];
        }
    
        return $string;
    
    }
    
    
    $msg = '';
    
    if($_POST['email']){
    	
    	// Requested with AJAX:
    	$ajax = ($_SERVER['HTTP_X_REQUESTED_WITH']  == 'XMLHttpRequest');
    	
    	try{
    		//validate email
    		if(!filter_input(INPUT_POST,'email',FILTER_VALIDATE_EMAIL)){
    			throw new Exception('Invalid Email!');
    		}
    		
    		//insert email
    		$mysqli->query("INSERT INTO coming_soon_emails
    						SET email='".$mysqli->real_escape_string($_POST['email'])."'");
    		
    		//if already exists in email column
    		if($mysqli->affected_rows != 1){
    			throw new Exception('You are already on the notification list.');
    		}
    		
    		if($ajax){
    			die('{"status":1}');
    		}
    		
    		//start creating unique 5 digit code
    		$unique_code = "";
    		$inserted = false;
    		
    		// Keep looping until we've inserted a record
    		while(!$inserted) {
    		
    		// Generate a code
    		$unique_code = generateCode();
    
    		// Check if it exists
    		if ($result = $mysqli->query("SELECT unique_code FROM coming_soon_emails WHERE unique_code = '$unique_code'")) {
    		
    		// Check no record exists
    		if ($result->num_rows == 0) {
    		
                // Create new record
                $mysqli->query("INSERT INTO coming_soon_emails (email,unique_code) VALUES ('" . $mysqli->real_escape_string($_POST['email']) . "','$unique_code')");
    		
                // Set inserted to true to ext loop
                $inserted = true;
    		
                // Close the result object
                $result->close();
    		
    		}
    		} else {
    		
    		// Quit if we can't check the database
    		die('Something went wrong with select');
        }   
    }
    
    	}
    	
    	catch (Exception $e){
    		
    		if($ajax){
    			die(json_encode(array('error'=>$e->getMessage())));
    		}
    		
    		$msg = $e->getMessage();		
    	}
    }
    ?>
    
    
    <!DOCTYPE html>
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>example</title>
    
    <link rel="stylesheet" type="text/css" href="css/styles.css" />
    
    </head>
    
    <body>
    
    <div id="container">
        
        <form id="form" method="post" action="">
        	<input type="text" id="email" name="email" value="<?php echo $msg?>" />
            <input type="submit" value="Submit" id="submitButton" />
        </form>
        
        <div id="thankyou">
        Thank you! <?php echo $unique_code;?></p>
    	</div>
    
        
    </div>
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.3/jquery.min.js"></script>
    <script src="js/script.js"></script>
    </body>
    </html>

tangoforce · March 15, 2011, 5:47pm

I’m sorry to say this but what you’re doing to check your 5 digit code exists is horrendous.

What happens when you’ve got 30,000 records in that table and you’re looping through it to see if a code exists? - A better way would be to use an auto_increment column which starts from 10000. Every time your script runs, input a new blank record and then use the mysql_insert_id() function to get the auto_increment number for the new record.

Thats how my barcode system has been working.

supraicer69 · March 15, 2011, 5:51pm

Thanks tangoforce - it won’t have that many records. We expect 500 or less.
Thanks for the advice though -much appreciated.

Do you have any clue as to why my code isn’t inserting the unique_code into the DB, nor outputting via echo?
Anything I’m missing or have mistaken?

Thanks again!

tangoforce · March 15, 2011, 6:15pm

Nope, not a clue.

supraicer69 · March 15, 2011, 6:17pm

thanks anyway. hopefully someone else can find the ssue…

tangoforce · March 15, 2011, 6:25pm

To be honest someone else shouldn’t need to.

Have you tried debugging it? - Here is a present for you:


//At top of your script place this:
$Debug = 0; //Change to 1 to use debugging

function debug($Input)
   {
   if ($Debug)
      {
      print "<pre>\
";
      var_dump($Input);
      print "</pre>\
";
      }
   }

//code to debug - use debug() anywhere you need to check a $Variable.
$Value = 'test';
debug($Value);

I’ve had to think up, theorize and test many ways of debugging some of my scripts (very awkward to debug filestream downloads)… and I do this purely as a part time hobby. If I can test/debug your code so can you.

supraicer69 · March 15, 2011, 6:29pm

placed it at the top, set debug to 1… then did…

// Generate a code
$unique_code = generateCode();
[B]debug($unique_code);[/B]

No dump. I have no idea what’s going on…

tangoforce · March 15, 2011, 6:32pm

Also your html shows echo $msg?> - with no semicolon.

supraicer69 · March 15, 2011, 6:34pm

nice pick up fixed - thanks!
however, still not working…

tangoforce · March 15, 2011, 6:41pm

Have you used my debugging code to test:

The value of $_POST[‘email’]
The value of $unique_code
That generatecode() is working
That the variables are still set just before exiting php into html

?

Somehow I doubt you’ve fully tested your code yet.

tangoforce · March 15, 2011, 6:43pm

Also you’ve got 2 SQL statements which try to put a email address into coming_soon_emails. If that column is unique and you’re trying to insert a matching email address then its going to fail on that 2nd SQL insert statement.

tangoforce · March 15, 2011, 6:46pm

Sorry just noticed this…

In that case it looks like generateCode() isn’t working. Try debugging it…

aamonkey · March 15, 2011, 6:50pm

tangoforce:


//At top of your script place this:
$Debug = 0; //Change to 1 to use debugging

function debug($Input)
   {
   if ($Debug)
      {
      print "<pre>\
";
      var_dump($Input);
      print "</pre>\
";
      }
   }

//code to debug - use debug() anywhere you need to check a $Variable.
$Value = 'test';
debug($Value);

That won’t work as you wrote it unless you global $Debug. Maybe you should debug your debug code

supraicer69:
I glanced over your code pretty quickly, but what stands out is you are attempting to insert the email row twice. Once with:

$mysqli->query("INSERT INTO coming_soon_emails 
                            SET email='".$mysqli->real_escape_string($_POST['email'])."'");

and a second time with this:

$mysqli->query("INSERT INTO coming_soon_emails (email,unique_code) VALUES ('" . $mysqli->real_escape_string($_POST['email']) . "','$unique_code')");

I don’t understand why you’re inserting it the first time without the $unique_code var set, plus you are not using valid SQL so to my eye that show cause an exception to be thrown every time from:

if($mysqli->affected_rows != 1){ 
                throw new Exception('You are already on the notification list.'); 
            }

supraicer69 · March 15, 2011, 6:56pm

Thanks aamonkey.

I have removed:

$mysqli->query("INSERT INTO coming_soon_emails SET email='".$mysqli->real_escape_string($_POST['email'])."'");

How can I fix the thrown exception error you pointed out?

tangoforce · March 15, 2011, 7:04pm

Whoops, thanks for that. @glacier: before the if($Debug) line put this: global $Debug;

aamonkey · March 15, 2011, 7:06pm

Since you are removing that line, you can remove the lines of code beneath

//if already exists in email column 
            if($mysqli->affected_rows != 1){ 
                throw new Exception('You are already on the notification list.'); 
            }

supraicer69 · March 15, 2011, 7:14pm

Updated code doesn’t insert the email or unique_code into the DB

updated code:

<?php

require "includes/connect.php";

        function generateCode($length = 5) {
        $characters = 'bcdfghjkmnpqrstvwxyz';
        $string = '';
        for ($i = 0; $i < $length; $i++) {
            $string .= $characters[rand(0, strlen($characters) - 1)];
        }
        return $string;
    }

$msg = '';

if($_POST['email']){
	
	// Requested with AJAX:
	$ajax = ($_SERVER['HTTP_X_REQUESTED_WITH']  == 'XMLHttpRequest');
	
	try{
		if(!filter_input(INPUT_POST,'email',FILTER_VALIDATE_EMAIL)){
			throw new Exception('Invalid Email!');
		}
		
		if($ajax){
			die('{"status":1}');
		}
		
		$unique_code = "";
		$inserted = false;
		
		// Keep looping until we've inserted a record
	while(!$inserted) {
    // Generate a code
    $unique_code = generateCode();

	
    // Check if it exists
    if ($result = $mysqli->query("SELECT unique_code FROM coming_soon_emails WHERE unique_code = '$unique_code'")) {
        // Check no record exists
        if ($result->num_rows == 0) {
            // Create new record
            $mysqli->query("INSERT INTO coming_soon_emails (email,unique_code) VALUES ('" . $mysqli->real_escape_string($_POST['email']) . "','$unique_code')");
            // Set inserted to true to ext loop
            $inserted = true;
            // Close the result object
            $result->close();
        }
    } else {
        // Quit if we can't check the database
        die('Something went wrong with select');
    }   
}

// Output the code
//echo $unique_code;


	}
	
	catch (Exception $e){
		
		if($ajax){
			die(json_encode(array('error'=>$e->getMessage())));
		}
		
		$msg = $e->getMessage();		
	}
}
?>

aamonkey · March 15, 2011, 7:46pm

your try/catch block is messed up, you were also catching an exception but then not doing anything with it.

I’ve modified your code to die with the exception error message if one gets thrown for debugging purposes:

<?php

require "includes/connect.php";



function generateCode($length = 5)
{
    $characters = 'bcdfghjkmnpqrstvwxyz';
    $string = '';
    
    for ($i = 0; $i < $length; $i++) {
        $string .= $characters[rand(0, strlen($characters) - 1)];
    }
    return $string;
}


$msg = '';


if($_POST['email']) {
    
    // Requested with AJAX:
    $ajax = ($_SERVER['HTTP_X_REQUESTED_WITH']  == 'XMLHttpRequest');
    

    try {
    
        if(!filter_input(INPUT_POST,'email',FILTER_VALIDATE_EMAIL)){
            throw new Exception('Invalid Email!');
        }
    
        if($ajax){
            die('{"status":1}');
        }

        $unique_code = "";
        $inserted = false;

        // Keep looping until we've inserted a record
        while(!$inserted) {
        
            // Generate a code
            $unique_code = generateCode();
    
            // Check if it exists
            if ($result = $mysqli->query("SELECT unique_code FROM coming_soon_emails WHERE unique_code = '$unique_code'")) {
            
            // Check no record exists
            if ($result->num_rows == 0) {
                
                // Create new record
                $mysqli->query("INSERT INTO coming_soon_emails (email,unique_code) VALUES ('" . $mysqli->real_escape_string($_POST['email']) . "','$unique_code')");
                
                // Set inserted to true to ext loop
                $inserted = true;
                
                // Close the result object
                $result->close();
            }
        }
        
    } catch (Exception $e){

        if($ajax){
            die(json_encode(array('error'=>$e->getMessage())));
        }

        $msg = $e->getMessage();
        die($msg);
    }      

} else {
    // Quit if we can't check the database
    die('Something went wrong with select');
}   



?>

supraicer69 · March 16, 2011, 1:22am

Thanks aamonkey!

Updated code, however it errors with;

Parse error: syntax error, unexpected T_CATCH in… on line 65

line: } catch (Exception $e){

supraicer69 · March 16, 2011, 1:52am

Another } was needed before the catch line.
Now it errors with:

“Something went wrong with select”

Topic		Replies	Views
Unique code based on users unique email address inserted into mysql table? PHP	6	4552	March 15, 2011
Adding random number into DB issue PHP	5	557	March 18, 2011
Help with this MySQL tutorial please Databases	2	417	March 18, 2011
Unique mysql record PHP	12	4087	December 14, 2014
Check email against a database PHP	23	1792	November 15, 2010

PHP/MySQL won't INSERT and echo as it should?

Related topics