Writing to MySQL with PHP

-1

I want to record this data more than once but I can not. I have 10 items each 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 not. With different names of all the fields I can write but everything stays the same place and I would like to separate the data in 10 in 10 so I can print a list later to be accessed individually. What I need to do? Is it in MySQL or PHP? Here is my PHP code that is doing this recording and I hope my question has been 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;
  ?>
    
asked by anonymous 17.09.2014 / 16:24

3 answers

1

Viewing your code, it looks like the error is in the ID insertion. if your ID field is the Primary Key you entering the same value twice, it generates a duplicate key conflict, which is why it should not be writing. Ideally, leave the ID field with auto increment in MySQL and add a new column to the position.

    
17.09.2014 / 16:33
0

Apparently your script is not using id with auto_increment.

If you double-click the same form, the first one will insert and the second will conflict with the existing key.

Then try to put auto_increment in your id column in mysql and remove it from the insert.

Unless you treat this in the form that calls the insert, but it is still subject to conflict.

    
17.09.2014 / 16:35
0

Your database modeling is wrong, can not save equal records in the database, they must have unique identifiers, usually the ID field, such as AUTO_INCREMENT , so you do not need to tell it in INSERT SQL statements because this value will be automatically generated by MySQL.

You have a lot of different data in the same table, this type of modeling is not recommended, try to separate , for example a table for artists , a table for > music , another table for users , and a table that relates songs to users . You could still create a table of playlists and albums but this depends on your project and need.

Sample 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 ConnectionMSi library:

<?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 switching from mysql to mysqli .

    
17.09.2014 / 17:23