shan Posted October 3, 2015 Share Posted October 3, 2015 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 Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted October 3, 2015 Share Posted October 3, 2015 Can you post the error message in full. Quote Link to comment Share on other sites More sharing options...
shan Posted October 3, 2015 Author Share Posted October 3, 2015 (edited) @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 Quote Link to comment Share on other sites More sharing options...
Barand Posted October 3, 2015 Share Posted October 3, 2015 You need space between OR and next name $orlogic .="uname='$user' OR "; ^ | space Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted October 3, 2015 Share Posted October 3, 2015 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.) Quote Link to comment Share on other sites More sharing options...
Solution Jacques1 Posted October 3, 2015 Solution Share Posted October 3, 2015 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. Quote Link to comment Share on other sites More sharing options...
shan Posted October 3, 2015 Author Share Posted October 3, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted October 3, 2015 Share Posted October 3, 2015 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. 1 Quote Link to comment Share on other sites More sharing options...
shan Posted October 3, 2015 Author Share Posted October 3, 2015 @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) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.