tanyaa tanyaa - 4 months ago 22
MySQL Question

can't add foreign key for table that has files

I'm building a project with angular and php, I added to my database table "file" that I can send files to him and retrieve all files information. now I'm trying to add a foreign key("Customer_id") from "Customers" table to connect the customer to specific file. but when I try to add a relation it says: "error relational features are disabled". can any one please help?can it be that the problem because the table has files?

this is my code:

insert-priceOffer.php

<?php
if(isset($_FILES['uploaded_file'])) {
header('Content-Type: text/html; charset=utf-8');
$dbLink = mysqli_connect('localhost', 'root', '', 'hamatkin');
mysqli_query($dbLink,"SET character_set_client = utf8");
mysqli_query($dbLink,"SET character_set_connection = utf8");
mysqli_query($dbLink,"SET character_set_results = utf8");
$name = iconv("utf-8", "cp936", $name);
// Make sure the file was sent without errors
if($_FILES['uploaded_file']['error'] == 0) {
// Connect to the database

if(mysqli_connect_errno()) {
die("MySQL connection failed: ". mysqli_connect_error());
}

// Gather all required data
$name = $dbLink->real_escape_string($_FILES['uploaded_file']['name']);
$mime = $dbLink->real_escape_string($_FILES['uploaded_file']['type']);

$target_dir = realpath(__DIR__ . '/../..')."/docs/";
$target_file = $target_dir . basename($_FILES["uploaded_file"]["name"]);
if( move_uploaded_file($_FILES["uploaded_file"]["tmp_name"], $target_file)){
echo "File Uploaded successfully";
}else{
echo 'Upload failed';
}
//ets try now - ok! thanks a lot!! did i checked it ok?yea, the file should have been there

$size = intval($_FILES['uploaded_file']['size']);

// Create the SQL query
$query = "
INSERT INTO `file` (
`name`, `mime`, `size`, `data`, `created`
)
VALUES (
'{$name}', '{$mime}', {$size}, '{$data}', NOW()
)";

// Execute the query
$result = $dbLink->query($query);

// Check if it was successfull
if($result) {
echo 'Success! Your file was successfully added!';
}
else {
echo 'Error! Failed to insert the file'
. "<pre>{$dbLink->error}</pre>";
}
}
else {
echo 'An error accured while the file was being uploaded. '
. 'Error code: '. intval($_FILES['uploaded_file']['error']);
}

// Close the mysql connection
// $dbLink->close();
}
else {
echo 'Error! A file was not sent!';
}

// Echo a link back to the main page
$newURL = "/hamatkin/#/allPriceOffers";
header('Location: '.$newURL);
?>


get-all-priceOffer.php

<?php
header('Content-Type: text/html; charset=utf-8');
$connection = mysqli_connect('localhost','root','','hamatkin');

mysqli_query($connection,"SET character_set_client = utf8");
mysqli_query($connection,"SET character_set_connection = utf8");
mysqli_query($connection,"SET character_set_results = utf8");

if(!$connection){
die("couldnt connect".mysqli_error);
}
$query = "SELECT id, name, created,mime FROM file";
$queryResult = $connection->query($query);

$queryResult2 = array();
if($queryResult === FALSE) {
die($connection->error);
}
if( $queryResult->num_rows>0){
while($row = $queryResult->fetch_assoc()){
$queryResult2[] = $row;
}
}
$queryResult3 = json_encode($queryResult2);

echo json_encode($queryResult2);

?>


Html for displaying:

<div class="table-responsive">
<table class="customer-list table table-striped">
<thead>
<tr>
<!-- <th>#</th> -->
<th class="Column-Header"> מספר הצעת מחיר</th>
<th class="Column-Header"> קובץ</th>
<th class="Column-Header">תאריך</th>


</tr>
</thead>
<tbody>
<tr ng-repeat="x in files | filter:search_query | orderBy: order_query:reverse_query" >
<!-- <td>{{$index + 1}}</td> -->
<td>{{ x.id}}</td>
<td><a href="http://localhost:8081/hamatkin/docs/{{ x.name}}"target="_blank">{{ x.name}}</a></td>
<td> {{ x.created}} </td>

</tr>
</tbody>
</table>
</div>


this is my table :

CREATE TABLE `file` (
`id` Int Unsigned Not Null Auto_Increment,
`name` VarChar(255) Not Null Default 'Untitled.txt',
`mime` VarChar(50) Not Null Default 'text/plain',
`size` BigInt Unsigned Not Null Default 0,
`data` MediumBlob Not Null,
`created` DateTime Not Null,
PRIMARY KEY (`id`)
)

Answer

Verify that the engines used in both of the tables are innoDB.

As you mentioned in chat, your files table was MyISAM, hence the Foreign Constraints were disabled!!.