fetch in PDO gets only one result

Question 1

I have this code

$sql = new PDO('mysql:host=localhost;dbname=b','root','root');
$f = $sql->query('select * from user');
$sql->setFetchMode(PDO::FETCH_ASSOC);

while($row = $f->fetch()){
     print_r($row);
}

The output is

Array
(
    [id] => 1
    [name] => turki
    [mail] => ablaf
    [pass] => 144
)
Array
(
    [id] => 2
    [name] => wrfwer
    [mail] => fwerf
    [pass] => werf
)

and that's what I really want. But if I do this

<?php

$sql = new PDO('mysql:host=localhost;dbname=b','root','root');
$f = $sql->query('select * from user');
$f->setFetchMode(PDO::FETCH_ASSOC);
 print_r($f->fetch());
?>

The output is

Array
(
    [id] => 1
    [name] => turki
    [mail] => ablaf
    [pass] => 144
)

It has one result but I have two rows in the table; why is that?

Question 2

Fetch should be used to display next row from database result

To get all rows you should use fetchAll();

PDOStatement::fetch — Fetches the next row from a result set
PDOStatement::fetchAll() — Returns an array containing all of the result set rows

Change your example to:

 <?php
 $sql = new PDO('mysql:host=localhost;dbname=b','root','root');
 $f = $sql->query('select * from user');
 $f->setFetchMode(PDO::FETCH_ASSOC);
 print_r($f->fetchAll());
 ?>

or if you want use PDOStatement::fetch to

 <?php
 $sql = new PDO('mysql:host=localhost;dbname=b','root','root');
 $f = $sql->query('select * from user');
 while($row = $sth->fetch(PDO::FETCH_ASSOC))
 {
   print_r($row);
 }
 ?>

Question 3

You have to use the fetch function according to your desired result.

If you need a single scalar value, no column name is ever needed:
```
 $Number1 = $stmt->fetchColumn();
```
If there are many results to be returned, but only one column per row, no need for the column name again:
```
 $numbers = $sth->fetchAll(PDO::FETCH_COLUMN);
```
It will return an array of numbers.
If you need a single row with multiple values, then use fetch()
If you need array of rows, use fetchAll()

Question 4

it is not PDO's fetchAll() of course, but your query.

Which is not

IN (1007,1011,1012,1013,1014)

but

IN ('1007,1011,1012,1013,1014')

and of course it will find only first value as this string will be cast to the first number

One have to create a query with placeholders representing every array member, and then bind this array values for execution:

$ids = array(1,2,3);
$stm = $pdo->prepare("SELECT * FROM t WHERE id IN (?,?,?)");
$stm->execute($ids);

To make this query more flexible, it's better to create a string with ?s dynamically:

$ids = array(1,2,3);
$in  = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM table WHERE column IN ($in)";
$stm = $db->prepare($sql);
$stm->execute($ids);
$data = $stm->fetchAll();

Question 5

You have to pass here the $_POST['postid']

if(isset($_POST['posteraser'])){
   $postid = $_POST['postid'];
   $sql = query("DELETE FROM post_list WHERE postid = '$postid' ");       
   redirect ('home.php');
}

OR as procedure way

$sql = query("DELETE FROM post_list WHERE postid = ? ",$postid);

Question 6

For this to work, you need to use the global variable scope, explained here: http://php.net/manual/en/language.variables.scope.php

$mysql_user = "NotTelling";
$mysql_password = "DefinatelyNotThis";
try
{
    $dbh = new PDO("mysql:host=somehost;dbname=somename", $mysql_user, $mysql_password);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $username = $_POST['username'];
    $inPword = $_POST['password'];
    $lat =  $_POST['lat'];
    $lon =  $_POST['lon'];

    $loggedin = "";
    $password_hash = "";
    $loggedinstatus = "";
    $pts = "";

    function getLoginInfo()
    {
        global $dbh, $username, $password_hash, $loggedinstatus, $pts;

        $sth = $dbh -> prepare('SELECT pword, loggedin, points FROM login WHERE uname = :uname');
        $sth->bindParam(':uname', $username, PDO::PARAM_STR);
        while($row = $sth->fetch(PDO::FETCH_ASSOC))
        {
            echo $row['pword'];
            echo $row['loggedin'];
            echo $row['points'];
        }
        $password_hash = $fetch['pword'];
        $loggedinstatus = $fetch['loggedin'];
        $pts = $fetch["points"];

        if($password_hash === null || $loggedinstatus === null || $pts === null)
        {
            die(json_encode(array("message" => "none")));
        }
        else
        {           
            return "more";
        }
    }

    function checkLoginCreds()
    {
        global $dbh, $inPword, $password_hash, $loggedinstatus, $username;

        if(crypt($inPword, $password_hash) === $password_hash)
        {
            switch($loggedinstatus)
            {
                case  "no":         
                    $sel = $dbh->prepare("UPDATE login SET loggedin='yes' WHERE uname = ?");
                    $sel->execute(array($username));
                    return "AllGood";
                    break;

                defaut:
                    return "alreadyin";
                    break;
            }
        }
        else
        {
            return "BadLogin";
        }
    }

    if(getLoginInfo() === "more")
    {
        echo json_encode(array("message" => checkLoginCreds()));
    }

    getLoginInfo();
}
catch(PDOException $e)
{
    echo $e->getMessage();
    file_put_contents('PDOErrors.txt', $e->getMessage(), FILE_APPEND);
}

But this can get messy very quickly.

I suggest you put the variables in an array or using OOP for a more robust solution: http://php.net/manual/en/language.oop5.php

fetch in PDO gets only one result

Answers

php,mysql,database,pdo