Forums
This topic is locked
[Help] Inserting a row from table1 to table2
29 Jan 2009 03:25:51 AM Moktar posted:
Hi all,I'm preparing a script ,
consists of :
-a user inserting page , into users_ins table
-an admin page do (show what users inserted , delete or prove an inserting to be shown in index.php ),
- and the index.php it shows the topics which was inserted by users , and admin has proved them . from proved_topic table
--[the problem]:
in the admin page :
when i click on prove button , how can i send the row i clicked into the proved_topic table and delete it from users_ins table
Replies
Replied 30 Jan 2009 10:46:54
30 Jan 2009 10:46:54 Alan C replied:
you have not said how you are developing this, php etc, so let's assume it's php . . .
you will need to pass the id in, and the fields will need to be exactly the same in both tables too
should get rid of a whole record - use with caution, you might be best to put a LIMIT on the end to make sure it can only delete one record
INSERT INTO table_2 SELECT * FROM tale_2 WHERE id=something
you will need to pass the id in, and the fields will need to be exactly the same in both tables too
DELETE FROM table_1 WHERE p_id=something
should get rid of a whole record - use with caution, you might be best to put a LIMIT on the end to make sure it can only delete one record
Replied 30 Jan 2009 15:17:09
30 Jan 2009 15:17:09 AM Moktar replied:
thanks for your response ,
i'm not a pro. in dm ,
i took your advice , but i'm not sure of the right script , so i used the delete serv behavior and tried to make some editing [
] but i think it is need a pro :
the user insert table means here : config
and the approve table means here down
Edited by - on 30 Jan 2009 15:18:26
i'm not a pro. in dm ,
i took your advice , but i'm not sure of the right script , so i used the delete serv behavior and tried to make some editing [
] but i think it is need a pro :the user insert table means here : config
and the approve table means here down
<?php require_once('Connections/config.php'); ?>
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
if ((isset($_GET['recordID'])) && ($_GET['recordID'] != "")) {
$insertSQL = sprintf("INSERT name, link, photo, site INTO down WHERE id=%s",
GetSQLValueString($_GET['recordID'], "int"));
mysql_select_db($database_config, $config);
$Result1 = mysql_query($insertSQL, $config) or die(mysql_error());
$insertGoTo = "admin.php";
if (isset($_SERVER['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $insertGoTo));
}
mysql_select_db($database_config, $config);
$query_fetch = "SELECT name, link, photo, site FROM config";
$fetch = mysql_query($query_fetch, $config) or die(mysql_error());
$row_fetch = mysql_fetch_assoc($fetch);
$totalRows_fetch = mysql_num_rows($fetch);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>
<body>
</body>
</html>
<?php
mysql_free_result($fetch);
?>
Edited by - on 30 Jan 2009 15:18:26
Replied 03 Feb 2009 20:07:52
03 Feb 2009 20:07:52 Alan C replied:
Hi,
this is not the easiest thing to do, I have to confess that I do a lot of my coding in php rather than DW. I know that DW can do a lot of things, but very often I find that it can not do what I need. If you are going to do a lot of database work then it's worth the time and effort that is needed to get more familiar with writing the php yourself. What I found was that first I needed to learn some mysql, then javascript, css and before you know it you can do a bit of everything but none of them in great depth
hope that helps [:I]
this is not the easiest thing to do, I have to confess that I do a lot of my coding in php rather than DW. I know that DW can do a lot of things, but very often I find that it can not do what I need. If you are going to do a lot of database work then it's worth the time and effort that is needed to get more familiar with writing the php yourself. What I found was that first I needed to learn some mysql, then javascript, css and before you know it you can do a bit of everything but none of them in great depth
hope that helps [:I]
Replied 13 May 2009 22:07:44
13 May 2009 22:07:44 Aadil Aadil replied:
hey jigs !
thanks for sharing such useful ledge but this question is still pending it really does rings the bells alan
thanks for sharing such useful ledge but this question is still pending it really does rings the bells alan
Replied 16 May 2009 19:35:08
16 May 2009 19:35:08 Alan C replied:
It's a while since I checked back here, [:I] thanks for the prod in the ribs . . .
I have tested this code and it works fine on my server
some explanation:
make sure you are connected to the database before executing this, you need a line something like this, the $luk_connect is the connection resource
I wrote this so it expects a GET parameter on the end of the url, with something like ?id=1 to pass the id of the record to be moved and then deleted. This is really open to abuse, but for my testing it was ok. Do NOT use this code as it is on any production site. If you are going to implement this then you need something really secure, because this code is changing the db AND deleting a record. My own way of doing that is to pass a one-time code as the url parameter, or pass the id in a session variable, that way it's pretty secure.
the echo lines are in there so you can see the actual sql query, I like to have that in there so that if I'm having a problem I can copy it then paste it into phpMyAdmin and make sure that it actually works.
There also needs to be some checking in there to test the query result, you need to know that the move has succeeded BEFORE you go on to do the delete, otherwise you are doomed [
]
think of this as proof of concept and not production code. BTW the two database tables need to have the same field names etc, and if you have auto increment set on the first table then that's ok, but remove it from the second table, otherwise you might get different id values being substituted. I've not tested that except for taking auto increment off table_2 so that I know that id will get the value from table_1.
I have tested this code and it works fine on my server
$moveSQL = sprintf("INSERT INTO %s SELECT * FROM %s WHERE %s=%d LIMIT 1", 'table_2', 'table_1', 'id', intval($_GET['id']));
echo __LINE__.' Move query was : '.$moveSQL.'<br />'; // comment out later
$Result1 = mysql_query($moveSQL, $luk_connect) or die(mysql_error());
// test $Result1 here, false = fail, true = success
// make sure this happened ok before deleting the record from table_1
$deleteSQL = sprintf("DELETE FROM %s WHERE %s=%d LIMIT 1", 'table_1', 'id', intval($_GET['id']));
echo __LINE__.' delete query was : '.$deleteSQL.'<br />'; // comment out later
$Result1 = mysql_query($deleteSQL, $luk_connect) or die(mysql_error());
// test $Result1 here
// make sure things went as expected and do something if not
some explanation:
make sure you are connected to the database before executing this, you need a line something like this, the $luk_connect is the connection resource
mysql_select_db($database_luk_connect, $luk_connect);
I wrote this so it expects a GET parameter on the end of the url, with something like ?id=1 to pass the id of the record to be moved and then deleted. This is really open to abuse, but for my testing it was ok. Do NOT use this code as it is on any production site. If you are going to implement this then you need something really secure, because this code is changing the db AND deleting a record. My own way of doing that is to pass a one-time code as the url parameter, or pass the id in a session variable, that way it's pretty secure.
the echo lines are in there so you can see the actual sql query, I like to have that in there so that if I'm having a problem I can copy it then paste it into phpMyAdmin and make sure that it actually works.
There also needs to be some checking in there to test the query result, you need to know that the move has succeeded BEFORE you go on to do the delete, otherwise you are doomed [
]think of this as proof of concept and not production code. BTW the two database tables need to have the same field names etc, and if you have auto increment set on the first table then that's ok, but remove it from the second table, otherwise you might get different id values being substituted. I've not tested that except for taking auto increment off table_2 so that I know that id will get the value from table_1.