CODEWITHSUNDEEP
phparraysmysqli
Stewart
Stewart

Reputation: 1

PHP array is not getting the data from mysqli query but the query works in PHPmyadmin

I am trying to show the result of my query but I am not getting any output from the php. I have tried several different loops but I cannot get anything to show on the page. The query works in PHPmyadmin and there are no errors showing when the webpage loads. This is the code. Let me know if you need any more information. I did print_r($data) and got this result :

mysqli_result Object ( [current_field] => 0 [field_count] => 8 [lengths] => [num_rows] => 2 [type] => 0 ) 2

$select = "SELECT u.`info_id`, u.`info`, u.`removed`, m.`date_added`, m.`title`, m.`summary`, m.`poster`, m.`imdb`, m.`cv` ".
            "FROM `user_movies` AS u ".
            "INNER JOIN `movies` AS m ".
            "USING (`movie_id`) ".
            "WHERE u.`user_id` = '{$_SESSION['user_id']}' ".
            "AND u.`info` = '0' AND u.`removed` = '0' ".
            "ORDER BY m.`date_added` DESC, m.`movie_id` ASC";
$data = mysqli_query($connect, $select) or die ('error getting user movies');
$movies = array();
while ($movie = mysqli_fetch_array($data));
    {       
    array_push($movies, $movie);
    }

foreach ($movies as $movie)
    {
    $id = str_replace(' ', '', $movie['title']);
    $poster = $movie['poster'];
    $title = $movie['title'];
    $summary = $movie['summary'];
    $imdb = $movie['imdb'];

    echo "<div class='movie' id='$id'><img src='$poster' alt='$title' />" .
    "<p><strong>$title</strong><br/>$summary<br/>" .
    "<a href='$imdb'title='www.imdb.com'>More info at IMDb</a></p></div>";
    }

Upvotes: 0

Views: 363

Answers (3)

Stewart
Stewart

Reputation: 1

The while loop had a semi colon at the end of it stopping the array_push from doing its job

while ($movie = mysqli_fetch_array($data));

should just be 

while ($movie = mysqli_fetch_array($data))

Upvotes: 0

TerryE
TerryE

Reputation: 10898

The SQL seems OK though in terms of style I prefer

            "WHERE u.user_id = '{$_SESSION['user_id']}' ".

My immediate Q is $_SESSION['user_id'] correctly set up because if not then the query would return zero rows.

Upvotes: 0

Don Briggs
Don Briggs

Reputation: 550

I have had a similar problem. I am presuming that you have already verified that your connection works, and that you have access to the database. I further presume that you have printed out the SELECT statement, and pasted it into PHPMyAdmin. I had done the same, and been driven crazy for a few hours.

PHPMyAdmin actually does some reformatting before it runs it. In particular, phpMyAdmin will put backticks ("`", the backwards single quote) around field names and/or table names. This is to specify that what is between the back-ticks is the name of a field of table. This is especially important if you use a reserved word as a field name. For example, if you had a field named "select", or "order", they would need back-ticks. In this particular instance, I suspect the field that you called "date". Put back-ticks around your field names and see what happens.

As a test, try removing that field from the query. Going forward, I would not use a field named "date". It is better to use something like "create_date", or "rental_date".

Good Luck.

Upvotes: 0

Related Questions