CODEWITHSUNDEEP
mysqljoincount
user11211072
user11211072

Reputation:

How count results from query

How to count results from this query using COUNT keyword?

SELECT COUNT (*) returns wrong result.

SELECT files.* FROM files
LEFT JOIN tags_files ON tags_files.id_file = files.id
LEFT JOIN tags ON tags.id = tags_files.id
WHERE tags.name LIKE '%search%' OR files.name LIKE '%search%'

Upvotes: 0

Views: 266

Answers (2)

VChiu
VChiu

Reputation: 131

You can use rowCount or num_rows depends on what you use, so it will only counts the rows that your query returns

example: 

<?php 
    //For PDO
    $sel = $dbh->prepare("SELECT files.* FROM files LEFT JOIN tags_files ON   tags_files.id_file = files.id LEFT JOIN tags ON tags.id = tags_files.id WHERE tags.name LIKE '%search%' OR files.name LIKE '%search%'");
    $sel->execute();
    $count = $sel->rowCount();
  
    //For mysqli
    $sel = "SELECT files.* FROM files LEFT JOIN tags_files ON   tags_files.id_file = files.id LEFT JOIN tags ON tags.id = tags_files.id WHERE tags.name LIKE '%search%' OR files.name LIKE '%search%'";
    $result = mysqli_query($con,$sel);
    $count = mysqli_num_rows($result);
?>

Upvotes: 0

Alireza A2F
Alireza A2F

Reputation: 519

Try this query if you want to count the results:

SELECT COUNT( files.id ) AS total_num FROM files
LEFT JOIN tags_files ON tags_files.id_file = files.id
LEFT JOIN tags ON tags.id = tags_files.id
WHERE tags.name LIKE '%search%' OR files.name LIKE '%search%'

Upvotes: 0

Related Questions