Member Avatar for mutago

Hello everyone, this mysql_deprecated works very fine and retrieves information but when i tried
moving it to PDO it displays nothing. can someone help me

thank you

working mysql_deprecated.php

<?php
require('config.php');
    $user = $_SESSION['log']['username'];
    $sql  = "SELECT * FROM users WHERE username='$user' OR friend='$user' AND status = 'accepted'";
$result = mysql_query($sql);
while($row = mysql_fetch_assoc($result))
{

echo 'fine';

if ($row['friend'] != $user) { $friend = $row['friend']; } else { $friend = $row['username']; }

$query  = "SELECT *,UNIX_TIMESTAMP() - date_created AS TimeSpent FROM post WHERE 
(username = '$friend' OR username = '$user') ORDER BY post_id DESC LIMIT 5";
$result = mysql_query($query);

while($row = mysql_fetch_assoc($result))

{   

echo 'data retrieved successfully';
}
}

?>


pdo.php

<?php
$db = new PDO (
    'mysql:host=localhost;dbname=testing', 
    'root', // username
    '' // password
);
$user = $_SESSION['log']['username'];
$sql = $db->prepare('
            SELECT * FROM users
            WHERE username= ?  OR friend = ? and status = accepted');


$sql->bindParam(1, $user, PDO::PARAM_STR);
$sql->bindParam(2, $user, PDO::PARAM_STR);
$sql->execute();

 while($row = $sql->fetch()) 
{

echo 'fine';

if ($row['friend'] != $user) { 
$friend = $row['friend']; 
} else { 
$friend = $row['username']; 
}


$query = $db->prepare('
    SELECT *,UNIX_TIMESTAMP() - date_created AS TimeSpent FROM post WHERE 
(username = :username OR username = :username1) ORDER BY post_id DESC LIMIT 5

');
        $query->execute(array(
            ':username' => $friend,
                          ':username1' => $user )); 
                 $result = $query->fetch();

                  while ($row = $result->fetch()) 

                       {
echo 'data retrieved successfully';
}
}

?>
  • 2 Contributors
  • 5 Replies
  • 217 Views
  • 3 Hours Discussion Span
  • Latest Post Latest Post by mutago

Dani AI

Generated

A few focused notes and a compact, correct PDO pattern for the same workflow.

SQL logic and fetch behavior to check first: SQL treats AND with higher precedence than OR, so use parentheses when you mean "(A OR B) AND C". Make sure string literals (like a status value) are quoted. With PDO, enable exceptions so SQL or bind errors are visible. Also avoid reusing the same loop variable for nested queries (outer $row vs inner $row) and don’t call fetch() and then try to call fetch() on that returned array — fetch from the statement object in a loop.

The example below shows a safe PDO setup, grouped WHERE clause, and correct fetch loops (uses positional placeholders for the inner query to reuse the same value cleanly):

try {
    $dbh = new PDO('mysql:host=localhost;dbname=testing;charset=utf8mb4', 'root', '', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);

    $userName = $_SESSION['log']['username'] ?? null;
    $userStmt = $dbh->prepare('SELECT * FROM users WHERE (username = :u OR friend = :u) AND status = :s');
    $userStmt->execute([':u' => $userName, ':s' => 'accepted']);

    while ($userRow = $userStmt->fetch()) {
        $friendName = ($userRow['friend'] !== $userName) ? $userRow['friend'] : $userRow['username'];

        $postStmt = $dbh->prepare(
            'SELECT *, UNIX_TIMESTAMP() - date_created AS TimeSpent
             FROM post
             WHERE username IN (?, ?)
             ORDER BY post_id DESC
             LIMIT 5'
        );
        $postStmt->execute([$friendName, $userName]);

        while ($postRow = $postStmt->fetch()) {
            // process each post row
        }
    }
} catch (PDOException $e) {
    error_log('PDO error: ' . $e->getMessage());
}

Small practical tips: set PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION while developing, use PDO::FETCH_ASSOC to match mysql_fetch_assoc, prefer different variable names for nested loops, and choose positional placeholders when the same value must appear multiple times in a statement. Credit to for spotting the quoted-status issue and for confirming the parameter-style fix; the above addresses the remaining confusion about fetch usage and SQL grouping.

Recommended Answers

All 5 Replies

Member Avatar for pritaeas

Missing single quotes around accepted

Member Avatar for mutago

i have tried adding the quotes but it seems not to work.

PDO is supposed to query database records or better displays " querry successful" on like the working mysql deprecated counter part. I think where i have problem is in sql statement

$sql = "SELECT * FROM friends WHERE username='$user' OR friend='$user' AND status = 'accepted'";

as variable [B]$user[/B] is used for username and friend simultaneously

and here also** while($row = mysql_fetch_assoc($result))** during PDO conversion

Member Avatar for mutago

my question again is what is the equivalent of this in PDO
while($row = mysql_fetch_assoc($result))

as mysql_fetch_array($result) =>

$result->fetch()) in PDO

thank you

Member Avatar for pritaeas

Check this example.

Member Avatar for mutago

Resolved. Thank you. I used ? instead of name parameters. again i quoted accepted like you said in the sql statement. whaw everything works

Thank you

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.