Page 1 of 1

problem with keeping an array in db--> help

Posted: Sun Dec 13, 2009 4:35 pm
by spirala
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);

Re: problem with keeping an array in db--> help

Posted: Mon Dec 14, 2009 1:31 am
by cpetercarter
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

Posted: Mon Dec 14, 2009 3:43 am
by spirala
ok, thanks for the information. I knew something was wrong :) thanks again

Re: problem with keeping an array in db--> help

Posted: Mon Dec 14, 2009 8:54 am
by AbraCadaver
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.
No, no, no.... Don't do that.

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);
The best bet would be use the `file_category` table as that looks like what it is for. If not, then you need a separate table to relate the chosen categories to the file.

Re: problem with keeping an array in db--> help

Posted: Mon Dec 14, 2009 4:35 pm
by spirala
Can you please help me with the code!????

Code: Select all

while($row = mysql_fetch_assoc($result)) {
    echo "<input type='checkbox' id='category' name='checkbox'>".$row['name']."<br>";
}
i have this while for dinamically writing all categories. and than this code:

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);
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.

Re: problem with keeping an array in db--> help

Posted: Mon Dec 14, 2009 5:17 pm
by AbraCadaver
You had this right before. I meant it's not checkbox[] in the $_POST array after it's submitted:

Code: Select all

while($row = mysql_fetch_assoc($result)) {
    echo "<input type='checkbox' id='category' name='[b]checkbox[][/b]'>".$row['name']."<br>";
} 
Just implode the array. No need for a loop.

Code: Select all

if(isset($_POST['upload'])) {
    $checkboxes = implode(',',  $_POST['checkbox']);
}
print_r($checkboxes);
Better would be serialize(), because then when you pull it out and unserialize() it will be an array again:

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.

Re: problem with keeping an array in db--> help

Posted: Mon Dec 14, 2009 5:21 pm
by spirala
it doesn't work again :( do you have any idea what should i do?

Re: problem with keeping an array in db--> help

Posted: Mon Dec 14, 2009 5:26 pm
by AbraCadaver
spirala wrote:it doesn't work again :( do you have any idea what should i do?
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.