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!

ElizabethGailLittle
ElizabethGailLittleAuthor
Inspiring
September 3, 2015

=> 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();


I finally got the database connection going.  Now I'm going to try to read some records and display them. 

In your $fruit example - you're assigning braeburn to apple, or apple to braeburn?  And are you binding apples to fruit_type?  So the $sql result is braeburn?

Do you have any preference between PDO & SQLi?

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