Clever Geek Handbook

What's the best way to show default values ​​for empty fields returned from a database query?

Is there a better way to write this code?

I want to show a default value ("No data") for any empty fields returned by the query:

$archivalie_id = $_GET['archivalie_id'];

$query =    "SELECT 
                a.*, 
                ip.description AS internal_project,
                o.description AS origin,
                to_char(ad.origin_date,'YYYY') AS origin_date  

            FROM archivalie AS a 
            LEFT JOIN archivalie_dating AS ad ON a.id = ad.archivalie_id                
            LEFT JOIN internal_project AS ip ON a.internal_project_id = ip.id
            LEFT JOIN origin AS o ON a.origin_id = o.id               

            WHERE a.id = $archivalie_id";

$result = pg_query($db, $query);

while ($row = pg_fetch_object($result))
{
    $no_data = '<span class="no-data">No data</span>';

    $internal_project = ($row->internal_project != '') ? $row->internal_project : $no_data; 
    $incoming_date = ($row->incoming_date != '') ? $row->incoming_date : $no_data; 
    $origin = ($row->origin != '') ? $row->origin : $no_data; 
}
0


source to share


4 answers


You can use a little helper function



function dbValue($value, $default=null)
{
    if ($default===null) {
        $default='<span class="no-data">No data</span>';
    }
    if (!empty($value)) {
        return $value;
    } else {
        return $default;
    }
}
+3


source


If this is not just sample code, you will definitely want to misinform this request by writing ...

$archivalie_id = pg_escape_string($_GET['archivalie_id']);

or you want to convert $ archivalie_id with intval () if it's always always an integer.



Also, I suggest replacing "No data" with a constant of type "_MYPROJECT_NODATA", so you can easily change how your data label is displayed or implement internationalization.

Then you used

define('_MYPROJECT_NODATA', '<span class="no-data">No data</span>');
+1


source


One approach would be to select the default right on the database server.

SELECT 
  IFNULL(NULLIF(a.field1, ''), 'No data')       AS field1, 
  IFNULL(NULLIF(a.field2, ''), 'No data')       AS field2, 
  IFNULL(NULLIF(ip.description, ''), 'No data') AS internal_project,
  IFNULL(NULLIF(o.description, ''), 'No data')  AS origin,
  to_char(ad.origin_date,'YYYY') AS origin_date  
FROM 
  archivalie AS a 
  LEFT JOIN archivalie_dating AS ad ON a.id = ad.archivalie_id                
  LEFT JOIN internal_project  AS ip ON a.internal_project_id = ip.id
  LEFT JOIN origin            AS o  ON a.origin_id = o.id               
WHERE 
  a.id = $archivalie_id

This way you can output the values ​​right away and you don't have to touch existing code. IFNULL(NULLIF())

turns blank lines into NULL

and NULL

into 'No data'

. If you only want to leave blank lines, use only IFNULL()

.

Architecturally, this may not have some elegance (depending on how you look at it), but it is effective.

+1


source


You can use the standard SQL COALESCE function to return a special string instead of zero, for example:

$query =    "SELECT 
            a.*, 
            COALESCE(ip.description,'NO_DATA') AS internal_project,
            COALESCE(o.description,'NO_DATA') AS origin,
            COALESCE(to_char(ad.origin_date,'YYYY'),'NO_DATA') AS origin_date

Then you can replace "NO_DATA" with the appropriate HTML in your program as others have suggested.

0


source






More articles:


All Articles