Your database modeling is wrong, cannot save equal records at the bank, they need to have unique identifiers, usually the field ID as AUTO_INCREMENT, so you don’t need to inform it in the INSERT SQL instructions because this value will be automatically generated by Mysql.
It has many different data in the same table, this type of modeling is not recommended, try to separate, for example a table for performers, a table for the songs, another table for users and a table that relates the songs to the users. You could still create a table of playlists and albuns but it depends on your design and need.
Example of modeling:
-- ----------------------------
-- Table structure for artists
-- ----------------------------
DROP TABLE IF EXISTS `artists`;
CREATE TABLE `artists` (
`id` int(4) NOT NULL AUTO_INCREMENT COMMENT 'first key',
`artistname` varchar(60) COLLATE utf8_bin NOT NULL COMMENT 'Nome do artista',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ----------------------------
-- Records of artists
-- ----------------------------
INSERT INTO `artists` VALUES ('1', 'Palavrantiga');
INSERT INTO `artists` VALUES ('2', 'Natalie Grant');
INSERT INTO `artists` VALUES ('3', 'Skillet');
INSERT INTO `artists` VALUES ('4', 'Kari Jobe');
-- ----------------------------
-- Table structure for tracks
-- ----------------------------
DROP TABLE IF EXISTS `tracks`;
CREATE TABLE `tracks` (
`id` int(4) NOT NULL AUTO_INCREMENT COMMENT 'first key',
`trackname` varchar(60) COLLATE utf8_bin NOT NULL COMMENT 'Nome da música',
`artist` int(4) NOT NULL COMMENT 'Código do artista',
`link` varchar(255) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ----------------------------
-- Records of tracks
-- ----------------------------
INSERT INTO `tracks` VALUES ('1', 'Breathe On Us', '4', 'http://open.spotify.com/track/1IFs5I1nHXudA3zV1CqQY4');
INSERT INTO `tracks` VALUES ('2', 'Hands To Heavens', '4', 'http://open.spotify.com/track/6NGcTKotjNCfgTyhzL5Cp4');
INSERT INTO `tracks` VALUES ('3', 'Closer to Your Heart', '2', 'http://open.spotify.com/track/0fPjgWjy57cyP4ZihqBBDZ');
INSERT INTO `tracks` VALUES ('4', 'Hero', '3', 'http://open.spotify.com/track/4CbKVDZkYKdv69I4bCaKUq');
INSERT INTO `tracks` VALUES ('5', 'Monster', '3', 'http://open.spotify.com/track/2UREu1Y8CO4jXkbvqAtP7g');
INSERT INTO `tracks` VALUES ('6', 'Rookmaaker', '1', 'http://open.spotify.com/track/6WDhNHrK2xfCzKJQxVhxJC');
-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` int(4) NOT NULL AUTO_INCREMENT COMMENT 'first key',
`username` varchar(60) COLLATE utf8_bin NOT NULL COMMENT 'Nome do usuário',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO `users` VALUES ('1', 'Carlos');
INSERT INTO `users` VALUES ('2', 'Pedro');
-- ----------------------------
-- Table structure for user_tracks
-- ----------------------------
DROP TABLE IF EXISTS `user_tracks`;
CREATE TABLE `user_tracks` (
`userId` int(4) NOT NULL COMMENT 'ID do usuário',
`trackId` int(4) NOT NULL COMMENT 'ID da música',
`order` int(3) NOT NULL DEFAULT '0',
PRIMARY KEY (`userId`,`trackId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ----------------------------
-- Records of user_tracks
-- ----------------------------
INSERT INTO `user_tracks` VALUES ('1', '1', '3');
INSERT INTO `user_tracks` VALUES ('1', '2', '1');
INSERT INTO `user_tracks` VALUES ('1', '3', '4');
INSERT INTO `user_tracks` VALUES ('1', '4', '2');
INSERT INTO `user_tracks` VALUES ('1', '6', '5');
INSERT INTO `user_tracks` VALUES ('2', '1', '0');
INSERT INTO `user_tracks` VALUES ('2', '3', '2');
INSERT INTO `user_tracks` VALUES ('2', '6', '0');
And an example query using the library Connectionmsi:
<?php
include_once 'ConnectionMSi.php';
$con = New ConnectionMSi('localhost','root','','test');
$sql = 'SELECT
u.username, a.artistname, t.trackname, t.link, ut.userId, ut.trackId, ut.order
FROM user_tracks ut
INNER JOIN users u ON ut.userId = u.id
INNER JOIN tracks t ON ut.trackId = t.id
INNER JOIN artists a ON t.artist = a.id
ORDER BY
ut.order, ut.userId, ut.trackId;';
$result = $con->ExecuteSQL($sql);
$registros = $result->fetch_all(MYSQLI_ASSOC);
echo '<table border="1" align="center" cellpadding="2">
<tr>
<th>Order</th>
<th>User Name</th>
<th>Artist Name</th>
<th>Track Name</th>
<th>Link</th>
</tr>';
foreach ($registros as $row) {
echo "
<tr>
<td>{$row['order']}</td>
<td>{$row['username']}</td>
<td>{$row['artistname']}</td>
<td>{$row['trackname']}</td>
<td><a href=\"{$row['link']}\">{$row['link']}</a></td>
</tr>";
}
echo '</table>';
PS.: Consider using a library to manage your database connection.
PS2.: Urgently consider changing mysql
for mysqli
.
Your code is wrong there, your commands
session_start();
andinclude 'connect.php';
is out of the tag<?php
– KaduAmaral
@Kaduamaral just put to exemplify that I am calling at the beginning of the page this. But I will edit, see if it is correct as I am doing.
– João Paulo Fricks
Why are you recording more than once? Change a field so you have to record more than once?
– Jorge B.
@Jorgeb. I want to record more than once why later I want to access these separate data.
– João Paulo Fricks
There’s no logic to that, so you probably don’t have repeat records in the database. For that you might as well have a field in the database that tells you what you want.
– Jorge B.
But why do you want to record more than once? It’s for different playlists? like my 10 playlist has repeated songs with your playlist?
– Jorge B.
Not @Jorgeb. I want to make 12 playlists for 1 year. Each month a playlist with 10 items (like the most played on a radio). Got it? Then I will make a list of these 12 playlists for the user to make a query. I want to use this screen inside an Android app, via Webview I’m doing. I will only give the end user this list of registered playlists. The registration part stays with me only so I can register each month, week or even a different playlist. It was clear?
– João Paulo Fricks
I do not know who is negativizing my doubt, but I am learning and instead of negativing could help with some constructive comment.
– João Paulo Fricks
Let’s go continue this discussão in chat.
– Jorge B.