I have an issue. I am beginner in php so i have problem with keeping an array with
categories in which one file belongs. One file can belong in one or more categories.
Here is the SQL:
REATE TABLE category (
category_ID int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary',
name char(15) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
PRIMARY KEY (category_ID)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;
/Data for the table category /
insert into category(category_ID,name) values (1,'video'),(2,'audio'),(3,'e-books'),(4,'word_documents'),(5,'pictures'),(6,'fonts'),(7,'other');
/Table structure for table download /
DROP TABLE IF EXISTS download;
CREATE TABLE download (
download_ID int(3) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary',
date_download date NOT NULL,
popularity mediumint(9) DEFAULT NULL,
file_ID int(3) unsigned NOT NULL,
PRIMARY KEY (download_ID),
KEY FK_download (file_ID),
CONSTRAINT FK_download FOREIGN KEY (file_ID) REFERENCES file (fileID) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/Data for the table download /
/Table structure for table file /
DROP TABLE IF EXISTS file;
CREATE TABLE file (
fileID int(3) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary',
name char(100) NOT NULL,
size float NOT NULL,
create_date date NOT NULL,
category_ID int(3) unsigned NOT NULL,
user_ID int(3) unsigned NOT NULL,
PRIMARY KEY (fileID),
KEY FK_file1 (category_ID),
KEY FK_file (user_ID),
CONSTRAINT FK_file FOREIGN KEY (user_ID) REFERENCES user (user_ID) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE file_category (
file_category_ID int(10) unsigned NOT NULL AUTO_INCREMENT,
file_ID int(3) unsigned NOT NULL,
category_ID int(3) unsigned NOT NULL,
PRIMARY KEY (file_category_ID),
KEY FK_file_category (file_ID),
KEY FK_file_category1 (category_ID),
CONSTRAINT FK_file_category FOREIGN KEY (file_ID) REFERENCES file (fileID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FK_file_category1 FOREIGN KEY (category_ID) REFERENCES category (category_ID) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE user (
user_ID int(3) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary',
first_name char(15) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
last_name char(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
e_mail char(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
username varchar(12) NOT NULL,
password text NOT NULL,
PRIMARY KEY (user_ID)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1;
and the code:
<?php
$sql = "SELECT name FROM category";
$result = mysql_query($sql);
?>
<tr>
<form action="upload.php" method="post">
<?php
while($row = mysql_fetch_assoc($result)) {
echo "<input type='checkbox' id='categories[]' name='checkbox[]'>".$row['name']."<br>";
}
$query2 = mysql_query("SELECT *
FROM filemanager.file
WHERE (category_ID = '$category_ID');");
$result = mysql_query($query2);
$category = mysql_real_escape_string($POST['name']);
if ($result) {
$array= mysql_fetch_assoc($result);
}
if(isset($POST['upload']))
{
for ($i=0; $i<count($POST['categories[]']);$i++) {
echo "<br />value $i = ".$POST['categories[]'][$i];
}
}
//$checkbox=$_POST['categories[]']; //takes the data from a post operation...
?>
here is the main query:
$query = "INSERT INTO file (name, size, create_date, user_ID, category_ID)
VALUES ('".$FILES['file']['name']."',
'".$FILES['file']['size']."',
'".date("y-m-d")."',
'".mysql_real_escape_string($SESSION['user'])."',
'".mysql_real_escape_string($SESSION['checkbox[]'])."')"; //only this thing doesn't work
print_r($_POST);
die ($query);
