[SOLVED] A problem no other forum has answered yet.. can you?

[0o0o0]

deleting all the info in a database... but leaving selected ones in ... ( repeated everyday)

 

eg.. I used football say..

 

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

 

is there a command  like "except where" ???

 

anyone who can help would be awsome, ive applied to to many forums this week lol.

 

thanks

[F1Fan]

DELETE FROM table WHERE name != 'whatever' AND name != 'whatever'

[xtopolis]

even tastier:

DELETE FROM table WHERE name NOT IN('Brett Farve','Dan Marino')

[F1Fan]

Downright YUMMY!

[0o0o0]

lol guys are funny... thanks.

 

Will these delete say all the other players rows and leave me with only marino and farves rows left in the db??

 

correct right?

 

It'll be in a cron script so its doin this every day.. or if it were just once I would do it manually.

 

(its not even a football db.. but using it to explain better what I wanna do. )

 

WHOA... now even a better question... importing from a cvs..file.. is there a possible way to rip only these two player for the csv before inserting into db? doubt it.. but thought Id ask.

 

 

[F1Fan]

Yup.

[0o0o0]

  lol this forum rocks you definately live up to the name.. I post a question and the answers there in mins! Freaks.

 

how about this ...  can you stop duplicates as they come into the db???  or only after by making a new temp table?

 

thanks again.

[F1Fan]

Well, you can run a select query before you insert, or you could add constraints on your DB table that won't allow that.

[0o0o0]

k now im gettin peeved..

 

I went into sql console tester thing in myphpadmin

 

and entered

 

DELETE FROM MYTABLE WHERE player NOT IN('Farve')

 

it ran it.. and succesfully did it..

 

so I wanted it to show me the exact php code and it gave me this...

 

$sql = 'DELETE FROM MYTABLE WHERE player NOT IN(\'Farve\')';

 

so i throw that in my php code and run it from my php page..

 

refilled the db full of data again..

 

and tried it.. and it will not trim it down to farves..

 

 

( again nfl just example)

 

any idea?

<?php
include("../lalalalala.php");                              
$connection = mysql_connect($host,$user,$password)   
      or die ("couldn't connect to server");
$db = mysql_select_db($database,$connection)         
      or die ("Couldn't select database");


$sql = 'DELETE FROM MYTABLE WHERE player NOT IN(\'Farve\')';

mysql_close($connection); 

echo 'Test'; 


?> 

 

 

[F1Fan]

I've never used single quotes around a query, and I think that may cause issues. Try this:

 

$sql = "DELETE FROM MYTABLE WHERE player NOT IN('Farve')";

[0o0o0]

im lost... doesnt seem to want to work.

 

 

but... it works in the sql tester console

[F1Fan]

I'm sorry, I'm really tired. You're never running the query. Add this:

mysql_query($sql);

after you declare $sql.

[0o0o0]

omg.. your right.. ha ha ! me and you both... my eyes are so bloodshot.. ive been searchin for how to do this for 24+ hours straight.

I can sleep now.

 

Thanks alot

 

[0o0o0]

ok I gotta ask one more question and im done for the weekend.

 

how is it when loading a csv file into a db... it overwrites the info in the db? Id like it to just add to the end of whats already in the db.

 

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

 

what am I missing or need to take out so the import will be added to the end of the db as the place to start loading.

 

Thanks.

[F1Fan]

To be honest, I've never done an import from a csv to a sql db.