Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    Banned
    Join Date
    Sep 2011
    Posts
    140
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Wrong approach for running a database?

    Hi all,



    I am using MySQL for the first time.
    The reason I am using it is for it to serve as a database for my newsletter system.

    In a nut shell, a user on my website signs up by entering their e-mail address and selecting which newsletter their wish to receive (Mens, Mens & Womens, or Womens). PHP then inserts that e-mail address into the relevant list. Then when it comes to to sending out the newsletters, I use the list of e-mail's in my MySQL database.

    I have a database (testdb), a table (table1) and three rows (mens, mensandwomens, womens).
    +---------------+--------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +---------------+--------------+------+-----+---------+-------+
    | mens | varchar(100) | YES | | NULL | |
    | mensandwomens | varchar(100) | YES | | NULL | |
    | womens | varchar(100) | YES | | NULL | |
    +---------------+--------------+------+-----+---------+-------+
    When I use the command "select * from testdb.newsletters;", I get this:
    +-----------------------+-------------------+-------------------+
    | mens | mensandwomens | womens |
    +-----------------------+-------------------+-------------------+
    | test@test.com | NULL | NULL |
    | test2@test.com | NULL | NULL |
    | test3@hotmail.com | NULL | NULL |
    +-----------------------+-------------------+-------------------+
    What I want to do, is to delete the entry "test@test.com". How would I do this?

    ALSO, if I am going the wrong way about doing this, then I would love to hear peoples opinions.

  • #2
    New Coder
    Join Date
    Aug 2011
    Posts
    51
    Thanks
    6
    Thanked 12 Times in 12 Posts
    try
    Code:
    DELETE FROM testdb.newsletters
    WHERE mens=test@test.com

  • Users who have thanked ASTP001 for this post:

    Democrazy (09-07-2011)

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Please read the manual as suggested in another forum. Read through section 3 which is the tutorial. Clearly you have not done this at a minimum as you are still posting very basic questions which are covered in the tutorial.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,014
    Thanks
    79
    Thanked 4,435 Times in 4,400 Posts
    On top of that, the DB design is really bad.

    I'd suggest something like this, instead:
    Code:
    CREATE TABLE subscriptions (
        email VARCHAR(100) PRIMARY KEY,
        men BOOLEAN DEFAULT FALSE,
        women BOOLEAN DEFAULT FALSE,
        menAndWomen BOOLEAN DEFAULT FALSE );
    Your design means that one person could use 3 different email addresses to subscribe to the 3 different newsletters.

    (Are there *really* 3? Or does "menAndWomen" just mean "subscribe to both"?)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,014
    Thanks
    79
    Thanked 4,435 Times in 4,400 Posts
    Also...wrong terminology:
    I have a database (testdb), a table (table1) and three rows (mens, mensandwomens, womens).
    No, you most certainly do *NOT* have "three rows".

    You have three *FIELDS*. Some people would say three columns, though I dislike that terminology.

    Spreadsheets have rows and columns.

    Databases have tables, records, and fields.

    You have three fields in one table.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Banned
    Join Date
    Sep 2011
    Posts
    140
    Thanks
    17
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by ASTP001 View Post
    try
    Code:
    DELETE FROM testdb.newsletters
    WHERE mens=test@test.com
    Thanks for the input bro , but it didn't work.


    Quote Originally Posted by Old Pedant View Post
    Your design means that one person could use 3 different email addresses to subscribe to the 3 different newsletters.[/code]

    (Are there *really* 3? Or does "menAndWomen" just mean "subscribe to both"?)
    Yeah I know it is really ****in **** hey LOL, I just wanted to see it work, I didn't care how inefficient and messy it was.

    No, there is really only two newsletters - mens and womens, the "mens & womens" was just a separate field where I would of sent them both.

    Now in regards to the table you suggested... I am guessing someone adds their e-mail into the list and then I associate that e-mail with a boolean of which newsletter they want to receive? If so, how do I set the boolean? If not, how does it work?
    Thinking ahead, if my suspicion is right on how the boolean works, I don't see a reason for the "mensandwomens" field to exist, as you can add the e-mail address to both booleans?
    Last edited by Democrazy; 09-07-2011 at 06:45 PM.

  • #7
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    Quote Originally Posted by Democrazy View Post
    Thanks for the input bro , but it didn't work.
    That is because they forgot to put quote marks around the email address since it is a string.

    Quote Originally Posted by Democrazy View Post
    Yeah I know it is really ****in **** hey LOL, I just wanted to see it work, I didn't care how inefficient and messy it was.

    No, there is really only two newsletters - mens and womens, the "mens & womens" was just a separate field where I would of sent them both.

    Now in regards to the table you suggested... I am guessing someone adds their e-mail into the list and then I associate that e-mail with a boolean of which newsletter they want to receive? If so, how do I set the boolean? If not, how does it work?
    When someone adds their email to the list you just set the boolean to true for the newsletters they subscribed to. That way you aren't repeating the email address multiple times.

    You can change which newsletters they are subscribed to with an UPDATE query later.

    Since there are just two newsletter then you can simplify the table even further:
    Code:
    CREATE TABLE subscriptions (
        email VARCHAR(100) PRIMARY KEY,
        men BOOLEAN DEFAULT FALSE,
        women BOOLEAN DEFAULT FALSE );
    OracleGuy

  • Users who have thanked oracleguy for this post:

    Democrazy (09-07-2011)

  • #8
    Banned
    Join Date
    Sep 2011
    Posts
    140
    Thanks
    17
    Thanked 0 Times in 0 Posts
    This is so awesome!
    Its actually quite simple, but I'm so use to things being dramas all the time (I use Linux lol) I think I am just over whelmed.

    So, how do I set booleans?

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,014
    Thanks
    79
    Thanked 4,435 Times in 4,400 Posts
    I assume you are using PHP.

    I am *NOT* a PHP person, but something like the following should work:

    Code:
    <?php
    $email = mysql_real_escape_string( $_REQUEST["email"] );
    $men = isset( $_REQUEST["menCheckbox"] ) ? "true" : "false";
    $women = isset( $_REQUEST["womenCheckbox"] ) ? "true" : "false";
    
    $sql = "INSERT INTO subscriptions (email, men, women) "
        . " VALUES('$email', $men, $women)";
    
    echo "<hr>DEBUG SQL: " . $sql . "<hr/>\n"; // comment this line out when it starts working
    
    ...
    MySQL allows you to use true and false for BOOLEAN fields *or* you can use the numbers 1 and 0 (since BOOLEAN fields are actually rendered as BIT fields).

    Notice that true and false are keywords and should *not* have apostrophes or quotes around them.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,014
    Thanks
    79
    Thanked 4,435 Times in 4,400 Posts
    In case you couldn't tell, that code assumes you had a <form> on the prior page something like this:
    Code:
    <form action="addSubscription.php">
    email: <input name="email"/>
    <br/>
    subscribe to:<br/>
    <label><input type="checkbox" name="menCheckbox"/> men's newsletter</label><br/>
    <label><input type="checkbox" name="womenCheckbox"/> women's newsletter</label><br/>
    <input type="submit" value="Subscribe" />
    </form>
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    Banned
    Join Date
    Sep 2011
    Posts
    140
    Thanks
    17
    Thanked 0 Times in 0 Posts
    Yes, I am using HTML + PHP.

    HTML:
    Code:
    <FORM action="confirmation.html" method="post">
    
    	<DIV>
    
    		<SPAN class="input">
    
    			Action:	
    
    				<SELECT name="action">
    
    					<OPTION>Register</OPTION>
    
    					<OPTION>Unregister</OPTION>
    
    				</SELECT>&nbsp&nbsp&nbsp
    
    			E-mail:
    				<INPUT name="e-mail" type="text"></INPUT>&nbsp&nbsp&nbsp
    
    			Newsletter: 
    
    				<SELECT name="newsletter">
    
    					<OPTION>Mens</OPTION>
    
    					<OPTION>Mens & Womens</OPTION>
    
    					<OPTION>Womens</OPTION>
    
    				</SELECT>&nbsp&nbsp&nbsp
    
    			<INPUT class="submit" type="submit" value="Submit">
    
    		</SPAN>
    
    	</DIV>
    
    </FORM>
    PHP:
    Code:
    <?php
    	$link = mysql_connect('localhost', 'testusr', 'testpw');
    	mysql_select_db('testdb', $link);
    	$email = $_POST['e-mail'];
    	if ($_POST['action'] == 'Register') {
    		if ($_POST['newsletter'] == 'Mens') {
    			$query = "INSERT INTO newsletters(mens) VALUES('$email')";
    		}
    		elseif ($_POST['newsletter'] == 'Mens & Womens') {
    			$query = "INSERT INTO newsletters(mensandwomens) VALUES('$email')";
    		}
    		elseif ($_POST['newsletter'] == 'Womens') {
    			$query = "INSERT INTO newsletters(womens) VALUES('$email')";
    		}
    	}
    	mysql_query ($query);
    	mysql_close($link);
    ?>
    The PHP code is not finished - there is no "Unregister" function. I will do that soon, for now I just want to get things flowing.

    .. but yeah, basicly I just want to make a simple and basic newsletter system. I will make it more advanced later on down the track with e-mail validation etc. For now I just want to get **** working.

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,014
    Thanks
    79
    Thanked 4,435 Times in 4,400 Posts
    <shrug/> I showed you how you could do it with just a pair of checkboxes, instead of the <select>. But it's all PHP code. You should be able to figure out how to use the <select> instead.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #13
    Banned
    Join Date
    Sep 2011
    Posts
    140
    Thanks
    17
    Thanked 0 Times in 0 Posts
    All good.
    Last edited by Democrazy; 09-08-2011 at 01:28 PM.


  •  

    Posting Permissions

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