Pdo Get Data from Database

PDO get data from database

The PDO method fetchAll() returns an array/result-set, which you need to assign to a variable and then use/iterate through that variable:

$users = $getUsers->fetchAll();
foreach ($users as $user) {
    echo $user['username'] . '<br />';
}

UPDATE (missing execute())

Also, it appears you aren't calling the execute() method which needs to happen after you prepare the statement but before you actually fetch the data:

$getUsers = $DBH->prepare("SELECT * FROM users ORDER BY id ASC");
$getUsers->execute();
$users = $getUsers->fetchAll();
...

Use PDO to extract value from database

You haven't executed $stmtt, so there's no result set to fetch.

$stmtt = $dbh->prepare("SELECT auth FROM auth where id=1");
$stmtt->execute();
$code = $stmtt->fetch(PDO::FETCH_ASSOC);

(PDO) Gather specific data from id

To avoid this kind of mistakes you need to use prepared statements of PDO (which also prevents SQL INJECTION)

$dbh = new PDO("mysql:host=$hostname;dbname=vector",$username,$password);

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // <== add this line

$id = $_GET['id'];

$sth = $dbh->prepare("SELECT * FROM users WHERE id = ?");
$sth->execute(array($id));
$data = $sth->fetchAll(PDO::FETCH_ASSOC);
print_r($data); // check  values are coming or not and then try to print it

Fetch data from db using PDO

Well, advise you were given is wrong.

Not use but learn.

You have to learn something before using it.

There are many tutorials on PDO around (all of them crappy ones though) but at least you can learn proper syntax from there

    $sql = "SELECT id, nivel, tipo, titulo, texto, ativa FROM account.quests_faq WHERE ativa='YES' ORDER BY nivel DESC";
    // look this string contains SQL query. so, the variable is named $sql
    $stmt = $conn->query($sql);
    // in the next line we are getting a statement object from the function query()
    // this is why variable called $stmt
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    // and now we can start iterating this statement.
    // statement, Carl. Not connection to database
    // which is called $conn, if you get an idea

also you have to enable error reporting for PDO.

And yes, as it was said in the other answer, your PHP syntax is also wrong. You are supposed to learn it too, instead of banging together random lines of code and then asking others to fix it for you.

Start from less complex syntax, from echoing one single variable without decoration. And ask one question per post. As for the PDO part you already got the answer

How to get data from row using id number in link using PDO prepared Statement

If I understand correctly, your question is asking how to use prepared statements correctly with your example. Let me answer this question.

Take a look at the comments I made along my changes:

<?php
include "/includes/db.php";
$id = $_GET['id'];
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,        // enable PDO errors
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,   // fetch associative arrays by default
    PDO::ATTR_EMULATE_PREPARES => false,                // Use native prepared statements
];
//                      You should always specify charset VVVVV
$pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password, $options);

// Prepare and execute SQL passing in the value of $id
$MyFAQlist = $pdo->prepare('SELECT * FROM MyFAQlist WHERE id = ? ORDER BY visits DESC');
$MyFAQlist->execute([$id]);
?>

Instead of the while loop you can simply use foreach:

<?php foreach($MyFAQlist as $row): ?>

getting single cell from database using PDO

You can create a white list of your column names and use it to select the right column. You can check the column against a white list with the help of in_array. The third parameter is very important as it checks that string is a string. You can only then safely concatenate the SQL with your PHP variables using PHP concatenation operator. For the good measure, the column names should be enclosed in backticks `, in case any of your column names is a reserved word or contains special characters.

$whiteListOfHeating = [
    'keyName',
    'den',
    'WC1',
    'hallway',
    'garage'
];

$room = 'garage';
if (in_array($room, $whiteListOfHeating, true)) {
    $sql = 'SELECT `'.$room.'` FROM heating WHERE id = 3';
    $stmt = $pdo->prepare($sql);
    // ...
} else {
    echo 'Invalid column name specified!';
}

Select data from database with PDO

$stmt = $db->prepare("SELECT id FROM tablename");
try{
    $stmt->execute();
}catch(PDOException $err){
    //some logging function
}
//loop through each row
while($result=$stmt->fetch(PDO::FETCH_ASSOC)){
    //select column by key and use
    echo $result['id'];
} 

alternatively you could use fetchAll to select whole dataset into a variable. Then you wouldn't need to loop

Heres a useful resource for PDO:

http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers

How can I use PDO to fetch a results array in PHP?

Take a look at the PDOStatement.fetchAll method. You could also use fetch in an iterator pattern.

Code sample for fetchAll, from the PHP documentation:

<?php
$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();

/* Fetch all of the remaining rows in the result set */
print("Fetch all of the remaining rows in the result set:\n");
$result = $sth->fetchAll(\PDO::FETCH_ASSOC);
print_r($result);

Results:

Array
(
    [0] => Array
        (
            [NAME] => pear
            [COLOUR] => green
        )

    [1] => Array
        (
            [NAME] => watermelon
            [COLOUR] => pink
        )
)

Get values from database using PHP PDO and update input to checked

You were really close, you did not fetch properly:

require("config.php"); 
if(empty($_SESSION['user']['id'])) 
{
    header("Location: index.php");
    die("Redirecting to index.php"); 
}   

$userid = $_SESSION['user']['id'];

$sql = "SELECT my_music, my_movies, my_weather, my_maps, my_news 
        FROM user_preferences 
        WHERE user_id = :userID";

$stmt = $db->prepare($sql);
$stmt->bindParam(":userID", $userid);
$stmt->execute();

$result = $stmt->fetch();

• You bind Params on the statement object not the connection
• You fetch on the statement as well not the connection
• fetchAll returns a 2 dimensional array if you want to see the content use var_dump not echo

---

<input id="mymusic"
       type="checkbox" 
       name="mymusic" 
       <?php echo ($result['my_music']==1 ? 'checked' : '');?>     
/>

<input id="mymovies"
       type="checkbox" 
       name="mymovies"  
       <?php echo ($result['mymovies']==1 ? 'checked' : '');?>
/>

---

Related Topics