I grab a number of topics in forum. Sometimes when forum has no topics in it. It says “This forum has topics”, but should be “This forum has 0 topics”. The variable is simple-way empty. I have to write in script specifically that
if (topicCount == "") topicCount = 0;
That means that SQL database returns null if the result is zero.
Can I tell database to explicitly send me 0 instead of null.
In order to spare programming cycles?
Can you make changes to the schema of this database? When you set up the database, you can set the default value of a field.
It’s a hosting. I can only access settings of my personal database. I don’t see anything near “Schema of database”-like. Can’t I do it by SQL query? After all MySQL uses MySQL to store it’s own settings. Isn’t it somehow changable?
Try a
SHOW CREATE TABLE table_name_here
query to see what the current schema is.
+--------+----------------------------------------------------------------------+
| Table | Create Table |
+--------+----------------------------------------------------------------------+
| topics | CREATE TABLE `topics` ( `id` int(9) NOT NULL AUTO_INCREMENT, |
| | `name` varchar(128) CHARACTER SET utf8 NOT NULL, `closed` tinyint(1) |
| | NOT NULL, `author` int(9) NOT NULL, `parent` int(9) NOT NULL, |
| | PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), KEY `id_2` (`id`)) |
| | ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--------+----------------------------------------------------------------------+
Note that table topics is (like mentioned) currently empty.
Yes, posting code can be a bit tricky a times
CREATE TABLE `topics` (
`id` int(9) NOT NULL AUTO_INCREMENT
, `name` varchar(128) CHARACTER SET utf8 NOT NULL
, `closed` tinyint(1) NOT NULL
, `author` int(9) NOT NULL
, `parent` int(9) NOT NULL
, PRIMARY KEY (`id`)
, UNIQUE KEY `id` (`id`)
, KEY `id_2` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
I don’t see a “topic count” field, so I’m guessing the problem is not so much with a query like
SELECT COUNT(id) AS topicCount FROM topics
but more of a problem with the code that deals with the returned result
(I’m a bit confused why there would be a need to have UTF-8 and Latin1)
topicCount: 0
As expected. That’s the problem, that’s WHEN it’s 0. It sends null to PHP script handler. I really need it to send 0 instead of null if there is no matches. Especially when my current query is:
SELECT COUNT(*) FROM topics WHERE a.b = c.d. If there are no results. Script says that field is empty, but I really need that 0 there.
(I’m a bit confused why there would be a need to have UTF-8 and Latin1)
I don’t know, I’m a newbie, I just play along and hope that it works, if it works, I’m not complaining xD.
Without seeing the PHP code involved I can only guess.
But you may be able to solve the problem by explicitly casting to an Int or maybe you need to use strict type comparison.
It’s simple SELECT query. Then while ($ans = $db->fetch_assoc()). And after this:
$ans["topicCount"] == "", true. It should be false, because it’s 0. But MySQL sends “null” not “0”.
I know I can do it by $db["topicCount"] = 0;, but I’d like to ask if it’s possible
to do just by SQL’s settings.
AFAIK a COUNT always returns an integer.
http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_count
Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value.
COUNT() returns 0 if there were no matching rows.
If you do a
var_dump($ans);
Do you see what you expect?
Might there be an error with the PHP code?
eg. a connection fail.
Ah. I found the problem I wrote forumTopicCount alias. But the script said $forumThreadCount.
But now’s new problem.
var_dump($forumThreadCount) says string(1) "0". Of course I could cast integer, but shouldn’t SQL give me an int? You just told me that it should always send int. It’s not super-problem. But would be preferred if it were int from origin.
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.