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

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.
baxleybAsked:
Who is Participating?
 
PromethylCommented:
Add the line "if (mysql_error()) echo mysql_error(); " after every query. Find the error. Fix the error.
0
 
snoyes_jwCommented:
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
 
Marcus BointonCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
baxleybAuthor Commented:
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
 
snoyes_jwCommented:
>>>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
 
baxleybAuthor Commented:
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
 
Marcus BointonCommented:
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
 
jeffparisCommented:


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
 
jeffparisCommented:
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
 
baxleybAuthor Commented:
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
 
jeffparisCommented:


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
 
Marcus BointonCommented:
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
 
Marcus BointonCommented:
Double check that the bday column does really exist: issue a 'SHOW COLUMNS FROM employee' in the mysql client or in phpMyAdmin.
0
 
jeffparisCommented:


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
 
jeffparisCommented:
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
 
baxleybAuthor Commented:
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
 
PromethylCommented:
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
 
baxleybAuthor Commented:
Everyone,

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

0
 
jeffparisCommented:






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
 
PromethylCommented:
$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
 
baxleybAuthor Commented:
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
 
PromethylCommented:
Let's take a look at those fields in the table. Have phpmyadmin?
0
 
baxleybAuthor Commented:
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
 
PromethylCommented:
Sure, but what's the table look like now. =)

It says it can't find that column... something is wrong.
0
 
baxleybAuthor Commented:
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
 
PromethylCommented:
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
 
jeffparisCommented:
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
 
jeffparisCommented:
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
 
Marcus BointonCommented:
> 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
 
baxleybAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.