2. PHPRunner General questions
  3. Recordset row_count best approach
This topic is locked

Recordset row_count best approach

 		11/20/2023 10:51:39 AM
PHPRunner General questions
dageci authorDevClub member

Hello,
I'm new to PHP, was in other languages for 20 years.
Because we all are using the row_count in a lot of places in our code blocks, is this the best and clean approach, or are you using something different?

Thanks,
Davor

$employee_id = $values["employee_id"];
$job_id = $values["job_id"];

$checkQuery = DB::PrepareSQL("SELECT Count(*) As cnt FROM employee_jobs WHERE employee_id = ':1' AND job_id = ':2'", $employee_id, $job_id);

$rs = DB::Query($checkQuery);

$result = $rs->fetchAssoc();

// Access the count and use null coalescing operator to handle null case
$row_count = $result['cnt'] ?? 0;

if ($row_count > 0) {
$message = GetCustomLabel("THIS_VALUE_IS_ALREADY_PRESENT");
return false;
}else{
return true;
}
admin 11/20/2023

Just use DB::DBLookup function, much shorter syntax.

$row_count = DB::DBLookup("your SQL query here");
if ($row_count > 0) { ...
C
 		cristi 11/20/2023

Your question is not much about PHP as it is about SQL because you are using PHP just to retrieve and do something with the result while the rows count itself is done in SQL as it should be - it is not very smart (efficient) to count rows in PHP) so the real question here is if there is a more efficient way to do the SQL checkQuery I think...
IMHO using it like how you wrote it there it is the best way - some say that using SELECT Count (id) is faster others that Select Count (1) but in my opinion it is not a lot of difference just make sure that the columns used in the where part are indexed columns - this is the most efficent way.
dageci authorDevClub member 11/20/2023

I wanted to know if there is a way to get a number of returned rows from a recordset without executing 2 SQL quries, one for the record count and another for working with the returned data.

For example, when we have something like this "SELECT * FROM OrderDetails WHERE OrderID = 1"

Everywhere in the forum I'm finding solutions with 2 SQL, first with the COUNT.

In dovumentation there is a while

while( $data = $rs->fetchAssoc() )

But in a lot of situations we need to run one code when recordcount is 0 and another when recordcount > 0

C
 		cristi 11/21/2023

Having two queries with proper indexed columns after 'where' is actually faster but if you want another method here it is one using mysqli_num_rows - basically the same as the one posted by admin but using PHP functions:

$query="Select * from table";
$res=mysqli_query($query);

if(mysqli_num_rows($res)>0)
{
//etc
}
else
{
//etc
}

mysqli_num_rows returns the number of rows in your query.