有没有办法结合mysqli查询或给他们一个优化序列？

Question

I wonder if it is possible to optimize my queries to avoid having to send ten requests, to check if three main tables and two composite tables has the needed elements and if not then insert them.

("Composite table" is what i call a table that holds two primary keys that both are a reference to the primary key in another table, cause i do not know the correct term)

So i have tried to see if i can get all the needed ids from the three main tables in one query, but i can not it it to work.

Here is some of the things i have tried.

select t1.a AS EID, t2.a AS CID, t3.a AS IID from (select ID as a from Events where event = "test3") as t1, (select ID as a from Coordinates where Coordinates = "10.22,14.15") as t2, (select ID as a from Intervals where Interval = "From 12:15 To 18:30") as t3;

SELECT ID AS eid FROM Events WHERE event = "test3" UNION ALL SELECT ID AS cid FROM Coordinates WHERE Coordinates = "10.22,14.15" UNION ALL SELECT ID AS iid FROM Intervals WHERE Interval = "From 12:15 To 18:30"

And this is what i am currently doing

if(!empty($this->event) && $this->event!== false){
        $sql = "SELECT ID FROM Events WHERE event = ?";
        $values = array($this->event);
    $db->setQuery($sql, $values);
    $db->singleFetch();

    if($db->getResults() == 1){
        $eid = $db->getFetch()[0]['ID'];
    }
    else{
        $sql = "INSERT INTO Events (event, Description) VALUES (?, ?)";
        $values = array($this->event, $this->description);
        $db->setQuery($sql, $values);
        $db->singleQuery();
        $eid = $db->getLast();
    }
}
//I am doing this for all three main tables,
//to get the ids needed for me to be able to do this.

if(isset($eid) && isset($cid)){
    $sql = "SELECT count(*) FROM EC WHERE EID = ? AND CID = ?";
    $values = array($eid, $cid);
    $db->setQuery($sql, $values);
    $db->singleFetch();
    if($db->getFetch()[0]['count(*)'] == 0){
        $sql = "INSERT INTO EC (EID, CID) VALUES (?, ?)";
        $values = array($eid, $cid);
        $db->setQuery($sql, $values);
        $db->singleQuery();
    }
}
//Which i then do for both composite tables

Either i get my fetch array with every possible combination or i do not even get my fetch array and other times i get it but with two of the same event id and nothing else.

Answer 1

You can write the stored procedure in MySQL for this and then you need to call that stored procedure