SQL Returns Wrong Result

Question

4.00/5 (1 vote)

See more:

This query always returns "1", Not quite sure whats wrong?
Thanks
DS

<pre lang="cs">if (mysql_real_connect(conn,"urlock.db.5513143.hostedresource.com","VendorCheck","Reader1234","urlock",0,NULL,0) !=0)
     {
    legal = mysql_query(conn, "SELECT COUNT(*) FROM tblURL WHERE IP = '192.168.1.1'");
        if( legal != 0 )
        {
            printf(mysql_error(conn)); return 99;
        }
        result = mysql_store_result(conn);
        printf("Legal: %d\n", mysql_num_rows(result));
        mysql_free_result(result);
        mysql_close(conn);

Posted 10-May-11 8:44am

Member 7766180

Add a Solution

3 solutions

Solution 2

If I'm interpreting the code correctly, then you're polling the resultset for the number of rows it has, not the actual returned value. The number of rows will always be 1, since a count query always returns one result. In other words, you're looking at the number of results, not the result itself. At least, that's what it looks like :)

Code in question:

printf("Legal: %d\n", mysql_num_rows(result));

Posted 10-May-11 9:18am

R. Hoffmann

Updated 10-May-11 9:21am

v2

Comments

Member 7766180 10-May-11 15:29pm

Spot on! How do I get it to return either a "1" if it does match or "0" if it doesn't?
Thanks
DS

Member 7766180 10-May-11 15:36pm

I want it to check if there is a match on the IP address. If there is a match then it's "1" No match "0"

R. Hoffmann 10-May-11 15:43pm

Sorry, I'm not familiar with MySQL at all, but this page may be of help: http://php.net/manual/en/function.mysql-query.php.

Seems like they
1. execute the function using mysql_query, which returns a resultset of some sort,
2. call mysql_fetch_assoc on the resultset to obtain the next row from it, which is returned,
3. and lastly, refer to a specific value in the row by using an indexer in the form of the field name to be returned.

OriginalGriff 10-May-11 15:37pm

Good catch!

R. Hoffmann 10-May-11 15:47pm

Thanks :)

Kim Togo 11-May-11 2:37am

Correct answer! My 5.
"SELECT COUNT(*) FROM tblURL" will ALWAYS return 1 row.
And in that row you have a column 0 - COUNT(*) - That holds the result.

R. Hoffmann 11-May-11 3:47am

Thanks!

Solution 1

Perhaps you only have one matching record in your database?

Posted 10-May-11 8:47am

OriginalGriff

Comments

Member 7766180 10-May-11 14:52pm

DUH? I've been at this too long! lol! Your right, I do only have one matching record. However: when I change the IP to one that isn't in the db, it still returns "1".
Thanks
DS

Member 7766180 10-May-11 15:14pm

In think it might be in the logic?
<pre>#include "stdafx.h"
#include <stdio.h>
#include <iostream>
#include <string>
#include <conio.h>
#include <fstream>
#include <windows.h>
#include <winsock.h>
#include <c:\\mysql\\include\\mysql.h>

using namespace std;

int main()
{
MYSQL_RES *result;
MYSQL* conn;
int legal;

conn = mysql_init(NULL);
if (mysql_real_connect(conn,"urlock.db.5513143.hostedresource.com","VendorCheck","Reader1234","urlock",0,NULL,0) !=0)
{
legal = mysql_query(conn, "SELECT COUNT(*) FROM tblURL WHERE IP = '192.168.1.99'");
if( legal != 0 )
{
result = mysql_store_result(conn);
printf(mysql_error(conn)); return 0;
}
else if( legal != 1 )
{
result = mysql_store_result(conn);
printf("Legal: %d\n", mysql_num_rows(result));
}
mysql_free_result(result);
mysql_close(conn);
}
else
{
cout << "Conn Failed!" <<endl;
}
system("PAUSE"); /*Remove After Testing*/
return 0;
}
</pre>

Member 7766180 10-May-11 15:17pm

Don't know what happened to the #includes!

Graham Breach 10-May-11 15:18pm

If the count is 0, you'll still get one row containing the value 0.

OriginalGriff 10-May-11 15:28pm

R Hoffman, and Graham are right: your query will return a row containing the count only. Unfortunately, there doesn't appear to be any way to directly call scalar functions as you can with .NET so you will have to retrieve the result yourself!

Member 7766180 10-May-11 15:38pm

There is only one record in db with that IP. It will either be there (return 1) or not (return 0)

OriginalGriff 10-May-11 15:45pm

Yes but the return of the query is a set of 1 records: the record contains a single column, containing the number of records in the DB which match the criteria. (In .NET I would use MySqlCommand.ExecuteScalar which would return the Count as an integer - not available to you, so you will have to read the result from "legal" row zero, column zero).

Member 7766180 10-May-11 16:03pm

C++ can't return integers!???! Oh boy. I ust want to find out whether there is a match or no match. Can I just run a SELECT query and if the result is NULL or 0?

Kim Togo 11-May-11 2:40am

Total correct OrginalGriff :-)
"SELECT COUNT(*) FROM tblURL" will ALWAYS return 1 row.
And in that row you have a column 0 - COUNT(*) - That holds the result.

Add a Solution

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

Richard MacCutchan · Accepted Answer · 2011-05-10T10:19:00

I wonder how many times this has to be explained to you in this and your previous question[^] before you understand? The SELECT command does not return a value giving the answer to your query, it returns a value telling you how many database rows it found. You then need to interpret the contents of those rows for the information you are looking for.

Posting comments like C++ can't return integers!???! Oh boy. merely shows how far away you are from understanding any of the responses that have been posted, and possibly both C++ and SQL.

SQL Returns Wrong Result

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0

SQL Returns Wrong Result

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us