Need help inserting into MySQL database table using PDO.

April 28, 2012

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.

April 28, 2012

What version of MySQL server are you running? And what version of PHP?

April 28, 2012

PHP 5.3.8 and MySQL 5.5

April 28, 2012

Do you have parameter logging enabled in your config file?

'enableParamLogging'=>true,

April 28, 2012

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.

April 29, 2012

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 :-)

Sign In

Need help inserting into MySQL database table using PDO.

Question

Stokkolm

Link to comment

Share on other sites

5 answers to this question

Recommended Posts

spikey_richie

Link to comment

Share on other sites

Stokkolm

Link to comment

Share on other sites

spikey_richie

Link to comment

Share on other sites

ZakO

Link to comment

Share on other sites

Stokkolm

Link to comment

Share on other sites

Recently Browsing 0 members

Similar Content

Posts

Recent Achievements

Popular Contributors

Tell a friend