problem with keeping an array in db--> help
Moderator: General Moderators
problem with keeping an array in db--> help
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);
-
cpetercarter
- Forum Contributor
- Posts: 474
- Joined: Sat Jul 25, 2009 2:00 am
Re: problem with keeping an array in db--> help
The file table is set up with one 'category_id' field. You cannot put an array of values into a db field, so you are limited to one category per file. If you want multiple categories, you need additional fields in the file table (eg 'category_id_1', 'category_id_2' etc), and functions to put a single value from the array into each field, and another to recreate the array when you take the data out again.
Re: problem with keeping an array in db--> help
ok, thanks for the information. I knew something was wrong
thanks again
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: problem with keeping an array in db--> help
No, no, no.... Don't do that.cpetercarter wrote:The file table is set up with one 'category_id' field. You cannot put an array of values into a db field, so you are limited to one category per file. If you want multiple categories, you need additional fields in the file table (eg 'category_id_1', 'category_id_2' etc), and functions to put a single value from the array into each field, and another to recreate the array when you take the data out again.
One problem is that checkbox is an array and there is no $_POST['checkbox[]']. It is $_POST['checkbox']. So you can implode() it into a list, or you can serialize() it before insert.
Code: Select all
$checkboxes = serialize($_POST['checkbox']);
// or
$checkboxes = implode(',', $_POST['checkbox']);
print_r($checkboxes);mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Re: problem with keeping an array in db--> help
Can you please help me with the code!????
i have this while for dinamically writing all categories. and than this code:
but something is missing and is not working right.
here is the output from the array
INSERT INTO file (name, size, create_date, user_ID, category_ID)
VALUES ('n535943306_1521462_3126268.jpg',
'75947',
'09-12-14',
'berna',
'')//only this is the problem, where suppose to be the categories where the file belongs.
Code: Select all
while($row = mysql_fetch_assoc($result)) {
echo "<input type='checkbox' id='category' name='checkbox'>".$row['name']."<br>";
}Code: Select all
$query2 = "SELECT * FROM file,file_category WHERE category_ID='$category_ID'";
$result = mysql_query($query2);
$user = mysql_real_escape_string($_POST['name']);
if ($result) {
$array= mysql_fetch_assoc($result);
}
if(isset($_POST['upload']))
{
for ($i=0; $i<count($_POST['category']);$i++) {
$checkboxes = implode(',', $_POST['checkbox']);
}
}
print_r($checkboxes);here is the output from the array
INSERT INTO file (name, size, create_date, user_ID, category_ID)
VALUES ('n535943306_1521462_3126268.jpg',
'75947',
'09-12-14',
'berna',
'')//only this is the problem, where suppose to be the categories where the file belongs.
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: problem with keeping an array in db--> help
You had this right before. I meant it's not checkbox[] in the $_POST array after it's submitted:
Just implode the array. No need for a loop.
Better would be serialize(), because then when you pull it out and unserialize() it will be an array again:
I still think you should use the `file_category` table instead, as that looks like what it is for.
Code: Select all
while($row = mysql_fetch_assoc($result)) {
echo "<input type='checkbox' id='category' name='[b]checkbox[][/b]'>".$row['name']."<br>";
} Code: Select all
if(isset($_POST['upload'])) {
$checkboxes = implode(',', $_POST['checkbox']);
}
print_r($checkboxes);Code: Select all
if(isset($_POST['upload'])) {
$checkboxes = serialize($_POST['checkbox']);
}
print_r($checkboxes);I still think you should use the `file_category` table instead, as that looks like what it is for.
Last edited by AbraCadaver on Mon Dec 14, 2009 5:22 pm, edited 1 time in total.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Re: problem with keeping an array in db--> help
it doesn't work again
do you have any idea what should i do?
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: problem with keeping an array in db--> help
Yes, you don't know what you are doing and need to figure that out before writing the code. You are trying to compare a category_id in the file_category table with category_id in the files table which is a list of text categories. You need to diagram your database and decide how you want it to work and what the relations are. I'm sorry, the more I look at your code, there is no easy fix.spirala wrote:it doesn't work againdo you have any idea what should i do?
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.