Solved

How can I fix my MySQL Query that is not returning resource in PHP?

Posted on 2007-11-17
44
311 Views
Last Modified: 2013-12-13
Im using a bunch of tutorials and examples to try and piece together the script for displaying an image stored in my database.

My script for downloading the image is stored separately, so that my "header" tags can eventually be passed, but at present, I am not able to connect to the database!

I have included a script to connect to the database (that works in another file) but fails to work here. Is there something that I am missing?  This is the current mysql_error = Invalid query: Access denied for user 'apache'@'localhost' (using password: NO)
<?php
 

if(isset($_GET['id'])) 

{ 

        include 'scripts/config.php'; 

		

        $id = $_GET['id']; 

        $query = "SELECT filename, filetype, filesize, bin_data FROM tbl_Files WHERE id_files = '$id'"; 

	$result = mysql_query($query);

		

	// Check result

	// This shows the actual query sent to MySQL, and the error. Useful for debugging.

	if (!$result) {

		$message  = 'Invalid query: ' . mysql_error() . "\n";

		$message .= 'Whole query: ' . $query;

		die($message);

	}

	

        //list($name, $type, $size, $content) = mysql_fetch_array($result); 
 

        //header("Content-Disposition: attachment; filename=$name"); 

        //header("Content-length: $size"); 

        //header("Content-type: $type"); 

        //echo $content; 
 

        include 'scripts/closedb.php'; 

        exit; 

} 

?>

Open in new window

0
Comment
Question by:manu_nicholas
  • 19
  • 19
  • 6
44 Comments
 
LVL 21

Expert Comment

by:nizsmo
Comment Utility
looks like you have either the wrong username or password to connect to your database.

the default is 'root' with no password, maybe you can give that a try?

mysql_connect('localhost','root','');
0
 

Author Comment

by:manu_nicholas
Comment Utility
Thats what it looks like yes, but I have double double checked my "include 'scripts/config.php';" file.
I am also including the same config.php file in another page which reads information from the database no problem.  

Either which way, the database I want to connect to is remote, not localhost.
0
 
LVL 21

Expert Comment

by:nizsmo
Comment Utility
oh it is remote? then why is your error "apache'@'localhost" (quoting from your original question)?
also has this "(using password: NO)" in your error, which means you did not enter a password.

Maybe you are including the wrong config.php?
0
 

Author Comment

by:manu_nicholas
Comment Utility
Nope - im completely stumped.

I just renamed my config.php file, and updated all links & includes to reflect this, incase there was something to do with .. i dont know, a file duplication or something on the server.  

I dont suppose you know whether it has anything to do with the web server itself?  Whether I have to many db connections or something? I've been working with PHP all of two weeks so still have plenty to learn about doing things the right way.
0
 
LVL 21

Expert Comment

by:nizsmo
Comment Utility
hmm how about showing your config file (but replace your sensitive data with maybe xxxxx)?

Or maybe you can manually try to connect (without using the config.php):

$conn = mysql_connect('your_server','username','password);
if(!$conn)
   die("cannot connect to server!");

does that work?
0
 

Author Comment

by:manu_nicholas
Comment Utility
Sure here is the config (recently renamed sivacon just to test).

Will try the manual connection now and let you know.
<?php

session_start();
 

// db properties

$dbhost = 'bcs.net.nz';

$dbuser = 'xxxxx'; 

$dbpass = 'xxxxx';    

$dbname = 'xxxxx';
 

// make a connection to mysql here

$conn = mysql_connect ($dbhost, $dbuser, $dbpass) or die ("Sorry, Im unable to connect you to the database at this time. <br><br> Error: " . mysql_error());

mysql_select_db ($dbname) or die ("Sorry, Im unable to connect you to the database '$dbname' at this time. <br><br> Error: " . mysql_error());

?>

Open in new window

0
 
LVL 21

Expert Comment

by:nizsmo
Comment Utility
hmm the error message doesn't look like it matches your config.php file :\
0
 

Author Comment

by:manu_nicholas
Comment Utility
Hmm, no it doesnt.  That error is being output from my display.php file (code at the top)..approximately lines 13-16.
0
 
LVL 21

Expert Comment

by:nizsmo
Comment Utility
ya i think it is using the wrong connection, it is using a localhost connection when i think it should be using the bcs.net.nz connection to connect to the database.

So manual connection doesn't work?
0
 

Author Comment

by:manu_nicholas
Comment Utility
No the manual connection hasnt worked.

The link at http://www.bcs.net.nz/~manun/resources.php includes config.php and obviously does so, as it displays the files / links I want it to, from mysql.  

I think php/mysql is trying to play games with me because it knows its all due tomorrow :S
0
 
LVL 21

Expert Comment

by:nizsmo
Comment Utility
Try replace this line in your code:
$result = mysql_query($query);

with this line:
$result = mysql_query($query,$conn);
0
 
LVL 7

Expert Comment

by:dansoto
Comment Utility
That error sometimes (misleading as it may be) comes from selecting a table that doesn't exist.  This can happen in a couple of ways:

1) Selecting the wrong table in  your connection parameters
2) Not being mindful of the case of the table names.  Table names are case sensitive.

Those are just a couple of things that come to mind to try....
0
 

Author Comment

by:manu_nicholas
Comment Utility
Okay cool, that got rid of the localhost error :)

BUT I still dont think my $result is being set... because the if statement below is still being run (although there is no error being put out at mysql_error() any more...
// Check result

// This shows the actual query sent to MySQL, and the error. Useful for debugging.

if (!$result) {

	$message  = 'Invalid query: ' . mysql_error() . "\n";

         $message .= 'Whole query: ' . $query;

	die($message);

}

Open in new window

0
 
LVL 21

Expert Comment

by:nizsmo
Comment Utility
so it is still going into the die($message) bit?

Try changing this line in your script:
$query = "SELECT filename, filetype, filesize, bin_data FROM tbl_Files WHERE id_files = '$id'";

to this:
$query = "SELECT filename, filetype, filesize, bin_data FROM tbl_Files WHERE id_files = $id";

annd see if ti works. looks like something wrong with your query.
0
 
LVL 7

Expert Comment

by:dansoto
Comment Utility
Try echoing out some of the variables to make sure they are getting set correctly:

echo $id;

echo $query;

Open in new window

0
 

Author Comment

by:manu_nicholas
Comment Utility
Yes, it is.

No success with rewriting the query however.
0
 
LVL 21

Expert Comment

by:nizsmo
Comment Utility
what do you get qhey you echo the query out as dansoto suggested?

echo $query;
0
 
LVL 7

Expert Comment

by:dansoto
Comment Utility
So when you echo $query it shows an id number in place of '$id' ?
0
 
LVL 7

Expert Comment

by:dansoto
Comment Utility
I suspect nothing ... but I've been known to be wrong on more than one occasion ..
0
 

Author Comment

by:manu_nicholas
Comment Utility
Hi Dansoto - yes my two variables are being stored.
$id and $query are both being output in my error message.

I totally appreciate all these suggestions!
$query = "SELECT filename, filetype, filesize, bin_data FROM tbl_Files WHERE id_files = $id";
 

$result = mysql_query($query,$conn);

		

	// Check result

	// This shows the actual query sent to MySQL, and the error. Useful for debugging.

	if (!$result) {

		$message  = 'Invalid query: ' . mysql_error() . "\n";

		$message .= 'Whole query: ' . $query;

		die($message);

	}

Open in new window

0
 

Author Comment

by:manu_nicholas
Comment Utility
yes, my $id is displayed at the correct id which has been passed from another php file using ?.

0
 
LVL 21

Expert Comment

by:nizsmo
Comment Utility
can you copy your exact query from echo $query so that we can see if anything is wrong with the query?

Basically make sure:
1. $id actually contains an id
2. table name tbl_Files actually exist
3. These fields filename, filetype, filesize, bin_data exist in the table tbl_Files (yes, all of them have to exist)

any of these can be causing the error.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 7

Expert Comment

by:dansoto
Comment Utility
Also, to see if the query is actually valid, run the query from a command line or query browser substituting real values for variables.  That will eliminate php from the puzzle.
0
 

Author Comment

by:manu_nicholas
Comment Utility
No problem:
PHP (echo $query):
$query = "SELECT filename, filetype, filesize, bin_data FROM tbl_Files WHERE id_files = $id";

HTML:
SELECT filename, filetype, filesize, bin_data FROM tbl_Files WHERE id_files = 7

Yes, I have checked and checked again my tbl_Files names were matching. I actually cut and pasted it from my resources.php file -> which uses a the same SELECT query but calls limited fields.
0
 

Author Comment

by:manu_nicholas
Comment Utility
sure dansoto - how do I do that?
0
 
LVL 21

Expert Comment

by:nizsmo
Comment Utility
do you have phpmyadmin?

copy and paste the query:
SELECT filename, filetype, filesize, bin_data FROM tbl_Files WHERE id_files = 7

in there and see if it executes properly.
0
 

Author Comment

by:manu_nicholas
Comment Utility
yep okay, sure that works fine and returns the correct details.
0
 
LVL 21

Expert Comment

by:nizsmo
Comment Utility
it does?? hmmmmm and yet you are getting into this if statement:

$query = "SELECT filename, filetype, filesize, bin_data FROM tbl_Files WHERE id_files = $id";
$result = mysql_query($query,$conn);

if (!$result) {
                $message  = 'Invalid query: ' . mysql_error() . "\n";
                $message .= 'Whole query: ' . $query;
                die($message);
        }

?? that is some weird stuff if you are, as there is nothing wrong with your query.
0
 

Author Comment

by:manu_nicholas
Comment Utility
Yes.. thats what I thought. Was actually hoping it was something obvious, but it appears not to be the case. Damn.
0
 
LVL 21

Expert Comment

by:nizsmo
Comment Utility
if you echo $result; what does it print out?

Also as a debug thing, try:
$query = "SELECT * FROM tbl_Files";
$result = mysql_query($query,$conn);

if (!$result) {
                $message  = 'Invalid query: ' . mysql_error() . "\n";
                $message .= 'Whole query: ' . $query;
                die($message);
        }

0
 
LVL 7

Expert Comment

by:dansoto
Comment Utility
Yeah...don't see how it's going into the 'if' statement if there is a result.  Maybe trying printing a result now?
Just a simple test for output...
while ($row = mysql_fetch_array($result)) {

echo $row['filename'];

echo '<br>';

}

Open in new window

0
 
LVL 21

Expert Comment

by:nizsmo
Comment Utility
Also try manually setting the db again (just to make sure), make sure you replace your_db_name with your actual db name:

$query = "SELECT * FROM tbl_Files";
mysql_select_db ("your_db_name",$conn) or die ("Sorry, Im unable to connect you to the database '$dbname' at this time. <br><br> Error: " . mysql_error());

$result = mysql_query($query,$conn);

if (!$result) {
                $message  = 'Invalid query: ' . mysql_error() . "\n";
                $message .= 'Whole query: ' . $query;
                die($message);
        }
0
 

Author Comment

by:manu_nicholas
Comment Utility
The code below returns a blank page, nothing is displayed from $result.
<?php
 

if(isset($_GET['id'])) 

{ 

	include 'scripts/config.php'; 

		

        $id = $_GET['id']; 

		$query = "SELECT * FROM tbl_Files";

                  $result = mysql_query($query,$conn);

		echo $result;

}

Open in new window

0
 
LVL 21

Expert Comment

by:nizsmo
Comment Utility
how about trying the simplified query about 3 posts up and also trying to select the db again in my last post?
0
 

Author Comment

by:manu_nicholas
Comment Utility
Okay, something different.
Its giving me the error saying its not connecting to my database.
0
 
LVL 21

Accepted Solution

by:
nizsmo earned 500 total points
Comment Utility
until you get it working, i suggest you move everything into 1 file, and comment the include config.php.

so your code will look something like this:
(change the details accordingly)
<?php

$conn = mysql_connect('your_host','username','password'); // adjust accordingly
 

if(!$conn)

    die("Error: " . mysql_error());
 

$dbname = "your_db_name";

$query = "SELECT * FROM tbl_Files";
 

mysql_select_db ($dbname,$conn) or die ("Sorry, Im unable to connect you to the database '$dbname' at this time. <br><br> Error: " . mysql_error());
 

$result = mysql_query($query,$conn);
 

if (!$result) {

                $message  = 'Invalid query: ' . mysql_error() . "\n";

                $message .= 'Whole query: ' . $query;

                die($message);

        }

        echo "error in your sql statement! Your statement is: $query";

                                    

?>

Open in new window

0
 

Author Comment

by:manu_nicholas
Comment Utility
wowo - its working!
I just put it all in one file and walah. I swear I did that before when you first mentioned it all of 30 posts ago?

However, yay! I really appreciate all your help on that one. Cheers!
0
 

Author Closing Comment

by:manu_nicholas
Comment Utility
Lots of trial and error - checking and double checking.
0
 
LVL 21

Expert Comment

by:nizsmo
Comment Utility
no problem! glad it is working!! finally! :)
0
 
LVL 21

Expert Comment

by:nizsmo
Comment Utility
where from NZ are you from? i'm from Hamilton :)
0
 

Author Comment

by:manu_nicholas
Comment Utility
Tauranga :) this is all for work on my final paper for a degree (like I said, due tomorrow, so hmmmm), think it will be a late night :S
0
 
LVL 21

Expert Comment

by:nizsmo
Comment Utility
ahh PHP eh all good! i just graduated too this semester :) so we similar afterall haha
0
 

Author Comment

by:manu_nicholas
Comment Utility
cool, congrats on that! Yeah, two more weeks to go then its all over and done with! Cant wait :) But cheers again for your help! Have a good night :D
0
 
LVL 21

Expert Comment

by:nizsmo
Comment Utility
all good! goodluck! :)
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now