Page 1 of 2
Mysql/Php error
Posted: Thu Nov 20, 2003 4:15 pm
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.
Posted: Thu Nov 20, 2003 5:59 pm
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.
Posted: Thu Nov 20, 2003 8:00 pm
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?
Posted: Thu Nov 20, 2003 8:18 pm
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...
Posted: Thu Nov 20, 2003 9:07 pm
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
Posted: Thu Nov 20, 2003 10:01 pm
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?
Posted: Thu Nov 20, 2003 10:03 pm
by infolock
oops, this post was wrong. i will look at the code again
Posted: Thu Nov 20, 2003 10:04 pm
by infolock
lol lilpunkskater, you are just too fast for me

Posted: Thu Nov 20, 2003 10:06 pm
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

Posted: Thu Nov 20, 2003 10:13 pm
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
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
Posted: Fri Nov 21, 2003 12:45 am
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.
Posted: Fri Nov 21, 2003 12:50 am
by infolock
then it's either in the table for category.c_id or the table for subcategory.cat_id
Posted: Fri Nov 21, 2003 1:49 am
by silowave
so do i fix that using phpmyadmin, or is there another way?
Posted: Fri Nov 21, 2003 2:05 am
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... )
Posted: Fri Nov 21, 2003 2:49 am
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