Click here to Skip to main content
14,299,281 members
Rate this:
Please Sign up or sign in to vote.
See more:
This statement returns the correct numbers, but when there are no results I need it to return '0'...I tried the standard way to do it but it doesn't debug. Right now it just shows a blank when a record isn't returned. This is a console app.

mysql_query(conn,"SELECT * FROM tblURL WHERE IP = '192.168.1.1','0'");
		my_ulonglong i = 0;
		res_set = mysql_store_result(conn);
		my_ulonglong numrows = mysql_num_rows(res_set);
		while ((row = mysql_fetch_row(res_set)) != NULL){
		printf("%s\n",row[i] != NULL ?
        row[i] : "NULL"); 


[edit]Code block added, inline code removed - OriginalGriff[/edit]
Posted
Updated 11-May-11 5:33am
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

Why don't you just test "numrows" and see if that is zero?
   
Comments
Member 7766180 11-May-11 12:22pm
   
Could I see an example? Please?
DS
Member 7766180 11-May-11 12:29pm
   
I'm sorry, I think Kim posted an example. My bag of chips was in the way! LOL!!!
Rate this:
Please Sign up or sign in to vote.

Solution 2

Use "SELECT COUNT(*) FROM tblURL WHERE IP = '192.168.1.1'". It will ALWAY return 1 row and 1 column where the count is.

via mysql command prompt:

mysql> SELECT COUNT(*) FROM tblURL WHERE IP = '192.168.1.1';
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+

If there is 9 rows where IP is '192.168.1.1'. If there is 0 (zero) result will be.

mysql> SELECT COUNT(*) FROM tblURL WHERE IP = '80.66.44.22';
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+

mysql_query(conn,"SELECT COUNT(*) FROM tblURL WHERE IP = '192.168.1.1'");
res_set = mysql_store_result(conn);
my_ulonglong numrows = mysql_num_rows(res_set);

if (numrows > 0)
{
  row = mysql_fetch_row(res_set));
  printf("Count is: %s\n",row[0]);
}
   
v2
Comments
Member 7766180 11-May-11 12:28pm
   
Thanks that works and it's cleaner!
DS
Kim Togo 11-May-11 12:32pm
   
Good :-)
Rate this:
Please Sign up or sign in to vote.

Solution 3

Like OriginalGriff said, it is much easier to test numrows for zero than to write a complicated sql to return 0. Rememeber you are doing select *, which means your return will have a bunch of fields (possibly) or in the case of 0 single field. It is much easier to check for returned rows.
   
Comments
Member 7766180 11-May-11 12:33pm
   
Correct

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100