OOP programming question

[Dennis_Gull]

Hello everyone,
I got a problem with my programming, lets take the following scenario:

I got some picture with a comment system, the picture information is located in one table and the comments is located in another table and each comment is connected to the picture through the id.

I now pull out the picture information with one function like this:

    function getPicture($cat) {
        $sql = "SELECT picture WHERE category = '$cat'";
        $result = mysqli_query($this->db,$sql);
        
        return mysqli_fetch_assoc($result);
    } 

And I now I want to select all the comments to each picture, before when I programmed "none OOP" i just did it like this:

$sql = "SELECT picture WHERE category = '$cat'";
$result = mysqli_query($this->db,$sql);
        
while($row = mysqli_fetch_assoc($result)) {
    $newSQL = "SELECT comments WHERE category = '$row['id']'";
    $newRESULT = mysqli_query($this->db,$newSQL);
    while($newROW = mysqli_fetch_assoc($newRESULT) {
        echo ....
    }
} 

But now I cant place an echo inside the while loop and I can only return one variable (right?).
Is there some way I can select the picture and the pictures comments with the same mysqli_query or something?

[Twey]

Well, you can, but it's bad style. What you probably want to do is return an array or, preferably, a result set, and then iterate over it.

If you're doing OO database work, I suggest going with a prebuilt ORM such as Propel.

[Dennis_Gull]

Well, you can, but it's bad style. What you probably want to do is return an array or, preferably, a result set, and then iterate over it.

If you're doing OO database work, I suggest going with a prebuilt ORM such as Propel.

What is a result set? The thing is I dont know how to store all the comments in one array value, is it possible to store an object or something in an array? How would you do this?

I feel like I need to learn this from the scratch so I dont think I want to use a prebuilt set. Im also taking over another guys work so most of the code is already written.

[Twey]

A result set is what you get back from mysql(i)_query() (or, technically, the resource to which that handle points). It has the advantage over an array that it is not necessary to store every result in memory at once: new rows are read in as you request them. This is vital for large tables, and good practice for reasons of scalability.

I feel like I need to learn this from the scratch so I dont think I want to use a prebuilt set.

It's not a prebuilt 'set', it's an object-oriented wrapper around database work. You should still be familiar with SQL, databases, and your specific database, but it is a convenience that makes working with databases in an OO environment a lot easier and will leave your code considerably neater. As an added benefit, you get a certain layer of abstraction and database independence for free.

[Dennis_Gull]

Lets say I'm storing all the values in the same result set, how would that code look like? I can't pull out the comments before I know what id I need to select and therefore I need to select the pictures before I can select the comments if I'm selecting pictures by category.

For this example I would probably just select the pictures and then in the foreach loop just select the comments, is that "bad" oop coding?

example:

$feeder = new getPictures();
foreach ($feeder->getLaterst("someCat") as $pic) {
	echo "some stuff";
	foreach($feeder->getComment($pic["id"]) as $comment) {
		echo $comment["someInfo"];
	}
}

[Twey]

No, that's OK so long as it's not inside a method. Looks as though it probably should involve breaking out of PHP parsing mode, though. Keep your code and output cleanly separated.

It may be fine OO coding, but it's bad database coding. It would be nicest to select the comments with a join in the original query, but that's tricky to do in an OO way without an ORM.

[Dennis_Gull]

I have never really understood why to use join in a sql query so I have never tried it, I followed a simple tutorial but im not quite sure if this is possible:

$sql = "SELECT pictures.name, pictures.id, comments.comment
FROM pictures LEFT JOIN comments ON
pictures.id = comments.id
WHERE pictures.category = '3'";

[boogyman]

as long as the comments.id is a foreign key (FK) that references pictures.id than yes that should be a correct query.

If the comments.id does not match the picture id, than you need to amend your query so that you are referencing the same picture.

When I am creating a FK that I will link between two tables, I make sure that the field name in the second table, is descriptive enough so i know what table and what field it belongs to. By having comments.id someone else other than you doesn't explicitly know that that is referencing the picture table and that its referencing the specific picture this comment is for.

[Dennis_Gull]

as long as the comments.id is a foreign key (FK) that references pictures.id than yes that should be a correct query.

If the comments.id does not match the picture id, than you need to amend your query so that you are referencing the same picture.

When I am creating a FK that I will link between two tables, I make sure that the field name in the second table, is descriptive enough so i know what table and what field it belongs to. By having comments.id someone else other than you doesn't explicitly know that that is referencing the picture table and that its referencing the specific picture this comment is for.

Thanks, I didn't know that foreign keys was required.
The comments.id was just an example and if im doing this for real I would assign something else like pid (picture id).