Writing to Mysql with PHP

Question

I want to record this data more than once but I can’t. I have 10 items each of them with $position,$track,$Artist,$Recorder coming from the form. I can record the first time, then I try to record again with the same data and I can’t. With different names of all fields I can record but everything is the same place and I would like to separate the data in 10 by 10 so I can print a list later to be accessed individually. What do I need to do? Is it in Mysql or PHP? Follow my PHP code that is making this recording and I hope my question is clear.

Success.php

<?php
@ob_start();
session_start();
include 'connect.php';
?>

//esse conteudo tenho no meio da pagina
<?php
  if (isset($_SESSION['tracks']) && count($_SESSION['tracks']) > 0):
     $traks = count($_SESSION['tracks']);
     for ($i=0; $i < $traks; $i++) {
        $posicao   = $_SESSION['tracks'][$i]['posicao'];
        $track     = $_SESSION['tracks'][$i]['track'];
        $artist    = $_SESSION['tracks'][$i]['artist'];
        $recorder  = $_SESSION['tracks'][$i]['recorder'];

        $order = "INSERT INTO `users`
                      (id,username,trackname,recordname)
                      VALUES
                      ('$posicao','$track','$artist','$recorder')";

        $result = mysql_query($order);
        if($result) {
          echo("<br>Input data is succeed");
        } else {
          echo("<br>Input data is fail");
        }

        echo "$posicao . {$track} - {$artist} - {$recorder}. <br>";
     }

  endif;
  ?>

Answer 1

Viewing your code, it seems that the error is in entering the ID. If your ID field is the Primary Key you enter the same value twice, it generates a duplicate key conflict, for this reason it should not be recording. Ideally leave the ID field with auto increment in Mysql and include a new column for the position.

Answer 2

Apparently your script is not using id with auto_increment.

If you call the same form twice, in the first you will enter and in the second you will conflict with the already existing key.

Then try to place auto_increment in your id column in mysql and remove it from Insert.

Unless you treat this in the form that calls the Insert, but it is still conflict-prone.

Answer 3

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.