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 2, 2015

How does one access MySQLi or PDO?

I provided a sample script in my first message for a PDO connection.

I have done object oriented programming in the past.  I think I’ll try to stick with php.

You can program PHP using procedural or object oriented approaches. The OO approach becomes mandatory when the code library is large. OO also avoids some of the annoyances of procedural coding, such as keeping variables from getting mixed up so that the wrong value is applied where you want a different value.


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.

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