PHP:"Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: "

[shan]

hi, guys can any one say where im going wrong in this code as im not able to get to display friend lists in both search_results.php and home.php.

 

the code in friend_list.php which is included in both home and search results page to display friends list:

<?php


$friends_html="";
$orlogic="";
$uname_s=  \htmlentities($_GET['u']);
$sql="select count(friend_id) from friends where friend_one=:uname_s and accepted='1' or friend_two=:uname_s and accepted='1'";
$stmt=$conn->prepare($sql);
$stmt->bindparam(":uname_s", $uname_s);
$stmt->execute();
$query_count=$stmt->fetchAll(PDO::FETCH_ASSOC);
$friend_count=$query_count[0];
if ($friend_count<1) {
    $friends_html="$uname_s has no friends yet";
    }
 else {
    $all_friends=array();
    $sql="select friend_one, friend_two from friends where friend_two=:uname_s and accepted='1' order by rand()";
    $stmt=$conn->prepare($sql);
    $stmt->bindparam(":uname_s",$uname_s);
    $stmt->execute();
    while ($row =$stmt->fetchAll(PDO::FETCH_ASSOC)) {
       
        array_push($all_friends, $row[0]['friend_one']);
      
        
    }
    $sql1="select * from friends where friend_one=:uname_s and accepted='1' order by rand()";
    $stmt1=$conn->prepare($sql1);
    $stmt1->bindparam(":uname_s",$uname_s);
    $stmt1->execute();
    while ($row1 = $stmt1->fetch(PDO::FETCH_ASSOC)) {
     
        array_push($all_friends, $row1['friend_two']);
        
        }
    $friendArrayCount=  count($all_friends);
    foreach ($all_friends as $key => $user) {
        $orlogic .="uname='$user' OR";
       
    }
    $orlogic1=  chop($orlogic, "OR");
    $sql2="select uname,avatar from user where :orlogic1";
    $stmt2=$conn->prepare($sql2);
    $stmt2->bindparam(":orlogic1",$orlogic1);
    $stmt2->execute();
    while ($row11 = $stmt2->fetchAll(PDO::FETCH_ASSOC)) {
     
      $friend_username=$row11[0]["uname"];
      $friend_avatar=$row11[0]["avatar"];
      if ($friend_avatar!=""){
          $friend_pic='user/'.$friend_username.'/'.$friend_avatar;
      }  else {
          $friend_pic='img/avatardefault.png';
          }
          echo '<a href="search_results.php?u='.$friend_username.'"><img class="friendpics" src="'.$friend_pic.'" height="80" width="80" alt="'.$friend_username.'" title="'.$friend_username.'">'.$friend_username.'</a>';


    }
    
    
 }

if you guys need any further info please let me know

 

 

[Ch0cu3r]

Can you post the error message in full.

[shan]

@ch0cu3r the error message is as follows when running the code as follows in home.php:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORuname='shan2batman1'' at line 1' in /opt/lampp/htdocs/project/notification/friend_list.php:44 Stack trace: #0 /opt/lampp/htdocs/project/notification/friend_list.php(44): PDOStatement->execute() #1 /opt/lampp/htdocs/project/home.php(72): include_once('/opt/lampp/htdo...') #2 {main} thrown in /opt/lampp/htdocs/project/notification/friend_list.php on line 44

 

the changed code looks like this:

$orlogic1=  chop($orlogic, "OR");
    $sql2="select uname,avatar from user where $orlogic1";
    $stmt2=$conn->prepare($sql2);
   //$stmt2->bindparam(":orlogic1",$orlogic1);
    $stmt2->execute();
    while ($row11 = $stmt2->fetchAll(PDO::FETCH_ASSOC)) {
     
      $friend_username=$row11[0]["uname"];
      $friend_avatar=$row11[0]["avatar"];
      if ($friend_avatar!=""){
          $friend_pic='user/'.$friend_username.'/'.$friend_avatar;
      }  else {
          $friend_pic='img/avatardefault.png';
          }
          echo '<a href="search_results.php?u='.$friend_username.'"><img class="friendpics" src="'.$friend_pic.'" height="80" width="80" alt="'.$friend_username.'" title="'.$friend_username.'">'.$friend_username.'</a>';


    }
    
    
 }

Edited October 3, 2015 by shan

[Barand]

You need space between OR and next name

$orlogic .="uname='$user' OR ";
                            ^
                            |
                           space

[mac_gyver]

that particular error is because you cannot bind sql syntax. you can only bind data values. your uname='$user' OR uname='$user' ... syntax cannot be bound into the query.

 

you don't even need the section of code where the error is at. you should be JOINing the user table to the friends table to get the avatar in the same query where you are getting the friends. in fact, all the queries you have shown can be replaced with one query (you would randomize the two different sections of the results, assuming you really want to randomize anything, by pre-processing the rows in your php code to store the two different sections of the results in their own array, then randomize each of those arrays in the php code.)

 

your code also has a problem with the first query. you cannot bind ONE parameter to multiple place-holders when using prepared queries. you would need to use two differently named place-holders and bind the $uname_s variable twice. the reason you aren't getting any php/mysql errors is because your pdo instance is emulating prepares and for emulated prepares, you can bind one parameter to multiple same-name place-holders (thanks php, NOT, for being consistent and doing what the documentation states.)

[Jacques1]

In case you have trouble selecting the friends “in both directions”, this can be done with a UNION. For example, the friends of “joe” can be selected with

SELECT
  friend_two AS friend_name
FROM
  friends
WHERE
  friend_one = 'joe'
  AND accepted
UNION
SELECT
  friend_one
FROM
  friends
WHERE
  friend_two = 'joe'
  AND accepted
;

Or even shorter:

SELECT DISTINCT
  IF(friend_one = 'joe', friend_two, friend_one) AS friend_name
FROM
  friends
WHERE
  (friend_one = 'joe' OR friend_two = 'joe')
  AND accepted
;

Now all you need to do is join this result set with the user table as suggested by mac_gyver.

 

[shan]

i'm sorry if i wasn't precise the error occurs in search_results.php page if the user views some profile who is not a friend and has not activated the account through email.

i have tried to query->stmt and load info, it appears fine when viewing a accepted friendship but if we look at a person who is not a friend i get the following error.

changed query now looks like this.

 

$sql1="select * from friends where friend_one=:uname_s order by rand()";
    $stmt1=$conn->prepare($sql1);
    $stmt1->bindparam(":uname_s",$uname_s);
    $stmt1->execute();
    while ($row1 = $stmt1->fetch(PDO::FETCH_ASSOC)) {
     
        array_push($all_friends, $row1['friend_two']);
        
        }
    $friendArrayCount=  count($all_friends);
    foreach ($all_friends as $key => $user) {
        $orlogic .="uname='$user' OR ";
    }
    $orlogic1=  chop($orlogic, " OR ");
    $sql2="select uname,avatar from user where $orlogic1";
    $stmt2=$conn->query($sql2);
   //$stmt2->bindparam(":orlogic1",$orlogic1);
    //s$stmt2->execute();
    while ($row11 = $stmt2->fetchAll(PDO::FETCH_ASSOC)) {
     
      $friend_username=$row11[0]["uname"];
      $friend_avatar=$row11[0]["avatar"];
      if ($friend_avatar!=""){
          $friend_pic='user/'.$friend_username.'/'.$friend_avatar;
      }  else {
          $friend_pic='img/avatardefault.png';
          }
          echo '<a href="search_results.php?u='.$friend_username.'"><img class="friendpics" src="'.$friend_pic.'" height="50" width="50" alt="'.$friend_username.'" title="'.$friend_username.'">'.$friend_username.'</a>';


    }
    
    
 }

error msg that i get now is as follows:

 

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1' in /opt/lampp/htdocs/project/notification/friend_list.php:42 Stack trace: #0 /opt/lampp/htdocs/project/notification/friend_list.php(42): PDO->query('select uname,av...') #1 /opt/lampp/htdocs/project/search_results.php(78): include_once('/opt/lampp/htdo...') #2 {main} thrown in /opt/lampp/htdocs/project/notification/friend_list.php on line 42

 

 

thanks for the replies.

Edited October 3, 2015 by shan

[Jacques1]

It doesn't matter which particular variation of that friend-selection code throws the exception, because it's always the same two errors:

• You try to emulate basic SQL feature like joins in PHP, which leads to lots of unnecessary queries and overly complex string constructions. If you had one simple query, I'm sure you'd be done already.
• Your code is wide open to SQL injections and syntax conflicts, because you've hastily given up prepared statements instead of actually fixing the problem.

Read the previous replies and consider brushing up on your SQL skills. This will help you much more than chasing syntax errors.

[shan]

@jacques1 thanks bro your solution has worked. thanks for the help bro. see you next time till an error throws up.(i'll consider your advice for studying mysql indepthly)