DaniWeb Forum Index > Web Development > PHP

MY PHP MySQL Function keeps returning error message: "Database Query Failed"

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Aug 2009

Posts: 5

Reputation: HFK11 is an unknown quantity at this point

Solved Threads: 0

HFK11

Offline

Newbie Poster

MY PHP MySQL Function keeps returning error message: "Database Query Failed"

Aug 31st, 2009

I have been working on this problem for a few weeks now and everything I have tried will not work. I am new to working with PHP and MySQL and have never use a forum, so you can say I am new to everything right now.

When I try to load the page in the web browser, I get this error message:

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /Users/herkinsfrancois/Sites/divine_endings/inculudes/function.php on line 33
Database query failed:

Here is the function I have been using and line 33 is the line in red.

function get_subject_by_id($subject_id) {
global $connection;
$query = "SELECT * ";
$query .= "FROM subjects ";
$query .= "WHERE id=" . $subject_id ." ";
$query .= "LIMIT 1";
$result_set = mysql_query($query, $connection);
confirm_query($result_set);
// REMEMBER:
// if no rows are returned, fetch_array will return false
if ($subject = mysql_fetch_array($result_set)) {
return $subject;
} else {
return NULL;
}
}

Any suggestions or feedback would be greatly appreciated!

-HFK

•

Join Date: Jan 2009

Posts: 1,312

Reputation: almostbob has a spectacular aura about

Solved Threads: 161

almostbob

Offline

Nearly a Posting Virtuoso

Re: MY PHP MySQL Function keeps returning error message: "Database Query Failed"

Aug 31st, 2009

just stating that $connection is global won't help much
$connection (see php.net mysql_connect) is ususally defined earlier than line 33 but after the global, something like

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$connection="mysql_connect('localhost', 'admin', '1admin') or die(mysql_error())";// host account password
$connection="mysql_connect('localhost', 'admin', '1admin') or die(mysql_error())";// host account password

which may be included in a 'connect.php' script if $connection is also accesssed elsewhere, from the look of the error message, there is no database connection
if there is a $connection defined, are the server account and password correct

Last edited by almostbob; Aug 31st, 2009 at 1:24 pm.

Failure is not an option It's included free
If at first you dont succeed, join the club
Of course its always in the last place you look, you dont keep looking after you find it

Please mark solved problems, solved

•

Join Date: Sep 2008

Posts: 210

Reputation: chrishea is an unknown quantity at this point

Solved Threads: 32

chrishea

Offline

Posting Whiz in Training

Re: MY PHP MySQL Function keeps returning error message: "Database Query Failed"

Aug 31st, 2009

I have had problems in the past trying to pass a resource like this and make it work. It was simpler to open a new connection. You will end up with more open connections and that might be a problem in some cases but it has never been a problem for me.

Having said that, it may also be that your original connect didn't work or you have lost the pointer before it was passed.

Last edited by chrishea; Aug 31st, 2009 at 1:44 pm.

Chris
See my list of PHP development tools at:
InnovationsDesign.net

•

Join Date: Aug 2009

Posts: 5

Reputation: HFK11 is an unknown quantity at this point

Solved Threads: 0

HFK11

Offline

Newbie Poster

Re: MY PHP MySQL Function keeps returning error message: "Database Query Failed"

Aug 31st, 2009

Oh Ok, I am going to work at it some more with all your suggestions and keep you updated on any solutions! Thanks so much for both of your help! This online forum stuff is kinda nice! Stay Tuned!!!

-HFK

•

Join Date: Aug 2009

Posts: 5

Reputation: HFK11 is an unknown quantity at this point

Solved Threads: 0

HFK11

Offline

Newbie Poster

Re: MY PHP MySQL Function keeps returning error message: "Database Query Failed"

Sep 1st, 2009

Ok, so the function I have posted earlier is still not working. When I take this function out of the code, everything else works. The rest of the coding is able to access the database, but when I include this one it does not work and gives me the error code. Something about the "query" is not working.

I do have the

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
 1.
      $connection="mysql_connect('localhost', 'admin', '1admin') or die(mysql_error())";// host account password
 1.
      $connection="mysql_connect('localhost', 'admin', '1admin') or die(mysql_error())";// host account password

as you suggested already in my "includes" page and it is working for the other functions. Its just that line 33 that is not working.

Here is everything I have on my function.php page:

Help with Code Tags

(Toggle Plain Text)

<?php
       function confirm_query($result_set) {
               if (!$result_set) {
                       die("Database query failed: " . mysql_error());
               }
       }
       
       function get_all_subjects() {
               $query = "SELECT *
                               FROM subjects
                               ORDER BY position ASC";
               $subject_set = mysql_query($query);
               confirm_query($subject_set);
               return $subject_set;
       }
       
       function get_all_for_subject($subject_id) {
               $query = "SELECT *
                       FROM pages
                       WHERE subject_id = {$subject_id}
                       ORDER BY position ASC";
               $pages_set = mysql_query($query, $connection);
               confirm_query($pages_set);
               return $pages_set;
       }
       
       function get_subject_by_id($subject_id) {
              
               $query = "SELECT * ";
               $query .= "FROM subjects ";
               $query .= "WHERE id=" . $subject_id ." ";
               $query .= "LIMIT 1";
               $result_set = mysql_query($query, $connection);
               confirm_query($result_set);
               // REMEMBER:
               // if no rows are returned, fetch_array will return false
               if ($subject = mysql_fetch_array($result_set)) {
                       return $subject;
               } else {
                       return NULL;
               }
       }
?>

Not sure where to go from here?!?! Any other suggestions? Thanks for your help thus far!

•

Join Date: Jan 2009

Posts: 1,312

Reputation: almostbob has a spectacular aura about

Solved Threads: 161

almostbob

Offline

Nearly a Posting Virtuoso

Re: MY PHP MySQL Function keeps returning error message: "Database Query Failed"

Sep 1st, 2009

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$query = "SELECT * FROM subjects  WHERE id='$subject_id' LIMIT 1";
//
$query = "SELECT * ";
$query .= "FROM subjects ";
$query .= "WHERE id=" . $subject_id ." ";
$query .= "LIMIT 1";
$query = "SELECT * FROM subjects  WHERE id='$subject_id' LIMIT 1";
//
$query = "SELECT * ";
$query .= "FROM subjects ";
$query .= "WHERE id=" . $subject_id ." ";
$query .= "LIMIT 1";

text field quotes? there arent any in the concatenated version

Last edited by almostbob; Sep 1st, 2009 at 6:43 am.

•

Join Date: Aug 2009

Posts: 5

Reputation: HFK11 is an unknown quantity at this point

Solved Threads: 0

HFK11

Offline

Newbie Poster

Re: MY PHP MySQL Function keeps returning error message: "Database Query Failed"

Sep 1st, 2009

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
 $connection="mysql_connect('localhost', 'admin', '1admin') or die(mysql_error())";// host account password
 $connection="mysql_connect('localhost', 'admin', '1admin') or die(mysql_error())";// host account password

as you suggested already in my "includes" page and it is working for the other functions. Its just that line 33 that is not working.

Here is everything I have on my function.php page:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
 <?php
       function confirm_query($result_set) {
               if (!$result_set) {
                       die("Database query failed: " . mysql_error());
               }
       }
 
       function get_all_subjects() {
               $query = "SELECT *
                               FROM subjects
                               ORDER BY position ASC";
               $subject_set = mysql_query($query);
               confirm_query($subject_set);
               return $subject_set;
       }
 
       function get_all_for_subject($subject_id) {
               $query = "SELECT *
                       FROM pages
                       WHERE subject_id = {$subject_id}
                       ORDER BY position ASC";
               $pages_set = mysql_query($query, $connection);
               confirm_query($pages_set);
               return $pages_set;
       }
 
       function get_subject_by_id($subject_id) {
 
               $query = "SELECT * ";
               $query .= "FROM subjects ";
               $query .= "WHERE id=" . $subject_id ." ";
               $query .= "LIMIT 1";
               $result_set = mysql_query($query, $connection);
               confirm_query($result_set);
               // REMEMBER:
               // if no rows are returned, fetch_array will return false
               if ($subject = mysql_fetch_array($result_set)) {
                       return $subject;
               } else {
                       return NULL;
               }
       }
?>
 <?php
       function confirm_query($result_set) {
               if (!$result_set) {
                       die("Database query failed: " . mysql_error());
               }
       }
       
       function get_all_subjects() {
               $query = "SELECT *
                               FROM subjects
                               ORDER BY position ASC";
               $subject_set = mysql_query($query);
               confirm_query($subject_set);
               return $subject_set;
       }
       
       function get_all_for_subject($subject_id) {
               $query = "SELECT *
                       FROM pages
                       WHERE subject_id = {$subject_id}
                       ORDER BY position ASC";
               $pages_set = mysql_query($query, $connection);
               confirm_query($pages_set);
               return $pages_set;
       }
       
       function get_subject_by_id($subject_id) {
              
               $query = "SELECT * ";
               $query .= "FROM subjects ";
               $query .= "WHERE id=" . $subject_id ." ";
               $query .= "LIMIT 1";
               $result_set = mysql_query($query, $connection);
               confirm_query($result_set);
               // REMEMBER:
               // if no rows are returned, fetch_array will return false
               if ($subject = mysql_fetch_array($result_set)) {
                       return $subject;
               } else {
                       return NULL;
               }
       }
?>

Is this what you meant by text field quotes? Not sure where to go from here?!?! Any other suggestions? Thanks for your help thus far!

•

Join Date: Jan 2009

Posts: 1,312

Reputation: almostbob has a spectacular aura about

Solved Threads: 161

almostbob

Offline

Nearly a Posting Virtuoso

Re: MY PHP MySQL Function keeps returning error message: "Database Query Failed"

Sep 1st, 2009

in a where clause

where column operator number

is ok

where column operator 'text'

text is quoted

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
query = "SELECT * ";
$query .= "FROM subjects ";
$query .= "WHERE id='" . $subject_id ."' ";// note extra single quotes around $subject
$query .= "LIMIT 1";
query = "SELECT * ";
$query .= "FROM subjects ";
$query .= "WHERE id='" . $subject_id ."' ";// note extra single quotes around $subject
$query .= "LIMIT 1";

my opinion (humble or otherwise) this is a bad way to set out a query, such a risk of typo, all the joins parse to

Help with Code Tags

php Syntax (Toggle Plain Text)

$query = "SELECT * FROM subjects  WHERE id='$subject_id' LIMIT 1";

is simpler, variables parse inside dquotes. or at least

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$query = "SELECT * FROM subjects  WHERE id='".$subject_id."' LIMIT 1";
$query = "SELECT * FROM subjects  WHERE id='".$subject_id."' LIMIT 1";

without some quote mysql sql will assume the text is a resource :- column, table,dbase -: name

Last edited by almostbob; Sep 1st, 2009 at 3:11 pm.

•

Join Date: Aug 2009

Posts: 5

Reputation: HFK11 is an unknown quantity at this point

Solved Threads: 0

HFK11

Offline

Newbie Poster

Re: MY PHP MySQL Function keeps returning error message: "Database Query Failed"

Sep 2nd, 2009

Hello,

I have changed over the codes as you have suggested to make things simpler and my result is still coming up with the same error message... I have even rewrote it to match the two other functions that are working and it still gives me the same error.

Is there another particular way you would write this code. What I am trying to do is write the function to display the subject names on the content page for my website. My functions for pages and subjects work fine and they access the database, but this one has me stumbled.

I am still open to suggestions. Thanks so much for all your efforts with this! I will still be plugging away with it.

•

Join Date: Sep 2009

Posts: 1

Reputation: starchild.no1 is an unknown quantity at this point

Solved Threads: 0

starchild.no1 starchild.no1 is offline

Offline

Newbie Poster

Re: MY PHP MySQL Function keeps returning error message: "Database Query Failed"

#10

Sep 18th, 2009

•

Originally Posted by HFK11

I have been working on this problem for a few weeks now and everything I have tried will not work. I am new to working with PHP and MySQL and have never use a forum, so you can say I am new to everything right now.

When I try to load the page in the web browser, I get this error message:

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /Users/herkinsfrancois/Sites/divine_endings/inculudes/function.php on line 33
Database query failed:

Here is the function I have been using and line 33 is the line in red.

function get_subject_by_id($subject_id) {
global $connection;
$query = "SELECT * ";
$query .= "FROM subjects ";
$query .= "WHERE id=" . $subject_id ." ";
$query .= "LIMIT 1";
$result_set = mysql_query($query, $connection);
confirm_query($result_set);
// REMEMBER:
// if no rows are returned, fetch_array will return false
if ($subject = mysql_fetch_array($result_set)) {
return $subject;
} else {
return NULL;
}
}

Any suggestions or feedback would be greatly appreciated!

-HFK

----------------------------------------------------------------------------
Hi,
I am new to PHP and Ruby on Rails
I don't know if you figured it out but the problem is in the WHERE clause. if you comment it out your page will work.
for some reason it is being passed as a string.

So do it this way:
$query .= "WHERE id=' " . $subject_id . " ' ";

it will work by wrapping single quotes around subject_id
i am still trying to figure out why though. I am thinking maybe its the version of PHP you are using.