unable to update SQL table with php program

Question

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

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.

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"; 

?>

Um, your not executing the query :s, [also don't directly insert params in the query](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1). — Lawrence Cherone, Jan 14 '18 at 05:49
@LawrenceCherone, I did not know about the `SQL Injection`. Thanks for pointing it out. See my updated code. Thanks again — Prasanna, Jan 14 '18 at 11:05

score 0 · Answer 1 · answered Jan 14 '18 at 06:02

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

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, Jan 14 '18 at 07:19

Hasan Ilyas · Accepted Answer · 2018-01-14T17:56:33.640

0

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

edited Jan 14 '18 at 17:56

answered Jan 14 '18 at 06:09

Hasan Ilyas

67
12

+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 Jan 14 '18 at 07:21
send me the code you are trying then ill amend it for you – Hasan Ilyas Jan 14 '18 at 08:32
Code updated. Please have a look when you have some time – Prasanna Jan 14 '18 at 16:18

unable to update SQL table with php program

2 Answers2