Select Column Data from Database (Cant be converted to number?)

Question

I need to be able to compare values from a column, not the number of times a value appears in the columns. I have multiples tables that all have the same 20 column 'title' entries, but another column 'position_order' are different values of numbers. I have a base table with the 'correct' values, and I want to go through each name in the column 'title' and calculate the difference between their 'position_order' and my base case table's 'position_order'

I believe I have it all working but when I query the database to find what value it has stored for the column 'position_order' for some reason it won't return as a number that I can do calculations on.

<?php
    echo "
        <h3 class='text-center'>Table Name</h3>
        <table class='table table-bordered'>
            <tr>
                <th>#</th>
                <th>Title</th>
                <th>Score</th>
            </tr>
            <tbody class='row_position'>"?>
            <?php

            require('db_config.php');
            $tablename = $_SESSION['username'] . "_laliga";
            $_SESSION['tablename'] = $tablename;
            $sql = "SELECT * FROM $tablename ORDER BY position_order";
            $users = $mysqli->query($sql);
            while($user = $users->fetch_assoc()){
            $sql1 = "SELECT `position_order` FROM '".$tablename."' WHERE `title` LIKE '".$user['title']."' ";
            $sql2 = "SELECT `position_order` FROM `laliga` WHERE `title` LIKE '".$user['title']."' ";
            $position1 = $mysqli->query($sql1);

            $position2 = $mysqli->query($sql2);

            ?>


<tr  id="<?php echo $user['id'] ?>">
                <td><?php echo $user['position_order'] ?></td>
                <td><?php echo $user['title'] ?></td>
                <td><?php echo abs($position1-$position2); ?></td>
            </tr>
        <?php } ?>
        </tbody>
    </table>

Here is the error log

[16-May-2019 22:19:48 UTC] PHP Notice: Object of class mysqli_result could not be converted to number in /Applications/MAMP/htdocs/user.php on line 75

Can you post the table create scripts so we can see the column types. — ryantxr, May 16 '19 at 22:30
As per the [documentation](https://www.php.net/manual/en/mysqli.query.php), mysqli->query returns a [result object](https://www.php.net/manual/en/class.mysqli-result.php). This is just a pointer to the data. It does not represent any specific item of data. How could it? What if your query return 1000 rows with 20 columns? As any decent PHP/mysql tutorial will show you (if you're paying attention) you need to first [fetch a row](https://www.php.net/manual/en/mysqli-result.fetch-assoc.php) into an array, and then you can get the data from each column in that array. — ADyson, May 16 '19 at 22:36
P.S. You should get into the habit of googling your error messages. You would find plenty of existing solutions, including this one: [mysqli\_result could not be converted to int in](https://stackoverflow.com/questions/32970565/mysqli-result-could-not-be-converted-to-int-in) — ADyson, May 16 '19 at 22:37
Thanks you all so much for the help, Im disappointed I did not find this question posted before, but I can assure you I did a fair amount of googling around — Marc-9, May 16 '19 at 22:44

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

0

See the documentation for mysqli_query: https://www.php.net/manual/en/mysqli.query.php

Return Values

For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object. For other successful queries mysqli_query() will return TRUE.

$position1 and $position2 are object instances, not fields from the database. Haven't used mysqli in forever but you probably want fetch_all instead?

edited Jun 20 '20 at 09:12

Community

1
1

answered May 16 '19 at 22:33

prieber

554
3
16

Yup that seems to be the problem, currently trying to see how I can convert from the object to a number. @ADyson mentioned I need to follow the docs and put it into an array. Will update if I solve it thanks all for the help – Marc-9 May 16 '19 at 22:41
I find it helps to do a lot of `var_dump`-ing. It's possible you just access it like `$position1->position_order`. – prieber May 16 '19 at 22:43

ryantxr · Accepted Answer · 2019-05-16T23:14:42.447

0

This:

$result = $mysqli->query($sql1);
if ( ! $result ) {
   throw new Exception('Query 1 failed');
}

Returns a mysqli_result object, not a number. You must extract the actual results from it.

if ( $result->num_rows ) {
    $row = $result->fetch_assoc();
    $position1 = $row['position_order'];
}
$result->close();

$result = $mysqli->query($sql2);
if ( ! $result ) {
   throw new Exception('Query 2 failed');
}

if ( $result->num_rows ) {
    $row = $result->fetch_assoc();
    $position2 = $row['position_order'];
}
$result->close();
if ( isset($position1) && isset($position2) ) {
   $abs = abs($position1-$position2);
} else {
   $abs = null;
}

// output here

edited May 16 '19 at 23:14

answered May 16 '19 at 22:38

ryantxr

4,119
1
11
25

It seems to think $result is a boolean? [16-May-2019 22:50:59 UTC] PHP Fatal error: Uncaught Error: Call to a member function fetch_assoc() on bool in /Applications/MAMP/htdocs/user.php:67 Stack trace: #0 {main} thrown in /Applications/MAMP/htdocs/user.php on line 67 – Marc-9 May 16 '19 at 22:51
This is how I am instantiating it $result = $mysqli->query($sql1); – Marc-9 May 16 '19 at 22:53
If it fails then it returns false which is a boolean. https://www.php.net/manual/en/mysqli.query.php – ryantxr May 16 '19 at 23:00
Ah ok thanks, I must have an improper SQL string because it should always return a result – Marc-9 May 16 '19 at 23:42
Yup error in the SQL string, additionally I had to switch which database I was connected too. Got it working – Marc-9 May 17 '19 at 00:53

Select Column Data from Database (Cant be converted to number?)

2 Answers2