Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 369
  • Last Modified:

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

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
manu_nicholas
Asked:
manu_nicholas
  • 19
  • 19
  • 6
1 Solution
 
nizsmoDeveloperCommented:
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
 
manu_nicholasAuthor Commented:
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
 
nizsmoDeveloperCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

 
manu_nicholasAuthor Commented:
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
 
nizsmoDeveloperCommented:
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
 
manu_nicholasAuthor Commented:
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
 
nizsmoDeveloperCommented:
hmm the error message doesn't look like it matches your config.php file :\
0
 
manu_nicholasAuthor Commented:
Hmm, no it doesnt.  That error is being output from my display.php file (code at the top)..approximately lines 13-16.
0
 
nizsmoDeveloperCommented:
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
 
manu_nicholasAuthor Commented:
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
 
nizsmoDeveloperCommented:
Try replace this line in your code:
$result = mysql_query($query);

with this line:
$result = mysql_query($query,$conn);
0
 
dansotoCommented:
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
 
manu_nicholasAuthor Commented:
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
 
nizsmoDeveloperCommented:
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
 
dansotoCommented:
Try echoing out some of the variables to make sure they are getting set correctly:

echo $id;
echo $query;

Open in new window

0
 
manu_nicholasAuthor Commented:
Yes, it is.

No success with rewriting the query however.
0
 
nizsmoDeveloperCommented:
what do you get qhey you echo the query out as dansoto suggested?

echo $query;
0
 
dansotoCommented:
So when you echo $query it shows an id number in place of '$id' ?
0
 
dansotoCommented:
I suspect nothing ... but I've been known to be wrong on more than one occasion ..
0
 
manu_nicholasAuthor Commented:
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
 
manu_nicholasAuthor Commented:
yes, my $id is displayed at the correct id which has been passed from another php file using ?.

0
 
nizsmoDeveloperCommented:
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
 
dansotoCommented:
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
 
manu_nicholasAuthor Commented:
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
 
manu_nicholasAuthor Commented:
sure dansoto - how do I do that?
0
 
nizsmoDeveloperCommented:
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
 
manu_nicholasAuthor Commented:
yep okay, sure that works fine and returns the correct details.
0
 
nizsmoDeveloperCommented:
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
 
manu_nicholasAuthor Commented:
Yes.. thats what I thought. Was actually hoping it was something obvious, but it appears not to be the case. Damn.
0
 
nizsmoDeveloperCommented:
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
 
dansotoCommented:
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
 
nizsmoDeveloperCommented:
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
 
manu_nicholasAuthor Commented:
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
 
nizsmoDeveloperCommented:
how about trying the simplified query about 3 posts up and also trying to select the db again in my last post?
0
 
manu_nicholasAuthor Commented:
Okay, something different.
Its giving me the error saying its not connecting to my database.
0
 
nizsmoDeveloperCommented:
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
 
manu_nicholasAuthor Commented:
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
 
manu_nicholasAuthor Commented:
Lots of trial and error - checking and double checking.
0
 
nizsmoDeveloperCommented:
no problem! glad it is working!! finally! :)
0
 
nizsmoDeveloperCommented:
where from NZ are you from? i'm from Hamilton :)
0
 
manu_nicholasAuthor Commented:
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
 
nizsmoDeveloperCommented:
ahh PHP eh all good! i just graduated too this semester :) so we similar afterall haha
0
 
manu_nicholasAuthor Commented:
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
 
nizsmoDeveloperCommented:
all good! goodluck! :)
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.

Join & Write a Comment

Featured Post

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.

  • 19
  • 19
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now