PHP mysqli query to check if a row exist

Question

I have a mysql table, I want to check if a row exists where columnA=$a and $columnB=$b. I dont need to select anything from there. What should be efficient query for that? Currently Im doing like,

if ($stmt = mysqli->prepare("SELECT * FROM TABLE WHERE columnA=? && columnB= ? LIMIT 1")) {
    $stmt->bind_param("ss", $a, $b);
    $stmt->execute();
    $stmt->store_result();
    $count=$stmt->num_rows;
    $stmt->close();
    }
    return ($count > 0 ? true : false);

score 6 · Accepted Answer · edited Jan 03 '13 at 21:48

6

Try with this:

if ($stmt = $mysqli->prepare("SELECT COUNT(*) FROM TABLE WHERE columnA=? && columnB=?")) {
    $stmt->bind_param("ss", $a, $b);
    $stmt->execute();
    $stmt->bind_result($count);
    $stmt->fetch();
    $stmt->close();
}

return ($count > 0 ? true : false);

Now you should be able to get it done

edited Jan 03 '13 at 21:48

Joe Staines

301
1
3
11

answered Mar 11 '11 at 16:13

Andrés Morales

793
7
20

score 3 · Answer 2 · answered Mar 11 '11 at 16:03

3

That is efficient query if you want to improve that query you should select one column only to speed up the query.

if ($stmt = mysqli->prepare("SELECT id FROM TABLE WHERE columnA=? && columnB= ? LIMIT 1")) {

answered Mar 11 '11 at 16:03

Shakti Singh

84,385
21
134
153

score 2 · Answer 3 · answered Mar 11 '11 at 16:04

Select just one field from the row instead of all of them (*). Even though you're not using them, mysql still has to fetch them from storage and get them ready for transmission.

SELECT columnA FROM table WHERE ...

Otherwise there's not much else you can do to improve things, other than putting indexes on columnA and columnB to make the searching go faster.

PHP mysqli query to check if a row exist

3 Answers3

Linked