Omit data already entered

[NigeB]

I have created a query, which populates a table, but I would like to omit any records that are already in the table. For example dogs_bfa_no = 1234 is already in the table to be populated and therefore I would like to populate with the next one in line

Any ideas / pointers appreciated code below -


// Select the distinct TRN's from totya

$query2 = "SELECT DISTINCT TRN FROM totya";
$result2 = mysql_query($query2);
while ($row=mysql_fetch_array($result2)) {

// For each Team Get the Top 4 Dogs

$team1 = $row["TRN"];
$query3 = "INSERT INTO totyb (TRN, Dogs_BFA_No, SumPoints)
SELECT TRN, Dogs_BFA_No, Sum(FB_Points) AS SumPoints FROM totya
WHERE TRN = '$team1'
GROUP BY Dogs_BFA_No
ORDER BY SumPoints DESC
LIMIT 4";
$result3 = mysql_query($query3);
}

mysql_free_result($result2);

 

[cLFlaVA]

There are a couple of ways to go about this. One way is to make the dogs_bfa_no a unique key. The attempt to add a duplicate value will generate an error, which you can suppress and move on.

The other way would be to query the database just before entering a value. In other words, do a [tt]SELECT * FROM totyb WHERE dogs_bfa_no = 'blah'[/tt] and if no rows are returned, go ahead and add the record.

*cLFlaVA
----------------------------
[tt]Sigs cause cancer.[/tt]

 

[NigeB]

I get the idea, I endevoured to take it a step further by deleting all the records from the original table for each dogs_bfa_no added, but for some reason no records are deleted Have I missed something obvious?

// Create a table with all relevent records for the year in question

$query = "CREATE TEMPORARY TABLE totya SELECT Venues.Date, Points.Dogs_BFA_No, Points.FB_Points,
Points.TRN FROM Points
LEFT JOIN Venues ON Points.Tournament_ID = Venues.Tournament_ID
WHERE Year(Venues.Date) = '2003'";
$mysql_result = mysql_query ($query, $db);

// Select the distinct TRN's from totya

$query2 = "SELECT DISTINCT TRN FROM totya ORDER by TRN";
$result2 = mysql_query($query2);
while ($row=mysql_fetch_array($result2)) {

// For each Team Get the Top 4 Dogs

$team1 = $row["TRN"];
$query3 = "INSERT INTO totyb (TRN, Dogs_BFA_No, SumPoints)
SELECT TRN, Dogs_BFA_No, Sum(FB_Points) AS SumPoints FROM totya
WHERE TRN = '$team1'
GROUP BY Dogs_BFA_No
ORDER BY SumPoints DESC
LIMIT 4";
$result3 = mysql_query($query3);

// Select the Top 4 dogs from each Team

$query4 = "SELECT TRN, Dogs_BFA_No, Sum(FB_Points) AS SumPoints FROM totya
WHERE TRN = '$team1'
GROUP BY Dogs_BFA_No
ORDER BY SumPoints DESC
LIMIT 4";
$result4 = mysql_query($query4);
while ($row1=mysql_fetch_array($result4))

// Delete all records of Top 4 dogs from Original Table

{
$bfa1 = $row1["Dogs_BFA_No"];
$query5 = "DELETE FROM totya WHERE Dogs_BFA_No = $bfa1";
$result5 = mysql_query($query5);
}

}

mysql_free_result($result2);





$query15 = "DELETE FROM totyb";
$mysql_result = mysql_query ($query15, $db);

 

[NigeB]

Oops, I had missed the ' ' from WHERE Dogs_BFA_No = $bfa1,
works a treat now, thanks for the pointers.

Cheers

Nige B