How to list table-specific data with PDO?

Asked

Viewed 7,460 times

3

How to list data specific of a table, but using PDO?

I’m starting to study and use PDO, however to some things that I am not able to find and that is well explained of how to read the data with a call WHERE in the search.

With the Mysqli is used this way, but how to convert to PDO??

$select     = mysqli_query($conn,"SELECT * FROM `tabela` WHERE `valor1` = '$var_1' AND `valor2` = '$var_2'");
$aRow       = mysqli_fetch_assoc($quti_mh);
$idt        = $aRow['id'];
php mysql pdo
  • 1

    I could not understand, what is the problem? sometimes there are no records that satisfy the where or it is wrong/reversed/too restrictive.

  • Apparently you don’t understand kkk. I want to know how to list a specific PDO query.. @rray

  • I think that that helps, gave the impression that the problem was in the consultation and not what methods(query(), prepare(), execute() fetch*()) are used to perform the query.

  • 1

    The same query works both in PDO and mysqli. I think the question should be "how to query in PDO" then. As for using Prepared statements, as answered, this is also possible (and recommended in most cases) in mysqli as well. In short, knowing how to do Where and knowing how to use the most specific features of PDO/mysqli are two different things.

2 answers

3


Simple consultation

To make a simple query with fixed parameters or without them can use the method query() he does exactly the same thing as the mysqli_query() i.e., it processes a query and returns a resultset that must be manipulated to extract the values.

The method that returns the database information is the fetch*() he has several flavors, arrays and objects are favourites.

fetch()

When you need to return only one record(be to make a "change/edit") prefer fetch() it returns an array/object in the 'right' structure not to do something like, echo $usuario[0]['nome'], just need $usuario['nome']

Return structure:

Array
(
    [id] => 1
    [nome] => Doge
    [senha] => ***
)

fetchAll()

To return all rows of a query use fetchAll() is recommended in case where there are few records.

Return structure

Array
(
    [0] => Array
        (
            [id] => 1
            [nome] => Doge
            [senha] => ***
        )

)

fetchColumn()

Returns a scalar (i.e., a pure value such as int, string, float etc is outside of this list arrays objects and other composite types) of a record, accepts an argument that is the query column Dice.

$db = new PDO('mysql:host=localhost;dbname=teste', 'usuario', 'senha');
//* = id, nome e senha
$query = $db->query("SELECT * FROM usuarios WHERE ativo = 1 limit 300");
$resultado = $query->fetchAll(PDO::FETCH_ASSOC);
foreach($resultado as $item){
    $item['nome'];
}

Consultation with Prepared statements

To avoid problems with sql Injection Prepared statements are the solution, because the values sent by the user are not placed directly in the sql statement, for more details see this

$db = new PDO('mysql:host=localhost;dbname=teste', 'usuario', 'senha');
$stmt = $db->prepare("SELECT * FROM usuarios WHERE id = ?");

if(!$stmt->execute()){
    echo '<pre>';
    print_r($stmt->errorInfo());
}

$resultado = $stmt->fetch(PDO::FETCH_ASSOC);

1

I think I understand what you mean That’s what you want?

$tables = $dbh->prepare("SELECT * FROM `tabela` WHERE `valor1` = ? AND `valor2` = ?");

$tables->execute(array('apple', 'green'));
$resultados = $tables->fetch(PDO::FETCH_ASSOC);

Browser other questions tagged php mysql pdo

You are not signed in. Login or sign up in order to post.