Help with a query

fatepower

Hi i have a problem.

Well i have a lottery system on my site. There is a small error in it.
If a user has bought tickets and then delete their account. You get a error when you view who have bought tickets. Thats because the user id has been stored in the tickets table. As the query is selecting the user info from the id of the user. Now my question is. How can i select with SQL all the users in the tickets table who does not exist anymore in the users table.

Then use a DELETE FROM tickets WHERE user=$USERID
So that user will be erased from tickets table because he are allready deleted from the users table.

Heres the code:

<?php
require_once("include/config.php");
require_once("include/functions.php");
dbconn();

standardheader(SOLD_TICKETS);
block_begin(SOLD_TICKETS);
?>

<table align="center" width="80%" class="lista">
<tr>
  <td class="header"><?php print ID; ?></td>
  <td class="header"><?php print USERNAME; ?></td>
  <td class="header"><?php print NUMBER_OF_TICKETS; ?></td>
  <td class="header"><?php print UPLOADED; ?></td>
  <td class="header"><?php print DOWNLOADED; ?></td>
  </tr>
<?php

$sql = mysql_query("SELECT user FROM tickets") or die (mysql_error());
if(mysql_num_rows($sql) == 0){
	print("<tr><td class=lista align=center colspan=5>".NO_TICKET_SOLD."</td></tr>");
}
else{
	while ($myrow = mysql_fetch_assoc($sql))
		$user[] = $myrow["user"];
		$user = array_values(array_unique($user));
		for ($i = 0; $i < sizeof($user); $i++)
			$tickets[] = mysql_num_rows(mysql_query("SELECT * FROM tickets WHERE user=$user[$i]"));
			for ($i = 0; $i < sizeof($user); $i++)
				$username[] = end(mysql_fetch_row(mysql_query("SELECT username FROM users WHERE id=$user[$i]")));
				for ($i = 0; $i < sizeof($user); $i++)
					$uploaded[] = makesize(end(mysql_fetch_row(mysql_query("SELECT uploaded FROM users WHERE id=$user[$i]"))));
					for ($i = 0; $i < sizeof($user); $i++)
						$downloaded[] = makesize(end(mysql_fetch_row(mysql_query("SELECT downloaded FROM users WHERE id=$user[$i]"))));
						for ($i = 0; $i < sizeof($user); $i++)
							echo "<tr><td class=lista>" . ($i + 1) . "</td><td class=lista>$username[$i]</td><td class=lista>$tickets[$i]</td><td class=lista>$uploaded[$i]</td><td class=lista>$downloaded[$i]</td></tr>";
}
?>

</table>
<?PHP
block_end();
stdfoot();
die;
?>

Hope you understand what i mean.
Anyway what i have done now is:

$qqq=mysql_query("SELECT user FROM tickets");
while ($ttt=mysql_fetch_assoc($qqq)) {

$mutt = mysql_query("SELECT users.id as id, tickets.user as user FROM users INNER JOIN tickets ON users.id!=tickets.user WHERE users.id=".$ttt["user"]."") or die (mysql_error());
$kullan = mysql_fetch_array($mutt);

}

But i don't get it to work ok 😕
Because there is 15 id numbers with 3157 in tickets and one id number with 1629. Those are not existing in users table.

Weedpacket

You prevent the problem in the first place by ensuring your database maintains referential integrity through a foreign key relationship. That relationship would state that the user id in the ticket table must match an existing user id in the user table. State further that when a user is deleted, that deletion should CASCADE so that the corresponding tickets are deleted as well.

To clean up the present mess: "DELETE FROM tickets WHERE tickets.user NOT IN (select id from users)".

If you don't want to delete the tickets, then it makes no sense to be deleting the users (because some of the information about the ticket is only relevant in the context of a particular user). Add another boolean column that marks the user's account as "closed" and henceforth only look at users whose accounts are not closed (create a view "SELECT * from users where closed=0" and work with that view to avoid having to keep remembering this). An alternative might be to create additional tables for closed user accounts and their tickets, and when deleting an account, copy all the relevant records across first. Mass storage is cheap. Deleting anything except transient stuff usually isn't necessary.

fatepower

Thank you for the reply. Well i do have disabled=yes or no. Same as closed. The problem is. When a user do get deleted by cleanup. He has maybe bought tickets. As the ticket table connect the user table by adding user id into tickets table. Yes so adding a key statement in tickets table is allready done. None existing users can't buy tickets. What my idea was here. Was to add a code into the beginning of the view_tickets.php so it will cleanup all bought tickets buy none existed user (With that i mean users that have deleted their account). How should you recommend that the code should look like?

Problem here is ofcourse also in the delete.php. As not all users who get cleaned up buy sanity, has bought tickets. You can't delete something from a table that not existst. So adding a permanently DELETE FROM tickets WHERE user=".$CURUSER["id"]." should generate error. Maybe adding the cleanup code into sanity instead. That would clean up all bought tickets with none existing user id (if there is any).