Sql Statement

[laado]

I have a question about SQL statement. Suppose I have two tables name “News” and “history”. I want to delete news from news table and insert this news (deleted news) into the history table. I want to know the SQL query what will be the SQL Statement of above mention problem. Please reply as soon as possible. Thanx

[justsomeguy]

It's not one statement, it's more than one. You can use SELECT INTO to copy rows (assuming both tables have the same structure and you aren't violating any constraints) or INSERT..SELECT, and after you've inserted then you use a DELETE query to delete from the original table.

[laado]

i write following code but it cannt work why?<title>Delete News</title><body=background=new2.jpg><?phpglobal $title;global $text1;global $text2;global $dtime;include("config.php"); $newsid = $_GET['newsid'];$result = mysql_query("select from news (title, dtime, text1, text2) VALUES ('$title',$dtime,'$text1','$text2')",$connect);$result = mysql_query("insert INTO history (title, dtime, text1, text2) VALUES ('$title',$dtime,'$text1','$text2')",$connect); $newsid = $_GET['newsid']; $result = mysql_query("DELETE FROM news WHERE newsid='$newsid' ",$connect); ?>plz tell me whts the problem?

It's not one statement, it's more than one. You can use SELECT INTO to copy rows (assuming both tables have the same structure and you aren't violating any constraints) or INSERT..SELECT, and after you've inserted then you use a DELETE query to delete from the original table.

[justsomeguy]

That is not valid syntax for a SELECT statement. Review this page:http://dev.mysql.com/doc/refman/5.0/en/select.htmlIf you're trying to get the values from that table and store the data in the global variables, that's not the correct way to do it. The steps are to 1) use mysql_query to get the result set, 2) loop through each row using a while loop with mysql_fetch_assoc to get each row, 3) assign the data from each row to your global variables. You would need to move the insert statement into the loop so that it runs the insert for each row.Or, like I said, you can use a single INSERT..SELECT statement to do all of that.http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

[laado]

what will be the sql statement if i want to choose yesterday date.

That is not valid syntax for a SELECT statement. Review this page:http://dev.mysql.com/doc/refman/5.0/en/select.htmlIf you're trying to get the values from that table and store the data in the global variables, that's not the correct way to do it. The steps are to 1) use mysql_query to get the result set, 2) loop through each row using a while loop with mysql_fetch_assoc to get each row, 3) assign the data from each row to your global variables. You would need to move the insert statement into the loop so that it runs the insert for each row.Or, like I said, you can use a single INSERT..SELECT statement to do all of that.http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

[justsomeguy]

Check the examples here:http://dev.mysql.com/doc/refman/5.1/en/dat...nction_date-addSELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY)SELECT CURDATE() - INTERVAL 1 DAY