PHP的execute_sql未插入数据，sample_synteny.txt未加载至MGSV数据库求助

Let's break down the problems you're facing and walk through actionable checks to fix them:

1. Debug the execute_sql Function #

First, let's rule out issues with the core SQL execution logic:

• Add error handling to catch database issues: Modify the function to surface any SQL errors that might be silently failing. For example, if you're using MySQLi:

  function execute_sql($sql) {
      // Replace $conn with your actual database connection variable
      $result = mysqli_query($conn, $sql);
      if (!$result) {
          // Print or log this error to get detailed feedback
          echo "SQL Execution Failed: " . mysqli_error($conn);
          return false;
      }
      return $result;
  }
  

• Validate the full INSERT query: The partial output you shared (insert into 304530020182800000020_synteny (org1, org1_start, org1_end, org2, org2...) suggests the query might be truncated, have missing values, or invalid syntax. Capture the complete generated SQL and check for:
  • Missing column names or value pairs
  • Unquoted string values (if any columns require string quoting)
  • Mismatched parentheses or commas
• Confirm database connection health: Ensure the connection is active when execute_sql runs—sometimes connections drop due to timeouts or incorrect credentials. Test the connection with a simple SELECT 1 query before running inserts.

2. Fix the syn($filename) File Loading Logic #

Since your table structure exists but has no data, the issue likely lies in how the file is parsed or how inserts are triggered:

• Verify file parsing works: Add debug prints to check if syn() is correctly reading rows from sample_synteny.txt and extracting values for all columns (org1, org1_start, org1_end, org2, org2_start, org2_end, length, SYNcolor). Print 1-2 parsed rows to confirm data matches the file content.
• Check batch insert syntax (if used): If the function generates a single batch insert, ensure it follows the correct format:

  INSERT INTO `304420020185300000016_synteny` 
  (org1, org1_start, org1_end, org2, org2_start, org2_end, length, SYNcolor)
  VALUES 
  ('val1', 123, 456, 'val2', 789, 1011, 200, '#ff0000'),
  ('val3', 1234, 5678, 'val4', 9012, 3456, 400, '#00ff00');
  

• Ensure inserts are committed: If your code uses transactions, make sure you call commit() after running all inserts. If auto-commit is disabled, uncommitted transactions will roll back, leaving the table empty.
• Check file permissions: Confirm the web server user (e.g., www-data on Apache) has read access to sample_synteny.txt. Without read permissions, the function can't load the file content at all.

3. Manual Testing to Isolate the Root Cause #

• Run the INSERT query directly: Take the full generated SQL and execute it manually in your database client (like phpMyAdmin or MySQL CLI). If it fails, the client will give you a precise error (e.g., data type mismatch for org1_start, duplicate key, or missing required values).
• Test with a minimal file: Create a tiny version of sample_synteny.txt with just 1-2 valid rows, then run syn() on it. This helps rule out issues with large file sizes or malformed rows in the middle of your original file.

内容的提问来源于stack exchange，提问作者user977828