Hi,

i have a code which is used to connect to database and get number of rows affteced from the SQL Query.
when i run the query connecting to postgres DB it gives num_row as 0, but when i run the query connecting to mssql server DB it gives num_row as -1. Below is my code.

$conn = odbc_connect('server','username','password');
$sql = "SELECT FROM clients WHERE client_code=''";

$result = odbc_exec($conn, $sql);
$num_rows = odbc_num_rows($result);
echo "num of rows :".$num_rows;

Kindly suggest

    What result do you want? Do you want every record from client? If so then don't write the WHERE-statement and it will return everything. Is client_code an int? If so then it might be because you send in a string that can't be converted to an int that is creating the problem with mssql.

    Do you only want to get the number of rows? Then use SELECT COUNT() instead of SELECT since it will be faster. In general, always name the columns you want returned instead of using SELECT *.

      Piranha wrote:

      What result do you want? Do you want every record from client? If so then don't write the WHERE-statement and it will return everything. Is client_code an int? If so then it might be because you send in a string that can't be converted to an int that is creating the problem with mssql.

      Do you only want to get the number of rows? Then use SELECT COUNT() instead of SELECT since it will be faster. In general, always name the columns you want returned instead of using SELECT *.

      I want to see if the record exist.
      i don't want to get num of rows, this is to just check because my query is giving me problem.

      I think The attachment should make you able to understand the actual problem.

      Regards

        It is the value in the COUNT(*) that is important since queries with only COUNT in them will always return exactly 1 row (unless there is some problem with the query).

        I still don't know if client_code is an int or not.

        Btw, why do you use odbc to connect to the database? Us the built-in PHP functions instead, it is much faster.

          Hi,

          The data type is nvarchar,

          Can you please tell me what built in function you are talking about ?

          Regards

            In MSSQL there are the following functions:

            mssql_bind
mssql_close
mssql_connect
mssql_data_seek
mssql_execute
mssql_fetch_array
mssql_fetch_assoc
mssql_fetch_batch
mssql_fetch_field
mssql_fetch_object
mssql_fetch_row
mssql_field_length
mssql_field_name
mssql_field_seek
mssql_field_type
mssql_free_result
mssql_free_statement
mssql_get_last_message
mssql_guid_string
mssql_init
mssql_min_error_severity
mssql_min_message_severity
mssql_next_result
mssql_num_fields
mssql_num_rows
mssql_pconnect
mssql_query
mssql_result
mssql_rows_affected
mssql_select_db

            and for postgres you have the following:

            pg_affected_rows
pg_cancel_query
pg_client_encoding
pg_close
pg_connect
pg_connection_busy
pg_connection_reset
pg_connection_status
pg_convert
pg_copy_from
pg_copy_to
pg_dbname
pg_delete
pg_end_copy
pg_escape_bytea
pg_escape_string
pg_execute
pg_fetch_all
pg_fetch_all_columns
pg_fetch_array
pg_fetch_assoc
pg_fetch_object
pg_fetch_result
pg_fetch_row
pg_field_is_null
pg_field_name
pg_field_num
pg_field_prtlen
pg_field_size
pg_field_type
pg_field_type_oid
pg_free_result
pg_get_notify
pg_get_pid
pg_get_result
pg_host
pg_insert
pg_last_error
pg_last_notice
pg_last_oid
pg_lo_close
pg_lo_create
pg_lo_export
pg_lo_import
pg_lo_open
pg_lo_read
pg_lo_read_all
pg_lo_seek
pg_lo_tell
pg_lo_unlink
pg_lo_write
pg_meta_data
pg_num_fields
pg_num_rows
pg_options
pg_parameter_status
pg_pconnect
pg_ping
pg_port
pg_prepare
pg_put_line
pg_query
pg_query_params
pg_result_error
pg_result_error_field
pg_result_seek
pg_result_status
pg_select
pg_send_execute
pg_send_prepare
pg_send_query
pg_send_query_params
pg_set_client_encoding
pg_set_error_verbosity
pg_trace
pg_transaction_status
pg_tty
pg_unescape_bytea
pg_untrace
pg_update
pg_version

            They could all be found here.

              Sorry, but I can't help you out further with why you get an error with MSSQL, I have no clue anymore.

                Hi,
                when i tried running the query with mssql i get the above error.
                This is to inform you that i have installed php using the installer not manually.

                Fatal error: Call to undefined function mssql_connect() in c:\Inetpub\wwwroot\test1.php on line 8

                  I think you have to add something to the .ini-file. Don't know what, but don't worry. It is in the manual, just to read it and you will get the answer.

                    Hi,

                    I don't want to tweak up my conifguration because i have already struggled with PHP configuration so i have decided to continue with the same odbc.

                    here is my PHP Code,

                    function verifyLogin($username, $password, $client_code)
{
  $returnval = $GLOBALS["LOGIN_SUCCESS"];
$conn = odbc_connect('server','Admin','passwrod'); 

  //see if client exists
$client_code = "*";
  $sql = "SELECT COUNT(*) FROM clients WHERE client_code='$client_code'";
  $result = odbc_exec($conn, $sql);
  $num_rows = odbc_num_rows($result); // Here I get 1 rows after using count(*)...thats ok.

  if ((odbc_num_rows($result) > 0) || ($client_code == "*"))
  {
    $sql = "SELECT active, password FROM users WHERE LOWER(username)='" . strtolower($username) . "' AND (client_code='*' or client_code='$client_code')";
    //here we get the user status and his password";

$result = odbc_exec($conn, $sql);
echo "<br>total num of rows :".odbc_num_rows($result); 
// here i get -1 rows.. However if we use select count(*) instead of filed name i get 1 row. i can use count(*) but when i use count(*) then i get errror 
// Notice: Undefined index: active in c:\Inetpub\wwwroot\dev\lib\login_functions.php on line 421.
//when i try $row["active"].

   if (odbc_num_rows($result) > 0)
    {
      $row = odbc_fetch_array($result);
      if ($row["active"] == false)
      {
        $returnval = $GLOBALS["LOGIN_USER_INACTIVE"];
      }
      //see if password is correct
      else if ($row["password"] != $password)   //need to store md5 password in database in the future
      {
        $returnval = $GLOBALS["LOGIN_PASSWORD_INCORRECT"];
      }
    }
    else
    {
      $returnval = $GLOBALS["LOGIN_USERNAME_NOT_FOUND"];
    }
  }
  else
  {
    $returnval = $GLOBALS["LOGIN_CLIENT_NOT_FOUND"];
  }

  return $returnval;
}

                    please friends i desperately need help. Please help me out to sort this issues

                      Hi Friends,

                      I got working, however I have one strange problem the IF conditions are not working, it is not supporting at all. Any idea why ?

                      Regards

                        Write a Reply...