Can't access the mysqli_result Object of an sql query

Question

Hi I have a problem with this method and it's driving me crazy.The query below should return one field from the database, an id where the filename is matched. Only one value should be returned. When I run the query I get an SQL object returned which looks fine:

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

However I cannot access the above query object no matter what way I try or at least I'm getting no value out of it. I did the exact same to get the package id and it works perfectly.

I used $row = $result_package_id->fetch_array(MYSQLI_ASSOC); to get the package_id and I tried that for the module_id but it didn't work. Tried the mysqli_fetch_array and it doesn't work either. At a loss of what to do next can anyone help?

ADDED getPackageId method and if statement where the two methods are called. Every time a query is successful the id and package id are retrieved and a new object is created with the two values.

function getId($fileName){
    $con = connect();
    if (!$con) {
        trigger_error(mysqli_error($con), E_USER_ERROR);
        die('Could not connect: ' . mysqli_error($con));
    }
    $yModuleId = 0;
    $sql_filename = mysqli_real_escape_string($con, $fileName);
    $query_module_id = "SELECT id FROM y_module WHERE fileName='" . $sql_filename . "'";
    $result_module_id = mysqli_query($con, $query_module_id);
    while($row_model = mysqli_fetch_array($result_module_id)){
        $yModuleId = $row_model['id'];
        return $yModuleId;
    }
}


function getYPackageId($package_name){
    $con = connect();
    if (!$con) {

        trigger_error(mysqli_error($con), E_USER_ERROR);
        die('Could not connect: ' . mysqli_error($con));
    }

    $sql_packageName = mysqli_real_escape_string($con, $package_name);

    $query_package_id = "SELECT id FROM y_package WHERE name='" . $package_name . "'";
    $result_package_id = mysqli_query($con, $query_package_id)  or die("__LINE__ : " . mysqli_error($con));

    while($row_package = mysqli_fetch_array($result_package_id)){
        $yPackageId = $row_package['id'];
        print_r($yPackageId);
        print_r("</br>");
        print_r("</br>");
        return $yPackageId;
    };
}

if($result_model && $result_package && $result_model_package) {
        $yModuleId = getId($fileName);
        $yPackageId = getYPackageId($package_name);
        $yIdObject = new YIds($yModuleId, $yaPackageId);
        $yIdObjects [] = $yIdObject;
        mysqli_query($con, "COMMIT");
        $message = array("success", "[SUCCESS]", "Model published successfully.",$module_id);
}

Answer 1

You can use

while ($row = $result->fetch_assoc()) {
   $saved[] = $row;
}

but I think from your code displayed a more important issue is that you seem to be mixing procedural and object orientated SQL querying.

So:

• 1) Rewrite yourcode to use objects, your usage of mysqli_ functions only returns arrays.

• 2) or alternatively, use the current code as an array because that's what it is, not an object.

Procedural

function getId($fileName){
     //this does nothing. Unless this is a custom function?
     //$con = connect();
    // should be:
    $con = mysqli_connect(details,...);
    if (!$con) {
        trigger_error(mysqli_error($con), E_USER_ERROR); //?
        die('Could not connect: ' . mysqli_error($con));
    }
    //$yModuleId = 0; //unneeded.
    $sql_filename = mysqli_real_escape_string($con, $fileName);
    $query_module_id = "SELECT id FROM y_module WHERE fileName='" . $sql_filename . "'";
    //add an error feedback for debugging:
    $result_module_id = mysqli_query($con, $query_module_id) or die("__LINE__.":".mysqli_error($con));
    while($row_model = mysqli_fetch_array($result_module_id)){
        $yModuleId = $row_model['id'];
        return $yModuleId;
    }
}

Object Orientated:

 $query_module_id = "SELECT id FROM y_module WHERE fileName='?'";
$con = new mysqli($details,...);
$thisQuery = $con->prepare($query_module_id);
$thisQuery->bind_param("s",$sql_filename);
$thisQuery->execute();
 while ($row = $thisQuery->fetch_assoc()) {
       $saved[] = $row;
    }
$thisQuery->close();

From this the $saved variable will be an array of results.

---

Additional notes:

• You are using MySQL COMMIT near the bottom of your code and this is for transactions but you have not shown you've setup or begun any MySQL transactions.

• You have a return inside a while statement in getYPackageId which means that the while wil only ever run once because as soon as it reaches the return it will do just that. Bad format.

• Remove the semi-colon after the closing bracket of the while statement. This is bad syntax.

Answer 2

I figured out the problem and I feel like such an idiot! The problem was here

if($result_model && $result_package && $result_model_package) {
    $yModuleId = getId($fileName);
    $yPackageId = getYPackageId($package_name);
    $yIdObject = new YIds($yModuleId, $yaPackageId);
    $yIdObjects [] = $yIdObject;
    mysqli_query($con, "COMMIT");
    $message = array("success", "[SUCCESS]", "Model published successfully.",$module_id);
}

I was running the commit after I was trying to get the id and package_id from the database that's why I wasn't getting any results. I changed it to this:

if($result_model && $result_package && $result_model_package) {
     mysqli_query($con, "COMMIT");
     $yModuleId = getId($fileName);
    $yPackageId = getYPackageId($package_name);
    $yIdObject = new YIds($yModuleId, $yaPackageId);
    $yIdObjects [] = $yIdObject;

    $message = array("success", "[SUCCESS]", "Model published successfully.",$module_id);
}

It worked perfectly. This is how the getId() method looked when I got it working

function getId($moduleName, $moduleRevision){


       $con = connect();
       if (!$con) {
           trigger_error(mysqli_error($con), E_USER_ERROR);
           die('Could not connect: ' . mysqli_error($con));
       }
       $sql_moduleName = mysqli_real_escape_string($con, $moduleName);
       $sql_moduleRevision = mysqli_real_escape_string($con, $moduleRevision);

       $query_module_id = "SELECT id, module_name, module_revision FROM y_module";
       $result_module_id = mysqli_query($con, $query_module_id);

       while($row = mysqli_fetch_assoc($result_module_id)){
          if($row['module_name'] === $moduleName && $row['module_revision'] == $moduleRevision){
          return $row['id'];
          }
       }
}

Thanks to Martin for all the help and the advice much appreciated! Hope this can help someone by not making the same stupid mistake as me.