Solved

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

Posted on 2007-11-17
44
328 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 19
  • 19
  • 6
44 Comments
 
LVL 21

Expert Comment

by:nizsmo
ID: 20306666
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
ID: 20306677
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
ID: 20306682
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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

Author Comment

by:manu_nicholas
ID: 20306707
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
ID: 20306711
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
ID: 20306716
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
ID: 20306722
hmm the error message doesn't look like it matches your config.php file :\
0
 

Author Comment

by:manu_nicholas
ID: 20306735
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
ID: 20306741
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
ID: 20306757
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
ID: 20306764
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
ID: 20306769
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
ID: 20306791
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
ID: 20306807
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
ID: 20306811
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
ID: 20306820
Yes, it is.

No success with rewriting the query however.
0
 
LVL 21

Expert Comment

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

echo $query;
0
 
LVL 7

Expert Comment

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

Expert Comment

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

Author Comment

by:manu_nicholas
ID: 20306828
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
ID: 20306830
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
ID: 20306831
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
 
LVL 7

Expert Comment

by:dansoto
ID: 20306834
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
ID: 20306849
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
ID: 20306852
sure dansoto - how do I do that?
0
 
LVL 21

Expert Comment

by:nizsmo
ID: 20306854
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
ID: 20306861
yep okay, sure that works fine and returns the correct details.
0
 
LVL 21

Expert Comment

by:nizsmo
ID: 20306865
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
ID: 20306869
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
ID: 20306871
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
ID: 20306878
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
ID: 20306884
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
ID: 20306886
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
ID: 20306889
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
ID: 20306915
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
ID: 20306922
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
ID: 20306947
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
ID: 31409768
Lots of trial and error - checking and double checking.
0
 
LVL 21

Expert Comment

by:nizsmo
ID: 20306956
no problem! glad it is working!! finally! :)
0
 
LVL 21

Expert Comment

by:nizsmo
ID: 20306959
where from NZ are you from? i'm from Hamilton :)
0
 

Author Comment

by:manu_nicholas
ID: 20306968
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
ID: 20306973
ahh PHP eh all good! i just graduated too this semester :) so we similar afterall haha
0
 

Author Comment

by:manu_nicholas
ID: 20306976
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
ID: 20306980
all good! goodluck! :)
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

728 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