Only get some columns from prepared mysqli statement

[flex_php]

I've just started using prepared statements and my php has been limited to get what I need out of the database. All has gone well until I'm changing one of my joined statements that I'm looping through to create 2 datasets from the result (perhaps the way I'm doing this is from very old database habits?).

 

Anyway, a query such as:

 

    $query = "SELECT table1.field1, table1.field2, table1.field3, table2.field11, table2.field22 FROM table1 INNER JOIN table2 ON table1.this = table2.that WHERE table1.field1 = ?"

 

Preparing the statement such as:

   

 

    $stmt = mysqli_prepare($this->connection, $query);

        mysqli_bind_param($stmt, 'i', $var1);

        mysqli_stmt_execute($stmt);

        $rows = Array();

        $row = new MyObject();

        mysqli_bind_result($stmt, $row->field1, $row->field2...all the fields listed in the query....);

        while (mysqli_stmt_fetch($stmt)) {

        $rows[] = $row;

        $row = new MyObject();

        mysqli_bind_result($stmt, $row->field1, $row->field2...all the fields listed in the query....);

        }

 

Now what I'd like to do is grab selective fields from the result.

Something like

 

    if (condition){

   

    $row = new MyObject1();

    $row->field1 = $stmt->field1;

   

    }else{

   

    $row = new MyObject2();

    $row->field11 = $stmt->field11;

   

    }

 

Does this make sense and how would I go about getting only the fields I want from the result?

[fenway]

You'll need column aliases to ensure that your field names don't collide.

[flex_php]

Thanks for the response. I don't have a problem with duplicate column names, what I'm looking to do is take selective fields from the result (The result comes back just fine). I'm ordering the result by a field ID -- I want to watch for this ID to change, and grab selective fields when this happens.

[fenway]

There's no concept of "ID changing" in mysql.

 

Ask for all of the fields, if you want to show the output selectively, that's up to you.

[flex_php]

Yes, I understand the "ID changing" isn't directly related to mysql. It's a quiz, questions & choices. Before I was using prepared statements I would use my query and be able to sort out the questions from the choices with the result object. Here I suppose I can go through the result, again, after the bind_result using all the fields in the parameters. It seems to do it 'my old way' with the prepared statement functions isn't possible, or ideal even before for all I know to be honest. I'm trying to figure out any 'best practices' as the last time I worked with a database with knowledge was with cobol.

[fenway]

The "old way" was a hack, which as you can't see, wasn't flexible.

[flex_php]

So is it more appropriate to execute a different query for each question within the first mysqli_stmt_fetch? Just having all those queries within a loop seems like it would be a bad idea but I'm probably wrong.

[fenway]

What loop? Get back all of the fields, all of the time.

[flex_php]

Thanks for the answers. Then am I better off breaking them off into my 2 different objects server or client side? Sorry if what I'm trying to do isn't clear. I could get all the records (questions & choices) and break them apart -- Or I could execute a query for each question to get the choices.

[fenway]

I don't know anything about your objects -- all I know is that you need to retrieve all of your records, and all of the fields, in one query -- how you process them is really up to you.