Editable form fields?

[chrisburton]

I’ll try to explain this as best as I can.

In phpMyAdmin’s columns, you are able to click on them and make changes. When you make a change, you’ll see a loading image appear (gear icon) as it updates that column. I’m trying to do the exact same thing using a form with editable fields and when a change is made, update the database. Is this at all possible? Can’t seem to wrap my head around the logic.

[chrisburton]

Hey, Gray (@TheDoc). Are you using AJAX to update the records or…? I’ve never worked with editable fields before.

[Rohithzr]

@chrisburton are you able to edit the field? Then you need a script saying onchange=load image (for a couple of seconds), get the value of the field and update through ajax, if successful then display the new value else error msg.

[chrisburton]

@TheDoc Hey, thanks for the insight. Out of curiosity, why are you still using the old mysql rather than mysqli?

[__]

@TheDoc »whispering« SQL Injection…

[__]

Because I don’t know what I’m doing ;)

Are you in a position to switch?
I used to advocate MySQLi because it was more fully-featured with MySQL, but PDO is damn close and is also much easier to use.

Thanks! I was trying to find a simple article.

Sure. The basic thing to remember is Never Trust User Input. At the very, very least, sanitize the input so it is safe for use in your query:

$name = mysql_real_escape_string( $_POST['name'] );

MySQLi and PDO make it easier+more efficient by supporting prepared statements. It lets you keep data completely separate from the statement itself.

[__]

Switching won’t have any impact on your using phpMyAdmin, so no worries there.

Are you reasonably comfortable with object-oriented PHP?

The code you posted above would be done like so with PDO:

try {
    // connect to your database
    $PDO = new PDO(
        "mysql:host=name_of_host;dbname=name_of_database;charset=utf8",
        "your_username",
        "your_password" 
    );

    // prepare SQL statement using :placeholders
    $SQL = "insert into your_table( Name,CustomerID,Email,Street,City,Zip,State,Country ) 
        values( :name,:id,:email,:street,:city,:zip,:state,:country )";
    $order = $PDO->prepare( $SQL );

    // execute SQL statement using input from POST
    // (it's now safe to do so)
    $success = $order->execute(
      array(
        ":name"    => $_POST['name'],
        ":id"      => $customer->id,
        ":email"   => $_POST['email'],
        ":street"  => $_POST['address'],
        ":city"    => $_POST['city'],
        ":state"   => $_POST['state'],
        ":country" => $_POST['country']
      )
    );
    // you still might want to validate the input,
    // e.g., make sure $_POST['email'] _is_ an email address, etc..

    // check if it worked
    if( ! $success ){
        throw new Exception( 'Failed to add customer to database.' );
    }
} catch (Exception $e) {
    $a = array('error' => true, 'message' => $e->getMessage() );

    // in production, of course, you'll want to log this instead.
    // you DO NOT want to echo this info to the user:
    // PDOExceptions will sometimes contain database credentials
    echo json_encode($a);
}

[__]

PDO has been bundled with PHP since version 5.1, so no, you shouldn’t have to install anything.

If you use PHP < 5.3.6, then the charset parameter I used in my example won’t work (it will be silently ignored), but there is a workaround if you can’t upgrade.

[__]

So that alone makes my row insert safe?

The whole idea is to separate the Query (the SQL Instructions) from the Data.

As long as you prepare the SQL with placeholders, yes: you’ll be safe from injection errors/attacks.

Of course, you still can ruin it if you try hard enough.

$goodExample = $PDO->prepare( "insert into myTable( myColumn ) values( :userdata )" );
$goodExample->execute( array( ":userdata"=>$_POST['userdata'] ) );

$badExample = $PDO->prepare( "insert into myTable( myColumn ) values( $_POST['userdata'] )" );
$badExample->execute();

Also, sorry for hijacking, Chris

Yes; sorry, Chris!

[chrisburton]

No apologies needed. Very insightful! Although, I sort of like the mysqli syntax and find it easier to use. However, the Kirby helper classes makes it even easier to do database stuff.

[__]

I sort of like the mysqli syntax and find it easier to use.

Do you mean how you can use plain functions instead of objects? That can be an advantage to people who are switching, as it is a less “abrupt” change from existing mysql_* code.

If you’re talking about how mysqli uses bound variables, then you should know that PDO allows that as well (I just didn’t use it in my example).

As I said above, I preferred MySQLi for a long time. In the end, I found PDO was more comfortable for me. They’re both excellent, though. At this point, I think it just comes down to preference.

the Kirby helper classes makes it even easier to do database stuff.

I like how they’re designed. They make building queries very easy (and seem to do a good job with security). They use the old mysql_* functions underneath, though.

[chrisburton]

@traq

They use the old mysql_* functions underneath, though.

Bastian updated the new beta toolkit to PDO.

https://github.com/getkirby/toolkit/blob/master/lib/database.php#L115

[__]

Hey, cool. He’s quite active with this, isn’t he? Good to see.

[chrisburton]

Thankfully, yes. He even hired on someone to help him. Kirby 2 (beta) was just released last week, I believe.

[chrisburton]

In case anyone was wondering, this is what I am trying to do:

http://www.appelsiini.net/projects/jeditable/default.html

Click the first jjjj text, change the value and click OK. That’s exactly what I am currently trying to accomplish except without a button.

[Author]

Posts