PHP - same value in mysqli bind param

Question

Can I optimize this code ?

mysqli_stmt_bind_param($statement, "iiiiiii", $user, $user, $user, $user, $user, $user, $user);

Used for this request :

$statement = mysqli_prepare($conn, "SELECT u_id, u_lastname, u_firstname, 
    (SELECT f_id from friend where (f_friend_one = u_id AND f_friend_two = ?) OR (f_friend_one = ? AND f_friend_two = u_id)),
    (SELECT f_type from friend where (f_friend_one = u_id AND f_friend_two = ?) OR (f_friend_one = ? AND f_friend_two = u_id))
FROM    user
WHERE   (( u_id = ( SELECT f_friend_one FROM friend WHERE f_friend_one = u_id AND f_friend_two = ? AND (f_type = 1 OR f_type = 4))) 
OR      ( u_id = ( SELECT f_friend_two FROM friend WHERE f_friend_one = ? AND f_friend_two = u_id AND (f_type = 1 OR f_type = 3))))
AND     u_id != ? ")
    or die(mysqli_error($conn));

Probably won't make a difference in run time, but I'm sure you could shorten the query, however, since it's not included... — Devon Bessemer, Oct 15 '15 at 22:40
The query could probably be simplified into a `JOIN` so you don't have to repeat all those queries. I'm trying to figure out what it's doing. — Barmar, Oct 15 '15 at 23:08
The query get the friend informations if they are friend : I have a friend table with three columns : id, f_friend_one (the user who send invitation), f_friend_two. — Okn, Oct 15 '15 at 23:15
It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Oct 06 '19 at 15:33

score 2 · Accepted Answer · answered Oct 15 '15 at 23:16

I think you can simplify it to this:

SELECT u_id, u_lastname, f_id, f_type
FROM user
JOIN friend
    ON (u_id = f_friend_one AND f_friend_two = ? AND f_type IN (1, 4))
        OR
       (u_id = f_friend_two AND f_friend_one = ? AND f_type IN (1, 3))
WHERE u_id != ?

It's possible that the WHERE clause at the end isn't needed, since someone can't send a friend request to themselves.

The OR may make this hard for MySQL to optimize, so it may be better to split it into two subqueries connected with UNION.

SELECT u_id, u_lastname, f_id, f_type
FROM user
JOIN friend ON u_id = f_friend_one
WHERE f_friend_two = ? AND f_type IN (1, 4))
    AND u_id != ?

UNION

SELECT u_id, u_lastname, f_id, f_type
FROM user
JOIN friend ON u_id = f_friend_two
WHERE f_friend_one = ? AND f_type IN (1, 3))
    AND u_id != ?

PHP - same value in mysqli bind param

1 Answers1