New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
Can I do this with PHP and MySQL?
Hey guys.. Not really played much with MySQL so not too sure if I can do this or not..
Basically I'll have a database full of some kind of code. When a user enters their email and clicks the button a web page, it takes the top code from the database, and emails it to them. Once this has been done, I want to remove that code from the database.. or at least have it moved elsewhere, so that anybody else who clicks the button will not receive the same code.
So basically: user clicks button, takes first entry from database, sends information to user, deletes entry from database.
Any ideas? Please be patient with a DB noob ;P
Cheers
Comments
Yeah, completely do-able.
you can create a new row on that table, so that when it emails it, you check (like, add a 1) to that row, and then, when you search it again, if it finds the 1, it will ignore it.
You don't even need MySQL for that...
@netomx yeah I thought about that.. just adding a "sent" column or something.. so that if one has been sent, it changes that value from zero to one, and only allows for using entries with a value of zero.
@subigo how so?
One approach would be to just have a text file with one code per line, and as a user is shot the code, give the user the topmost code and remove it from the text file.
This is a possible approach but would it be possible to hide this from users? Maybe with .htaccess?
You don't need text files to be in the web folder for php to access them.
@black so I could have the PHP and form file at let's say: /var/www/form.html and /var/www/mail.php
And then the text file at /home/user/codes.txt
And they'd be accessible? Thanks btw guys, appreciate your patience
Yep, make sure those text files are readable by whatever username your web server runs on, most likely, www-data
Something like that.
If you want to use php to edit the text file, make sure you have read and write permissions to the folder "/home/user/codes.txt"
@black thanks a lot
. Now just to find out why PHP is taking ages to send an email 
.
logs?
The only problem with text files is race conditions, two people accessing at once, one person writing while another is reading, etc. That's kind of the point of databases.
A simple autoincrement column in MySQL would do nicely. I don't think MySQL has the SEQUENCE data type yet but I haven't looked.
Depends on what kind of code you're talking about sending. If it's some kind of random activation code, you could just use PHP to create a random number, then store that number in its own text file (or xml file), send it to the user, then delete the file.
If it's a specific list of "codes" that you need to send, just put them all in a single text (or xml) file and have PHP take the code from the first line, send it, delete it. If you're worried about having a ton of users and having two users access the same file at the same time, the fix is simple... put each code in a separate file.
@raindog308 will be very low traffic for now, and the chances of having two concurrent users at the moment is pretty much zero. Thanks for the insight though
.
@subigo it is a specific list of code. I've currently got it setup to read the top line, but now need it to remove that line afterwards. Any ideas?
<?php $myFile = "codes.txt"; $lines = file($myFile); $code = $lines[0]; $to = "[email protected]"; $subject = "Your code"; $email = $_REQUEST['email'] ; $message = "Thank you for asking for a code. Your code is: $code"; $headers = "From: $email"; $sent = mail($to, $subject, $message, $headers) ; if($sent) {print "Email sent."; } else {print "Error sending email"; } ?>^What I have so far
Just open the file again, unset the first line, save it again.
@liamwithers
$lines = file('codes.txt'); // read file to array $code = $lines[0]; unset($lines[0]); // unset first line file_put_contents('codes.txt',$lines,LOCK_EX); // write file + lock while writingEDIT: Looks like subigo was thinking the same thing
@subigo @Adam
Tried both of those and don't seem to work. I assume they're fine to be in the same script?
Oh how I hate being retarded
Check if the file you are opening belongs to the group or username of your webserver
@netomx Ahaha, thanks man
. Wasn't owned by www-data, but is now.. fixed the problem and all is working 
.
I blame the fact that I'm still up at 5:30am :P.
Thanks @subigo and @Adam .. think I'm all sorted now
$lines = file('codes.txt'); // read file to array $code = $lines[0]; unset($lines[0]); // unset first line (is_writable(__DIR__ . '/')) ? file_put_contents('codes.txt',$lines,LOCK_EX) : exit('Folder isn\'t wrtibale. Check folder permissions.'); $to = '[email protected]'; $subject = 'Your code'; $email = $_REQUEST['email']; $message = 'Thank you for asking for a code. Your code is: ' . $code; $headers = 'From: ' . $email; $sent = mail($to, $subject, $message, $headers) ; ($sent) ? echo 'Email sent.' : echo 'Error sending email.';Please don't use flatfiles due to the race conditions mentioned above. Try MySQL or, if you want something leaner, have a go at SQLite.
Database table structure:
Id is an auto-incrementing column (MySQL allows you set this for the column). Code is just a VARCHAR. Used is a BOOLEAN/TINYINT that can be either 0 or 1 based on whether the code has been used. EmailAddress is a VARCHAR that sets the e-mail address when a code is requested. This way you don't lose any data when a code is requested, and you can still ensure only one code is handed out at the same time.
<?php $result = mysql_query("SELECT * FROM codes WHERE `Used` = '0' LIMIT 1"); $row = mysql_fetch_assoc($result); $code = $row['Code']; $sEmail = mysql_real_escape_string($_POST['emailaddress']); if(mysql_query("UPDATE codes SET `Used` = '1', `EmailAddress` = '{$sEmail}' WHERE `Id` ='{$row['Id']}'")) { ... e-mail code goes here ... } else { die("An error occurred."); } ?>Note that this does not take into account failure during the e-mail sending.
An improvement over @joepie91 's recommendation would be to have a separate table with ID and email and always add an entry to that table when someone requested a code. That way you avoid NULL-cells which is more elegant.
However, use PDO or mysqli instead of what @joepie91 used, the mysql_* functions
@joepie91 @gsrdgrdghd @debug Thanks guys, I'll be working on a database version of this soon.. but for now a text file will be fine. Probably have a look at a database at the start of next week.
The idea is that once a code has been used, it cannot be accessed again. Also, users should be limited to one code each.
I've placed some PHP to validate the email, but shall be doing this with JavaScript to verify on the client side too.
Currently using a cookie that will prevent the user from getting a code more than once. Anybody able to suggest any more efficient methods? I guess I could have another table in a database that works on the basis of if an email address is used, it is assigned something like "emailUsed = 1". Then check for to see if emailUsed is 1, if it is then prevent access, else allow it.
Only thing is, this whole system will work on a timescale of maybe 2 weeks. So after 2 weeks, I'll need the emailUsed to reset, as users will be allowed to get another code by then.
Cheers for the input btw guys, could be an interesting project
To validate emails you can use the function filter_var. I would link it but I'm mobile now
@debug Yeah that's what I'm using:
SQLite can have the same race condition issues. But the truth is, unless you have a lot of read/writes, it's not going to be an issue if you lock the file. And there's plenty of ways to prevent it, even if you have a ton of users. Not only would MySQL for his setup be overkill and bloat, it would be slower. There are times you need a full-blown database server... sending out a code is not one of those times.
@subigo
So you'd recommend sticking to the text file implementation I have currently?
I'd at least recommend sqlite.
Cookies can be deleted or manipulated by the user. Use the database. No need for additional tables, joepie91's table structure provides for it -- the 'Used' column.
+1