?
Solved

NOT ABLE TO GET A RESULT FROM MY QUERY- NO ERRORS - PHP

Posted on 2005-04-12
30
Medium Priority
?
265 Views
Last Modified: 2008-03-04
Hello,

I have script that is running a query without any errors but I am not able to get a result even though i know the query statement is valid. Basically I have an if statement that checks a condition and then runs a query, if the query is true and returns at least 1 row then do something else, if not then do something.

Pretty straight forward but it's not returning any rows but when I run the same exact query in MySQL Query Editor it returns a row and even when I count the # of rows affected by the query it returns a row.

MY QUERY///////////////////////////////
SELECT * FROM employee WHERE Bday = '03/21' AND email = 'userid'

MY COUNT////////////////////////////
select count(*) FROM employee WHERE Bday = '03/21' AND email = 'userid'

MY CODE//////////////////////////////////////////////////////////////////////////////
if (!empty($userid)){
                              $userid = trim($userid);
                              $query = "SELECT * FROM employee WHERE Bday = '$btotal' AND email = '$userid' ";
                              $result = @mysql_query($query) or header ("CAN'T CONNECT TO DATABASE");
                              //IF THERE IS ONE RESULT THEN UPDATE THE DATABASE
                              if(@mysql_num_rows($result) == 1){
                              $stat = "i found something!";
                                                }
                                                //IF THERE ARE NO RESULTS THEY HAVE TO REEENTER THEIR BIRTHDATE
                                                else if(@mysql_num_rows($result) == 0){
                                                $stat = "i didn't find anything!";
                                                
                                                }
      }
      
mysql_close();

/////////////////////////////////////////////////////////////
75 Points if someone can identify and correct my query or code.
0
Comment
Question by:baxleyb
  • 9
  • 8
  • 6
  • +2
30 Comments
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 13762965
Please echo $query and post the output.  Also change the line
if(@mysql_num_rows($result) == 1){
to
if(@mysql_num_rows($result) >= 1){
0
 
LVL 25

Expert Comment

by:Marcus Bointon
ID: 13763029
You could also do:

$query = "SELECT * FROM employee WHERE Bday = '$btotal' AND email = '$userid' LIMIT 1";

so that it will only ever return either 0 or 1 results.

Excessive use of @ is generally a bad idea. Do your error checking properly.
0
 

Author Comment

by:baxleyb
ID: 13763064
Squinky, tried it and it didn't work. The only reason why I use the @ symbol is because I tend to get script errors when I don't use the @ symbol.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 33

Expert Comment

by:snoyes_jw
ID: 13763119
>>>The only reason why I use the @ symbol is because I tend to get script errors when I don't use the @ symbol.

So why not fix the errors?
0
 

Author Comment

by:baxleyb
ID: 13763241
snoyes_jw,

#1 I don't think the @ symbol is related to the issue I am having, I removed them and received the following error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /devel/web/webback/Authentication/Brandon/Process2.php on line 27

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /devel/web/webback/Authentication/Brandon/Process2.php on line 35

#2 I will deal with te use of the @ symbol later but for now I need my query issue resolved.

Thanks.
0
 
LVL 25

Expert Comment

by:Marcus Bointon
ID: 13763537
Er, this line is complete nonsense:

$result = @mysql_query($query) or header ("CAN'T CONNECT TO DATABASE");

a) it will not prevent the code from continuing (which would explain subsequent errors)
b) it's not a valid header so you'll probably never see it

It just sounds like you're not connected to your database - suppressing the errors is not going to help. Check the status of your mysql_connect call, and make sure what it returns is a valid mysql resource (var_dump it).
0
 
LVL 7

Accepted Solution

by:
Promethyl earned 200 total points
ID: 13764030
Add the line "if (mysql_error()) echo mysql_error(); " after every query. Find the error. Fix the error.
0
 
LVL 1

Assisted Solution

by:jeffparis
jeffparis earned 100 total points
ID: 13764149


If you are using a web form to post the varaibles $btotal and $userid, then use this:



      if (isset($_GET["userid"]) || isset($_GET["$btotal"])) {

            $clean_user_id = addslashes(trim($_GET["userid"]));
            $clean_btotal = addslashes(trim($_GET["btotal"]));
            $query ='SELECT * FROM employee WHERE Bday = ' . $clean_btotal . ' AND email = ' . $clean_user_id";
            $result = mysql_query($query);

                  if (mysql_num_rows($result)<1) {
                        $stat = 'I didn't find any results.';
                        } else {
                              $stat = "i didn't find anything!";
                        }
            mysql_close();

      else {
            echo 'I am sorry, you did not pass all variables to the script.';
            die;
      }
     








/////////////////////////////////////////////////////////////////////////


If $userid and %btotal is being passed through a URL query line, use this:



      if (isset($_POST["userid"]) || isset($_POST["$btotal"])) {

            $clean_user_id = addslashes(trim($_POST["userid"]));
            $clean_btotal = addslashes(trim($_POST["btotal"]));
            $query ='SELECT * FROM employee WHERE Bday = ' . $clean_btotal . ' AND email = ' . $clean_user_id";
            $result = mysql_query($query);

                  if (mysql_num_rows($result)<1) {
                        $stat = 'I didn't find any results.';
                        } else {
                              $stat = "i didn't find anything!";
                        }
            mysql_close();

      else {
            echo 'I am sorry, you did not pass all variables to the script.';
            die;
      }
     



/////////////////////////////////////////////////////////////////////////


If $userid and %btotal is being called from within the script, use this




      if (isset($_POST["userid"]) || isset($_POST["$btotal"])) {

            $clean_user_id = addslashes(trim($userid));
            $clean_btotal = addslashes(trim($btotal));
            $query ='SELECT * FROM employee WHERE Bday = ' . $clean_btotal . ' AND email = ' . $clean_user_id";
            $result = mysql_query($query);

                  if (mysql_num_rows($result)<1) {
                        $stat = 'I didn't find any results.';
                        } else {
                              $stat = "i didn't find anything!";
                        }
            mysql_close();

      else {
            echo 'I am sorry, you did not pass all variables to the script.';
            die;
      }
     







///////////////////////////////////////////////////////////////////////////


Let me know if you have any more problems. This is my standard, so it is a time tested method.

If you want error reporting, check /ect/php.ini and make sure that error reporting is turned ON.


Also, never do this:

'SELECT *'


Do this instead:

'SELECT userid, email, hours_worked'


That was an example, but if you use *, and there is sesitive data LIKE PASSWORDS, you could be setting oyurself up for a hacking. The '*' means to select ALL fields, including those in which you may not wish to be available to the public.


0
 
LVL 1

Expert Comment

by:jeffparis
ID: 13764252
TWO CORRECTIONS, MY BAD:



CORRECTION #1:

If  $userid and $btotal is being called from within the script, use this




     if ( $userid == "" AND $btotal == ""  ) {

          $clean_user_id = addslashes(trim($userid));
          $clean_btotal = addslashes(trim($btotal));
          $query ='SELECT * FROM employee WHERE Bday = ' . $clean_btotal . ' AND email = ' . $clean_user_id";
          $result = mysql_query($query);

               if (mysql_num_rows($result)<1) {
                    $stat = 'I didn't find any results.';
                    } else {
                         $stat = "i didn't find anything!";
                    }
          mysql_close();

     else {
          echo 'I am sorry, you did not pass all variables to the script.';
          die;
     }







CORRECTION #2:

if (isset($_GET["userid"]) || isset($_GET["$btotal"])) {

should be

if (isset($_GET["userid"]) AND isset($_GET["$btotal"])) {

also....

if (isset($_POST["userid"]) || isset($_POST["$btotal"])) {

should be

if (isset($_POST["userid"]) AND isset($_POST["$btotal"])) {

0
 

Author Comment

by:baxleyb
ID: 13764310
Promethyl, Thank you for your answer.

Now I get this message:

Unknown column 'bday' in 'where clause'

Originally the column name was Bday so I made the column name lower case thinking that maybe the case had something to do with it but MySQL still doesn't recognize the column.

My ORIGINAL SQL statement was this:

"SELECT * FROM employee WHERE Bday = '$btotal' AND email = '$userid' ";

I changed the statement to thinking maybe the order had something to do with it, but it still doesn't work:

"SELECT * FROM employee WHERE  email = '$userid' AND Bday = '$btotal'";

I ran a new query with just returning the email column and the query ran fine:

"SELECT * FROM employee WHERE  email = '$userid' ";

I still don't have a resolution for this issue, does anyone have any ideas?

BB

0
 
LVL 1

Expert Comment

by:jeffparis
ID: 13764312


HERE IS WITH ALL CORRECTIONS AND ERROR REPORTING:






If you are using a web form to post the varaibles $btotal and $userid, then use this:



      if ( isset($_GET["userid"]) AND isset($_GET["$btotal"]) ) {

            $clean_user_id = addslashes(trim($_GET["userid"]));
            $clean_btotal = addslashes(trim($_GET["btotal"]));
            $query ='SELECT * FROM employee WHERE Bday = ' . $clean_btotal . ' AND email = ' . $clean_user_id";
            $result = mysql_query($query) or die(mysql_error( ));

                  if (mysql_num_rows($result)<1) {
                        $stat = 'I didn't find any results.';
                        } else {
                              $stat = "i didn't find anything!";
                        }
            mysql_close();

      else {
            echo 'I am sorry, you did not pass all variables to the script.';
            die;
      }
     








/////////////////////////////////////////////////////////////////////////


If $userid and $btotal is being passed through a URL query line, use this:



      if ( isset($_POST["userid"]) AND isset($_POST["$btotal"]) ) {

            $clean_user_id = addslashes(trim($_POST["userid"]));
            $clean_btotal = addslashes(trim($_POST["btotal"]));
            $query ='SELECT * FROM employee WHERE Bday = ' . $clean_btotal . ' AND email = ' . $clean_user_id";
            $result = mysql_query($query) or die(mysql_error( ));

                  if (mysql_num_rows($result)<1) {
                        $stat = 'I didn't find any results.';
                        } else {
                              $stat = "i didn't find anything!";
                        }
            mysql_close();

      else {
            echo 'I am sorry, you did not pass all variables to the script.';
            die;
      }
     



/////////////////////////////////////////////////////////////////////////


If $userid and $btotal is being called from within the script, use this




      if ( $userid == "" AND $btotal == "" ) {

            $clean_user_id = addslashes(trim($userid));
            $clean_btotal = addslashes(trim($btotal));
            $query ='SELECT * FROM employee WHERE Bday = ' . $clean_btotal . ' AND email = ' . $clean_user_id";
            $result = mysql_query($query) or die(mysql_error( ));

                  if (mysql_num_rows($result)<1) {
                        $stat = 'I didn't find any results.';
                        } else {
                              $stat = "i didn't find anything!";
                        }
            mysql_close();

      else {
            echo 'I am sorry, you did not pass all variables to the script.';
            die;
      }
     



There might be a stray ( or ) in there, but that is the easy part, give it a try and go from there. This should treat you well.
0
 
LVL 25

Expert Comment

by:Marcus Bointon
ID: 13764607
There's a persistent error in your scripts jeff; the first line should be:

if ( isset($_POST['userid']) AND isset($_POST['btotal']) ) {

note the change from $btotal that could be causing all kinds of trouble, which was compounded by unnecessary double quoting.

the quoting in your SQL is wrong too:

$query = "SELECT * FROM employee WHERE Bday = '$clean_btotal' AND email = '$clean_user_id'";

and the logic in your last example was very strange:

  if (mysql_num_rows($result)<1) {
                    $stat = 'I didn't find any results.';
                    } else {
                         $stat = "i didn't find anything!";
                    }

So you'll never find anything!

I think this is still a simple connection issue and nothing to do with the subsequent errors. The code should look roughly like this:

if(array_key_exists('userid', $_POST) and array_key_exists('btotal', $_POST)) {
      $clean_user_id = addslashes(trim($_POST['userid']));
      $clean_btotal = addslashes(trim($_POST['btotal']));
      $db = mysql_connect('localhost', 'mysql_user', 'mysql_password');
      if (!$db) {
            die('Could not connect: ' . mysql_error());
      }
      mysql_select_db('mydb', $db);
      $query = "SELECT * FROM employee WHERE Bday = '$clean_btotal' AND email = '$clean_user_id' LIMIT 1";
      if ($result = mysql_query($query)) {
            if (mysql_num_rows($result) > 0) {
                  print "found\n";
            } else {
                  print "not found\n";
            }
      } else {
            print mysql_error( );
      }
} else {
      print "I am sorry, you did not pass all variables to the script.\n";
}

Substitute your own database login and name strings. Note that (for efficiency) this won't attempt to connect to the db unless you actually submit the correctly named variables.
0
 
LVL 25

Expert Comment

by:Marcus Bointon
ID: 13764662
Double check that the bday column does really exist: issue a 'SHOW COLUMNS FROM employee' in the mysql client or in phpMyAdmin.
0
 
LVL 1

Expert Comment

by:jeffparis
ID: 13764745


1. $query = "SELECT * FROM employee WHERE Bday = '$clean_btotal' AND email = '$clean_user_id'"; is incorrect. A properly trained programmer does it this way:

$query = "SELECT * FROM employee WHERE Bday = ' . $clean_btotal . ' AND email = ' . $clean_user_id; for security reasons.




2.

  if (mysql_num_rows($result)<1) {
                    $stat = 'I didn't find any results.';
                    } else {
                         $stat = "i didn't find anything!";
                    }

If the result is less than 1 the report that there are no rows. How is that strange?



3. if(array_key_exists('userid', $_POST) and array_key_exists('btotal', $_POST)) { // would be more efficient, you are correct.



0
 
LVL 1

Expert Comment

by:jeffparis
ID: 13764762
My bad, this is it:

  if (mysql_num_rows($result)<1) {
                    $stat = 'I didn't find any results.';
                    } else {
                         $stat = "I found the data';
                    }
0
 

Author Comment

by:baxleyb
ID: 13764877
jeffparis,

thanks for all of your suggestions and I am aware of not retrieving all data over the net especially passwords, i just use the select * statement by default.

I dont think it is a scripting error but rather a database error or something else.

I believe this error is the reason why my query is failing:

Unknown column 'bday' in 'where clause'

0
 
LVL 7

Expert Comment

by:Promethyl
ID: 13764942
Correct.

Do the SHOW COLUMNS FROM employee to see the fields. If you can't go direct use ...

Try "select * from employee" and try this after your query:

for ($i = 0; $i < mysql_num_fields($result); $i++) {
   print "<th>".mysql_field_name($result, $i)."</th>\n";
}

We need to know the exact field names and capitalization.

Although you can add backticks arould the column name, I don't think that's your problem.
0
 

Author Comment

by:baxleyb
ID: 13764966
Everyone,

I did the show columns and bday is definitely there and the spelling is 'bday'

0
 
LVL 1

Expert Comment

by:jeffparis
ID: 13764967






Ok, then make sure that 'Bday exists' and remember that it is case-sensitive.


But, here is the improved code I have:



     if ( isset($_POST['userid']) AND isset($_POST['btotal']) ) {

          $clean_user_id = addslashes(trim($_POST['userid']));
          $clean_btotal = addslashes(trim($_POST['btotal']));

          $query ='SELECT * FROM employee WHERE Bday = ' . $clean_btotal . ' AND email = ' . $clean_user_id;
          $result = mysql_query($query) or die(mysql_error( ));

               if (mysql_num_rows($result)<1) {
                          $stat = 'I didn't find any results.';
                    } else {
                        $stat = "Here are the results";
                    }

          mysql_close();

     else {
          echo 'I am sorry, you did not pass all variables to the script.';
          die;
     }

0
 
LVL 7

Expert Comment

by:Promethyl
ID: 13764986
$query ='SELECT * FROM employee WHERE Bday = ' . $clean_btotal . ' AND email = ' . $clean_user_id;

This line is still wrong.

Should show:

$query ='SELECT * FROM employee WHERE `bday`=' . $clean_btotal . ' AND `email`=' . $clean_user_id;

See if that works!
0
 

Author Comment

by:baxleyb
ID: 13765750
Still getting the same error:

Unknown column 'bday' in 'where clause'

This is my result page i am using for troubleshooting:

Unknown column 'bday' in 'where clause'

birthday variable
Birthday: 03/21

userid variable
Email : jscott@serve.org

Ignore this, just another variable and has nothing to do with the query
Register :

This is where I should get something indicating that I got a result from my query
Result:

This is the query statement I am running
Query: SELECT * FROM employee WHERE email = 'jscott@serve.org' and bday = '03/21'

This is a variable to tell me if it found data or not
Stat: i didn't find anything!
0
 
LVL 7

Expert Comment

by:Promethyl
ID: 13766026
Let's take a look at those fields in the table. Have phpmyadmin?
0
 

Author Comment

by:baxleyb
ID: 13766147
HERE IS MY CREATE TABLE STATEMENT

I HOPE THIS IS HELPFUL:

create table employee (
   Last varchar(60) not null,
   Middle varchar(20),
   `First` varchar(60),
   Title varchar(80),
   City varchar(40),
   State char(2),
   Phone1 varchar(20),
   Phone2 varchar(20),
   Fax varchar(20),
   Emp_id int(4) unsigned not null auto_increment,
   email varchar(80),
   Start_month char(2),
   Start_day char(2),
   Start_year varchar(4),
   department_id int(11) not null default '0',
   SERVE_Employed enum('data','data2') not null default 'data',
   Bio blob,
   `status` enum('active','inactive') not null default 'active',
   contact_id int(11) not null default '0',
   username varchar(10),
   `password` varchar(10),
   bday varchar(40),
   Stat varchar(25) not null,
   primary key (Emp_id))
   type=MyISAM;
0
 
LVL 7

Expert Comment

by:Promethyl
ID: 13766360
Sure, but what's the table look like now. =)

It says it can't find that column... something is wrong.
0
 

Author Comment

by:baxleyb
ID: 13766416
Tell me about it, I've been racking my brain all day.

The current table structure is what I posted. If I remove the bday column from my query then the query runs with no problems.
0
 
LVL 7

Expert Comment

by:Promethyl
ID: 13766584
Make reserved word? Don't know how. Perhaps a structred procedure on another account? Try enclosing bday in backticks...

Select `bday` from employees

See what that does. Did you have access to phpmyadmin? Might clear up some of htis.
0
 
LVL 1

Expert Comment

by:jeffparis
ID: 13768001
Try this, try changing it from bday to employee.bday instead, that should not make a difference, but it will for sure tell you that it is looking into the correct table.

$query ='SELECT * FROM employee WHERE employee.bday=' . $clean_btotal . ' AND employee.email=' . $clean_user_id;

Do not enclose the name of the database field in any form of quotes, only the variables being inserted. In the instance above, the single quotes tell php that the statement is literal, except where the breaks are with the variables.

You do have at least one row created, right? This COULD cause a problem, depending on your system. WHat version of php, mysql, and what OS do you have this running on? If you have a control panel like cPanel or Plesk,that would help also.

Can we see how you are connecting to your database? Just don't inculde the real username, database name, or password.
0
 
LVL 1

Expert Comment

by:jeffparis
ID: 13768019
Also, are what exactly are oyu using to perform this query now, the same thing or something different? If we can see the whole picture, that would help a lot.
0
 
LVL 25

Expert Comment

by:Marcus Bointon
ID: 13770223
> A properly trained programmer does it this way:
> $query = "SELECT * FROM employee WHERE Bday = ' . $clean_btotal . ' AND email = ' . $clean_user_id; for security reasons.

No. This way is in fact more of a security risk because you're not quoting the insertion of variables into your SQL (thus risking an injection attack), plus it's also slower, untidier and less readable. Despite what you say, it is generally a good idea to backtick-quote table and field names just in case you happen to have used MySQL reserved words (as promethyl also noted in his last post). I also resent your fatuous 'properly trained' implication - I have two computing degrees and over 15 years professional programming experience; I know what I'm doing.
0
 

Author Comment

by:baxleyb
ID: 13969050
Sorry for the late reply, been real busy on other projects.

thanks promethly for the echo mysql_error() tip which helped out alot.

I created a new table in a different database and all of my original code worked fine.

Thanks again to everyone for their help.

BB
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question