Skip to main content
ElizabethGailLittle
ElizabethGailLittle
Inspiring
August 19, 2015
Answered

Has anyone written routines for insert/update/delete for MySQL records?

  • August 19, 2015
  • 1 reply
  • 1594 views

I would prefer not to use Spry or other third-party methods.  I'm comfortable with coding, so that's not an issue.  Thanks!

This topic has been closed for replies.
Correct answer Rob Hecker2

I don't know what I'm doing wrong, but I cannot connect with my database by plugging my variables into the PDO connection script you gave me.  I need to study up on the PDO or SQLi syntax, because I don't understand the -> and => commands.  I can't even get the connection failure message to appear.  I appreciate your help, but I have so far had no success.


=> is used to assign a value to an associative array key, like this:

$fruit =array("apple"=>"braeburn","pear"=>"bartlet");

-> is used in OO to taken an action on an object. That's my on-the-fly definition. On the left is the object name and on the right is what to do. For instance, you may be setting a property or running a method.

In the connection script, you don't even need to understand what's going on. You just need to set the variables in the first three lines . . .then, your queries must always refer to the connection object. Since I use $dbh as the connection object name, a query would be like this:

$sql=$dbh->prepare("SELECT * FROM fruit WHERE fruit_type=:fruit_type");
$sql->bindValue("fruit_type", "apples");
$sql->execute();

1 reply

Rob Hecker2
Rob Hecker2
Legend
August 19, 2015

Using PDO (the alternative is mySQLi. Both are fine):

DELETE:

$dbh->exec("DELETE FROM $table WHERE $id_type='$id'");

UPDATE: (with bound values and try/catch to report failure)

$sql=$dbh->prepare("UPDATE closure SET
    closure_date=:closure_date 
    WHERE closure_id =:closure_id");
$sql->bindValue("closure_date", $closure_date);
$sql->bindValue("closure_id", $closure_id); 
try{
$sql->execute() or $response = "<p style='color:red'>INSERT FAILED!</P>";
}catch(PDOException $e)
{ echo $e;}

INSERT:

$sql=$dbh->prepare("INSERT INTO courses SET title =:title WHERE course_id=:course_id");

$sql->bindValue("course_id", $course_id);
$sql->bindValue("title", $title);

try{             
$sql->execute() or $response = "<p style='color:red'>INSERT FAILED!</P>";
}catch(PDOException $e)
{    echo $e;}

Here is an example of a connection script for PDO


<?php
$dsn ='mysql:dbname=database_1;host=localhost;port=3306';
$user='rumplestiltskin';
$password='wd93466mQQ';
try {
$dbh = new PDO($dsn, $user, $password, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
//$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);  //don't show errors (on remote)
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //show errors (on development)
$dbh->query=("set names utf8");
$dbh->exec('SET CHARACTER SET utf8');
$dbh->exec('SET character_set_server=utf8');
}
catch (PDOException $e) {
     die('Connection failed: '.$e->getMessage());
}

ElizabethGailLittle
ElizabethGailLittleAuthor
Inspiring
August 20, 2015

What is PDO?  There is syntax I do not recognize (->), but I will try to adapt to php and see where I get.  I haven't written input routines in ages, so that will be a challenge too.

Thanks for the response!

Rob Hecker2
Rob Hecker2
Legend
August 20, 2015

What is PDO?

There are three different "engines" PHP can use to communicate with MySQL databases.

  • MYSQL - This method is deprecated and must never be used
  • MySQLi - Similar to MYSQL, but newer and more powerful
  • PDO - An object oriented method that is database agnostic (meaning it can be used with a variety of databases, not just MySQL)

There is syntax I do not recognize (->)

This is object oriented syntax. The PDO method requires it. Although PHP was not originally an object oriented programming language, its OO capabilities have matured and it is the preferred way to write code.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices