PHP/MySQL 获取表中每隔一行的问题/停止添加记录两次

Question

我有一个存储所有内容重复项的数据库，我们只需要提取其中的 1 个副本。该表是:

+----+------------+-----------+-------------+
| id | locationId | name      | description |
+----+------------+-----------+-------------+
|  1 |          1 | rgh       | lol         |
|  2 |          1 | rgh       | lol         |
|  3 |          2 | tbh       | gjbh        |
|  4 |          2 | tbh       | gjbh        |
|  5 |          3 | lolcbj999 | abububh     |
|  6 |          3 | lolcbj999 | abububh     |
|  7 |          4 | test      | vhjj        |
|  8 |          4 | test      | vhjj        |
|  9 |          5 | ghh       | yhhh        |
| 10 |          5 | ghh       | yhhh        |
+----+------------+-----------+-------------+

我们尝试使用 %2 ==0 方法，但问题是，请注意 locationId 如何成为此表的外键

+----+--------+-----------+-----------+-----------+
| id | walkId | longitude | latitude  | timestamp |
+----+--------+-----------+-----------+-----------+
|  1 |      1 | -4.067431 | 52.416904 |         2 |
|  2 |      2 | -4.067398 | 52.416908 |         0 |
|  3 |      3 | -4.067419 | 52.416904 |         3 |
|  4 |      4 | -4.067482 | 52.416897 |         1 |
|  5 |      5 | -4.067458 | 52.416897 |         1 |
+----+--------+-----------+-----------+-----------+

我们想在 XML 文件中显示这些内容，那么我们该如何做呢？基于事实。我们似乎无法理解这一点，我们可以返回所有赔率，因此 wlak id 1,3,5 但错过 2 和 4，反之亦然。有什么想法吗？

如果有一种方法可以阻止记录被添加两次，我们会更喜欢它如果使用此数据库 PHP 代码，我们需要更改什么:

<?php
/**
 * This file contains database related functions.
 *
 * @file
 */

# prevent users from accessing this page via their browser
if ( !defined( 'ENTRYPOINT' ) ) {
  die( 'This is not a valid entry point.' );
}

require dirname( __FILE__ ) . '/../config.php';
/**
 * This function runs the query that will attempt to create the correct tables
 * in the database.
 *
 * @return a string detailing errors encountered, or FALSE if the query worked.
 */
function createDatabase() {
  $db = openConnection();

  $sql = <<<'SQL'
  CREATE TABLE tbl_routes (
    id int NOT NULL AUTO_INCREMENT, title varchar(255), shortDesc varchar(255), longDesc varchar(255),
    hours float(12), distance float(12), PRIMARY KEY (id));
  CREATE TABLE tbl_locations (
     id int NOT NULL AUTO_INCREMENT, walkId int NOT NULL, latitude float(12), longitude float(12), timestamp float(12),
     PRIMARY KEY (id), FOREIGN KEY (walkId) REFERENCES tbl_routes(id));
   CREATE TABLE tbl_places (
     id int NOT NULL AUTO_INCREMENT, locationId int NOT NULL, description varchar(255),
     PRIMARY KEY (id), FOREIGN KEY (locationId) REFERENCES tbl_locations(id));
  CREATE TABLE tbl_images (
    id int NOT NULL AUTO_INCREMENT, placeId int NOT NULL, photoName varchar(255),
    PRIMARY KEY (id), FOREIGN KEY (placeId) REFERENCES tbl_places(id));
SQL;

  $query = executeSql( $sql );
  if ( !is_string( $query ) ) {
    return FALSE;
  } else {
    return $query;
  }
}

/**
 * This function inputs a walk into the database.
 *
 * @param a walk object, as created by the upload.php function
 * @return a string describing the error, or SUCCESS if things worked
 */
function inputWalk( $walk ) {
  # TODO
  # we need to escape the inputs to proof against SQL injections
  # see rfc 4389 2.5.2
  $values = [
    "NULL", # ID is assigned by the database thanks to AUTO_INCREMENT
    "'$walk->title'",
    "'$walk->shortDesc'",
    "'$walk->longDesc'",
    "NULL",
    "NULL"
    ];

  $sql = 'INSERT INTO tbl_routes VALUES (' . implode( $values, ',' ) . ');';

  $db = openConnection();
  $query = mysqli_query( $db, $sql );
  if ( is_bool( $query ) && $query === TRUE ) {
    # the SQL query worked and the data is stored
    # we need to request the data back to see what the ID has been set to
    $walkId = mysqli_insert_id( $db );
    foreach ( $walk -> locations as &$location ) {
      $query = null;
      $values = null;
      $values = [
        "NULL", # location ID
        $walkId,
        "'" . mysqli_real_escape_string( $db, $location->latitude ) . "'",
        "'" . mysqli_real_escape_string( $db, $location->longitude ) . "'",
        "'" . mysqli_real_escape_string( $db, $location->timestamp ) . "'"
      ];
      $query = "INSERT INTO tbl_locations VALUES (" . implode( $values, ',' ) . ");";
      $sql =  mysqli_query( $db, $query );
      if ( $sql === FALSE ) { return $query; }
      # now need to get the locationId
      $locID = mysqli_insert_id( $db );

      foreach ( $location -> descriptions as &$description ) {
        $description[0] = mysqli_real_escape_string( $db, $description[0] );
        $description[1] = mysqli_real_escape_string( $db, $description[1] );
        # TODO: BUG: doesn't actually work...
        $sql = "INSERT INTO tbl_places VALUES (NULL, '$locID', '$description[0]', '$description[1]');";
        $query = mysqli_query( $db, $sql );
        if ( $query === FALSE ) { return mysqli_error( $db ); }
      }


      foreach ( $location -> images as &$image ) {
        $query = "INSERT INTO tbl_images VALUES (NULL, $locID, '$image[0]');";
        mysqli_query( $db, $sql );
      }
    }

    # everything worked
    return 'SUCCESS'; # TODO: make this less hackish
  } else {
    return $query;
  }

}

/**
 * This function opens a connection to the database.
 * @return an active database connection.
 *
 * Use closeConnection() to close it again.
 */
function openConnection() {
  global $DB_ADDR, $DB_USER, $DB_PASS, $DB_NAME;
  $db = mysqli_connect( $DB_ADDR, $DB_USER, $DB_PASS, $DB_NAME );
  if ( !$db ) {
    # connection failed
    die( 'Could not connect to MySQL' );
  }
  return $db;
}

/**
 * Executes an SQL statement.
 * @param the SQL to execute
 * @return the result of the action
 *
 * On success it will return a mysqli_result() object when the SQL statement is
 * a SELECT, SHOW, DESCRIBE or EXPLAIN. Other successes will return TRUE.
 *
 * Failures will return a string explaining the error. Use is_object() to
 * discern between the error string and a result.
 */
function executeSql( $sql ) {
  $db = openConnection();
  $query = mysqli_query( $db, $sql );
  if ( $query === FALSE ) {
    $query = mysqli_error( $db );
  }
  closeDatabase( $db );
  return $query;
}

/**
 * Closes an active database connection.
 * @param the database connection to close.
 */
function closeDatabase( $db ) {
  mysqli_close( $db );
}

/**
 * Fetches the first field of the first row of a result
 * @param The result to fetch
 * @return the first field of the first row
 * @deprecated
 */
function fetchID( mysqli_result $result ) {
  return $result->fetch_row()[0];
}

干杯，克里斯。

Answer 1

您可以使用分组依据:

select min(id), locationId, name, description 
from table t
group by locationId, name, description;

这在大表上可能不太有效。相反，您可以在 table(locationId, name, description, id) 上创建索引并执行以下操作:

select id, locationId, name, description
from table t
where not exists (select 1
                  from table t2
                  where t2.locationId = t.locationId and t2.name = t.name and
                        t2.description = t.description and t2.id < t.id
                 );

这将从每个组中选择第一个，并且对于上述索引可能相当有效。