Inner Joins MySQL Image Display
Inner Joins MySQL Image Display
<?php
$html = '';
if (isset($_GET['sletBruger']))
$id = secInput($_GET['id']);
$billede = secInput($_GET['billede']);
if (checkElement($id))
if (is_file('../img/'.$billede))
unlink('../img/'.$billede);
$sql = "DELETE brugere.*, bruger_billeder.* FROM brugere
INNER JOIN brugerroller
ON brugerroller.ID = brugere.fk_rolle
INNER JOIN bruger_billeder
ON bruger_billeder.billednavn = bruger_billeder.fk_bruger
WHERE brugere.ID = ".$id;
deleteFromDb($sql);
?>
<h5>Oprettede Brugere</h5>
<table class="table table-striped table-bordered table-hover">
<thead>
<tr>
<th>#</th>
<th>NAVN</th>
<th>EMAIL</th>
<th>MOBILNUMMER</th>
<th>NIVEAU</th>
<th>KILOMETER</th>
<th>BILLEDE</th>
<th>REDIGER</th>
<th>SLET</th>
</tr>
</thead>
<tbody>
<?php
//SQL - henter det som vi har valgt, i databasen. Inner join parrer brand og kategori fra produklisten, med hhv brand listen og kategorilisten.
$sql = "SELECT DISTINCT
brugere.*, brugere.navn, brugere.email, brugere.mobil, brugere.kilometer,
faerdighedsroller.f_navn, brugerroller.rollenavn, bruger_billeder.billednavn
FROM brugere
INNER JOIN faerdighedsroller
ON faerdighedsroller.ID = brugere.fk_faerdighed
INNER JOIN brugerroller
ON brugere.fk_rolle = brugerroller.ID
INNER JOIN bruger_billeder
ON bruger_billeder.billednavn = bruger_billeder.fk_bruger
ORDER BY ID DESC";
$arr = getData($sql, $conn);
foreach($arr as $value)
$html .= '
<tr>
<td>'.$value['ID'].'</td>
<td>'.$value['navn'].'</td>
<td>'.$value['email'].'</td>
<td>'.$value['mobil'].'</td>
<td>'.$value['f_navn'].'</td>
<td>'.$value['kilometer'].'</td>
<td><img src="../images/' . $value['billednavn'] . '" alt="' . $value['billednavn'] . '"></td>
<td><a href="?s=sections/redigerBruger&id='.$value['ID'].'"><i class="fa fa-edit ">Rediger</i></a></td>
<td><a class="btn btn-danger" onclick="return confirm('Er du sikker på du vil slette '.$value['navn'].'');" href="?s=sections/brugere&id='.$value['ID'].'&sletBruger">Slet</a></td>
</tr>
';
$html .= '
</tbody></table>
';
return $html;
?>
There are too many inner joins for me to see around, so I appreciate any help.
What I am missing is the image to be displayed.
At the 4th image, you can see it how image would look.
It works on that page, but not the one I am doing now.
bruger = user
slet = delete
rediger = edit
billednavn = image name
bruger_billeder = user image
bruger_roller = user roles
faerdighedsroller = niveau
all images are on bruger_billeder table column billednavn
fk_bruger = fk users its in bruger_billeder aswell
I need it to show the image that's on the other table.
Warning: Invalid argument supplied for foreach() in kajakklubben-hadminsectionsbrugere.section.php on line 63
2 Answers
2
i solved this problem, thanks for all your help
<?php
$html = '';
if (isset($_GET['sletBruger']))
$id = secInput($_GET['id']);
$billede = secInput($_GET['billede']);
if (checkElement($id))
if (is_file('../img/'.$billede))
unlink('../img/'.$billede);
$sql = "DELETE brugere.*, bruger_billeder.* FROM brugere
INNER JOIN brugerroller
ON brugerroller.ID = brugere.fk_rolle
INNER JOIN bruger_billeder
ON bruger_billeder.fk_bruger = bruger.ID
WHERE brugere.ID = ".$id;
deleteFromDb($sql);
?>
<h5>Oprettede Brugere</h5>
<table class="table table-striped table-bordered table-hover">
<thead>
<tr>
<th>#</th>
<th>NAVN</th>
<th>EMAIL</th>
<th>MOBILNUMMER</th>
<th>NIVEAU</th>
<th>KILOMETER</th>
<th>BILLEDE</th>
<th>REDIGER</th>
<th>SLET</th>
</tr>
</thead>
<tbody>
<?php
//SQL - henter det som vi har valgt, i databasen. Inner join parrer brand og kategori fra produklisten, med hhv brand listen og kategorilisten.
$sql = "SELECT DISTINCT
brugere.*, brugere.navn, brugere.email, brugere.mobil, brugere.kilometer,
faerdighedsroller.f_navn, brugerroller.rollenavn, bruger_billeder.billednavn
FROM brugere
INNER JOIN faerdighedsroller
ON faerdighedsroller.ID = brugere.fk_faerdighed
INNER JOIN brugerroller
ON brugere.fk_rolle = brugerroller.ID
INNER JOIN bruger_billeder
ON bruger_billeder.fk_bruger = brugere.ID
ORDER BY ID DESC";
$arr = getData($sql, $conn);
foreach($arr as $value)
$html .= '
<tr>
<td>'.$value['ID'].'</td>
<td>'.$value['navn'].'</td>
<td>'.$value['email'].'</td>
<td>'.$value['mobil'].'</td>
<td>'.$value['f_navn'].'</td>
<td>'.$value['kilometer'].'</td>
<td><img src="../images/' . $value['billednavn'] . '" alt="' . $value['billednavn'] . '"></td>
<td><a href="?s=sections/redigerBruger&id='.$value['ID'].'"><i class="fa fa-edit ">Rediger</i></a></td>
<td><a class="btn btn-danger" onclick="return confirm('Er du sikker på du vil slette '.$value['navn'].'');" href="?s=sections/brugere&id='.$value['ID'].'&sletBruger">Slet</a></td>
</tr>
';
$html .= '</tbody></table>';
return $html;
?>
This answer is a first test version, so to say. Its purpose is to find out where your problem exactly resides (in your PHP functions code, in your db settings, or in the data you have in your db tables).
So, instead of your PHP page, use my two pages (brugere.php and connect.php), exactly as they are. But, first, in the page connect.php, change my database credentials with yours! Run the two pages and see how it works. Depending on the results or on an eventual raised error/exception/warning we will then have a starting point to solve your problem.
brugere.php
connect.php
connect.php
Note: In the beginning, let's agree to concentrate ourself on the selecting records part (e.g. on successfully running the SELECT statement), not on the deleting table data part (e.g. on successfully running the DELETE statement).
SELECT
DELETE
I also provided a list of table definitions and data, that I used in my tests. Maybe we will not need it. But I posted it so that you can make your own picture of how the tables and their relations should be, or should have have been, defined.
Since you didn't provide the name/type of the data access extension that you are using (PDO, object-oriented mysqli, or procedural mysqli), I decided to use PDO in my codes. After we find the problem it will be very easy to switch the code to whatever extension you are you using.
I'll wait for a feedback from you.
Good luck.
brugere.php:
<?php
require 'connection.php';
$brugerDeleted = FALSE;
$imagesDir = '../images/';
if (isset($_GET['sletBruger']))
$brugerId = $_GET['id'] ?? '';
/*
* ===========================================
* Validate the bruger id sent in query string
* ===========================================
*/
if (empty($brugerId))
$errors = 'No bruger id provided. Please provide one.';
elseif (!is_numeric($brugerId))
$errors = 'The provided bruger id must be an integer.';
else
/*
* =========================================================
* Get the billednavn for the bruger id sent in query string
* =========================================================
*/
$sql = 'SELECT billednavn
FROM bruger_billeder
WHERE fk_bruger = :fk_bruger';
$statement = $connection->prepare($sql);
$statement->execute([
':fk_bruger' => $brugerId,
]);
$billednavn = $statement->fetchColumn(0);
/*
* ===========================================================
* Delete the file defined by the value of "billednavn" column
* in "bruger_billeder" table from the images directory
* ===========================================================
*/
if (FALSE !== $billednavn && is_file($imagesDir . $billednavn))
unlink($imagesDir . $billednavn);
/*
* ===================================================================
* Delete the bruger identified by the bruger id sent in query string.
* The corresponding billednavn is automaticaly deleted too, because
* of the FK defined in "bruger_billeder" table.
* ===================================================================
*/
$sql = 'DELETE FROM brugere
WHERE ID = :ID';
$statement = $connection->prepare($sql);
$statement->execute([
':ID' => $brugerId,
]);
$brugerDeleted = TRUE;
/*
* =======================
* Get the list of brugere
* =======================
*/
$sql = 'SELECT
brug.*,
fr.f_navn,
br.rollenavn,
bb.billednavn
FROM brugere AS brug
INNER JOIN bruger_billeder AS bb ON brug.ID = bb.fk_bruger
INNER JOIN faerdighedsroller AS fr ON brug.fk_faerdighed = fr.ID
INNER JOIN brugerroller AS br ON brug.fk_rolle = br.ID
ORDER BY brug.ID DESC';
$statement = $connection->prepare($sql);
$statement->execute();
$brugere = $statement->fetchAll(PDO::FETCH_ASSOC);
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
<meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes" />
<meta charset="UTF-8" />
<!-- The above 3 meta tags must come first in the head -->
<title>Demo</title>
<link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" type="text/css" rel="stylesheet" />
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
<script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.3/umd/popper.min.js" integrity="sha384-ZMP7rVo3mIykV+2+9J3UJ46jBk0WLaUAdn689aCwoqbBJiSnjAK/l8WvCWPIPm49" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
<style type="text/css">
body padding: 30px;
.centered text-align: center;
img max-width: 200px !important;
</style>
</head>
<body>
<div class="container">
<h5>Oprettede Brugere</h5>
<?php
if (isset($errors))
foreach ($errors as $error)
?>
<div class="alert alert-danger alert-dismissible fade show" role="alert">
<?php echo $error; ?>
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>
<?php
elseif ($brugerDeleted)
?>
<div class="alert alert-success alert-dismissible fade show" role="alert">
The selected bruger has been successfully deleted.
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>
<?php
?>
<table class="table table-striped table-bordered table-hover">
<thead>
<tr>
<th>#</th>
<th>NAVN</th>
<th>EMAIL</th>
<th>MOBILNUMMER</th>
<th>NIVEAU</th>
<th>KILOMETER</th>
<th class="centered">BILLEDE</th>
<th class="centered">REDIGER</th>
<th class="centered">SLET</th>
</tr>
</thead>
<tbody>
<?php
if (!empty($brugere))
foreach ($brugere as $bruger)
$id = $bruger['ID'];
$navn = $bruger['navn'];
$email = $bruger['email'];
$mobil = $bruger['mobil'];
$kilometer = $bruger['kilometer'];
$fNavn = $bruger['f_navn'];
$rollenavn = $bruger['rollenavn'];
$billednavn = $bruger['billednavn'];
?>
<tr>
<td><?php echo $id; ?></td>
<td><?php echo $navn; ?></td>
<td><?php echo $email; ?></td>
<td><?php echo $mobil; ?></td>
<td><?php echo $fNavn; ?></td>
<td><?php echo $kilometer; ?></td>
<td class="centered">
<img src="<?php echo $imagesDir . $billednavn; ?>" alt="<?php echo $billednavn; ?>" class="img-fluid img-thumbnail" />
</td>
<td class="centered">
<a href="?s=sections/redigerBruger&id=<?php echo $id; ?>"
class="btn btn-success btn-sm">
<i class="fa fa-edit"></i>
</a>
</td>
<td class="centered">
<a href="?s=sections/brugere&id=<?php echo $id; ?>&sletBruger"
onclick="return confirm('Er du sikker på du vil slette '<?php echo $navn; ?>'');"
class="btn btn-danger btn-sm">
<i class="fa fa-trash"></i>
</a>
</td>
</tr>
<?php
else
?>
<tr>
<td colspan="9">
No records found
</td>
</tr>
<?php
?>
</tbody>
</table>
</div>
</body>
</html>
connection.php:
<?php
/*
* This page contains the code for creating a PDO connection instance.
*/
// Db configs.
define('HOST', 'localhost');
define('PORT', 3306);
define('DATABASE', 'tests');
define('USERNAME', 'root');
define('PASSWORD', 'root');
define('CHARSET', 'utf8');
/*
* Error reporting.
*
* @link http://php.net/manual/en/function.error-reporting.php
*/
error_reporting(E_ALL);
ini_set('display_errors', 1); /* SET IT TO 0 ON A LIVE SERVER! */
/*
* Create a PDO instance as db connection to db.
*
* @link http://php.net/manual/en/class.pdo.php
* @link http://php.net/manual/en/pdo.constants.php
* @link http://php.net/manual/en/pdo.error-handling.php
* @link http://php.net/manual/en/pdo.connections.php
*/
$connection = new PDO(
sprintf('mysql:host=%s;port=%s;dbname=%s;charset=%s', HOST, PORT, DATABASE, CHARSET)
, USERNAME
, PASSWORD
, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => FALSE,
PDO::ATTR_PERSISTENT => FALSE,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]
);
Table "bruger_billeder":
CREATE TABLE `bruger_billeder` (
`fk_bruger` int(11) unsigned NOT NULL,
`billednavn` varchar(255) NOT NULL,
PRIMARY KEY (`fk_bruger`),
CONSTRAINT `bruger_billeder_ibfk_1` FOREIGN KEY (`fk_bruger`) REFERENCES `brugere` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `bruger_billeder` (`fk_bruger`, `billednavn`)
VALUES
(1,'image-3.jpg'),
(2,'image-1.jpg'),
(3,'image-4.jpg'),
(4,'image-1.jpg'),
(5,'image-2.jpg');
Table "brugere":
CREATE TABLE `brugere` (
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`navn` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`mobil` varchar(8) NOT NULL,
`kilometer` varchar(255) NOT NULL,
`fk_tilmelding` int(11) NOT NULL,
`fk_faerdighed` int(11) unsigned NOT NULL,
`fk_rolle` int(11) unsigned NOT NULL,
PRIMARY KEY (`ID`),
KEY `fk_faerdighed` (`fk_faerdighed`),
KEY `fk_rolle` (`fk_rolle`),
CONSTRAINT `brugere_ibfk_1` FOREIGN KEY (`fk_faerdighed`) REFERENCES `faerdighedsroller` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `brugere_ibfk_2` FOREIGN KEY (`fk_rolle`) REFERENCES `brugerroller` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `brugere` (`ID`, `navn`, `email`, `password`, `mobil`, `kilometer`, `fk_tilmelding`, `fk_faerdighed`, `fk_rolle`)
VALUES
(1,'asdf','kjhkjhj','ziasdf','kjhdf','234',0,2,5),
(2,'hhkjh','iuzaiusdf','mashdfk','ziqewr','673',0,2,5),
(3,'izziu','trere','jhghjf','bnbngh','877',0,2,1),
(4,'oiuoiu','rtzadskf','kjhfasdf','cbvcbvc','654',0,3,3),
(5,'piuoiu','rztrtr','mnbfd','ewtre','49',0,1,4);
Table "brugerroller":
CREATE TABLE `brugerroller` (
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`rollenavn` varchar(25) NOT NULL,
`niveau` tinyint(2) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `brugerroller` (`ID`, `rollenavn`, `niveau`)
VALUES
(1,'jhkjhj',1),
(2,'iuzot',0),
(3,'uziqwezr',0),
(4,'etretre',1),
(5,'poiuiou',0);
Table "faerdighedsroller":
CREATE TABLE `faerdighedsroller` (
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`f_navn` varchar(255) NOT NULL,
`niveau` tinyint(3) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `faerdighedsroller` (`ID`, `f_navn`, `niveau`)
VALUES
(1,'iuziuz',0),
(2,'iouoiu',0),
(3,'uzizu',1),
(4,'kljh',0),
(5,'trerte',1);
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Comments are not for extended discussion; this conversation has been moved to chat.
– Samuel Liew♦
Sep 5 '18 at 3:56