Wrong approach for running a database?

[Democrazy]

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 |
+-----------------------+-------------------+-------------------+
| [email protected] | NULL | NULL |
| [email protected] | NULL | NULL |
| [email protected] | NULL | NULL |
+-----------------------+-------------------+-------------------+

What I want to do, is to delete the entry "[email protected]". How would I do this?

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

[ASTP001]

try

DELETE FROM testdb.newsletters
WHERE [email protected]

[guelphdad]

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.

[Old Pedant]

On top of that, the DB design is really bad.

I'd suggest something like this, instead:

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"?)

[Old Pedant]

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.

[Democrazy]

try

DELETE FROM testdb.newsletters
WHERE [email protected]

Thanks for the input bro , but it didn't work.

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?

[oracleguy]

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.

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:

CREATE TABLE subscriptions (
    email VARCHAR(100) PRIMARY KEY,
    men BOOLEAN DEFAULT FALSE,
    women BOOLEAN DEFAULT FALSE );

[Democrazy]

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?

[Old Pedant]

I assume you are using PHP.

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

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

[Old Pedant]

In case you couldn't tell, that code assumes you had a <form> on the prior page something like this:

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

[Democrazy]

Yes, I am using HTML + PHP.

HTML:

<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:

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

[Old Pedant]

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

[Democrazy]

All good.