Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource [Archive]

View Full Version : Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

chriswattsuk

06-30-2008, 07:36 PM

Hi all,

I'm a newbie so please forgive me! I've tried finding the answer to this problem for a good couple of hours but not got anywhere... :confused:

I'm getting the following error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\chris\petdisplay.php on line 28

<?php
/* Program: petdisplay.php
* Desc: Displays all pets in selected category.
* /
?>
<html>
<head><title>Pet Catalog</title></head>
<body>
<?php
$user="catalog";
$host="localhost";
$password="p4ssword";
$database = "PetCatalog";
$connection = mysql_connect($host,$user,$password)
or die ("Couldn't connect to server");
$db = mysql_select_db($database,$connection)
or die ("Couldn't select database");
$pettype = "horse"; //horse was typed in a form by user
$query = "SELECT * FROM Pet WHERE petType='$pettype'";
$result = mysql_query($query)
or die ("Couldn't execute query.");

/* Display results in a table */
$pettype = ucfirst($pettype)."s";
echo "<h1>$pettype</h1>";
echo "<table cellspacing='15'>";
echo "<tr><td colspan='3'><hr></td></tr>";
while ($row = mysql_fetch_array($result))
{
extract($row);
$f_price = number_format($price,2);
echo "<tr>\n
<td>$petName</td>\n
<td>$PetDescription</td>\n
<td align='right'>\$$f_price</td>\n
</tr>\n";
echo "<tr><td colspan='3'><hr></td></tr>\n";
}
echo "</table>\n";
?>
</body></html>

Can anybody suggest what the problem might be? I'm working through my PHP + MySQL For Dummies book, but unfortunately the book doesn't offer any troubleshooting at this point! I've retyped the code and checked it many times but can't fathom the problem!

The offending line (28) is:

while ($row = mysql_fetch_array($result))

Many thanks in anticipation of any help! :)

Nile

06-30-2008, 07:39 PM

This means that your connection to the database failed. Make sure that thats correct.

chriswattsuk

06-30-2008, 08:01 PM

Thanks for your reply - I thought this piece of code throws up an error if the database connection fails?

$connection = mysql_connect($host,$user,$password)
or die ("Couldn't connect to server");

I've checked the db connection details and they're correct - I've also tried with the root localhost user. I've also double checked the case and it all seems to tally.

Any other ideas?

Nile

06-30-2008, 08:04 PM

Try this:

mysql_connect($host,$user,$password)
or die ("Couldn't connect to server because: ".mysql_error());
mysql_select_db($database)
or die ("Couldn't select database because: ".mysql_error());

And then make sure the database has also the correct name.

chriswattsuk

06-30-2008, 08:18 PM

I've inserted that code as described, still getting exactly the same output though on the page... no more details about the error.

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\chris\petdisplay.php on line 28

Thanks for your help.

Nile

07-01-2008, 12:39 AM

Well, I'm just cleaning up your code here:

<?php
/* Program: petdisplay.php
* Desc: Displays all pets in selected category.
* /
?>
<html>
<head><title>Pet Catalog</title></head>
<body>
<?php
$user="catalog";
$host="localhost";
$password="p4ssword";
$database = "PetCatalog";
mysql_connect($host,$user,$password)
or die ("Couldn't connect to server because: ".mysql_error());
mysql_select_db($database)
or die ("Couldn't select database because: ".mysql_error());
$pettype = "horse"; //horse was typed in a form by user
$query = "SELECT * FROM `Pet` WHERE `petType`='{$pettype}'";
$result = mysql_query($query)
or die ("Couldn't execute query because: ".mysql_error());

/* Display results in a table */
$pettype = ucfirst($pettype)."s";
echo <<< TABLE
<h1>$pettype</h1>
<table cellspacing='15'>
<tr><td colspan='3'><hr></td></tr>
TABLE;
while ($row = mysql_fetch_array($result))
{
extract($row);
$f_price = number_format($price,2);
echo <<<TEXTA
<tr>\n
<td>$petName</td>\n
<td>$PetDescription</td>\n
<td align='right'>\$$f_price</td>\n
</tr>\n
TEXTA;
echo "<tr><td colspan='3'><hr></td></tr>\n";
}
echo "</table>\n";
?>
</body></html>

Jas

07-01-2008, 03:46 AM

Nile: that post seems pointless, unless you made some corrections I am missing?

chriswattsuk:

Not to intrude, but that error usually has nothing to do with the connection (some times it does, though), it has to do with the query. Specifically, the message is saying that the results you are trying to turn into an array (the var $results) is not a valid MySQL result, probably because the query was wrong. Try echo-ing out the query and check for syntax errors. You could also try:

echo $query;
$results = MySQL_Query($query) or die(MySQL_Error());

And then let us know what it echo's out.

If that doesn't work, consider the table/database privileges. Do you have access to the table/database?

allahverdi

07-01-2008, 05:11 AM

umm. I think you all missed one thing.

change this:

$row = mysql_fetch_array($result)

to this:

$row = mysql_fetch_array($result, MYSQL_ASSOC)

kuau

07-01-2008, 07:11 AM

I'm just a newbie but I thought you weren't allowed to use capitals in table names (?) in MySQL.

$query = "SELECT * FROM Pet WHERE petType='$pettype'";

allahverdi

07-01-2008, 09:59 AM

kuau,

I'm not agree with you. I think it depends on table and column name.

I have used capitals and they are working fine. For example:

$result = mysql_query("SELECT * FROM tablename WHERE Id = '$id'");

kuau

07-01-2008, 10:18 AM

Your example shows the tablename as lowercase, so you seem to prove my point while claiming to disagree. (?) You are supposed to put the command reserved words in capitals and the table name in lowercase, just as you did.

I am simply suggesting that Chris might try renaming his table from 'Pet' to 'pet'. If you read about MySQL rules here: http://dev.mysql.com/doc/refman/5.1/en/name-case-sensitivity.html you'll see why they recommend using all lowercase for compatibility, portability, and to avoid having to mess with the mySQL .ini file.

allahverdi

07-01-2008, 10:22 AM

You are right they recommend. But it works ))) Actually i don't use them. But have used before...

kuau

07-01-2008, 10:47 AM

I just noticed that Chris used capitals in his database name too. I really think that is asking for trouble. I would rename the database from PetCatalog to petcatalog and make sure all table names are lowercase. MySQL automatically converts database and table names to lowercase during backup and storage - not sure what happens when it restores. It is OK to use mixed case in field names but not database or table names.

I once used a hyphen in a table name (eg. users-new) and nothing worked until I changed it to an underscore (users_new). Won't do that again.

allahverdi

07-01-2008, 11:52 AM

kuau, yes you are right. That is another problem of Chris. But problem what he asked, i answered in page 1 ^^.

P.S. I'm not sure too. But logically in restore, it must go back... :D

Jas

07-01-2008, 06:45 PM

umm. I think you all missed one thing.

change this:

$row = mysql_fetch_array($result)

to this:

$row = mysql_fetch_array($result, MYSQL_ASSOC)

How will changing the array type help? :confused: The error is not in the type of array, it is in the resource being turned into an array-- the query is not working.

BTW: there is nothing wrong with capital letters in Database names or table names. Think of them like variables-- you wouldn't call a variable $VARIABLE, but you could. I have used capital letters many times (not as common practice, mind, but sometimes it's just easier for readability).
But you should never use special chars, like the hyphen, because that does cause problems.

chriswattsuk

07-01-2008, 09:23 PM

Hi all,

And thanks ever so much for everybody's responses! I'll address each of them!

Nile: I created a new page based on your cleaned-up code, but got the following error when I ran it:

Parse error: syntax error, unexpected $end in C:\wamp\www\chris\petdisplay2.php on line 45

Jas: I'm fairly sure my query is right - when I run the query by querying the MySQL db directly it returns the relevant records. I tried inserting your code:

echo $query;
$results = MySQL_Query($query) or die(MySQL_Error());

But wasn't 100% sure where I should insert it, I placed it beneath the line:

$result = mysql_query($query)

I still received the same error as before with no more helpful info. Re privileges + permissions, I've checked this and have used the root@localhost user which has full permissions but it's still giving me the error.

Allahverdi: I tried adding MYSQL_ASSOC: as follows:

$row = mysql_fetch_array($result, MYSQL_ASSOC)

But again-no joy!

Kuau: Re the uppercase lowercase suggestion, the book I'm working from displays the databases/tables with an initial uppercase letter, and same for tables - I've copied this in my db and as you'll see further down below I've since discovered that capital letters are not an issue.

SO!!! Thanks for your responses. What I tried tonight after your suggestions was moving onto the next tutorial that my book offers based on near enough the same code... a slightly more advanced page which uses a for loop rather than a while loop.

Strangely the slightly different page loaded perfectly!! Which puts to rest any debate about uppercase/lowercase, incorrect login, privileges etc.

So, whilst this is good, my inquisitive brain isn't satisfied! Why does this code continue to fail:

<?php
/* Program: petdisplay.php
* Desc: Displays all pets in selected category.
* /
?>
<html>
<head><title>Pet Catalog</title></head>
<body>
<?php
$user="root";
$host="localhost";
$password="";
$database = "PetCatalog";
$connection = mysql_connect($host,$user,$password)
or die ("Couldn't connect to server");
$db = mysql_select_db($database,$connection)
or die ("Couldn't select database");
$pettype = "horse"; //horse was typed in a form by user
$query = "SELECT * FROM Pet WHERE petType='$pettype'";
$result = mysql_query($query)
or die ("Couldn't execute query.");

/* Display results in a table */
$pettype = ucfirst($pettype)."s";
echo "<h1>$pettype</h1>";
echo "<table cellspacing='15'>";
echo "<tr><td colspan='3'><hr></td></tr>";
while ($row = mysql_fetch_array($result))
{
extract($row);
$f_price = number_format($price,2);
echo "<tr>\n
<td>$petName</td>\n
<td>$petDescription</td>\n
<td align='right'>\$$f_price</td>\n
</tr>\n";
echo "<tr><td colspan='3'><hr></td></tr>\n";
}
echo "</table>\n";
?>
</body></html>

Whilst this code runs perfectly?!:

<?php
/* Program: petDescripFor.php
* Desc: Displays a numbered list of all pets in
* selected category.
*/
?>
<html>
<head><title>Pet Catalog</title></head>
<body>
<?php
$user="root";
$host="localhost";
$password="";
$database = "PetCatalog";
$connection = mysql_connect($host,$user,$password)
or die ("couldn't connect to server");
$db = mysql_select_db($database,$connection)
or die ("couldn't select database");
$pettype = "horse"; //horse was typed in a form by user
$query = "SELECT * FROM Pet WHERE petType='$pettype'";
$result = mysql_query($query)
or die ("Couldn't execute query.");
$nrows = mysql_num_rows($result);

/* Display results in a table */
echo "<h1>Horses</h1>";
echo "<table cellspacing='15'>";
echo "<tr><td colspan='4'><hr></td></tr>";
for ($i=0;$i<$nrows;$i++)
{
$n = $i + 1; #add 1 so that numbers don't start with 0
$row = mysql_fetch_array($result);
extract($row);
$f_price = number_format($price,2);
echo "<tr>\n
<td>$n.</td>\n
<td>$petName</td>\n
<td>$petDescription</td>\n
<td align='right'>\$$f_price</td>\n
</tr>\n";
echo "<tr><td colspan='4'><hr></td></tr>\n";
}
echo "</table>\n";
?>
</body></html>

Are there any particularly clever people who want to offer their suggestions?! :confused:

Nile

07-01-2008, 09:28 PM

Jas: I'm fairly sure my query is right - when I run the query by querying the MySQL db directly it returns the relevant records. I tried inserting your code:

echo $query;
$results = MySQL_Query($query) or die(MySQL_Error());

But wasn't 100% sure where I should insert it, I placed it beneath the line:

$result = mysql_query($query)

I still received the same error as before with no more helpful info. Re privileges + permissions, I've checked this and have used the root@localhost user which has full permissions but it's still giving me the error.

Instead of putting it under the line, replace it with the line.

allahverdi

07-02-2008, 03:53 PM

You mean this is your full code?

<?php
/* Program: petdisplay.php
* Desc: Displays all pets in selected category.
* /
?>
<html>
<head><title>Pet Catalog</title></head>
<body>
<?php
$user="root";
$host="localhost";
$password="";
$database = "PetCatalog";
$connection = mysql_connect($host,$user,$password)
or die ("Couldn't connect to server");
$db = mysql_select_db($database,$connection)
or die ("Couldn't select database");
$pettype = "horse"; //horse was typed in a form by user
$query = "SELECT * FROM Pet WHERE petType='$pettype'";
$result = mysql_query($query)
or die ("Couldn't execute query.");

/* Display results in a table */
$pettype = ucfirst($pettype)."s";
echo "<h1>$pettype</h1>";
echo "<table cellspacing='15'>";
echo "<tr><td colspan='3'><hr></td></tr>";
while ($row = mysql_fetch_array($result))
{
extract($row);
$f_price = number_format($price,2);
echo "<tr>\n
<td>$petName</td>\n
<td>$petDescription</td>\n
<td align='right'>\$$f_price</td>\n
</tr>\n";
echo "<tr><td colspan='3'><hr></td></tr>\n";
}
echo "</table>\n";
?>
</body></html>

Full copy of this???

I copied this to dw. And guess what i see? Full comment till
/* Display results in a table */

If your code full like this. (I wish it won't be). Change to it:

<?php
/* Program: petdisplay.php
* Desc: Displays all pets in selected category.
*/
?>
<html>
<head><title>Pet Catalog</title></head>
<body>
<?php
$user="root";
$host="localhost";
$password="";
$database = "PetCatalog";
$connection = mysql_connect($host,$user,$password)
or die ("Couldn't connect to server");
$db = mysql_select_db($database,$connection)
or die ("Couldn't select database");
$pettype = "horse"; //horse was typed in a form by user
$query = "SELECT * FROM Pet WHERE petType='$pettype'";
$result = mysql_query($query)
or die ("Couldn't execute query.");

/* Display results in a table */
$pettype = ucfirst($pettype)."s";
echo "<h1>$pettype</h1>";
echo "<table cellspacing='15'>";
echo "<tr><td colspan='3'><hr></td></tr>";
while ($row = mysql_fetch_array($result))
{
extract($row);
$f_price = number_format($price,2);
echo "<tr>\n
<td>$petName</td>\n
<td>$petDescription</td>\n
<td align='right'>\$$f_price</td>\n
</tr>\n";
echo "<tr><td colspan='3'><hr></td></tr>\n";
}
echo "</table>\n";
?>

Your problem was, you didn't close your comment correctly :(. But i think just you copied here incorrectly...

chriswattsuk

07-02-2008, 07:28 PM

ALLAHVERDI!!!!!!!!!!!! I don't believe it!!! That's it! :) :) :)

I'm so happy! Pathetic I know!!! Woooooaooooo!!!!

So just for nice conclusion purposes, the error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\chris\petdisplay.php on line 28

Was being caused by nothing other than.... an incorrectly closed code comment:

<?php
/* Program: petdisplay.php
* Desc: Displays all pets in selected category.
* /
?>

Which when changed to:

<?php
/* Program: petdisplay.php
* Desc: Displays all pets in selected category.
*/
?>

Worked perfectly! Thanks to everybody for their kind and helpful input! Very very strange that it showed the error as being a problem so far down the page (line 28!)

kuau

07-02-2008, 08:23 PM

Bravo Allahverdi!!!! Talk about not seeing the forest for the trees haha. If you look back, the whole thing is bright orange. Duh!

Jas

07-03-2008, 01:33 AM

Heh. I saw that when he finally put it in PHP tags, but at that point I thought it was just a typo he made when posting the code. :D

To avoid problems like that in the future, I would recomend that you get a syntax highlighter like PHP Eclipse (http://www.eclipse.org/pdt/). (Actually, PHP eclipse is a full IDE, not just a syntax highlighter.)

motormichael12

07-03-2008, 04:48 AM

umm. I think you all missed one thing.

change this:

$row = mysql_fetch_array($result)

to this:

$row = mysql_fetch_array($result, MYSQL_ASSOC)

I'm thinking that's the problem, it has to be a problem with that line... otherwise the way he has it set up it would have die()d a long time ago

allahverdi

07-03-2008, 04:53 AM

motormichael12, solved already ^^. Look at page 2 :)

I'm happy that i could help to you :D

motormichael12

07-03-2008, 04:56 AM

Oh lol didn't notice there were other pages, sorry :O

Anyway I am using that same function and had the error just yesterday and come on today to see someone else get it... weird :O

Now if all you php/mysql scripting support could redirect your attention to my topic xD

allahverdi

07-03-2008, 05:04 AM

If you want paste your code here and say the error. I think we can help you...

motormichael12

07-03-2008, 05:10 AM

Already made a topic earlier: http://www.dynamicdrive.com/forums/showthread.php?t=33994

medayn

04-12-2009, 03:04 AM

I HAVE THIS ERROR..

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in c:\program files\easyphp1-8\www\e-exam\proses_hapus2_group.php on line 13

THIS MY CODING

<?php
session_start();
include 'dbase.php';

$group=$_GET['group'];

session_register("group");
$_SESSION["group"]=$group;

//echo $_SESSION["id"]; exit;

$sql=mysql_query("SELECT * FROM kumpulan WHERE group = '$group'");
$record=mysql_fetch_array($sql, MYSQL_ASSOC);

?>

<p> </p>
<form name="form1" method="post" action="hapus_pasti_group.php">
<p align="center" class="style1" style="font-weight: bold">ANDA PASTI UNTUK PADAM MAKLUMAT INI? </p>
<p class="style1"> </p>
<table width="89" height="53" border="1" align="center">
<tr>
<td width="79" height="23" bgcolor="#990099"><div align="center" style="font-size: 14px; color: #FFFFFF"><span style="font-weight: bold">KOD PROGRAM </span></div></td>
</tr>
<tr>
<td height="22"><?php echo $record["group"];?></td>
</tr>
</table>
<div align="center"></div>
<p align="center" class="style1">
<label>
<input name="btn_hapus" type="submit" id="btn_hapus" value="PADAM" onclick "return pasti()"/>
</label>
</p>
<p align="right"><strong> MEDAYN SYSTEM </strong></p>
<p><a href="delete_program.php"><img src="image/Set-02c-june.gif" width="58" height="40" border="0"></a></p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
</form>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>

ANYBODY CAN HELP ME

borris83

04-12-2009, 05:39 AM

$sql=mysql_query("SELECT * FROM kumpulan WHERE group = '$group'");

Try splitting the above code into two lines.. Also try echoing your query itself to know what exactly it looks like

$query = "SELECT * FROM kumpulan WHERE group = '$group'";

\\You can comment out the below line for later; Now use it just for testing

echo $query;

$sql=mysql_query($query);

medayn

04-12-2009, 01:55 PM

ganu

04-13-2009, 01:53 PM

hai chriswattsuck

Please remove this code .if you want to put the html code after the php with out using <html><head><body> tags u can directly use the <tabl> <tr>..tags at any where.

at the begining of the page you should write php code only.try this.

<?php
/* Program: petdisplay.php
* Desc: Displays all pets in selected category.
* /
?>
<html>
<head><title>Pet Catalog</title></head>
<body>

Thanks & Regards
anu

Muahahah

04-13-2009, 06:16 PM

Instead of use mysql_fetch_array() use mysql_fetch_assoc(). (Could work)

ganu

04-14-2009, 05:19 AM

hai chriswattsuk,

first you delete the code at the starting of your program that is

<?php
/* Program: petdisplay.php
* Desc: Displays all pets in selected category.
* /
?>
<html>
<head><title>Pet Catalog</title></head>
<body>

why because

at the starting of the program we have to put the php code ,reason is some browsers do not allow the code.try it

bye

ganu

04-14-2009, 05:23 AM

remove this code at the starting of the program then you cant get such type of errors/warnings.

<?php
/* Program: petdisplay.php
* Desc: Displays all pets in selected category.
* /
?>
<html>
<head><title>Pet Catalog</title></head>
<body>

borris83

04-14-2009, 08:54 AM

after i try wif ur coding..
im also hav a problem..

SELECT * FROM kumpulan WHERE group = '-STANDBY-'
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in c:\program files\easyphp1-8\www\e-exam\proses_hapus2_group.php on line 19

Interesting because, I created a test database according to your query and I ran your code, but I get the same error in the same line...

I tried all I could, modifying different things, even changing the group '-STANDBY-' to just 'standby' etc...

What could be really the problem????:confused:

May be somewhere I am failing and I need to learn a lot more SQL