duanpu8830
2014-03-26 00:50
浏览 32
已采纳

变量不会插入MySQL表中

I have this script that takes a variable and puts it into a table. My connect.php file is correct and selects the right database, but for some reason this data will not insert. It says it succeeds but nothing gets put in the table

My Script

if(!empty($_POST['phrase'])){
    $phrase = $_POST['phrase'];
    $phrase = mysql_real_escape_string($phrase);
    $phrase = preg_replace("/[^A-Za-z ]/", "", $phrase);
    $query5 = mysql_query('SELECT phrase FROM dictionary WHERE phrase = "'.$phrase.'" '); // Check the database 


    if(mysql_num_rows($query5)>0){
        null;
    }else{
        echo $phrase;
        $query10 = "INSERT into dictionary(adjective, noun, phrase, subject, verb) values(NULL, NULL, '$phrase', NULL, NULL)";
        $result = mysqli_query($query10); 
        echo "<pre>Debug: $query10</pre>\m";
        //$result = mysqli_query($con, $query);
        if ( false===$result ) {
            printf("error: %s
");
        }
        else {
            echo 'done.';
        }
        mysqli_close();
    }
    //Debugging Purpose
    echo '<span class="error" > -'. $phrase .'</span>';
}

It echos 'done.' but the table doesn't update

here is how my table is setup

-- Database: `dictionary`
--
CREATE DATABASE IF NOT EXISTS `dictionary` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `dictionary`;

-- --------------------------------------------------------

--
-- Table structure for table `adjective`
--

CREATE TABLE IF NOT EXISTS `adjective` (
`id` int(10) NOT NULL,
`adjective` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `adjective` 
--

INSERT INTO `adjective` (`id`, `adjective`) VALUES
(1, 'orange'),
(2, 'blue');

-- --------------------------------------------------------

--
-- Table structure for table `noun`
--

CREATE TABLE IF NOT EXISTS `noun` (
`id` int(10) NOT NULL,
`noun` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `noun`
--

INSERT INTO `noun` (`id`, `noun`) VALUES
(1, 'tamaleh');

-- --------------------------------------------------------

--
-- Table structure for table `phrase`
--

CREATE TABLE IF NOT EXISTS `phrase` (
`id` int(10) NOT NULL,
`phrase` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `phrase`
--

INSERT INTO `phrase` (`id`, `phrase`) VALUES
(0, 'Hello There'),
(1, 'Hello World');

-- --------------------------------------------------------

--
-- Table structure for table `subject`
--

CREATE TABLE IF NOT EXISTS `subject` (
`id` int(10) NOT NULL,
`subject` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `subject`
--

INSERT INTO `subject` (`id`, `subject`) VALUES
(1, 'I'),
(2, 'The');

-- --------------------------------------------------------

--
-- Table structure for table `verb`
--

CREATE TABLE IF NOT EXISTS `verb` (
 `id` int(10) NOT NULL,
 `verb` text NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `verb`
--

INSERT INTO `verb` (`id`, `verb`) VALUES
(1, 'jumping');
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • douchun1859 2014-03-26 01:01
    已采纳

    Note:

    Lets re-establish first your connection to MySQL database:

    $con = new mysqli("host", "username", "password", "dictionary"); /* REPLACE NECESSARY DATA INSIDE */
    
    /* CHECK CONNECTION */
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s
    ", mysqli_connect_error());
        exit();
    }
    

    Then check each POST data from your form:

    if(!empty($_POST["phrase"])){ /* IF SUBMITTED phrase DATA IS NOT EMPTY */
    
      /* LETS CHECK FIRST IF THE PASSED ON phrase DATA ALREADY EXIST IN THE phrase TABLE */
      if($stmt = $con->prepare("SELECT * FROM phrase WHERE phrase = ?")){
        $stmt->bind_param("s", $_POST["phrase"]); /* BIND THE PASSED ON DATA TO THE QUERY */
        $stmt->execute(); /* EXECUTE THE QUERY */
        $stmt->store_result(); /* STORE THE RESULT */
        $noofphrase = $stmt->num_rows; /* GET THE NUMBER OF RESULTS OF THE EXECUTE QUERY */
    
          if($noofphrase ==0){ /* IF phrase IS NOT YET IN THE phrase TABLE */
            $stmt2 = $con->prepare("INSERT INTO phrase (phrase) VALUES (?)"); /* START INSERT QUERY */
            $stmt2->bind_param("s", $_POST["phrase"]); /* BIND THE PASSED ON DATA TO THE QUERY */
            $stmt2->execute(); /* EXECUTE THE INSERT QUERY */
            $stmt2->close(); /* CLOSE THE INSERT QUERY PREPARED STATEMENT */
          }
    
        $stmt->close(); /* CLOSE THE PREPARED STATEMENT */
      } /* END OF PREPARED STATEMENT */
    
    } /* END OF IF phrase IS NOT EMPTY */
    

    Then repeat this for adjective, noun, subject, and verb.

    Less Tedious and Less Populated Code

    It's a tedious work. The code will look populated. So why don't we just create a function where it will parametize the keyed-in data, table name, and column name. This is a better approach because they run in the same pattern.

    <?php
    
      function CheckAndInsert ($data, $tablename, $columnname){
    
        global $con;
    
        if(!empty($data)){ /* CHECK IF KEYED-IN DATA IS NOT EMPTY */
    
          /* LETS CHECK FIRST IF THE PASSED ON phrase DATA ALREADY EXIST IN THE phrase TABLE */
          if($stmt = $con->prepare("SELECT * FROM ? WHERE ? = ?")){
            $stmt->bind_param("sss", $tablename, $columnname, $data); /* BIND THE PASSED ON DATA TO THE QUERY */
            $stmt->execute(); /* EXECUTE THE QUERY */
            $stmt->store_result(); /* STORE THE RESULT */
            $noofresult = $stmt->num_rows; /* GET THE NUMBER OF RESULTS OF THE EXECUTE QUERY */
    
              if($noofresult ==0){ /* IF phrase IS NOT YET IN THE phrase TABLE */
                $stmt2 = $con->prepare("INSERT INTO ? (?) VALUES (?)"); /* START INSERT QUERY */
                $stmt2->bind_param("sss", $tablename, $columnname, $data); /* BIND THE PASSED ON DATA TO THE QUERY */
                $stmt2->execute(); /* EXECUTE THE INSERT QUERY */
                $stmt2->close(); /* CLOSE THE INSERT QUERY PREPARED STATEMENT */
              } /* END OF CHECKING NO OF RESULTS FROM FIRST QUERY */
    
            $stmt->close(); /* CLOSE THE PREPARED STATEMENT */
          } /* END OF PREPARED STATEMENT */
    
        } /* END OF CHECKING
    
      } /* END OF FUNCTION */
    
    ?>
    

    So when user submits the form, just call this function.

    CheckAndInsert($_POST["phrase"], "phrase", "phrase");
    CheckAndInsert($_POST["adjective"], "adjective", "adjective");
    CheckAndInsert($_POST["noun"], "noun", "noun");
    CheckAndInsert($_POST["subject"], "subject", "verb");
    CheckAndInsert($_POST["verb"], "subject", "verb");
    
    已采纳该答案
    打赏 评论

相关推荐 更多相似问题