mysql null values

garevn · May 11, 2011

hello i want to have the following querrylets say i have this tablename /color/ price/ manufactureraaa/ ppp / 10 / NULLbbb / ppp / 10 / NULLccc / ppp/ 10 / NULLi want to display all products so to start with SELECT * FROM PRODUCTSand i dont get displayed the fields where values are nullso running that querry i get name /color / price aaa /ppp / 10bbb / ppp / 10ccc /ppp / 10Is there a way to do that? i cant find something useful so far.. tnx

birbal · May 11, 2011

Where are you runinng the query? phpmyadmin or any php script?

garevn · May 11, 2011

Where are you runinng the query? phpmyadmin or any php script?

On a php script.I want a to have one php page for multiple products depending on user options. thats why i need something like that

birbal · May 11, 2011

all the data actualy is coming into your script when you fetch the data and if it stored the data in variable as null ,it will not print anything if you echo it out. If you var_dump() your data you may notice that particula field is also there and it holds the value NLL. you have to check it that particular column is null or not if its null do something ..else do something other. You can use is_null() to determine it.

garevn · May 11, 2011

As i understand using isnull is like give our own values to the null ones. sorry but I dont really underdand what u saying. I just want to hide the fields with the null values

birbal · May 11, 2011

You want to show the null valuse like NULL right? then you need to do something like

while($row=mysql_fetch..........){if(is_null($row['somecolumn']))$row['somecolumn']='NULL';}

is_null() will return true if the given variable value is NULL. More details on it can be found on the above link

garevn · May 11, 2011

No i want to hide all fields which values are nulls.Like the example i gave on my 1st post which hide the manufacturer field

birbal · May 11, 2011

It will be still the same technuique.You need to just replace

$row['somecolumn']='NULL';

to

$row['somecolumn']='';

thescientist · May 11, 2011

either way you can use is_null to test for the value, and then either set the value to an empty string birbal showed, or do something else. The first step is testing the value, after that you can do whatever you need.

garevn · May 11, 2011

<?php require_once('Connections/Mysitedb.php'); ?><?phpif (!function_exists("GetSQLValueString")) {function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") {  if (PHP_VERSION < 6) {    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;  }  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);  switch ($theType) {    case "text":      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";      break;        case "long":    case "int":      $theValue = ($theValue != "") ? intval($theValue) : "NULL";      break;    case "double":      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";      break;    case "date":      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";      break;    case "defined":      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;      break;  }  return $theValue;}}mysql_select_db($database_Mysitedb, $Mysitedb);$query_Recordset1 = "select * from products";$Recordset1 = mysql_query($query_Recordset1, $Mysitedb) or die(mysql_error());$row_Recordset1 = mysql_fetch_assoc($Recordset1);$totalRows_Recordset1 = mysql_num_rows($Recordset1);?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>Untitled Document</title></head><body></body></html><?phpmysql_free_result($Recordset1);?>

Using is_null function i think i just replace the null value with an empty value. what i want is to complete remove all collumns which have values equal null. (Like the example on my 1st post which completly remove the maufacturer collumn)Is that possible be done using is_null?Lets say i have this php file atm how should i modified it.

birbal · May 12, 2011

Your code has some problems. If you want to show all the returned fields then you have to loop through the resultset.At first i think you may want to take look at http://w3schools.com/php/php_mysql_select.asp this.At First make sure that all fields are coming as you expected.

Using is_null function i think i just replace the null value with an empty value.

No, as already we told above it will check given value is null or not. the link in post 4 holds more info

garevn · May 12, 2011

ok i added this one to check if color which values are null will display or not but it was still in my results.

<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));{if(is_null($row_Recordset1['color']))$row_Recordset1['color']='';}?>

my full code which displays all my fields at the moment is below

<?php require_once('Connections/Mysitedb.php'); ?><?phpif (!function_exists("GetSQLValueString")) {function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") {  if (PHP_VERSION < 6) {    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;  }  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);  switch ($theType) {    case "text":      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";      break;        case "long":    case "int":      $theValue = ($theValue != "") ? intval($theValue) : "NULL";      break;    case "double":      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";      break;    case "date":      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";      break;    case "defined":      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;      break;  }  return $theValue;}}$maxRows_Recordset1 = 10;$pageNum_Recordset1 = 0;if (isset($_GET['pageNum_Recordset1'])) {  $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];}$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;mysql_select_db($database_Mysitedb, $Mysitedb);$query_Recordset1 = "SELECT * FROM products WHERE charid=9";$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);$Recordset1 = mysql_query($query_limit_Recordset1, $Mysitedb) or die(mysql_error());$row_Recordset1 = mysql_fetch_assoc($Recordset1);if (isset($_GET['totalRows_Recordset1'])) {  $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];} else {  $all_Recordset1 = mysql_query($query_Recordset1);  $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);}$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>Untitled Document</title></head><body><table border="1" cellpadding="1" cellspacing="1">  <tr>    <td>productid</td>    <td>image</td>    <td>cores</td>    <td>Cache</td>    <td>Lithography</td>    <td>Integrated Graphics</td>    <td>manufacturer</td>    <td>sochet</td>    <td>productname</td>    <td>charid</td>    <td>price</td>    <td>Corespeed</td>    <td>USB 3.0</td>    <td>Memory size</td>    <td>Memory slots</td>    <td>Color</td>    <td>Watts</td>    <td>EnergyEff</td>    <td>Discription</td>    <td>chipset</td>    <td>Connection</td>    <td>channel</td>    <td>Memory speed</td>    <td>scantype</td>    <td>usage</td>    <td>inches</td>    <td>resolution</td>    <td>contrast</td>  </tr>  <?php do { ?>    <tr>      <td><?php echo $row_Recordset1['productid']; ?></td>      <td><?php echo $row_Recordset1['image']; ?></td>      <td><?php echo $row_Recordset1['cores']; ?></td>      <td><?php echo $row_Recordset1['Cache']; ?></td>      <td><?php echo $row_Recordset1['Lithography']; ?></td>      <td><?php echo $row_Recordset1['Integrated Graphics']; ?></td>      <td><?php echo $row_Recordset1['manufacturer']; ?></td>      <td><?php echo $row_Recordset1['sochet']; ?></td>      <td><?php echo $row_Recordset1['productname']; ?></td>      <td><?php echo $row_Recordset1['charid']; ?></td>      <td><?php echo $row_Recordset1['price']; ?></td>      <td><?php echo $row_Recordset1['Corespeed']; ?></td>      <td><?php echo $row_Recordset1['USB 3.0']; ?></td>      <td><?php echo $row_Recordset1['Memory size']; ?></td>      <td><?php echo $row_Recordset1['Memory slots']; ?></td>      <td><?php echo $row_Recordset1['Color']; ?></td>      <td><?php echo $row_Recordset1['Watts']; ?></td>      <td><?php echo $row_Recordset1['EnergyEff']; ?></td>      <td><?php echo $row_Recordset1['Discription']; ?></td>      <td><?php echo $row_Recordset1['chipset']; ?></td>      <td><?php echo $row_Recordset1['Connection']; ?></td>      <td><?php echo $row_Recordset1['channel']; ?></td>      <td><?php echo $row_Recordset1['Memory speed']; ?></td>      <td><?php echo $row_Recordset1['scantype']; ?></td>      <td><?php echo $row_Recordset1['usage']; ?></td>      <td><?php echo $row_Recordset1['inches']; ?></td>      <td><?php echo $row_Recordset1['resolution']; ?></td>      <td><?php echo $row_Recordset1['contrast']; ?></td>    </tr>    <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));{if(is_null($row_Recordset1['color']))$row_Recordset1['color']='';}?></table></body></html><?phpmysql_free_result($Recordset1);?>

In my query where i have charid=9 the columns that contain not null values are like 8-9 so i want to hide all others with the null one values

birbal · May 13, 2011

move the condition checking of the $row_Recordset1['color'] inside the loop. if you want to hide the whole column invisible then make that column declaration (eg. <td>Color</td>) too conditional if it is null then show it.

garevn · May 13, 2011

hello.Sorry i am not really experienced and i dont really get what i have to do.atm i add one more is null for resolutionand i moved them ..

$query_Recordset1 = "SELECT * FROM products WHERE charid=9";    {if(is_null($row_Recordset1['color']))$row_Recordset1['color']='';}    {if(is_null($row_Recordset1['resolution']))$row_Recordset1['resolution']='';}

what i have to do with <td>resolution</td><td>color</td>u mean something like ?

'</TD><TD>';            if (is_null($row_Recordset1["color"]))              echo ' ';         else              echo $row_Recordset1["color"];            echo '</TD></TR>';

i hope not all false

birbal · May 13, 2011

there could be two possible way.one your query can return multiple rows where some column of a row can be null some of them cant be null.in this case there will not make sense to hide the column name cause its possible that that column can hold value for other row. there you can only format the column data where it value is null.another is.. it can only return one row. where you can format the column name to be visible or invisible depending on its column data. if this the case ( i guess it is the your case) then you can do something like...

while($row_Recordset=mysql_fetch.....){?><table border="1" cellpadding="1" cellspacing="1"><tr><td>productid</td><td>cores</td><?phpif(!is_null($row_Recordset1['color'])){?><td>Color</td><?php}?><td>inches</td><td>resolution</td><td>contrast</td></tr><tr><td><?php echo $row_Recordset1['productid']; ?></td><td><?php echo $row_Recordset1['cores']; ?></td> <?phpif(!is_null($row_Recordset1['color'])){?> <td>   <?php	 echo $row_Recordset1['color']	?> </td><?php}?><td><?php echo $row_Recordset1['inches']; ?></td><td><?php echo $row_Recordset1['resolution']; ?></td><td><?php echo $row_Recordset1['contrast']; ?></td></tr>

it will be same as for all filed where you need to this kind of formatting

garevn · May 13, 2011

ok its seems as an easy way to follow those steps for the fields i need.at the moment i get no errors but my result is "no database selectet"...my current code

<?php require_once('Connections/Mysitedb.php'); ?><?phpif (!function_exists("GetSQLValueString")) {function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") {  if (PHP_VERSION < 6) {    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;  }  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);  switch ($theType) {    case "text":      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";      break;        case "long":    case "int":      $theValue = ($theValue != "") ? intval($theValue) : "NULL";      break;    case "double":      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";      break;    case "date":      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";      break;    case "defined":      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;      break;  }  return $theValue;}}$maxRows_Recordset1 = 10;$pageNum_Recordset1 = 0;if (isset($_GET['pageNum_Recordset1'])) {  $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];}$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;$query_Recordset1 = "SELECT * FROM products WHERE charid=9";$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);$Recordset1 = mysql_query($query_limit_Recordset1, $Mysitedb) or die(mysql_error());$row_Recordset1 = mysql_fetch_assoc($Recordset1);if (isset($_GET['totalRows_Recordset1'])) {  $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];} else {  $all_Recordset1 = mysql_query($query_Recordset1);  $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);}$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>Untitled Document</title></head><body><table border="1" cellpadding="1" cellspacing="1">  <tr>    <td>productid</td>    <td>image</td>    <td>cores</td>    <td>Cache</td>    <td>Lithography</td>    <td>Integrated Graphics</td>    <td>manufacturer</td>    <td>sochet</td>    <td>productname</td>    <td>charid</td>    <td>price</td>    <td>Corespeed</td>    <td>USB 3.0</td>    <td>Memory size</td>    <td>Memory slots</td>    <?phpif(!is_null($row_Recordset1['color'])){?><td>Color</td><?php}?>    <td>Watts</td>    <td>EnergyEff</td>    <td>Discription</td>    <td>chipset</td>    <td>Connection</td>    <td>channel</td>    <td>Memory speed</td>    <td>scantype</td>    <td>usage</td>    <td>inches</td>    <td>resolution</td>    <td>contrast</td>  </tr>  <?php do { ?>    <tr>      <td><?php echo $row_Recordset1['productid']; ?></td>      <td><?php echo $row_Recordset1['image']; ?></td>      <td><?php echo $row_Recordset1['cores']; ?></td>      <td><?php echo $row_Recordset1['Cache']; ?></td>      <td><?php echo $row_Recordset1['Lithography']; ?></td>      <td><?php echo $row_Recordset1['Integrated Graphics']; ?></td>      <td><?php echo $row_Recordset1['manufacturer']; ?></td>      <td><?php echo $row_Recordset1['sochet']; ?></td>      <td><?php echo $row_Recordset1['productname']; ?></td>      <td><?php echo $row_Recordset1['charid']; ?></td>      <td><?php echo $row_Recordset1['price']; ?></td>      <td><?php echo $row_Recordset1['Corespeed']; ?></td>      <td><?php echo $row_Recordset1['USB 3.0']; ?></td>      <td><?php echo $row_Recordset1['Memory size']; ?></td>      <td><?php echo $row_Recordset1['Memory slots']; ?></td><?phpif(!is_null($row_Recordset1['color'])){?> <td>   <?php     echo $row_Recordset1['color']    ?> </td><?php}?>      <td><?php echo $row_Recordset1['Watts']; ?></td>      <td><?php echo $row_Recordset1['EnergyEff']; ?></td>      <td><?php echo $row_Recordset1['Discription']; ?></td>      <td><?php echo $row_Recordset1['chipset']; ?></td>      <td><?php echo $row_Recordset1['Connection']; ?></td>      <td><?php echo $row_Recordset1['channel']; ?></td>      <td><?php echo $row_Recordset1['Memory speed']; ?></td>      <td><?php echo $row_Recordset1['scantype']; ?></td>      <td><?php echo $row_Recordset1['usage']; ?></td>      <td><?php echo $row_Recordset1['inches']; ?></td>      <td><?php echo $row_Recordset1['resolution']; ?></td>      <td><?php echo $row_Recordset1['contrast']; ?></td>    </tr>    <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));?></table></body></html><?phpmysql_free_result($Recordset1);?>

birbal · May 13, 2011

check in for connections/mysitedb.phpthere should be some function like mysql_select_db() where you need to specify the correct database name. Probably its not finding any and returning false.

garevn · May 13, 2011

Oh yes i recreate the page.now i get the following error Notice: Undefined index: color in C:\wamp\www\MySite\checknull.phpon the lines i have added the code.

  <?phpif(!is_null($row_Recordset1['color'])){?><td>Color</td><?php}?>

and

<?phpif(!is_null($row_Recordset1['color'])){?> <td>   <?php     echo $row_Recordset1['color']    ?> </td><?php}?>

my current code is

<?php require_once('Connections/Mysitedb.php'); ?><?phpif (!function_exists("GetSQLValueString")) {function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") {  if (PHP_VERSION < 6) {    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;  }  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);  switch ($theType) {    case "text":      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";      break;        case "long":    case "int":      $theValue = ($theValue != "") ? intval($theValue) : "NULL";      break;    case "double":      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";      break;    case "date":      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";      break;    case "defined":      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;      break;  }  return $theValue;}}$maxRows_Recordset1 = 10;$pageNum_Recordset1 = 0;if (isset($_GET['pageNum_Recordset1'])) {  $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];}$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;mysql_select_db($database_Mysitedb, $Mysitedb);$query_Recordset1 = "select * from products where charid=9";$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);$Recordset1 = mysql_query($query_limit_Recordset1, $Mysitedb) or die(mysql_error());$row_Recordset1 = mysql_fetch_assoc($Recordset1);if (isset($_GET['totalRows_Recordset1'])) {  $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];} else {  $all_Recordset1 = mysql_query($query_Recordset1);  $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);}$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>Untitled Document</title></head><body><table border="1" cellpadding="1" cellspacing="1">  <tr>    <td>productid</td>    <td>image</td>    <td>cores</td>    <td>Cache</td>    <td>Lithography</td>    <td>Integrated Graphics</td>    <td>manufacturer</td>    <td>sochet</td>    <td>productname</td>    <td>charid</td>    <td>price</td>    <td>Corespeed</td>    <td>USB 3.0</td>    <td>Memory size</td>    <td>Memory slots</td>      <?phpif(!is_null($row_Recordset1['color'])){?><td>Color</td><?php}?>    <td>Watts</td>    <td>EnergyEff</td>    <td>Discription</td>    <td>chipset</td>    <td>Connection</td>    <td>channel</td>    <td>Memory speed</td>    <td>scantype</td>    <td>usage</td>    <td>inches</td>    <td>resolution</td>    <td>contrast</td>  </tr>  <?php do { ?>    <tr>      <td><?php echo $row_Recordset1['productid']; ?></td>      <td><?php echo $row_Recordset1['image']; ?></td>      <td><?php echo $row_Recordset1['cores']; ?></td>      <td><?php echo $row_Recordset1['Cache']; ?></td>      <td><?php echo $row_Recordset1['Lithography']; ?></td>      <td><?php echo $row_Recordset1['Integrated Graphics']; ?></td>      <td><?php echo $row_Recordset1['manufacturer']; ?></td>      <td><?php echo $row_Recordset1['sochet']; ?></td>      <td><?php echo $row_Recordset1['productname']; ?></td>      <td><?php echo $row_Recordset1['charid']; ?></td>      <td><?php echo $row_Recordset1['price']; ?></td>      <td><?php echo $row_Recordset1['Corespeed']; ?></td>      <td><?php echo $row_Recordset1['USB 3.0']; ?></td>      <td><?php echo $row_Recordset1['Memory size']; ?></td>      <td><?php echo $row_Recordset1['Memory slots']; ?></td>      <?phpif(!is_null($row_Recordset1['color'])){?> <td>   <?php     echo $row_Recordset1['color']    ?> </td><?php}?>      <td><?php echo $row_Recordset1['Watts']; ?></td>      <td><?php echo $row_Recordset1['EnergyEff']; ?></td>      <td><?php echo $row_Recordset1['Discription']; ?></td>      <td><?php echo $row_Recordset1['chipset']; ?></td>      <td><?php echo $row_Recordset1['Connection']; ?></td>      <td><?php echo $row_Recordset1['channel']; ?></td>      <td><?php echo $row_Recordset1['Memory speed']; ?></td>      <td><?php echo $row_Recordset1['scantype']; ?></td>      <td><?php echo $row_Recordset1['usage']; ?></td>      <td><?php echo $row_Recordset1['inches']; ?></td>      <td><?php echo $row_Recordset1['resolution']; ?></td>      <td><?php echo $row_Recordset1['contrast']; ?></td>    </tr>    <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?></table></body></html><?phpmysql_free_result($Recordset1);?>

birbal · May 13, 2011

you are probably misspelling the Color and color. it is case sensitive make sure its in correct case

garevn · May 13, 2011

OMG it works!!really tnx man for your time you spend one me i will go finish my project now you were really helpful!!

mysql null values

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived