problem with keeping an array in db--> help
Posted: Sun Dec 13, 2009 4:35 pm
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);
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);