PHP mysql with PDO

I need help here…

For years I have been cobbling scripts together and making this or that with PHP and MySQL but this is like a once a year kinda thing and recently I have been judged for using the old mysql_ commands in my scripts. I am now trying to use the new PDO method and I am going bald with the concept. Just when something works it fails and miserably at that!! I could make this script in 15 minutes and implement it on my server too, if I was using the old methods. But to be able to learn I have updated my server to php7 and now I am going to learn this new method if it kills me. Alas here if where I need the help and I apologize in advance for my stupidity and lack of knowledge. I will try as I can to grasp all that is taught.

This script is going to be behind a secured directory in apache2 using .htaccess it receives sms messages from Twilio as they are sent to my numbers and then is supposed to just load the messages into MySQL well it doesnt work. I have been trying for the last week now and I am no further than I was a week ago.

[php]
$dbhost = “localhost”;
$dbname = “";
$dbusername = "
*”;
$dbpassword = “******”;
$charset = “utf8”;
$link = new PDO(“mysql:host=$dbhost;dbname=$dbname;charset=$charset”, $dbusername, $dbpassword);

$statement = $link->prepare(“INSERT INTO sms_messages(MessageSid, SmsSid, AccountSid, MessagingServiceSid, From, To, Body, NumMedia, FromCity, FromState, FromZip, FromCountry, ToCity, ToState, ToZip, ToCountry)
VALUES(:MessageSid, :SmsSid, :AccountSid, :MessagingServiceSid, :From, :To, :Body, :NumMedia, :FromCity, :FromState, :FromZip, :FromCountry, :ToCity, :ToState, :ToZip, :ToCountry)”);
$statement->execute(array(
“MessageSid” => $_POST[“MessageSid”],
“SmsSid” => $_POST[“SmsSid”],
“AccountSid” => $_POST[“AccountSid”],
“MessagingServiceSid” => $_POST[“MessagingServiceSid”],
“From” => $_POST[“From”],
“To” => $_POST[“To”],
“Body” => “”.$body."",
“NumMedia” => $_POST[“NumMedia”],
“FromCity” => $_POST[“FromCity”],
“FromState” => $_POST[“FromState”],
“FromZip” => $_POST[“FromZip”],
“FromCountry” => $_POST[“FromCountry”],
“ToCity” => $_POST[“ToCity”],
“ToSate” => $_POST[“ToSate”],
“ToZip” => $_POST[“ToZip”],
“ToCountry” => $_POST[“ToCountry”]
));

$link = “”;
header(“content-type: text/xml”);
echo “<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n”;

?>

Send away the messages go into a black hole of darkness and cannot be seen!
[/php]

This is about the 100th iteration of this little guy. The back end when it receives the text from someone it POST to this php script.

Study this tutorial. https://phpdelusions.net/pdo

You cannot attempt coding once a year and expect to be very successful at it.

I understand that and I didn’t just try and use the old mysql_ method, I actually took the advise to heart and I jumped in head first. I have upgraded the old php5.1 to php7. I have been taking the time to read the sites on PDO including the site you linked to. Therein lays another problem. I have tried the examples listed there and I read thru the material more than once (probably more than a dozen actually) but with the mysql_connect() I was able to clearly see why it did what it did. I could construct queries and connect to the DB with no issues. I cant seem to wrap my head around PDO and the more I look at “foo and bar” on php.net and some of the others the more confused I get.

[php]
$dbcall = new PDO(“mysql:host=$dbhost;dbname=$dbname”, $dbusername, $dbpassword);

$statement = $dbcall->exec(“INSERT INTO messages(from, to, body)
VALUES(‘piss’, ‘off’, ‘pdo’)”);

?>
[/php]

and

[php]

<?php $dbhost = "localhost"; $dbname = "pdo"; $dbusername = "root"; $dbpassword = "845625"; //fake stuff $link = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbusername, $dbpassword); $statement = $link->prepare("INSERT INTO testtable(name, lastname, age) VALUES(:fname, :sname, :age)"); $statement->execute(array( "fname" => "Bob", "sname" => "Desaunois", "age" => "18" )); [/php] So this last one I have struggled with I found this on StackExchange and created a blank db & table and it works adding records. But If I modify it and change the tablename(name, lastname, age) to the correct table then it fails every time. Why does it work on one table and not on another? Both the real and test tables were in the same DB with the same credentials! All I did was tell it to put the info in a different table. Poof fails!!! The concept here seems simple... we use PDO to open the db and prepare() to set the stage for for the query then execute() the query. But I am clearly missing something!!

If you want to get down nitty gritty with PDO then I would suggest browsing php.net for PDO methods (functions) and using the world database to goof around with. You can find world.sql here -> https://dev.mysql.com/doc/index-other.html

Here’s just an example that I did goofing around with PDO and I created a sandbox folder where I keep all my php files that I tinker around with.

[php]<?php
define(‘DATABASE_HOST’, ‘your_localhost’);
define(‘DATABASE_NAME’, ‘sandbox’); // Can the db anything you want:
define(‘DATABASE_USERNAME’, ‘root’); // Usually root for a local server:
define(‘DATABASE_PASSWORD’, ‘your_password’);
define(‘DATABASE_TABLE’, ‘cms’);

$db_options = [
/* important! use actual prepared statements (default: emulate prepared statements) /
PDO::ATTR_EMULATE_PREPARES => false
/
throw exceptions on errors (default: stay silent) /
, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
/
fetch associative arrays (default: mixed arrays) /
, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
];
$pdo = new PDO(‘mysql:host=’ . DATABASE_HOST . ‘;dbname=world;charset=utf8’, DATABASE_USERNAME, DATABASE_PASSWORD, $db_options);
/

  • Grab the Amount of Records ($row) by setting a LIMIT (in this case it’s 50).
    */
    $query = ‘SELECT Name, CountryCode, District, Population FROM city ORDER BY CountryCode DESC LIMIT 50’;
    $stmt = $pdo->query($query);
    ?>
Display From Database Table div.container { display: block; width: 100%; max-width: 400px; height: auto; background-color: orange; padding: 10px; margin: 10px auto; } p.cityRow { font-family: Arial, Helvetica, sans-serif; font-size: 1.0rem; line-height: 1.5; color: #2e2e2e; } p.cityRow span.tabStyle { margin-left: 10px; } <?php /* * I used the World Database (from php.net) and the table city for an example. * I even styled it a little bit. Use class when it is needed and there is really no need to * increment it. Though I don't know exactly whatcha doing and I couldn't use your database, * so I subsituted. */ echo '
' . "\n"; while ($row = $stmt->fetch()) {
        echo '<p class="cityRow">'
        . '<span class="tabStyle">' . $row['Name'] . '</span>'
        . '<span class="tabStyle">' . $row['CountryCode'] . '</span>'
        . '<span class="tabStyle">' . $row['District'] . '</span>'
        . '<span class="tabStyle">' . $row['Population'] . '</span>'
        . '</p>' . "\n";
    }
    echo "</div>\n";
    ?>
</body>
[/php]

the key is to breakdown your script in easy to understand variables and/or functions, plus don’t be afraid of commenting your code. It’s a good way to look back to see what you did with that particular script.

An Example:
[php] /*
* Secure the Password by hashing the user’s password.
*/
$data[‘password’] = password_hash($data[‘password’], PASSWORD_DEFAULT, array(“cost” => 15));

/* Set the query variable */
$query = 'INSERT INTO users (username, password, email, birthday, date_added) VALUES (:username, :password, :email, :birthday, NOW())';

/* Prepare the query */
$stmt = $pdo->prepare($query);

/* Execute the query with the stored prepared values */
$result = $stmt->execute([
    ':username' => $data['username'],
    ':password' => $data['password'],
    ':email' => $data['email'],
    ':birthday' => $data['birthday']
]); // End of execution:
if ($result) {
    return true;
}[/php]

HTH John

While you don’t want to do this when you get down on this, put your code in a Try/Catch block and output the error to the screen so you can see any DB problems. Turn on Php error reporting so you can see the Php errors.

Also, it might help if you download my PDO Bumpstart Database from My signature.

I spent days trying to figure out why on earth every example would work and sometimes not work this was super frustrating and I have bald spots on my head. No one here in the forums could even see the problems I was having. Dont feel bad no forum caught the problem.

[php]1
2
3
4
5
6
7
8

$dbcall = new PDO(“mysql:host=$dbhost;dbname=$dbname”, $dbusername, $dbpassword);

$statement = $dbcall->exec(“INSERT INTO messages(from, to, body)
VALUES(‘piss’, ‘off’, ‘pdo’)”);

?>[/php]

I was working with a table that had a column named “from” or "From and that is a mysql CLAUSE so every single time I tried to create a connection and insert a record it would fail. Then I started using Try/Catch to dump errors to the browser and then copying and pasting them into google. I found one post on like the 6th page of google results and it said you cannot reliably use “from” as a column name without quoting the column name in back ticks ` but that the best thing to do was change the column name. So I changed the column name and all of a sudden many of my attempts work perfectly fine!

Just thought I would share my frustration in hopes that someone else could learn from it as well.

Chad

from is a mysql reserved word. You might want to study the entire list of reserved and keywords.
https://dev.mysql.com/doc/refman/5.7/en/keywords.html

just another proof coding without error handling is just making life miserable for yourself

Sponsor our Newsletter | Privacy Policy | Terms of Service