CODEWITHSUNDEEP
phpsql-serverpdo
Veronica
Veronica

Reputation: 541

Variable declared in sql statement But cannot get the data to show

I am having an issue where the data is not showing up. When I put an OR on it it does show up but The or causes say user2 to have the same data as user1 . 

<?php
      include 'users.php';
      require_once("db_connect.php");

     //prepared statement with PDO to query the database
     $stmt = $db->prepare("SELECT * FROM requests WHERE User='.$user1.' AND status='Received' ORDER BY id DESC);
     $stmt->execute();  
    ?>

users.php

<?php 

$user1 = xxxx;
$user2 = zzzz;

?>

Upvotes: 1

Views: 49

Answers (2)

Kevin Nelson
Kevin Nelson

Reputation: 7663

$stmt = $db->prepare("SELECT * FROM requests WHERE User='.$user1.' AND status='Received' ORDER BY id DESC);

IF $user1 = 1, then your SQL is going to be: 

SELECT * FROM requests WHERE User='.1.'.

You have double-quotes on the outer-string, so you are not escaping the string and concatenating, those will be literal dots, and you're never closing your outer-string at the end. Also, I'm not sure of the point of using prepare if you are going to inject your variable directly into the string...?

Should look more like this:

$stmt = $db->prepare("SELECT * FROM requests WHERE User=:user AND status='Received' ORDER BY id DESC");
$stmt->execute(array(':user' => $user1));

If you want to do it your way, you can fix what looks like typos with:

$stmt = $db->prepare("SELECT * FROM requests WHERE User='".$user1."' AND status='Received' ORDER BY id DESC");

OR

$stmt = $db->prepare("SELECT * FROM requests WHERE User='{$user1}' AND status='Received' ORDER BY id DESC");

However, both of these methods are NOT SAFE. They are at risk for SQL-Injection attacks. You should use the prepared statement as I showed with the :user variable that PDO first sanitizes before injecting into your string.

To get your count:

$stmt = $db->prepare("SELECT COUNT(*) AS rows_cnt FROM requests WHERE status='Received' AND User=:user");  
$stmt->execute(array(':user'=>$user1));
if( false !== ($row = $stmt->fetch(PDO::FETCH_ASSOC)) ) { 
    echo $row['rows_cnt'];  
}

Upvotes: 2

jack
jack

Reputation: 185

probably is a typo but seem that you don't close a "at the end of the line

 $stmt = $db->prepare("SELECT * FROM requests WHERE User='.$user1.' AND status='Received' ORDER BY id DESC);

So try this:

 $stmt = $db->prepare("SELECT * FROM requests WHERE User='.$user1.' AND status='Received' ORDER BY id DESC");

Upvotes: 0

Related Questions