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

[ElizabethGailLittle]

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

[Rob Hecker2]

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]

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]

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.

[ElizabethGailLittle]

Thanks for the information. I have done object oriented programming in the past. I think I’ll try to stick with php. How does one access MySQLi or PDO?

[Rob Hecker2]

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.

[ElizabethGailLittle]

Thanks!  I used the wrong word - by access I meant information for me to learn to use it.  I have data connections working now.  I'm just trying to write routines so non-techie people can maintain the data.

I found the MySQLi and PDO information in Dave Powers' book and will begin.  It sounds as if PDO will be my best bet because of flexibility.

This forum has been extremely helpful!  I got my site phone-friendly using tips given here, got a routine working to pass variables to the next page, etc., and knew I'd get help on this.  You are all great!

[ElizabethGailLittle]

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.

[Rob Hecker2 · Accepted solution]

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

[ElizabethGailLittle]

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?

[Rob Hecker2]

Do you have any preference between PDO & SQLi?

I use only PDO, but I could just as easily gone the other way. Each has its strengths. No one using MySQLi should wish they had gone the other way.

you're assigning braeburn to apple, or apple to braeburn?

braeburn to apple

And are you binding apples to fruit_type?  So the $sql result is braeburn?

Not exactly, the result set might return pippins, Macintosh, Pink Rose and Braeburn.

[ElizabethGailLittle]

Thanks - that helps. I'm feeling overwhelmed! Most of the content on my site comes from a n sql database, and that means a good bit of serious rewriting. I have not yet so far executed a query and displayed the data using PDO. I'll get there, but the syntax is so different I see lots of coding, testing, recoding ahead.

[ElizabethGailLittle]

So => assigns properties and -> calls methods? Is there a list of inherent methods somewhere?

[Rob Hecker2]

So => assigns properties and -> calls methods?  Is there a list of inherent methods somewhere?

That's not what I said. The first  has to do with arrays, the other with objects.

Methods are simply what functions are called when they are within classes.

[ElizabethGailLittle]

Thanks again for the help.  I finally got a page converted from SQL to SQLi displaying data from 4 tables.  I decided on SQLi just because the code is a little closer to what I already have going.