CODEWITHSUNDEEP
phpmysqloptimization
Jan Seda
Jan Seda

Reputation: 25

Making 5 mysql queries into 1

I have mysql table with columns id, pos and status.
And I need to swap pos value of exact id with the nearest smaller pos value. But I need to count only rows with status = 1.
In other words it should work like this:

$pos = mysql_result(mysql_query("select pos from foo where id = $id"),0);
$min = mysql_result(mysql_query("select min(pos) from foo where status = 1"),0);
if($pos != $min){
  $i = mysql_result(mysql_query("SELECT pos from foo where pos < $pos ORDER by pos DESC LIMIT 1"),0);
  mysql_query("update tbc_sidebar set pos = $i where id = $id");
  mysql_query("update tbc_sidebar set pos = pos + 1 where id <> $id AND pos = $i");
}

It works just fine like this, but I think it's not the best solution to have 5 queries because of one simple thing. Is there any way how to put it all into less queries? Thanks!

Upvotes: 0

Views: 129

Answers (3)

user1285324
user1285324

Reputation:

Store procedure is a best choice to execute multiple queries together.

Upvotes: 0

user1191247
user1191247

Reputation: 12998

I have not tried this but it should work. It is not pretty!

UPDATE tbc_sidebar main
INNER JOIN (
    SELECT t1.id id1, t1.pos pos1, t2.id id2, t2.pos pos2
    FROM tbc_sidebar t1
    INNER JOIN tbc_sidebar t2
        ON t1.pos > t2.pos
        AND t2.status = 1
    WHERE t1.id = $id
    ORDER BY t2.pos DESC
    LIMIT 1
) AS tmp
    ON main.id = tmp.id1 OR main.id = tmp.id2
SET main.pos = CASE
        WHEN main.id = tmp.id1 THEN tmp.pos2
        WHEN main.id = tmp.id2 THEN tmp.pos1
    END

Upvotes: 0

tereško
tereško

Reputation: 58444

There are two thing you can do: use a stored procedure [1] [2] or send multiple queries at once with transaction. It kinda depends on which side you want to keep it all.

Also , i would strongly recommend for you to drop the old mysql_* functions as a way of accessing MySQL database. They are more then 10 years old, not maintained anymore and the community has begun the process of deprecation. You should not write new code with mysql_* in 2012. Instead you should learn how to use PDO or MySQLi as the API for accessing DB.

Here are two tutorials you might look at:

Upvotes: 1

Related Questions