Display zero's

[leo99]

I've looked everywhere, please help.

I'm working with Easyphp v2.x as my local server. The mysql database I'm using at home is exactly the same as the one I uploaded to my isp. Mysql versions are both 5.x.

I made a report and some rows in certain columns are supposed to be just empty, because there is no value. On my local server they are plain empty as they should, but at my isp's server they display a zero (0).

I'm really at a loss. The columns are set to INT and NULL with no default.

What/where should I look?

Thanks already!

[sneakyimp]

Not sure what you mean by Easyphp. It would be helpful to see some code. I'm guessing that you are running a query to retrieve some data from the database and for some reason the output for these INT/NULL fields is coming out as a zero rather than the empty string.

Have you verified that your isp's data actually contains null values? It's quite possible that when you created the DB on the ISP server that you set a default value of zero rather than null. Or something like that.

Also, your mistake could be in the PHP in which case we'd need to see a bit of your code.

[bradgrafelman]

Can you show us a "SHOW CREATE TABLE tblName" and point out which column(s) are giving 0 instead of null values? Worked fine on my MySQL 5.0.27 server:

mysql> CREATE TABLE leo99 (
    -> col1 INT NULL
    -> ) Engine=MyISAM;
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO leo99 VALUES ();
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM leo99;
+------+
| col1 |
+------+
| NULL |
+------+
1 row in set (0.02 sec)

Perhaps show us the code that you use to INSERT values into the table? Also show us how you are retrieving/outputting the values.

[leo99]

@: Easyphp installs Apache, mySql and PHP on a local machine. Great package. It's, well... really easy 🙂

Ok, here is the CREATE TABLE:

CREATE TABLE tblcats (
ADF_CAT_ID int(11) NOT NULL auto_increment,
ADF_CAT_Manufacturer_ID int(11) default NULL,
ADF_Vehical_ID int(11) default NULL,
ADF_CAT_CC varchar(20) default NULL,
ADF_CAT_Fuel_Direction int(11) default NULL,
ADF_CAT_Fuel int(11) default NULL,
ADF_CAT_Valves int(11) default NULL,
ADF_CAT_Description varchar(255) default NULL,
ADF_CAT_Date_From_Month varchar(50) default '"-"',
ADF_CAT_Date_From_Year varchar(50) default '"-"',
ADF_CAT_Date_To_Month varchar(50) default '"-"',
ADF_CAT_Date_To_Year varchar(50) default '"-"',
ADF_CAT_Ref varchar(50) default NULL,
ADF_CAT_Fitting_Kit varchar(50) default NULL,
ADF_CAT_Notes varchar(255) default NULL,
ADF_CAT_Weight varchar(50) default NULL,
ADF_CAT_Length varchar(50) default NULL,
ADF_CAT_Record_Created int(11) default '0',
ADF_CAT_Record_Amended int(11) default NULL,
ADF_CAT_approved int(11) NOT NULL default '0',
ADF_CAT_cert varchar(150) default NULL,
PRIMARY KEY (ADF_CAT_ID),
KEY ADF_CAT_ID (ADF_CAT_ID),
KEY ADF_CAT_Manufacturer_ID (ADF_CAT_Manufacturer_ID),
KEY ADF_Vehical_ID (ADF_Vehical_ID),
KEY tblFuelDirectiontblCats (ADF_CAT_Fuel_Direction),
KEY tblFueltblCats (ADF_CAT_Fuel),
KEY tblValvestblCats (ADF_CAT_Valves),
KEY tblVehicaltblCats (ADF_Vehical_ID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=1 AUTO_INCREMENT=1777 ;

One of the columns that give me trouble is ADF_CAT_Valves. It pulls its data from column ADF_Valve on tblvalves, which looks like this:

CREATE TABLE tblvalves (
ADF_Valve_ID int(11) NOT NULL default '0',
ADF_Valve int(11) default NULL,
PRIMARY KEY (ADF_Valve_ID),
KEY ADF_Valve_ID (ADF_Valve_ID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I hope this makes some sense. Remember, this is the Newbie section 😉

[sneakyimp]

what does the guilty query look like? is ADF_CAT_Valves used to join tblcats to tblvalves? have you looked at the data in your DB to make sure those values are actually NULL? Remember, the problem could have occurred when you were moving the data from one DB to the other.

[leo99]

sneaky,

This is the query:

Select
tblcats.ADF_CAT_ID,
tblcats.ADF_CAT_Manufacturer_ID,
tblcats.ADF_Vehical_ID,
tblcats.ADF_CAT_CC,
tblcats.ADF_CAT_Fuel_Direction,
tblcats.ADF_CAT_Fuel,
tblcats.ADF_CAT_Valves,
tblcats.ADF_CAT_Description,
tblcats.ADF_CAT_Date_From_Month,
tblcats.ADF_CAT_Date_From_Year,
tblcats.ADF_CAT_Date_To_Month,
tblcats.ADF_CAT_Date_To_Year,
tblcats.ADF_CAT_Ref,
tblcats.ADF_CAT_Fitting_Kit,
tblcats.ADF_CAT_Notes,
tblcats.ADF_CAT_Weight,
tblcats.ADF_CAT_Length,
tblcats.ADF_CAT_Record_Created,
tblcats.ADF_CAT_Record_Amended,
tblcats.ADF_CAT_approved,
tblcats.ADF_CAT_cert,
tblmanufacturer.ADF_Manufacturer_ID,
tblmanufacturer.ADF_Manufacturer_Name,
tblvehical.ADF_Vehical_ID,
tblvehical.ADF_Vehical_Model,
tblvalves.ADF_Valve_ID,
tblvalves.ADF_Valve,
tblfuel.Vehical_Fuel_ID,
tblfuel.Vehical_Fuel_Type,
tblfueldirection.ADF_Fuel_Direction_ID,
tblfueldirection.ADF_Fuel_Direction
From
((((tblmanufacturer tblmanufacturer RIGHT OUTER JOIN tblcats tblcats ON (tblmanufacturer.ADF_Manufacturer_ID = tblcats.ADF_CAT_Manufacturer_ID)) LEFT OUTER JOIN tblvehical tblvehical ON (tblvehical.ADF_Vehical_ID = tblcats.ADF_Vehical_ID)) LEFT OUTER JOIN tblvalves tblvalves ON (tblvalves.ADF_Valve_ID = tblcats.ADF_CAT_Valves)) LEFT OUTER JOIN tblfuel tblfuel ON (tblfuel.Vehical_Fuel_ID = tblcats.ADF_CAT_Fuel)) LEFT OUTER JOIN tblfueldirection tblfueldirection ON (tblfueldirection.ADF_Fuel_Direction_ID = tblcats.ADF_CAT_Fuel_Direction)
Order by
tblcats.ADF_CAT_Manufacturer_ID Asc

I'm pretty sure the values are actually NULL, but how can I really tell?

Thanks!

[sneakyimp]

Run the query and look at the raw data that comes back. You can query MySQL using the command line interface (CLI) on linux machines. Or you can use a tool like SQLYog or PHPMyAdmin. If that data comes back with zeros, then you probably have zeros in your data.

BTW, I'm not super duper query guy but don't OUTER joins result in TONS of data? I'm guessing that this query was formulated by some kind of framework or development tool?

[leo99]

Its partly dbQwiksite, partly myself and partly done by a (paid) mysql/php developer. I'd gladly pay for someone to do it right, but it never works out right when you ask a coder to do a basically simple job. The estimate always triples.

Never mind, I'll check the data for zero's again, as you suggest.

[leo99]

Sneakyimp,

I ran the query in SQLYog. The data comes back as (NULL). That's ok, isn't it?

[bradgrafelman]

Thread moved to the Database forum (hopefully one of the DB guru's can shed some light on the NULL/zero problem).

[sneakyimp]

Actually he says the values are coming back NULL which suggests there's something wrong with his PHP.

Leo if you are certain that the query is returning NULL values then the problem is likely in your code. Given that your code is probably being generated by some funky dev tool the problem could be anywhere so I'm not sure what to tell you there. Either ditch the funky tool and write your own PHP or consult tech support for the tool--maybe they have a forum or something.

Just to be sure the fault is with the tool you may want to try writing your own php script to see if the values are in fact zeros. Something like this:

$db = mysql_connect  ('YourMySQLServer', 'DBUserName', 'YourPassword')
  or die('Could not connect');
mysql_select_db('YourDataBaseName')
  or die('Could not select db');

$sql = "SELECT BLAH BLAH (put your query in here";
$result = mysql_query($sql)
  or die('query failed');
$i = 0;
while ($row = mysql_fetch_assoc($result)) {
  echo "record $i\n";
  print_r($row);
  $i++;
}

If you see the zeros in the output there then your database is returning zeros which would suggest that you actually have zeros in your db so the problem is with your data. If the zeros are not there, it's your code.

[bradgrafelman]

[man]var_dump/man can help determine what a variable's value truly is.

Out of curiosity, can you show us a small code snippet where you display data? It'd be understandable if you saw 0 for null values if you used sprintf() with a '%d' format, for example.

[leo99]

There is no sprintf() anywhere in the code. Could the "&nbsp" down here be the culprit?

print "<td align=Default >";
    print $cellvalue;
    print "</td>";
    $cellvalue = "" . $row[15] . "";
    if ($cellvalue != "") {
        $cellvalue = str_replace(array("\n\r","\r\n","\n","\r"),"<br>",$cellvalue);
    }
    else { 
        $cellvalue = "&nbsp;";

[sneakyimp]

Nope.   is html for a non-breaking space character.

[leo99]

I know, but maybe just the fact that it is not ""? Anyway, it looks like I have to live with it.

Thank you all for your suggestions. Lets rest this case...