• 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.

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.

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

This topic is now closed to further replies.
 Share
Go to question listing

  • Posts

    • Should Microsoft be forced to stop promoting Edge over other browsers?

      By cleverclogs · Posted

      I haven't paid for windows since windows 8. So I guess I would be happy to pay for a new version if it meant seeing fewer adverts (none) or product pushes. But that applies to _any_ service or OS.
    • Microsoft wants to end printer driver headaches with Windows Ready Print

      By cork1958 · Posted

      Edit: Oops! Wrong topic! :(
    • Save 35% on Sony's SS-CS5M2 3-way high-res bookshelf speakers

      By TarasBuria · Posted

      Save 35% on Sony's SS-CS5M2 3-way high-res bookshelf speakers by Taras Buria Sony is currently offering a big discount on its SS-CS5M2 bookshelf speaker, saving you 35% on a set of high-quality audio equipment. The SS-CS5M2 is a passive 3-way bookshelf speaker with a 5.12-inch woofer, a 25 mm soft-dome tweeter, and a 19 mm super tweeter. This design allows different drivers to handle different parts of the sound spectrum for a clearer, more detailed audio when watching movies or listening to music. The compact cabinet size allows you to place these speakers on shelves, desks, or stands, making them a practical choice for apartments, bedrooms, and small living rooms. Despite its compact size, the SS-CS5M2 delivers up to 100 W of power. Note that since the speakers are passive, you will need an amplifier to drive them. However, if you do, you can use them for high-resolution music, thanks to a claimed frequency response of 53 Hz - 50 kHz. It is able to extend so far high in the spectrum as a result of those super tweeters. While they will work with most amplifiers and AV receivers, Sony says this pair is a perfect match for its AV receivers, such as STRDH190, 590, 790, or 1000. Sony CS Bookshelf Speakers SS-CS5M2 3-Way 3-Driver Hi-res - $178 | 36% off on Amazon US This Amazon deal is US-specific and not available in other regions unless specified. This is a first-party seller link (at the time of article publishing); ensure that you also purchase from a first-party seller link only. If you don't like it or want to look at more options, check out the previous deals that we have covered, OR you can also visit Amazon US deals page. Get Prime (SNAP), Prime Video, Audible Plus or Kindle / Music Unlimited. Free for 30 days. As an Amazon Associate, we earn from qualifying purchases.
    • EU Commission explains why Siri AI isn't launching in the EU, and Apple is to blame

      By AltecXP · Posted

      So they somehow expect Apple to easily make it so that if I install say DeepSeek that DS can then handle all the tasks that Siri would be doing while integrated in the OS? That sounds like just rediculous.
    • AMD 9070XT

      By Mockingbird · Posted

      For ray-tracing, the Radeon RX 9070 XT is better than the GeForce RTX 5070, but worse than the GeForce RTX 5070 Ti The Radeon RX 9070 XT is similar to the GeForce RTX 5070 Ti in rasterization Both AMD and NVIDIA have had serious issues with drivers in the past, so I can't say that one is better or worse than the other. Yes. AMD has better support Linux than does NVIDIA. Use Display Driver Uninstaller (DDU) to uninstall NVIDIA's drivers before installing AMD's drivers. That's up to you. Supplies of memory is unpredictable because AI using up a lot of memory. As a result, there is a lot of volatility in video card prices.

  • Recent Achievements

    • One Month Later
      pinnclepd earned a badge
      One Month Later
    • First Post
      X-No-file earned a badge
      First Post
    • One Month Later
      johnjacobb40 earned a badge
      One Month Later
    • One Year In
      Primer1st earned a badge
      One Year In
    • Experienced
      JayZJay went up a rank
      Experienced

  • Popular Contributors

    1. 1
      +primortal
      510
    2. 2
      PsYcHoKiLLa
      215
    3. 3
      +Edouard
      145
    4. 4
      Steven P.
      88
    5. 5
      ATLien_0
      83
    Show More

  • Tell a friend

    Love Neowin? Tell a friend!