SQL bind two tables into one SQL statement in a specific way

Question

I have two SQL tables with columns:

menu:

id [AI]
name
description

subcategories:

id [AI]
name
pid (subcategory parent id)
mid (menu id)

In the new version of my website there is no menu anymore and "subcategories" are now changed into "categories":

id
name
description
parent_id

The old database is still in use, so I'm making my migration script and here is the part with categories:

$new_db->query("TRUNCATE TABLE `categories`");
$ids = [];
$menu_list = $old_db->fetch("SELECT * FROM `menu` ORDER BY `id`");
foreach($menu_list as $menu)
{
    $id = $new_db->insert("categories", [
        "name" => $menu["name"],
        "description" => $menu["description"],
        "parent_id" => "0"
    ]);
    $ids[$menu["id"]] = $id;
    
}
$subcategories = $old_db->fetch("SELECT * FROM `subcategories` ORDER BY `id`");
foreach($subcategories as $subcategory)
{
    $pid = 0;
    $desc = "";
    if($subcategory["mid"] > 0)
    {
        $menu = $old_db->fetch_first("SELECT `id`, `description` FROM `menu` WHERE `id` = '".$subcategory["mid"]."' LIMIT 1");
        $pid = $ids[$menu["id"]];
        $desc = $menu["description"];
    }
    else
    {
        $pid = $subcategory["pid"];
    }
    $new_db->insert("categories", [
        "name" => $subcategory["name"],
        "description" => $desc,
        "parent_id" => $pid
    ]);
}

It works but I'm pretty sure it could be done better with lesser cost. Can I make a SQL statement which will bind menu and subcategories into one result list and then insert them all?

Yes. For further help, please see [Why should I provide an MCRE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Dec 26 '20 at 16:39
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Dec 26 '20 at 16:41
You can do a lot of things to improve your code, but I guess is better to use [SQL Stored Procedures](https://www.w3resource.com/mysql/mysql-procedure.php) or create your own ORM, or just use something like [Doctrine](https://www.doctrine-project.org/) — Milad, Dec 26 '20 at 16:56
Or just learn about joins because that's what your for loops imitate. — Shadow, Dec 26 '20 at 17:06

score 0 · Answer 1 · answered Dec 26 '20 at 20:10

0

I think you just want a JOIN?

SELECT * 
FROM `categories` c 
JOIN `subcategiries` s
ON s.pid = c.id

answered Dec 26 '20 at 20:10

delboy1978uk

12,118
2
21
39

SQL bind two tables into one SQL statement in a specific way

1 Answers1