Mysql query not collect data/ check if the email is already in the db

Mysql query not collect data/ check if the email is already in the db
0

#1

Hi guys. I have a question. What’s wrong with this query? I have table newsletter_subscribers and column sub_email and it has the same email as I collected into $email_data.

$email_data = $_POST['email-address']; 
$result  =mysql_query("SELECT sub_email 
FROM newsletter_subscribers WHERE sub_email='$email_data'");

$result collects nothing


#2

I’m giving you hints below, instead of solving it for you, okay?

$email_data is your variable
If you do ‘$email_data’ (with the quote marks), then you’re specifically looking for that value in your column.
What you need to do is build up your query string so you’re passing the value of $email_data instead of passing ‘$email_data’ in your SQL query.
So you’ll need to do some string concatenation in building up your SQL query.


#3

Unfortunately I didn’t figure it out. Don’t know the answer


#4

From the code you supplied, it’s almost impossible to determine where your problem lies.

I guess you’re using PHP, here’s example #2 from the PHP documents mysql_query page:

// Formulate Query
// This is the best way to perform an SQL query
// For more examples, see mysql_real_escape_string()

$query = sprintf("SELECT firstname, lastname, address, age FROM friends 
    WHERE firstname='%s' AND lastname='%s'",
    mysql_real_escape_string($firstname),
    mysql_real_escape_string($lastname));

If you modify your query, what do you get?

What error message, if any, do you get?

What is the value of $email_data?

Do you have a database connection?

Does the table and/or column exist in the database?

Does the corresponding row exist?

Do you get any data returned if you omit the where clause?

Please supply more details if you want your problem solved.


#5

I don’t understand how exactly to change my code with that one you give.
My goal is to check if this email is already in the database and if it is do not insert it again

The whole code looks like this.
$email_data is an email from the form
yes, connection is
yes, table, column and row are there
withous WHERE also nothing

<?php
require_once('../config.php');
define("PROJECT_ROOT", realpath("./"));

require_once(PROJECT_ROOT . '/functions.php');
$message_posted = false;

if(isset($_GET['status']) ) {
	if ($_GET['status'] == "message-posted") { $message_posted = true; }
}

$title = "Agaf Design - ".$lang['contact'];//change title to signup 
$meta_desc = "Contact Agaf Design Studio";

$banner_img="../images/site-images/banner-contact-img.jpg";// change banner signup

require_once('../header.php');
?>

<div class="contact-main-wrapper"> <!-- START OF FORM AND CONTACT -->
    <div class="contact-main-inner-wrapper">
        <div class="form-and-contact-main-wrapper">
            <div class="form-and-contact-inner-wrapper">
                <div class="container">
                    <div class="row">
                        <div class="form-main-wrapper clearfix"> <!-- START OF FORM MAIN WRAPPER -->
							<div class="about-us-main-wrapper">
							<?php
                                $hostname = "";
                                $username = "";
                                $password = "";
                                $dbname = "";

                                // Establish a connection with the db
                                $conn = new mysqli($hostname, $username, $password, $dbname);
                                // Check if connection was successful
                                if ($conn->connect_error) {
                                    die("Connection failed: " . $conn->connect_error);
                                } 

                                $email_data = $_POST['email-address'];//collecting email from form

                                $result = mysql_query("SELECT sub_email FROM newsletter_subscribers WHERE sub_email='$email_data'");
                                echo $result .'if the same<br/>';
                               
                    
                                    if(filter_var($email_data, FILTER_VALIDATE_EMAIL)){ // validating the email
                                        


                                        $sql_email = "INSERT INTO newsletter_subscribers (sub_email)VALUES ('" . $email_data . "')";
                                    }
                                    if ($conn->query($sql_email ) === TRUE) {
                                        echo "Thanks for signing up, you'll receive your first newsletter soon";
                                    } 
                                    else 
                                    {
                                        echo "Sorry, we couldn't add you to the newsletter because there is a problem with your email. Please check it carefully and then submit it again";
                                    }
                                
                                $conn->close();
							
                            ?>
                            </div>
                        </div> <!-- END OF FORM MAIN WRAPPER -->
                    </div>
                </div>
            </div>
        </div>
    </div>
</div> <!-- END OF FORM AND CONTACT --> 

<img id="preload" src="<?php echo $banner_img; ?>" style="display: none;" />
<?php
require_once('../footer.php');
?>

#6

Did you read the PHP manual page for mysql_query in the link I gave?


#7

sorry, I’m new to this topic and always have problem with creating those queries, Yes, I’ve read it before but it didn’t help me much. I think I just need a simple explanation.


#8

So a few concerns with your initial code.

  1. You are mixing mysqli and mysql library code, you need to use one or the other not both. I highly recommend using MySQLi as the escape string functions for mysql are not secure due to issues with character encoding (Full details out of scope of this answer - Curious? Read: https://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string).
  2. You are not properly escaping your input (PHP’s filter_var only goes so far). Use mysqli_real_escape_string to properly escape your input before inserting it into the database.

#9

I need to check if the email is already there(in db) and if it is do not insert it again so I think mysqli_real_escape_string maybe not very good because I want new email to be inserted, and all of them, new and all have the same $.


#10

Unfortunately you put your application at security risk without the escape string. This means all your customers emails could get leaked. This could lead to very angry customers and is a bad business decision. Put in the extra effort and escape your input.

Read: https://guide.freecodecamp.org/php/security/sql-injection/ for more information.