most recent record from mysql

Beginners - Learning PHP
#1

Ok I have a email list and in the list there might end up being duplicates and what im wondering is where to start here is a basic outline of the project

I have 3 columns in my sql table id player name and email what I am looking for is if someone adds in another record for player x that the table looks and pulls for the most recent record that isn’t blank and puts it in the table I have no clue on where to even start

[code]

<?php $con = mysql_connect("localhost","dbs_dbs","password"); if (!$con){ die("Can not connect: " . mysql_error()); } mysql_select_db("dbs_forms",$con); $sql = "SELECT * FROM playersemails ORDER BY playername ASC "; $mydata = mysql_query($sql,$con); echo ""; while($record = mysql_fetch_array($mydata)){ echo ""; echo ""; echo ""; echo ""; } echo "
Player Name Emails
" . $record['playername'] . "" . $record['email'] . "
"; mysql_close ($con); ?> [/code]

any help would be much appreciated

#2

Not sure Im following what exactly you want, But to add to your table use INSERT INTO table_name.

#3

ok to clarify

I have the form set up and its only able to be updated by admin but if someone changes their email instead of going into the db I just fill out the form but when the show emails list is pulled up as it shows now will show my name with new and old email
I need that list to only show the new email addy

#4

For security reasons I would never let the user change his/her email address, unless it’s done very securely and that is a tough order. Even your big companies have issues with that one and every now & then you hear them running in to security issues in the news It’s very easy for a person to say I lost my email address, when in fact they are not the ones who even own that address. Even if you do it securely (Let them change the email address), I treat the users email address like a password. By that I mean I don’t look at them and the only way I can see their email address if I go into phpadmin, but even then I don’t look at them. To me you are just opening a can of worms letting them change their email, but that is just my opinion and you know what they say about opinions. ;D Anyways getting back to your question if I’m understanding it correctly couldn’t you just drop the echo off on the old email statement to hide it?

#5

Also are playernames unique? If not, I don’t think you can do anything because you won’t be able to tell if:

ID: 34 playername: John email: [email protected]

and

ID: 58 playername: John email: [email protected]

is the same player, John, with two different e-mail addresses or two different players with the name John.

#6

the playernames are all unique I host backgammon tourneys on a site online and they make sure all the names are unique

#7

the players are not the ones that will be changing it the tournament room directors will be yo give you a little more understanding

I host backgammon tournaments on a gaming site every month 1st 2nd and 3rd receives toc invites this list is so that we have an ongoing database of email addys from present and past winners of the months toc tournament of champions qualifiers

#8

OK, so on your “admin” page do some validation to check if a playername already exists, if it does, update the existing record, other wise add a new record. Something like this:

[php]$strPlayername = $_POST[“Playername”];
$strEmail = $_POST[“Email”];

mysql_select_db(“dbs_forms”,$con);
$sql = “SELECT ID FROM playersemails WHERE playername=’” . $strPlayername . “’ LIMIT 1”;
$mydata = mysql_query($sql,$con);
$record = mysql_fetch_array($mydata);
if (is_array($record)){
mysql_query(“UPDATE playersemails SET email=’” $record[‘email’] “’ WHERE ID=” . $PlayerID);
}
else{
mysql_query(“INSERT INTO playersemails(playername, email) VALUES(’” . $strPlayername . “’,’” . $strEmail . "’);
}[/php]

Before someone else points it out, yes I know you shouldn’t be using mysql_query any more and should be using mysqli or PDO with prepared statements. ;D

#9

ok so I have to do it with this I cant just pull most recent record in to the table that isn’t null reason is a lot of these emails I will be importing from an excel sheet the form page is just if someone advises me their email has changed so I only have to do 1 at a time why I was kind of hoping there was a way to just pull the most recent record that isn’t empty and post it into the table

#10

reason I need it changed in the coding above that I posted is eventually long hard job and prob will be here lots ofr it is I am eventually gonna do something that will take
Nojoks’s Tourney Bracket Tool Version 1.2.1.84
Tournament: DeepBlueSea
Date: 04/20/2013
Day: Saturday
Scheduled Start: 1:00
Actual Start: 4:00:02 PM
Closed: 4:11:43 PM
Host: justme67 (UBG_shorty)
Number of Players: 19

1st place: DESERTWARRIOR
1st place email: email addy
2nd place: poetree
2nd place email: email addy
3rd place: TM7_crazykarma
3rd place email: email addy

START POINTS
DBS_Arielle 1
heli_guy2 1
keithsgame 1
MiaBela 1
PAL_USA_14KT 1
pirat3 1
TM7_sundgo 1
UBG_Angel_D_8 1
ziko_14 1
BOMBER 3
Antoinette 4
mawdryn65 4
olddanny1 4
The_Romano 4
ADG_Ronycezar 7
onaroll 7
TM7_crazykarma 19
poetree 32
DESERTWARRIOR 45
UBG_shorty 10
STOP POINTS

and post the emails to the emails database and then also take the points and add them up as well to keep a monthly record
and finally pull the emails and players names from top section and make a toc list I know this will be a lot of grey hairs but if I change it in the form every time someone posts their tour the players name at top that have email addys will be duplicated and over a month I could have 10 entrys for one name

#11

I think I’ve got it:

SELECT ID, playername, email FROM playersemails
WHERE ID IN (SELECT MAX(ID) AS ID FROM playersemails GROUP BY playername)

#12

so I paste that into my $sql variable

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<?php$con = mysql_connect("localhost","dbs_dbs","password");
if (!$con){	die("Can not connect: " . mysql_error());
}
mysql_select_db("dbs_forms",$con);
$sql = "SELECT * FROM  playersemails ORDER BY playername ASC ";
$mydata = mysql_query($sql,$con);
echo "<table border=1><tr><th> Player Name</th><th> Emails</th></tr>";
while($record = mysql_fetch_array($mydata))
{
	echo "<tr>";	echo "<td>" . $record['playername'] . "</td>";
	echo "<td>" . $record['email'] . "</td>";	echo "</tr>";
}
echo "</table>";
mysql_close ($con);
?>
</body>
</html>
#13

Yes, like this:

[php]

<?php$con = mysql_connect("localhost","dbs_dbs","password"); if (!$con){ die("Can not connect: " . mysql_error()); } mysql_select_db("dbs_forms",$con); $sql = "SELECT ID, playername, email FROM playersemails WHERE ID IN (SELECT MAX(ID) AS ID FROM playersemails GROUP BY playername) ORDER BY playername ASC "; $mydata = mysql_query($sql,$con); echo ""; while($record = mysql_fetch_array($mydata)) { echo ""; echo ""; echo ""; echo ""; } echo "
Player Name Emails
" . $record['playername'] . "" . $record['email'] . "
"; mysql_close ($con); ?> [/php]
#14

thank you for all your help
it is now working

#15

ok one more little bit is now im looking at the list and where the latest one entered is blank for email it is showing up the null one cause it was posted last is there a way of joining a is not null in with it i tried it but it blanked out the whole list

#16

Like this:

[php]$sql = "SELECT ID, playername, email FROM playersemails WHERE ID IN (SELECT MAX(ID) AS ID FROM playersemails WHERE email IS NOT NULL GROUP BY playername) ORDER BY playername ASC ";[/php]

#17

just tested it and its still showing the last email entered for the player even if its blank

#18

Hmm, not sure, I set up a test table and it works for me. ::slight_smile:

#19

Make sure that you aren’t inserting empty strings. An empty string is not the same as NULL

If you are inserting empty strings, you’ll want to fix that and update the existing table e.g.

UPDATE `playersemails` SET `email` = NULL WHERE `email` = '';

OR… you can actually check that it is NOT NULL and not an empty string:

WHERE `email` > ''
#20

That’s a very good point, I was using Microsoft SQL Server to test things and by default it inserts NULL, whereas MySQL inserts an empty string. Might be an idea to check for both:

[php]$sql = "SELECT ID, playername, email FROM playersemails WHERE ID IN (SELECT MAX(ID) AS ID FROM playersemails WHERE email IS NOT NULL OR email <> ‘’ GROUP BY playername) ORDER BY playername ASC ";[/php]

Alternatively you could try something like this:

[php]$sql = "SELECT ID, playername, email FROM playersemails WHERE ID IN (SELECT MAX(ID) AS ID FROM playersemails WHERE CONCAT(email, ‘zzz’) = ‘zzz’ GROUP BY playername) ORDER BY playername ASC ";[/php]

If this is just useless data, you could also use this to tidy up the playersemails table:

[php]$sql = "DELETE FROM playersemails WHERE ID NOT IN (SELECT ID, playername, email FROM playersemails WHERE ID IN (SELECT MAX(ID) AS ID FROM playersemails WHERE email IS NOT NULL OR email <> ‘’ GROUP BY playername) ORDER BY playername ASC) ";[/php]

Sponsor our Newsletter | Privacy Policy | Terms of Service