Is there a way to combine mysqli queries or give them a sequence for optimization?

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.

https://stackoverflow.com/questions/1775168/multiple-select-statements-in-single-query/1775272#1775272 — Simon Larsen, Feb 11 '19 at 09:49

score 0 · Answer 1 · answered Feb 08 '19 at 12:40

0

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

answered Feb 08 '19 at 12:40

Dharmesh Vasani

475
2
4
19

That sounds interesting and i'm definitely gonna look into it, but from what i can see i was more after something like this. https://stackoverflow.com/a/1775272/9912983 To decrease the amount of calls needed. – Simon Larsen Feb 11 '19 at 09:34

Is there a way to combine mysqli queries or give them a sequence for optimization?

1 Answers1