[Resolved] mySQL delete question

[adavis]

I have 2 tables. One holds students and the other is a roster table for athletes. At the beginning of school, I need to purge all the student id from the roster table for students who have graduated or otherwise not returned.

What I have in mind is just doing something like this:

$sql = "SELECT * FROM roster ORDER BY athleteid";
$result = mysql_query($sql,$dblink) or die(mysql_errno);
while ($row = mysql_fetch_array($result)) {
  $sql2 = "SELECT * FROM student WHERE studentid = $row['athleteid']";
  $result2 = mysql_query($sql2,$dblink) or die(mysql_errno);
  $count = 0;
  while ($row2 = mysql_fetch_array($result2)) {
    $count++;
  }
  if ($count > 0) {
    $sql3 = "DELETE FROM roster WHERE athleteid= $row['athleteid']";
    blah... blah... blah....
  }
}

will that work or is there a better approach???

Thanks,
Alisa

[devinemke]

is there some field in the student table that indicates that a student has left? how do you know when a student is gone?

[adavis]

The actual tables are like this:
the student table looks like ths:

studentid  int(10)  UNSIGNED No  0  

lastname   varchar(30)   No      

firstname   varchar(30)   No      

nickname   varchar(20)   Yes  NULL    

grade   varchar(5)   No      

bg   varchar(10)   No   

and the roster table looks like this:

id   int(11)  UNSIGNED No    auto_increment  

sport   varchar(20)   No      

team   varchar(20)   No      

bg   varchar(10)   No      

athleteid   smallint(7)  UNSIGNED No  0    

number   tinyint(7)   Yes  0    

position   varchar(255)   Yes  NULL    

where athleteid is the same as studentid

[tsinka]

Hi,

with recent MySQL versions you can use JOINs with DELETE statements so try something like this:

DELETE student FROM roster,student WHERE roster.studentid=student.id

But I wonder if it makes sense to have records in the roster table with the given student id since the record with that student id is delete.

If you want to delete the records from both tables just use DELETE FROM instead of DELETE student FROM

Thomas

[adavis]

What I want to do is delete out of the roster if the athleteid was NOT found in the student table. I had about 2 seconds to look that that delete statement, but I'd hate to delete 200 records of my 500 records if it didn't work the way I thought it would. Yikes!

[tsinka]

Ok,

then try this (but on a test db first):

DELETE roaster FROM roaster LEFT JOIN student ON roaster.studentid=student.id WHERE student.id IS NULL

This should delete all records from roaster that don't have a matching record in student.

Thomas

[adavis]

I think I've confused you. What I want to do is delete all the athletes out of roster who have graduated or otherwise are no longer enrolled at the school. There are a handful of them every year and if they play multiple sports, they will be in roster for each sport they play.

Student table:[FONT=courier new]
1 Doe John Jack 12 B
3 Doe Jane Jill 11 G
4 Doe James Jim 10 B
[/font]
Roster table:[FONT=courier new]
1 Baseball.....V B 1 5 RF/P
2 Basketball...V B 1 5 Guard
3 Baseball.....V B 2 7 LF
4 Basketball...V B 2 6 Forward
5 Softball.....V G 3 2 1B
6 Soccer.......V G 3 9 Striker
[/font]

So, I want to delet records 3 and 4 out of the Roster table because student 2 is no longer at the school, refering to the table structures I provided before. The 5th column of the roster is the athleteid which is the same as studentid.

[tsinka]

Ok,

I should've read your posts more carefully

DELETE roster FROM roster LEFT JOIN student ON roster.athleteid=student.studentid WHERE student.studentid IS NULL

You can check if the right records are selected by

SELECT roster.* FROM roster LEFT JOIN student ON roster.athleteid=student.studentid WHERE student.studentid IS NULL

Thomas

[adavis]

The select statement works great! It finds 21 matches (or non-matches, actually), but when I try the delete statement, I get an error message. I'm just running this at the sql tab of phpMyAdmin:

   DELETE rosters 
   FROM rosters 
   LEFT JOIN HS_studenttable 
   ON rosters.athleteid=HS_studenttable.studentid 
   WHERE HS_studenttable.studentid IS NULL

[FONT=courier new]You have an error in your SQL syntax near 'rosters FROM rosters LEFT JOIN HS_studenttable ON rosters.athleteid=HS_studentta' at line 1[/FONT]

(those are the actual table names. Sorry, I was in a real hurry this morning.)

[adavis]

I got to thinking. Would this be it?

DELETE FROM rosters 
  LEFT JOIN HS_studenttable 
         ON rosters.athleteid=HS_studenttable.studentid 
      WHERE HS_studenttable.studentid IS NULL

[adavis]

This is what I ended up with:

$sql = "SELECT rosters.* 
FROM rosters 
LEFT JOIN HS_studenttable 
ON rosters.athleteid=HS_studenttable.studentid 
WHERE HS_studenttable.studentid IS NULL 
ORDER BY athleteid"; 
$result = mysql_query($sql,$dblink) or die("ERROR: SQL query failed:<br>$sql\n");
while ($row = mysql_fetch_array($result)) { 
  $sqlD = "DELETE FROM rosters WHERE id = '".$row['id']."'";
  $resultD = mysql_query($sqlD,$dblink) or die("ERROR: SQL query failed:<br>$sqlD\n");
  print "Deleting athlete: ".$row['athleteid'].", record: ".$row['id']." from roster<br />\n";
}

Kind of a combination of the two, but the left join select found the records I wanted to remove and then I could be more sure that's what I wanted to delete. I also made a copy of rosters before I ran the script just to be on the safe side.

Thanks...

[tsinka]

Glad to hear that you solved the problem 🙂

These delete queries using join only work with MySQL >=4.0.0 so I think that you have MySQL 3.x running.

Thomas

[adavis]

Yes, version 3.23.55.

But it worked and I'm happy, my coaches are happy, and now I can just run that little script at the beginning of each semester when we withdrawn students from the student table to clean every thing up. Before, they still show up in the roster with just their first name and a 0 by their grade. I want to keep this a "webmaster" script and not something the coaches running, but I guess it won't hurt anything either way becuase it won't delete anyone out of the roster that is in the student table.

Oh well, I'll figure something out eventually. I'm still trying to get this all together so that it flows well. I've been working on it (the whole school site [sport, grade, layout, etc...]) for 2 1/2 years, part time. I think it would be much stronger and better if I could work on it more but I have a 40 hour/week job in addition to volunteering my web duties at the school.

I need more work on joins. I just can't seem to get my brain wrapped around them. Thanks for the help.

Alisa

[tsinka]

There is a tutorial which has a nice description of JOINS:
http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.shtml

Thomas

[TVBanks98]

adavis,

Just thought I'd offer you a different approach. If you ALTERed your tables to add a foreign key constraint with cascading delete you could simply delete the row from the HS_studenttable and the referential integrity would havdle the rest.

Here's a link for some fun and exciting reading

Best of luck,
TVB

[adavis]

For what version of mySQL is this available. It is not in my power to upgrade at this time from version 3.23.55.

[TVBanks98]

adavids,

The manual says:

"In MySQL Server 3.23.44 and up, the InnoDB storage engine supports checking of foreign key constraints, including CASCADE, ON DELETE, and ON UPDATE. See section 16.7.4 FOREIGN KEY Constraints. "

Here's a better link with more info on Foreign Keys, Cascading Delete and Joins.

Good Luck !
TVB

[adavis]

Thanks, gives me something to read and think about. I'm always trying to learn something new 😉

Thanks again,
Alisa