Searched for 7 hours.. heres a huge question

0o0o0

Searched the net all night.. coming up with the same repitious tutorials, and explainations.. but not what im looking for ...

here we go...

lets just say its football... and just say its a fans site for all players in the league say a forum.....

guessing 700 players then 50,000 million fans...

ok lets say the db is PACKED... and gotta cut down on the players.. to cut down on the 50,000 fans posts.

so out of the entire db.. I wanna keep brett farve and i dunno.. tom bradey

but wanna delete the other guessing 698 players..

...................... how would I do this?

delete from TableName where field1=value doesnt work for me here.. cause I need to keep two..

almost like ...

delete * from table ( instead of where) "all but" player=tom_bradey and player = brett_farve

lol anyhow if anyone can help me out as you can see im a little tired.. I was gonna use fish, and fisherman as an example.. lol

thanks so much.

Piranha

Basically there are 3 ways that I can think of:

Probably the easiest and best way. But the database have to be taken out of production for a little while. Basically you create a temp table (temp) that is a copy of the main table (main). Then copy the information you want to save into temp. Delete all from main. And last copy everything from temp to main.
A query like this one:

delete from table where column != 'abc' and column != 'def';

A query with subqueries:

delete form table t1 where t1.id not in (select t2.id from table t2 where t2.column = 'abc' or t2.column = 'def')

Oh, and one last thing. Test the query you create on test data before you run it live.

0o0o0

hmm..

delete form table t1 where t1.id not in (select t2.id from table t2 where t2.column = 'abc' or t2.column = 'def')

is this like a delete from table with the exception of any line containing "brett farve"

type thing?

Theres nothing that can be used to sit a few entries out on each delete?

Delete * from NFL except where player = Brett Farve and player = Dan Marino

? nothing? no "except where" in php?

Piranha

Well, this is SQL and not PHP. Database queries is Structured Query Language.

There are no "except where" in SQL. But you can use different operators in the where-clausule to get the same result. Look in the manual to see the different operators.

It seems to me that you have to play around with the different operators to understand how they work. If you have problems with it afterwards please don't hesitate to ask again.

HalfaBee

you could do

Delete * from NFL where player != 'Brett Farve' or player != 'Dan Marino'

Piranha

That wouldn't work HalfaBee. Test it, that query would remove all the rows in the table.

0o0o0

easiest fix.. figured it out

$sql = "DELETE FROM NFL WHERE agent_id NOT IN('Brett_Farve' and 'Dan_Marino' )";

dumps the rest everytime the database is updated..

and leaves these two players...

( nfl used as example )

Thanks tho!

0o0o0

got another one.. anyone know? while im searchin online.

I have a csv pulled into the Table...

I DO NOT want it to over write whats already in the table.. just add the cvs contents to the end...

heres what I got..
I added IGNORE in before INTO TABLE... stil no work.

$sql = 'LOAD DATA LOCAL INFILE \'/home/sites/www.mywebsite.com/data/data.csv\' INTO TABLE `NFL` FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\' ESCAPED BY \'\\\\\' LINES TERMINATED BY \'\\r\\n\''; 
$result = mysql_query($sql) or die('Query failed: ' . mysql_error());

Any other ideas what to add??