Fetching data error relational tables

Question

I am trying to retrieve some data from my database where I have a join in place. I have 3 tables:

users column=user_id cats column=cat_id user_cat_join columns-user_is & cat_id

The user_cat_join table are related to to the corresponding tables.

I am trying to get the data out the table to only show the categories assigned to the user I am logged in as. So, I am getting the users ID from the session function and then trying to draw out the data I require.

require ('../db_con.php');

// BUILD AND DISPLAY THE CATEGORY LIST (RICOH BUILD)

function build_cat_list()
{

    global $dbc;

    // DEFINE THE QUERY:
    $user = $_SESSION['user'];
    $q = "SELECT cat_name, cat_icon, cat_color
                                FROM cats
                                INNER JOIN user_cat_join
                                ON cats.cat_id = user_cat_join.cat_id
                                WHERE user_cat_join.user_id = $user"; 
    $r = mysqli_query ($dbc, $q); // Run the query.


    // FETCH AND PRINT ALL THE RECORDS
    while ($row = mysqli_fetch_array($r)) {
    echo '

    <a href="view_cat.php?cat_id='.$row["cat_id"].'">
        <div class="indexBox"">
            <div class="indexBoxHeader"  style="background-color:'.$row["cat_color"].'"><p>'
                .$row["cat_icon"].'</p>
                </div>
            <div class="indexBoxFooter">
                <p>'.$row["cat_name"].'</p>
            </div>
        </div>
    </a>';

    }
}

when displaying this I get the following error?

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\MAMP\htdocs\home.php on line 33

The above is referencing this:

while ($row = mysqli_fetch_array($r)) {

I have done somre further error reporting and by doing this:

if($r === FALSE) { 
     printf(mysqli_error($dbc));
    exit();
}

I get this:

Unknown column '5ad3e66c5e1e7747707c480dca04fc85' in 'where clause'

I have no idea where that is referring to?

UPDATE

<?php
    error_reporting(E_ALL);
    ini_set('display_errors', '1');

    // BUILD AND DISPLAY THE CATEGORY LIST (RICOH BUILD)

    global $dbc;

    // DEFINE THE QUERY:
    $user = $_SESSION['user'];

    $q = sprintf("
        SELECT
            cat_name, cat_icon, cat_color
        FROM
            cats
        INNER JOIN
            user_cat_join
        ON
            cats.cat_id = user_cat_join.cat_id
        WHERE
            user_cat_join.user_id = '%s'
        ",
        mysqli_real_escape_string($dbc, $user)
        );


     $r = mysqli_query ($dbc, $q); // Run the query.

    if($r === FALSE) { 
        printf(mysqli_error($dbc));

    }  


    // FETCH AND PRINT ALL THE RECORDS
    while ($row = mysqli_fetch_array($r)) {

       var_dump($row);

    echo '
    <a href="view_cat.php?cat_id='.$row["cat_id"].'">
        <div class="indexBox"">
            <div class="indexBoxHeader"  style="background-color:'.$row["cat_color"].'"><p>'
                .$row["cat_icon"].'</p>
                </div>
            <div class="indexBoxFooter">
                <p>'.$row["cat_name"].'</p>
            </div>
        </div>
    </a>';

    }
?>

http://stackoverflow.com/questions/15439919/mysqli-fetch-array-expects-parameter-1-to-be-mysqli-result-boolean-given-in — Narendrasingh Sisodia, Jun 24 '15 at 13:46

score 0 · Answer 1 · answered Jun 24 '15 at 13:57

0

You have to quote your user variable:

$q = "SELECT cat_name, cat_icon, cat_color
                            FROM cats
                            INNER JOIN user_cat_join
                            ON cats.cat_id = user_cat_join.cat_id
                            WHERE user_cat_join.user_id = '$user'";

answered Jun 24 '15 at 13:57

Gunni

519
5
14

Hey I still get `Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\MAMP\htdocs\home.php on line 331 back at me – PhpDude Jun 24 '15 at 14:01
Is the error "Unknown column '5ad3e66c5e1e7747707c480dca04fc85' in 'where clause'" still the same? – Gunni Jun 24 '15 at 14:12
I am just getting a blank screen now with no errors and errors set to on. – PhpDude Jun 24 '15 at 14:21
Try to print the query now, so you can see how it looks like. Seems like the query now has the correct syntax, but does not have any resulting rows. – Gunni Jun 24 '15 at 14:27
Still getting nothing, I will post my code up in my question – PhpDude Jun 24 '15 at 14:34

score 0 · Accepted Answer · answered Jun 24 '15 at 13:59

Shouldn't your WHERE be like below, otherwise your current WHERE clause looks like WHERE user_cat_join.user_id = 5ad3e66c5e1e7747707c480dca04fc85; which your DB engine thinking is a separate column and so complaining about the same.

WHERE user_cat_join.user_id ='". $user. "'"

In short it should looks like

$q = "SELECT cat_name, cat_icon, cat_color
                            FROM cats
                            INNER JOIN user_cat_join
                            ON cats.cat_id = user_cat_join.cat_id
                            WHERE user_cat_join.user_id ='". $user. "'";

Fetching data error relational tables

2 Answers2