Cannot populate select using stored procedure in php

Question

require_once "Constants.php";
require_once "database.php";
require_once "fun_class.php";
$fun_class = new fun_class();
$database = new database();
$myConnection = $database->connect_database(SERVER,USER,PASSWORD,DATABASE);

I have this at the top of the page (header area- thats because I have session thing going on) and then I have a second php opening and closing tab at in the place where the filling is supposed to happen:

<select>
    <?php
    $fun_class->fillDropDownCategory($myConnection);
    ?>          
</select>

In my fun_class.php I have this function:

 function fillDropDownCategory($mysqli) {
        echo " 1 ";
        $display_block = "";

        //Call Stored Procedure 
        if ($result = $mysqli->query("call rb_selectCategory()")) {         
        echo " 2 ";
        //  If there are no contacts returned
            if ($result->num_rows < 1) {
            //no records
                echo " 3 ";
                $display_block .= "<p><em>Sorry, no records to select!</em>
                </p>";
            } else {   

                echo " 4 ";
            //  For each record returned populate the select list
                while ($recs = $result->fetch_object()) {
                    $id = $recs['category_id'];
                    $display_name = stripslashes($recs['name']);

                    $display_block .= "<option value=\'" . $id . "\'>" . 
                    $display_name . "</option>";
                }
            }
            //free result
            $result->free();            
            //Get the last id entered - needed for the other tables
            echo " 5 ";     
            //So you can run another stored proedure after a select
            $mysqli->next_result();
        }
        else
        {
        echo " 6 ";
        $display_block .= "<p><em>Sorry, no records to select!</em></p>";
    }
        echo " 7 ";
        return($display_block);
    }

When I enter the page the select is empty and when I enter the source code of the site I can see this:

           <select>
            1  6  7         
           </select>

Which is the output from my debugging echo'es

My stored procedure is:

BEGIN
SELECT category_id, name AS name

FROM xxx.category

ORDER BY name;

END

when executed (in the phpmyadmin) it returns two tables one called category_id with the id's and the second one is called name and it has category names that are assigned to the id's.

I am pretty new to php and there is probably something wrong with my function but because of the lack of experience I cannot find the mistake.

`$result = $mysqli->query("call rb_selectCategory()"); //Call Stored Procedure if ($result = $mysqli->query("call rb_selectCategory()")) {` you're doing this twice; it's not necessary and could be the root of the problem. — Funk Forty Niner, Jan 14 '18 at 03:05
@FunkFortyNiner right, that's my mistake I tried to take out the if and adding the `$result = ...` without it to see if that would work. Forgot to take it out thanks. — ptK, Jan 14 '18 at 03:07
add `mysqli_error($mysqli)` and see what it returns. Use php's error reporting also. — Funk Forty Niner, Jan 14 '18 at 03:08
@FunkFortyNiner `Fatal error: Cannot use object of type stdClass as array in C:\xampp\htdocs\xxx\fun_class.php on line 46`. Line 46 is the `$id = $recs['category_id'];` inside while loop. — ptK, Jan 14 '18 at 03:10
Having a stored procedure for something that just runs a query is some serious over-engineering. Just run the query. If that query is somehow significant then it's time to create a `VIEW` that encapsulates that. — tadman, Jan 14 '18 at 03:17
If you need to call `stripslashes` on your data, your data is broken and needs to be cleaned. — tadman, Jan 14 '18 at 03:17
A lot of problems can be detected and resolved by [enabling exceptions in `mysqli`](https://stackoverflow.com/questions/14578243/turning-query-errors-to-exceptions-in-mysqli) so mistakes aren't easily ignored. — tadman, Jan 14 '18 at 03:18
@tadman I have to use stored procedure. Thank you for that exceptions information, it's very useful! — ptK, Jan 14 '18 at 03:22
Ah, you have one of *those* DBAs running the project. Good luck! — tadman, Jan 14 '18 at 03:23

score 0 · Answer 1 · answered Jan 14 '18 at 03:16

0

I had

$id = $recs['category_id'];
$display_name = stripslashes($recs['name']);

changed it to

$id = $recs->category_id;
$display_name = stripslashes($recs->name);

Thanks to everyone for trying!

answered Jan 14 '18 at 03:16

ptK

19
4

@FunkFortyNiner maybe because this one doesn't contain part about echoing values? – DrKey Jan 14 '18 at 03:24

score -1 · Accepted Answer · answered Jan 14 '18 at 03:11

-1

Since you need to print those string in HTML, you need to use functions like echo. In your fillDropDownCategory() function you're echoing something (e.g. echo " 1 ";) but at the end you're returning a variable.

This variable is not going to be printed just because it gets returned, so you need to to print it just by doing

<select>
    <?php
    echo $fun_class->fillDropDownCategory($myConnection);
    ?>          
</select>

Of course you also need to remove your echo calls in fillDropDownCategory() to avoid print some unwanted strings in HTML.

answered Jan 14 '18 at 03:11

DrKey

3,365
2
29
46

Doing that makes the site load to ` – ptK Jan 14 '18 at 03:13
That happens because `mysqli_result::fetch_object()` actually returns an instance of `stdClass` so you need to fetch values in OOP way (e.g. `$recs->category_id`) or just use `mysqli_result::fetch_assoc()` to get result as associative array. – DrKey Jan 14 '18 at 03:17
Why downvote a well formed answer without leaving any comment? – DrKey Jan 14 '18 at 03:31

Cannot populate select using stored procedure in php

2 Answers2