NYCPHP Meetup

Tue Oct 9 09:04:48 EDT 2007

Just in case others have been suffering from the same problem I have cut and 
pasted my test table (authorised_users) into this posting, in order that 
others who want to follow it through can.I have also left the MySQL 
mysql_error() debugging tool in just in anybody wants to try it for 
themselves. The problem lay in the database and table call and the only way 
I found that out was through the mysql_error() routine. All the strange 
message all stemmed from the wrong type of database and table calls.

Paul

<?php session_start(); ob_start(); ?>
<?PHP
/*
mysql> use test_db;
Database changed
mysql> desc authorised_users;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| user_name     | varchar(35) | YES  |     | NULL    |       |
| company       | varchar(50) | YES  |     | NULL    |       |
| user_id       | varchar(16) | YES  |     | NULL    |       |
| user_passcode | varchar(16) | YES  |     | NULL    |       |
| account       | varchar(8)  | YES  |     | NULL    |       |
| access_code   | varchar(8)  | YES  |     | NULL    |       |
| testrecord    | varchar(3)  | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
7 rows in set (0.20 sec)
mysql> select * from authorised_users where user_id = 'qwerty' and 
user_passcode
= '123456' and account = 29764404;
+-----------+---------+---------+---------------+----------+-------------+------------+
| user_name | company | user_id | user_passcode | account  | access_code | 
testrecord |
+-----------+---------+---------+---------------+----------+-------------+------------+
|           |         | qwerty  | 123456        | 29764404 | 79334716    | 0 
|
+-----------+---------+---------+---------------+----------+-------------+------------+
1 row in set (0.59 sec)
mysql> select access_code from authorised_users where user_id = 'qwerty'
and user_passcode= '123456' and account = 29764404;
+-------------+
| access_code |
+-------------+
| 79334716    |
+-------------+
1 row in set (0.00 sec)  */
// ++++++++++++++++++++++++PHP CODING ++++++++++++++++++++++++++
// INITIATE VARIABLES
// ==================
  $userid = "qwerty";
  $passcode = "123456";
  $account = 29764404;
  $result  = "empty";
  $access = 00000000;
  $query = "rubbish";
  echo ('Before $userid     = '  . $userid    . "<br>");
  echo ('Before $account    = '  . $account   . "<BR>");
  echo ('Before $passcode   = '  . $passcode  . "<br><br>");
  echo ('Before $result     = '  . $result    . "<BR>");
  echo ('Before $access     = '  . $access    . "<BR>");
  echo ('Before $query      = '  . $query     . "<BR><BR>");
  echo ('========================================================' . 
"<BR>");
// CONNECT & READ DB
  mysql_connect("localhost",'paul','enter');
  mysql_select_db(test_db) or die( "Unable to select database");
// ==============================================================
$query = "SELECT access_code FROM authorised_users WHERE user_id = '$userid' 
AND user_passcode = '$passcode' AND account = '$account'";
// =============================================================
$result   = mysql_query($query);
      if   (!$result)
       {     echo ('Check $result' . "<BR>");
             echo ('Using mysql_error() - To show the actual query sent to 
MySQL, and the error. ' . "<br><br>");
             $message  = 'Invalid query: ===> ' . mysql_error() . "<BR>";
             $message .= 'Whole query: ===> ' . $query;
             die($message); }
      $data   = mysql_fetch_array($result);
      $access = $data['access_code'];
      echo '$data  = ' . $access . "<BR>";
      echo ('$query = MYSQL_QUERY( select ACCESS_CODE from 
AUTHORISED_USERS)ETC. ETC.' . "<br>");
      echo ('========================================================' . 
"<br>");
      echo ("<br>");
      echo ('AFTER $USERID              = '  . $userid    . "<BR><BR>");
      echo ('AFTER $PASSCODE            = '  . $passcode  . "<BR><BR>");
      echo ('AFTER $ACCOUNT             = '  . $account   . "<br><br>");
      echo ('AFTER $ACCESS              = '  . $access    . "<BR>");
      echo ('========================================================' . 
"<br>");
?>

OUTPUT FROM TEST

Before $userid = qwerty
Before $account = 29764404
Before $passcode = 123456

Before $result = empty
Before $access = 0
Before $query = rubbish

========================================================
$data = 79334716
$query = MYSQL_QUERY( select ACCESS_CODE from AUTHORISED_USERS)ETC. ETC.
========================================================

AFTER $USERID = qwerty

AFTER $PASSCODE = 123456

AFTER $ACCOUNT = 29764404

AFTER $ACCESS = 79334716
========================================================

----- Original Message ----- 
From: "PaulCheung" <paulcheung at tiscali.co.uk>
To: "NYPHP Talk" <talk at lists.nyphp.org>
Sent: Monday, October 01, 2007 2:33 PM
Subject: Re: [nycphp-talk] MySQL Monitor V PHP & MySQL

> Hi Michael,
>
> I have taken onboard all the comments and research things further and was
> eventual able to force an error message
> Warning: mysql_query() [function.mysql-query]: Access denied for user
> 'ODBC'@'localhost' (using password: NO) in C:\xampp\htdocs\tp_test.php on
> line 38
> Armed with this info I changed the PHP.INI and used
> grant all privileges on *.* to ODBC at localhost identified by enter with 
> grant option;
> both with and without the password and still I cannot move ahead.
>
> The error message is a "mysql error 1045". There appears to be one PHP 
> rule for Linux and another for Windows.
> Apart from the obvious of moving from Windows to Linux  does anybody have 
> any ideas or suggestion that will help me overcome
> this problem. From what I have read on the MySQL website there does not 
> appear to be any help there
> as what I am trying to accomplish works in MySQL command mode. It only 
> falls over, for me anyway, when trying the same thing using PHP & MySQL.
>
>
>
>
>
>
>
> ---- Original Message ----- 
> From: "Michael Southwell" <michael.southwell at nyphp.com>
> To: <talk at lists.nyphp.org>
> Sent: Saturday, September 22, 2007 12:41 AM
> Subject: Re: [nycphp-talk] MySQL Monitor V PHP & MySQL
>
>
>> Quoting PaulCheung <paulcheung at tiscali.co.uk>:
>>
>>> I have researched, checked and tried everything I can think of and
>>> still cannot get it to work.
>>
>>> SELECT access_code FROM authorised_users WHEN
>>
>> I am not familiar with WHEN but that could be my own ignorance
>>
>>> $rs = @mysql_select_db( "test_db", $conn )
>>>  or die( "Could not select database" );
>>>
>>> $rs = @mysql_select_db( "test_db", $conn ) or die( "Could not select
>>> database" );
>>
>> This line is doubled but that is not your problem.
>>
>>>
>>> $sql = "SELECT access_code
>>>        FROM authorised_users
>>>        user_id = '$userid' AND
>>
>> This is the reason that you are getting different results:  you left  out
>> the WHEN that you had above (unless this is not an accurate
>> representation of your code); whether WHEN is a legitimate keyword is  a
>> different issue.
>>
>> -- 
>> Michael Southwell
>> Vice President, Education
>> NYPHP TRAINING http://nyphp.com/training/indepth
>> _______________________________________________
>> New York PHP Community Talk Mailing List
>> http://lists.nyphp.org/mailman/listinfo/talk
>>
>> NYPHPCon 2006 Presentations Online
>> http://www.nyphpcon.com
>>
>> Show Your Participation in New York PHP
>> http://www.nyphp.org/show_participation.php
> _______________________________________________
> New York PHP Community Talk Mailing List
> http://lists.nyphp.org/mailman/listinfo/talk
>
> NYPHPCon 2006 Presentations Online
> http://www.nyphpcon.com
>
> Show Your Participation in New York PHP
> http://www.nyphp.org/show_participation.php 

NYCPHP Meetup

NYPHP.org

[nycphp-talk] MySQL Monitor V PHP & MySQL