How do i retrieve columns from multiple tables specific to user session id?

Question

I am trying to select columns from 5 tables all from the same session id. However i am receiving this: Trying to get property 'num_rows' of non-object. Here's my code:

<?php
    include 'db.php';

    $sql = "SELECT users.username, 
                    bank.cash_on_hand, bank.bank, 
                    ranking.rank, ranking.level_points, 
                    _location.location, 
                    bullets.bullets, bullets.backfire 
                    FROM users, bank, bank, ranking, ranking, _location, bullets, bullets 
                    WHERE id = '".$_SESSION["id"]."' ";

    $result = $conn->prepare($sql);
        
    if($result-> num_rows > 0)
    {
        while ($row = $result-> fetch_assoc())
        {
            echo    "<table>".
                    "<tr>
                    <th>username |</th>
                    <th>cash on hand  |</th>
                    <th>bank &nbsp;&nbsp;&nbsp;&nbsp; |</th>
                    <th>rank |</th>
                    <th>level_points |</th>
                    <th>location &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</th>
                    <th>bullets &nbsp;&nbsp;&nbsp; |</th>
                    <th>backfire &nbsp;&nbsp;&nbsp;</th>
                    </tr>".

                    "<tr>".
                    "<td>". $row["username"]. "</td>".
                    "<td>". $row["cash_on_hand"]. "</td>".
                    "<td>". $row["bank"]. "</td>".
                    "<td>". $row["rank"]. "</td>".
                    "<td>". $row["level_points"]. "</td>".
                    "<td>". $row["_location"]. "</td>".
                    "<td>". $row["bullets"]. "</td>".
                    "<td>". $row["backfire"]. "</td>".
                    "</tr>".

                    "</tr>".
                    "</table>";
        }
    }
    else
    {
        echo "0 result";
    }
?>

And i receive this error: Trying to get property 'num_rows' of non-object in C:\wamp64\www\phil\player_info_bar.php on line 27

& line 27 is this: if($result-> num_rows > 0)

How do i resolve this?

Try to [join](https://learnsql.com/cookbook/how-to-join-multiple-3-plus-tables-in-one-statement/) tables. — Robin Singh, Feb 08 '21 at 08:42

score 1 · Answer 1 · answered Feb 08 '21 at 08:54

1

You have prepared the sql but you haven't executed it yet.

Try: $result->execute(); $res = $stmt->get_result(); $row = $res->fetch_assoc();

answered Feb 08 '21 at 08:54

Cinquième Dimension

29
4

score -1 · Answer 2 · answered Feb 08 '21 at 08:59

-1

You may have two options to execute your query

First: Replace $result = $conn->prepare($sql) with below

$stmt = $conn->prepare($sql);
$result = $stmt->execute();

Or Second: Replace $result = $conn->prepare($sql) with below

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

Note: Your query should also contain table names once per table.

answered Feb 08 '21 at 08:59

Hitesh

132
1
9

please see edit above. thanks – Phil Gibson Feb 08 '21 at 09:08
Your old query statement must work no need use join. Just use table name once per table with execute() or query() – Hitesh Feb 08 '21 at 09:18

score -3 · Accepted Answer · edited Feb 20 '21 at 13:57

-3

At first look, your query is wrong (at least for ambiguous column name id) so fail and don't return rows.

Assuming all your tables are related to the session by the column name id.

     "SELECT users.username, 
            bank.cash_on_hand, bank.bank, 
            ranking.rank, ranking.level_points, 
            _location.location, 
            bullets.bullets, bullets.backfire 
     FROM users
     INNER JOIN bank ON bank.id = users.id
     INNER JOIN ranking  ON ranking.id = users.id
     INNER JOIN _location  ON _location.id = users.id
     INNER JOIN bullets  ON bullets.id = users.id
     WHERE users.id = '".$_SESSION["id"]."' ";

Anyway you should not use improperly the same table in FROM clause because this just produce a Cartesian product and return the same row multiplied for the related occurrence in both tables

You should not use the old join syntax based on comma separated table' names and where condition you should use explicit join clause based on join declaration and ON clause.

And last (but not least) you should not use php var in where clause .. you are at risk for SQL injection. To avoid this you should take a look at prepared statement and binding param for MySQL driver.

And prepare don't execute query. Try this:

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

edited Feb 20 '21 at 13:57

halfer

19,824
17
99
186

answered Feb 08 '21 at 08:58

ScaisEdge

131,976
10
91
107

please see edit above. thanks – Phil Gibson Feb 08 '21 at 09:08
@phil answer updated . with a suggestion – ScaisEdge Feb 08 '21 at 09:14
so changing prepare to query and removing execute worked. Ty! This is how i have it: `$stmt = $conn->query($sql); if($stmt-> num_rows > 0) { while ($row = mysqli_fetch_array($stmt)) {` And its working fine. tyvm – Phil Gibson Feb 08 '21 at 09:24
.. .well ... but take a deep look at prepared statement .. binding param and exectute .. the risk for sqlijection is a is not to be underestimated – ScaisEdge Feb 08 '21 at 09:26
yes i recently added bind param to my other script and ive just now implemented it into this code. so i removed `$stmt = $conn->query($sql);` & added `$stmt = $conn->prepare($sql); $stmt->bind_param('i', $user_id); $user_id = (int)$_SESSION['id']; $stmt->execute(); $result = $stmt->get_result();` from my other script. & ofc changing this `WHERE users.id = '".$_SESSION["id"]."' ";` to `WHERE users.id = ? ";` Ty for your help. – Phil Gibson Feb 08 '21 at 09:35

How do i retrieve columns from multiple tables specific to user session id?

3 Answers3