• 0

Need help inserting into MySQL database table using PDO.

Asked by Stokkolm,

 Share

Question

Grand Master

Stokkolm

Posted
Posted

Here's the code:


<?php
class DB {
	private $_conn;

	public function openDB() {
		$dsn = "mysql:host=localhost;dbname=news";
		$username = "root";
		$password = "password";

		try {
			$this->_conn = new PDO( $dsn, $username, $password );
			$this->_conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
		} catch ( PDOException $e ) {
			echo "Connection failed: " . $e->getMessage();
		}
	}

	public function closeDB() {
		$this->_conn = null;
	}

	public function selectData( $myQuery ) {
		$rows = $this->_conn->query( $myQuery );

		foreach ( $rows as $row ) {
			echo "Index: " . $row['id'] . "<br />";
			echo "Title: " . $row['title'] . "<br />";
		}
	}

	public function insertData( $tableName ) {

		$q = $this->_conn->prepare("DESCRIBE " . $tableName);
		$q->execute();
		$getFields = $q->fetchAll(PDO::FETCH_COLUMN);

		$dbFieldCount = count( $getFields );
		$implodedFields = implode( ", :", $getFields );

		//$sql = "INSERT INTO " . $tableName . " ( " . implode( ", ", $getFields ) . " ) VALUES ( :" . $implodedFields . " )";
		$sql = "INSERT INTO " . $tableName . " VALUES ( :" . $implodedFields . " )";
		echo "$sql<br />";

		try {
			$insert = $this->_conn->prepare( $sql );

			foreach ( $getFields as $dbKey => $dbValue ) {
				foreach( $_POST as $formKey => $formValue ) {
					if ( $dbValue == 'id' ) {
						$insert->bindValue( '\":' . $dbValue . '\"', null, PDO::PARAM_INT );
						echo "$dbValue<br />";
						break;
					} else if ( is_int( $formValue ) && $dbValue == $formKey ) {
						$insert->bindValue( '\":' . $dbValue . '\"', $formValue, PDO::PARAM_INT );
						echo "$formValue<br />";
						break;
					} else if ( is_string( $formValue ) && $dbValue == $formKey ) {
						$insert->bindValue( '\":' . $dbValue . '\"', $formValue, PDO::PARAM_STR );
						echo "$formValue<br />";
						break;
					}
				}
			}

			$insert->execute();
		} catch ( PDOException $e ) {
			echo "Query failed: " . $e->getMessage();
		}
	}

}
?>
<!DOCTYPE html>
<html>
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
		<title></title>
	</head>
	<body>
		<?php

		if ($_POST) {
			$conn = new DB();
			$conn->openDB();
			$conn->insertData( 'login' );
			$conn->closeDB();
		}

		?>

		<form action="#" method="POST" name="register">
			<label for="username">Username</label><br />
			<input type="text" id="username" name="username"><br />
			<label for="password">Password</label><br />
			<input type="password" id="password" name="password"><br />
			<label for="email">Email Address</label><br />
			<input type="text" id="email" name="email"><br />
			<input type="submit" value="Submit" />
		</form>

	</body>
</html>
[/CODE]

The database table only contains the four fields that the query is attempting to insert into. For some reason I get the error: Query failed: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined.

I have troubleshot by echoing the output of the foreach loops and it always returns four items, I'm not sure what parameter isn't defined. I have also played around with including the field names in the $sql string as well as not including them. Same results either way. Please help if you can.

Link to comment
Share on other sites

5 answers to this question

Recommended Posts

  • 0
Veteran

ZakO

Posted
Posted

You shouldn't be wrapping the bindValue column names in quotes (that's why it isn't finding any of the parameters), e.g. change:

$insert->bindValue( '\":' . $dbValue . '\"', $formValue, PDO::PARAM_STR );[/CODE]

to

[CODE]$insert->bindValue( ":$dbValue", $formValue, PDO::PARAM_STR );[/CODE]

Same with all the other bindValue calls. You don't actually even need the ":" in newer versions but it's probably better to keep it in for compatibility reasons.

--------

Two extra notes about the code,[i] all[/i] posted values are strings in PHP, so your [b]is_int[/b] conditional will never be true, and you don't need the second loop, just check if [b]array_key_exists([/b][b]$dbValue, $_POST)[/b] then access the key directly. Also, be careful if you're going to use a function like that in production across a lot of forms so you don't accidently open yourself up to a mass assignment vulnerability :p.

Link to comment
Share on other sites
  • 0
Grand Master

Stokkolm

Posted
  • Author
Posted

Thanks for you help, that worked beautifully. My goal with this function is to kind of make it a universal INSERT function that can be reused for any insert call. I'll have to do some research on avoiding a mass assignment vulnerability now though. Thanks so much for your help :-)

Link to comment
Share on other sites
This topic is now closed to further replies.
 Share
Go to question listing