Select values from 3 tables

[elieobeid7]

I have a table called items, it has size_id and color_id as foreign keys.

the table color has id, and name. the table size has id and name.

I want to echo all items and $color->name Where items->color_id= $color->id And echo $size->name where $size->id = $items->size_id Something like that, I'm creating a table, I want to json encode them and send them to jquery datatables. I tried lazy load, didn't work, or I didn't know how to do it, do I have to do a join on both tables or would lazy load do the trick?

[elieobeid7]

And thank you for keep replying to people. It's the main reason I'm using your library honestly. Otherwise I would write queries the old way. I tried redbeanphp and left because of that. And even tried laravel voyager, when I saw a big number of open issues with no replies I left.

I promise I'll try to make this my last question. Selection is the trickiest part

[oscarotero]

Following the naming conventions of tables/fields, this should work:

//Select all items:
$items = $db->items->select()->run();

//Optionally, just for performance, you can preload all colors and sizes:
$items->color;
$items->size;

//Print the size/color names using the tableName->fieldName
foreach ($items as $item) {
    echo $item->color->name;
    echo $item->size->name;
}

//Echo all data as json:
echo $items;

//Or get all data as array:
$array = $items->toArray();

I hope this helps.

[elieobeid7]

Thank you, it's awesome! I wish at some point you extend this library to do validation because I'm relying on other libraries for that, and maybe to be an ORM too. Since doctrine relies on Symphony and I don't want that, Propel seems the same. It would be nice if this library could be a small ORM without the huge dependencies and learning curve.

Initially I started using redbeanphp, but you cannot set default values, nor timestamp, nor anything, it seems to guess and bad documentation and all that.

Btw last week I was hired to do a php script that embeds youtube videos into webpage. And now I see you developed a library for it and damn I could have saved time! And you also created gettext! I just noticed, I was going to use it 3 weeks ago. Didn't know you did it! You're awesome thanks a lot

[oscarotero]

In theory, this library allows to create a small ORM, just need to configure the classes you want to instantiate for each table. Here's more info: https://github.com/oscarotero/simple-crud#creating-your-own-tables It's not so powerful than a real orm, but it's not the scope of this library. Thanks for the compliment :)

[elieobeid7]

How to properly insert the foreign keys using the db above as example? Everything was working last week, not anymore, my latest tries

$color = $db->color->create([
    'name' => $_POST['color']
]);

$size = $db->size->create([
    'size' => $_POST['size']
]);

$product = $db->post->create([
    'title' => $title,
    'description' => $text,
    'price' => $price,
    'user_id' => $_SESSION['admin_id'],
    'product_id' => 'id',
    'quantity' => $qantity,
    'size_id' => $size->id,
    'color_id' => $color->id

]);

//Relate with the category
$product->relate($cat);
$product->relate($color);
$product->relate($size);

//Save all in the database
$product->save()

The error is

[Fatal error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'color_id' cannot be null in /opt/lampp/htdocs/web/emall/production/app/vendor/simple-crud/simple-crud/src/SimpleCrud.php:230 Stack trace: #0 /opt/lampp/htdocs/web/emall/production/app/vendor/simple-crud/simple-crud/src/SimpleCrud.php(230): PDOStatement->execute(Array) #1 /opt/lampp/htdocs/web/emall/production/app/vendor/simple-crud/simple-crud/src/Queries/Mysql/Insert.php(76): SimpleCrud\SimpleCrud->execute('INSERT INTO po...', Array) #2 /opt/lampp/htdocs/web/emall/production/app/vendor/simple-crud/simple-crud/src/Queries/Mysql/Insert.php(58): SimpleCrud\Queries\Mysql\Insert->__invoke() #3 /opt/lampp/htdocs/web/emall/production/app/vendor/simple-crud/simple-crud/src/Row.php(227): SimpleCrud\Queries\Mysql\Insert->run() #4 /opt/lampp/htdocs/web/emall/production/app/vendor/simple-crud/simple-crud/src/Row.php(273): SimpleCrud\Row->save() #5 /opt/lampp/htdocs/web/emall/production/app/vendor/simple-crud/simple-crud/src/Row.php(262 in /opt/lampp/htdocs/web/emall/production/app/vendor/simple-crud/simple-crud/src/SimpleCrud.php on line 230](url) ` I'm sure there's a simpler solution. In fact I closed it 24 days ago because I had no issues

[elieobeid7]

and how to delete as well, and I won't ever open an issue again

[oscarotero]

I see various things:

• $product->relate($color) modifies the color_id field, so you don't need to set this value in the array used on create the product. And the same for size and other related data.
• Note that $db->color->create() does not insert the value in the database, just create a new instance, so it does not have the id yet. The id is generated on insert the value in the database (using, for exampel $size->save()).
• $product->relate($color) executes automatically $color->save() if it does not have id.

[oscarotero]

and how to delete as well, and I won't ever open an issue again

Delete a row: $product->delete() Delete a relation: $product->unrelate($color)

[elieobeid7]

Now it worked like so

$db->size[] = [
    'size' => $size
];

$colorr = $db->color->select()->one()->where('color = :color', [':color' => $color])->run();
$sizee = $db->size->select()->one()->where('size = :size', [':size' => $size])->run();

$product = $db->post->create([
    'title' => $title,
    'description' => $text,
    'price' => $price,
    'user_id' => $_SESSION['admin_id'],
    'product_id' => 'id',
    'quantity' => $qantity,
    'color_id' =>$colorr->id,
    'size_id' => $sizee->id

]);

//Relate with the category
$product->relate($cat);
$product->relate($colorr); // if I remove this it won't work
$product->relate($sizee);  // if I remove this it won't work

//Save all in the database
$product->save();

Strangely $cat doesn't need to be inserted as you said, but $sizee and $colorr do, otherwise I get an error saying color_id can't be null

[oscarotero]

$cat may be related in different way, so the id needed is the product's id, so it needs to save the product before the other relations.

I'd do the following:

$color = $db->color->create(['color' => $colorValue])->save();
$size = $db->color->create(['size' => $sizeValue])->save();
$product = $db->post->create($data);

$product->relate($color);
$product->relate($size);
$product->relate($cat);

$product->save();

[oscarotero]

Anyway, a good improvement for this library is to allow to relate several things at once, so it can choose the appropiate order to avoid errors. Something like:

$product->relate($color, $size, $cat);

[elieobeid7]

I just tried and didn't work, they have to be added inside the insert arrray

    $post = $db->post->create([
        'title' => $title,
        'description' => $description,
        'price' => $price,
        'user_id' => $_SESSION['admin_id'],
        'quantity' => $quantity,
        'category_id' => $cat,
        'product_id' => $product->id,
        'size_id' => $size->id,
        'color_id'=> $color->id
    ]);

    $post->relate($color);
    $post->relate($size);
    $post->relate ($product);
    $post->save();

Otherwise it would give for example color_id cannot be null and so on. So unless I'm doing something wrong I guess there's no need for relate() in my case, after all I'm inserting it by hand.

all are foreign keys in the post table, relationship restrict

[oscarotero]

Ok, I see the problem. $post->relate($color) saves the post in the database, but if $size or $cat is not related, it will fail. Due it's not supported to execute many relations at the same time, it's needed to insert the relations by hand. I will fix this. Thanks.

[elieobeid7]

When you fix it let me know to update thanks a lot!

and can I do htmlspecialchars($db->user[data], ENT_QUOTES, 'UTF-8');

to prevent xss? or htmlpurifier? could you implement an option within the library to protect against xss so the user doesn't have to use a third option, just insert, would be awesome. That way we wouldn't have to worry about xss or sql injection.

[oscarotero]

This library is protected agains SQL injection, but XSS is out of the scope, you should use other library for this purpose.