0

From the code below, this what I'm trying to achieve: If citizenID found by the $get_friends query are 1 and 2. I would like the while loop to first get all the rows for 1 and then do the same for 2. The code I currently have below its only retrieving the last row for 1 and 2. Please help.

<?php

include ('session_start.php');
include_once('db_connect_universal.php');

$user_id = $_SESSION['sess_id'];

//GET USER FRIENDS AND DETAILS
$get_friends = "SELECT * FROM citizens
                INNER JOIN user_details 
                ON citizens.citizenID=user_details.UserID 
                WHERE citizens.userID = '".$user_id."'";

$results_get_friends = $conn->query($get_friends);

$num_rows_friends = mysqli_num_rows ($results_get_friends);

while ($row_friends = $results_get_friends->fetch_assoc()) {

    $citizenID = $row_friends['citizenID'];

    //GET RATINGS AND COMMENTS
    $sql = "SELECT * FROM comments
                INNER JOIN organiser ON comments.movieID=organiser.movieID 
            WHERE comments.userID= '".$citizenID."'  
              AND ratings_n_comments.Time BETWEEN DATE_SUB(CURDATE(), INTERVAL 28 DAY) 
                                              AND DATE_ADD(CURDATE(), INTERVAL 10 DAY)
            ORDER BY comments.Time DESC";

    $result = $conn->query($sql);

    $num_rows = mysqli_num_rows ($result);

    $row = $result->fetch_assoc(); 

    $n_Rating[]= array(
                        'dp' => $row_friends['display_pics'],
                        'uname' => $row_friends['Uname'],
                        'poster'=> $row['mooster'],
                        'title'=> $row['moitle'],
                        'genre'=> $row['moenre'],
                        'comment'=> $row['Comment'],
                        'mid'=> $row['mID'],
                        'check' => 'data'
                    );
}

header('Content-Type: application/json');
echo json_encode ($n_Rating);

}

//detailed error reporting
if (!$sql) {
    echo 'MySQL Error: ' . mysqli_error($conn);
    exit;
}
?>
RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
chiboz
  • 73
  • 8
  • Sensible code indentation makes debugging code much easier for you – RiggsFolly Feb 07 '17 at 18:41
  • `if (!$sql) {` ?? `$sql` is a string literal containing a query. NOT a result from issuing a query. So your error reporting apart from being way too late in the process wont ever give you any help like reporting errors – RiggsFolly Feb 07 '17 at 18:44
  • You seem to have an unnecessary `}` – RiggsFolly Feb 07 '17 at 18:46
  • Hi RiggsFolly thanks for your suggestions, however can you please help me with my main issue? – chiboz Feb 07 '17 at 18:49
  • Add `ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` to the top of your script. This will force any `mysqli_` errors to generate an Exception that you cannot miss or ignore. – RiggsFolly Feb 07 '17 at 18:50
  • Thanks RiggyFolly, but this isn't my main issue :( – chiboz Feb 07 '17 at 18:51
  • If you add that code and run your script, you will see any MYSQLI errors, That woudl be a good start. If there are none, then we can try and continue – RiggsFolly Feb 07 '17 at 18:53
  • There are no errors, the script is outputting what has been coded, I just don't know how to code what I want. – chiboz Feb 07 '17 at 18:55

1 Answers1

1

You are only fetching one row with $row = $result->fetch_assoc();.

Are you trying to fetch all the comments for the users?

Because then you'll need to loop trough everything $result has just like you are doing with while ($row_friends = $results_get_friends->fetch_assoc())

Also instead of concatenating your SQL query, try using prepare and bind_param to avoid possible SQL injection

Community
  • 1
  • 1
jkrnak
  • 956
  • 6
  • 9
  • Silly me, all good now, thank you so much jkrnak. Also thanks for your suggestions on using prepare and bind_param, will look into it. – chiboz Feb 07 '17 at 20:37