Display results in the webpage that are not yet in records list in the database using PHP

Question

I have this query that displays all the result in a webpage. My concern is how will I be able to query something that does not exist in the database? For example it will display "no records Yet", if there is no data in the database. Here's my code below.

<?php
           require("script/connect.php");
           $sql = "SELECT
                    COUNT(*)as num
                    FROM
                    users_tbl ";
           $res12 = mysql_query($sql);

              $id = $_GET['id'];
              $sql = "SELECT 
                      teachers_tbl.id,
                      teachers_tbl.teachers_name,
                      users_tbl.teachers_id,
                      users_tbl.A1,
                      users_tbl.A2,
                      users_tbl.A3,
                      users_tbl.A4,
                      users_tbl.A5,
                      users_tbl.A6,
                      users_tbl.A7,
                      users_tbl.A8,
                      users_tbl.A9,
                      users_tbl.A10,
                      users_tbl.B1,
                      users_tbl.B2,
                      users_tbl.B3,
                      users_tbl.B4,
                      users_tbl.B5,
                      users_tbl.B6,
                      users_tbl.B7,
                      users_tbl.B8,
                      users_tbl.B9,
                      users_tbl.B10,
                      users_tbl.C1,
                      users_tbl.C2,
                      users_tbl.C3,
                      users_tbl.C4,
                      users_tbl.comments
                      FROM
                      teachers_tbl
                      INNER JOIN users_tbl ON users_tbl.teachers_id = teachers_tbl.id 
                      WHERE teachers_tbl.id = '$id'";
              $res = mysql_query($sql);
              while($row = mysql_fetch_array($res)){
                if(mysql_num_rows(mysql_query($sql)) != NULL){
                  echo "<p style='text-align:center; color:red;'>NO RECORDS YET.</p>";
                }else{
                  echo "RESULT PAGE FOR  - ".$row['teachers_name']."";
                  include("script/result.php");
                }
              }

          ?>

I should warn you that this code is susceptible to SQL injection attacks. Here is a link to another SO question about SQL injection and PHP. http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Will, Feb 08 '14 at 16:04

Krish R · Answer 1 · 2014-02-08T15:51:23.077

0

Rewrite your php code as, mysql_num_rows() — Get number of rows in result

if(mysql_num_rows($res)==0){
    echo "<p style='text-align:center; color:red;'>NO RECORDS YET.</p>";
}else{
    while($row = mysql_fetch_array($res)){          
            echo "RESULT PAGE FOR  - ".$row['teachers_name']."";                    
    }
 }

Reason for, While loop only execute if query has data, moving out the mysql_num_rows from inside the while loop and you can check the condition.

NOTE: use mysqli_* or PDO functions instead of using mysql_* functions(deprecated)

edited Feb 08 '14 at 15:51

answered Feb 08 '14 at 15:41

Krish R

22,583
7
50
59

hmm wont work my records in the tables is set default to NULL – user3287511 Feb 08 '14 at 15:48
`mysql_num_rows()` — Get number of rows in result – Krish R Feb 08 '14 at 15:51
ahh yes but ahm i have one field that is a teachers_id then after that fields are default to NULL – user3287511 Feb 08 '14 at 15:53
but why it wont execute the no records yet? – user3287511 Feb 08 '14 at 15:58
`$res = mysql_query($sql) or die(mysql_error());` use this line and find the error. – Krish R Feb 08 '14 at 16:00
no errors found.. ahm i think your code is correct but ahm only it will not execute to the no records yet.. – user3287511 Feb 08 '14 at 16:07
copy your query and run in mysql query window and find whether it has row or not – Krish R Feb 08 '14 at 16:09
is that possible that if number of rows is NULL then there is a not null ?? that query works? – user3287511 Feb 08 '14 at 16:19
mysql_num_rows always returns `int` – Krish R Feb 08 '14 at 16:20
hmm i dont know why it wont work it wont execute on the no records found ,, for sure no errors in it – user3287511 Feb 08 '14 at 16:23
did you run the query in your query window and checked the rows count – Krish R Feb 08 '14 at 16:25

O. Jones · Answer 2 · 2014-02-08T16:13:58.133

When I need to do this, I use a code pattern like so:

$res = mysql_query($sql);
$rowcount = 0;
while($row = mysql_fetch_array($res)){
    if ( /* some condition checking whether the present row is valid */) {
        $rowcount++;
        echo "RESULT PAGE FOR  - ".$row['teachers_name']."";
        include("script/result.php");
    } 
}
if ( 0 == $rowcount ) {
    echo "<p style='text-align:center; color:red;'>NO RECORDS YET.</p>";
}

This is for three reasons. First, a valid result set can be an empty result set (no rows). Second Second, it looks like you will have rows in your result set which are somehow incomplete, and you need to check for that condition using PHP. Finally, mysql_num_rows() and related row count functions don't always work on all systems, especially before fetching the first row of the result set; I've developed the habit of distrusting them and just counting the rows myself.

(I'll let somebody else pester you about converting to mysqli or pdo.)

*"I'll let somebody else pester you about converting to mysqli or pdo."* - Krish already did, and Will. ;-) — Funk Forty Niner, Feb 08 '14 at 16:13
I edited this after your comment to @KrishR that "i have one field that is a teachers_id then after that fields are default to NULL" — O. Jones, Feb 08 '14 at 16:14

Display results in the webpage that are not yet in records list in the database using PHP

2 Answers2