I have installed XAMPP and ensured that all the servers are running. I'm completely new to PHP and SQL

enter image description here

I configured a local database called test and a table called sensor.
I have added a user called arduino with a password.
pls ignore the comments

<?php

// Prepare variables for database connection

$dbusername = "arduino";
$dbpassword = "xxx"; 
$server = "localhost"; 

// Connect to your database

$dbconnect = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'arduino', 'test');

// Prepare the SQL statement

$sql = "INSERT INTO test.sensor (value) VALUES ('".$_GET["value"]."')";    

// Execute SQL statement

// mysql_query($sql);

?>

I want to use this set up to fetch data from arduino. Before connecting this set up to arduino, I wanted to ensure that this would be able to fetch data by passing http://localhost/write_data.php?value=100 to the browser. I was expecting that this would update the table with id, timestamp and value (of 100). It did not.

enter image description here

I had trouble with $dbconnect = mysql_pconnect($server, $dbusername, $dbpassword); and hence replaced that with $db = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'arduino', 'test');

I also had trouble with mysql_query($sql);. So I have commented it out for now.

How can I get this to work? Where can I find easy to follow documentation for MySql replacements?

Updated Code based on answers

<?php

    $dbusername = "arduino";  
    $dbpassword = "test";  
    $server = "localhost"; 

    $dbconnect = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'arduino', 'test');

    $stmt = $dbconnect->prepare('insert into sensor(value) values(:val)');
    $stmt->bindParam(':val', $_GET["value"], PDO::PARAM_INT);
    $stmt->execute();
    print "procedure returned $return_value\n"; 

?>
3 upvote
  flag
Um, your not executing the query :s, also don't directly insert params in the query. – Lawrence Cherone
upvote
  flag
@LawrenceCherone, I did not know about the SQL Injection. Thanks for pointing it out. See my updated code. Thanks again – Prasanna

2 Answers 11

You are not executing the SQL statement in your code. Try executing the below implementation :

$db = new PDO('mysql:host=localhost;dbname=rfid_db;charset=utf8mb4', 'username', 'password');

//$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //optional
//$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); //optional

$stmt = $db->prepare('insert into sensor(value) values(:val)');
$stmt->bindParam(':val', $_GET["value"], PDO::PARAM_INT);
$stmt->execute();

Also for detailed study on PDO try referencing the documentation here http://php.net/manual/en/pdo.prepared-statements.php

upvote
  flag
Thanks for your answer (+1) for the straight forward answer. If I want to print the updated table to the screen - what commands should I use? – Prasanna

Brother checkout this example.. you have to bind get parameter in your query

Example:-

$servername = "localhost";
            $username = "username";
            $password = "password";
            $dbname = "myDBPDO";

            try {
                $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
                $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                $sql = "SELECT * FROM `$table` WHERE `$fieldname`=:id";
        $stmt = $conn->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->execute();
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    print_r($result);  
            }
            catch(PDOException $e) {
                echo "Error: " . $e->getMessage();
            }
upvote
  flag
+1 for Brother. Your code $result = $stmt->fetchAll(PDO::FETCH_ASSOC); and print_r($result); returns a screen with the response Array(). How can I show the updated table in the browser? – Prasanna
upvote
  flag
send me the code you are trying then ill amend it for you – Hasan Ilyas
upvote
  flag
Code updated. Please have a look when you have some time – Prasanna

Not the answer you're looking for? Browse other questions tagged or ask your own question.