Mysql/Php error

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

silowave
Forum Newbie
Posts: 7
Joined: Thu Nov 20, 2003 4:15 pm

Mysql/Php error

Post by silowave »

Hey I recently transfered my website to a dedicated server, along with the mysql database taht it uses and throughout the site I get this error while trying to open a picture
select * from category,subcategory,catalog where category.c_id=subcategory.cat_id and subcategory.s_id=catalog.sub_id and catalog.pr_id=
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/httpd/vhosts/mydomain.com/httpdocs/details.php on line 17

keep in mind i didnt change the code whatsoever while transfering this site over to a new server. thanks in advance for your help.
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

well, the reason it's giving you that is because it's saying your sql query is incorrect. how about posting the entire query statement you are using for the call.

best thing to do is to use or DIE statements at the end of each mysql call..

so, your statements should look something like this :

Code: Select all

<?php

mysql_connect('localhost', 'username', 'password') or die('Could not connect due to  '.mysql_error());

mysql_select_db('myDB') or die ('Unable to select DB due to  '.mysql_error());

$sql = "select * from category,subcategory,catalog where category.c_id=subcategory.cat_id and subcategory.s_id=catalog.sub_id and catalog.pr_id=......") or die('Query failed due to  '.mysql_error());

$result=mysql_query($sql) or die('Result call failed due to  '.mysql_error());

$row = mysqL_fetch_row($result) or die ('Fetch Rows failed dut to  '.mysql_error());

?>
this should at least give you an idea of what you are dealing with.
silowave
Forum Newbie
Posts: 7
Joined: Thu Nov 20, 2003 4:15 pm

Post by silowave »

thank you for that. using that or Die statement i get this error
select * from category,subcategory,catalog where category.c_id=subcategory.cat_id and subcategory.s_id=catalog.sub_id and catalog.pr_id=Query failed due to You have an error in your SQL syntax near '' at line 1
where do i go to fix that syntax.
Also ive noticed that the old server this website was on used php version 4.3.3 and the new server (from which im getting the above error) uses php version 4.2.2 Does this have anything to do with it?
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

can you post that entire query from you script ?

as in from the $sql to the ;

thanks.


edit : btw, having php 4.2.2 would not generate this error. its a problem with your actual query you are using...
silowave
Forum Newbie
Posts: 7
Joined: Thu Nov 20, 2003 4:15 pm

Post by silowave »

sorry about that. ill just show you the whole script just in case
<?php
require("constant.php");
if(!($link=mysql_pconnect($dbhost, $dblogin, $dbpass))) {
(sprintf("error connecting to host %s, by user %s", $dbhost, $dblogin)) ;
exit() ;
}
//select database
if (!mysql_select_db($dbname, $link)) {
(sprintf("Error in selecting %s database", $dbname)) ;
(sprintf("error:%d %s", mysql_errno($link),
mysql_error($link))) ;
exit() ;
}
echo "select * from category,subcategory,catalog where category.c_id=subcategory.cat_id and subcategory.s_id=catalog.sub_id and catalog.pr_id=$id";
$select=mysql_query("select * from category, subcategory, catalog where category.c_id=subcategory.cat_id and subcategory.s_id=catalog.sub_id and catalog.pr_id=$id", $link)or die('Query failed due to '.mysql_error());

$row=mysql_fetch_row($select)or die('Query failed due to '.mysql_error());

$row[10]=ereg_replace("\n", "\n<br>", $row[10]);
?>
<html>
<head>
<title>CONTI AORO Catalog</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="st.css" type="text/css">
</head>
<body bgcolor="#14334F" topmargin="0" leftmargin="0">
<table width=100% cellpadding=10 cellspacing=0 border=0>
<tr>
<td class=a0 colspan=2><strong>
<?=$row[1]?> / <?=$row[4]?> / <?=$row[7]?></strong>
</td>
</tr>
<tr><td colspan=2 align=center><img src=catalog/big<?=$row[9]?> border=1 class=table></td></tr>

<? if ($row[10]<>"") {
echo "<tr><td align=right class=a0 width=60 valign=top><strong>Description:</td><td class=a0 width=340><strong>
$row[10]</strong>
</td>
</tr>";}?>

</table>
</body>
</html>
The section in bold is the part thats causing the problem i believe. The thing is, this same code worked fine on my old server. thanks for you help
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post by d3ad1ysp0rk »

Code: Select all

echo "select * from category,subcategory,catalog where category.c_id=subcategory.cat_id and subcategory.s_id=catalog.sub_id and catalog.pr_id=$id";
why do you echo that?

Code: Select all

$select=mysql_query("select * from category, subcategory, catalog where category.c_id=subcategory.cat_id and subcategory.s_id=catalog.sub_id and catalog.pr_id=$id", $link)or die('Query failed due to '.mysql_error());
your logic seems weird.. can you explain where and what category.c_id and the other ones are?
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

oops, this post was wrong. i will look at the code again
Last edited by infolock on Thu Nov 20, 2003 10:05 pm, edited 2 times in total.
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

lol lilpunkskater, you are just too fast for me :P
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post by d3ad1ysp0rk »

i try ;)

but still.. its confusing.. cuz he's saying "WHERE var1=var2

and when u dont put a dollar sign infront ($), then it reads it as a DB variable

so what would the point of comparing DB variables??

oh well :P
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

but still.. its confusing.. cuz he's saying "WHERE var1=var2

and when u dont put a dollar sign infront ($), then it reads it as a DB variable

so what would the point of comparing DB variables??

oh well
i think he's comparing the values of tables not variables :P



anyways, this line confuses me....

Code: Select all

<php

$select=mysql_query("select * from category, subcategory, catalog where category.c_id=subcategory.cat_id and subcategory.s_id=catalog.sub_id and catalog.pr_id=$id", $link)or die('Query failed due to '.mysql_error()); 

?>

I assume the $link if your connection to the database, but why call it ? you already have a pconnect called uptop that keeps the connection alive no matter what. also, there is no reason to be sending that username/password to it again (which, again i'm assuming is what $link is for ).

Also, when calling variables inside a query, i usually escape out like this ( and this is without the $link variable cuz i don't think you need it )

Code: Select all

<?php

$select=mysql_query("select * from category, subcategory, catalog where category.c_id=subcategory.cat_id and subcategory.s_id=catalog.sub_id and catalog.pr_id='".$id."'")or die('Query failed due to '.mysql_error()); 

?>

try that, and see if it works. there might be something else i'm missing here...i'll post it if i find it
silowave
Forum Newbie
Posts: 7
Joined: Thu Nov 20, 2003 4:15 pm

Post by silowave »

k i mustve forgotten to mention that this isnt my code, its just a webiste of a friend i uploaded to my server and i hardly know any php, i do c++.
I removed the $link that you guys said was irrelevant and i also changed the last part of the code to your version. I keep getting
Query failed due to You have an error in your SQL syntax near '' at line 1"
So i dont know where to look for the error in the syntax. any changes you guys think would help, id like to hear. thanx once again.
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

then it's either in the table for category.c_id or the table for subcategory.cat_id
silowave
Forum Newbie
Posts: 7
Joined: Thu Nov 20, 2003 4:15 pm

Post by silowave »

so do i fix that using phpmyadmin, or is there another way?
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

really man, i don't know cuz i'm not sure what that $link did.

instead, i'll give you an idea of what this query is doing, and you'll have to go through your code and find out what is messig it up.

$select=mysql_query( this tells php you are gonna do a query of the following statement:


"select * ( this says "select everything" )

from category, subcategory, catalog (these are your tables, category, subcategory, and catalog )

where category.c_id=subcategory.cat_id (says to select everything where the field c_id in your category table is equal to cat_id in your subcategory table )

and subcategory.s_id=catalog.sub_id and catalog.pr_id='".$id."'") (says to also select everything where the s_id in your subcategory table is equal to $id of your catalog table )

or die('Query failed due to '.mysql_error()); (ends the query and asks if there was an error ).


After looking at it, i think I know what your problem is. The problem may be that you aren't populating the variable $id with any information.

is there any way i can see your code, i can probably fix this in a matter of seconds.



edit : I mean ur code for the constant.php

also put the $link back in the query as it was before til we figure this out.


argh, edit 2 : make sure you edit out any sensitive informatoin ( such as usernames or passwords... )
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Doesn't look like $id is getting passed to the SQL statement - probably a register_globals problem, have a read of:

viewtopic.php?t=511

Mac
Post Reply