Forum Moderators: coopster
CREATE TABLE `projects` (
`projectname` varchar(32) NOT NULL default '',
`mission` varchar(32) NOT NULL default '',
`vision` varchar(32) NOT NULL default ''
) TYPE=MyISAM;INSERT INTO `projects` VALUES ('hoodlum', 'h1', 'h2');
INSERT INTO `projects` VALUES ('bullwinkle', 'b1', 'b2');
INSERT INTO `projects` VALUES ('partcode', 'p1', 'p2');
and the following code to show the table:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><html>
<head>
<title>Test delete</title>
<style type="text/css">
body { font: normal 10px Arial, sans-serif; }
td { padding: 3px; margin: 0; font: normal 12px Arial, sans-serif; }
thead td { color: white; font-weight: bold; background-color: #986; border-top: solid 3px #653; }
tbody td { border: 0; border-bottom: solid 1px #FED; }
tbody td.metric { background-color: #FFD; border-left: solid 1px #FED; }
tbody td.value { background-color: #FFE; border-right: solid 1px #FED; }
</style>
</head>
<body>
<?php
include('DB.class.php');
$table = "projects";
function StartsWith($main, $start) {
return (substr($main, 0, strlen($start)) == $start);
}
function isNumeric($type) {
return (StartsWith($type,"tinyint") ¦¦ StartsWith($type,"smallint") ¦¦ StartsWith($type,"mediumint")
¦¦ StartsWith($type,"int") ¦¦ StartsWith($type,"bigint") ¦¦ StartsWith($type,"float") ¦¦ StartsWith($type,"double"));
}
function isBoolean($type) {
return StartsWith($type, "bool");
}
function isBlob($type) {
return (StartsWith($type, "blob") ¦¦ StartsWith($type, "mediumblob") ¦¦ StartsWith($type, "LongBlob"));
}
function process_data($data, $datamodel, $cols) {
$temp = "";
foreach($datamodel as $model) {
$field = $model['Field'];
$type = $model['Type'];
if(isNumeric($type)) {
$temp .= "&$field=$data[$field]";
} else if(isBoolean($type)) {
$temp .= "&$field=$data[$field]";
} else if(isBlob($type)) {
//_____nothing to do
} else
$temp .= "&$field=$data[$field]";
}
$str = "<tr><td><a href='?action=edit$temp'>edit</a></td><td><a href='?action=delete$temp'>delete</a></td>";
foreach($cols as $c) {
$data[$c] = ($data[$c]==null)? " " : $data[$c];
$str .= "<td>" . $data[$c] . "</td>";
}
$str .= "</tr>\n";
return $str;
}
$db = new DB(); // connect to database
// get the table's structure
$q = "DESC " . $table;
$db->fetch($q);
$datamodel = $db->result;
// get the table's data
$q = "SELECT * FROM " . $table;
$db->fetch($q);
$data = $db->result;
// display the data
$cols = is_array($data[0])? array_keys($data[0]) : array_keys($data);
$str .= "<table><thead><tr><td>edit</td><td>delete</td><td>" . implode("</td><td>",$cols) . "</td></tr>\n</thead>\n";
$str .= "<tbody>\n";
if(!is_array($data[0]))
$str .= process_data($data, $datamodel, $cols);
else
foreach($data as $d)
$str .= process_data($d, $datamodel, $cols);
$str .= "</tbody>\n</table><p>";
echo $str;
// check if user invoked a delete command
if($_GET['delete']) {
$q = "DELETE FROM " . $table . " WHERE ";
$start = 0;
// assume the table has no key or index: build up the query column by column
foreach($datamodel as $d) {
$field = $d['Field'];
$type = $d['Type'];
$current = $_GET[$field];
if(isNumeric($type)) {
if($start)
$q .= ' AND ';
else
$start = 1;
$q .= "$field=$current";
} else if(isBoolean($type)) {
$values .= '$current';
} else if(isBlob($type)) {
//____nothing to do
} else {
if($start)
$q .= ' AND ';
else
$start = 1;
$q .= "$field='$current'";
}
}
$db->execute($q);
?>
<script language="JavaScript">
location.href="test_delete.php";
</script>
<?php
}
?>
</body>
</html>
The problem is, the deletion of a row does not yield the desired result. Perhaps there is a requirement to use forms anyway? I do not see why this approach should not work.
DB.class.php is the name of the database wrapper I am using with the following functions:
$db = new DB(): connects to the database using default db-name, uid, pwd
$db->fetch($q)/execute($q): executes query $q
$db->result: returns result set
Thanks in advance
Correct syntax
"DELETE FROM news WHERE id=$id";
Your solution
1- does not work
2- misses the point
Make sure your query is what you want it to be. Echo it to the browser for confirmation. Also, go into your class and add this to your code:
//example
$result = mysql_query($query) [b]or [url=http://us3.php.net/manual/en/function.die.php]die[/url]([url=http://us3.php.net/manual/en/function.mysql-error.php]mysql_error[/url]());[/b]
