Simple but annoying problem when querying multiple tables from samedatabase

20/12/2007, 18h49

Hi,

I am using PHP to query two tables in the same database, but am
getting a "mysql_query(): supplied resource is not a valid MySQL-Link
resource" error.

$hostname_story = "mysql.mydomain.com";
$database_story = "cms";
$username_story = "user";
$password_story = "password";
$story = mysql_pconnect($hostname_story, $username_story,
$password_story) or trigger_error(mysql_error(),E_USER_ERROR);

$colname_story = "-1";
if (isset($_GET['id'])) {
$colname_story = (get_magic_quotes_gpc()) ? $_GET['id'] :
addslashes($_GET['id']);
}
mysql_select_db($database_story, $story);
$query_story = sprintf("SELECT * FROM story WHERE id = %s",
GetSQLValueString($colname_story, "int"));
$story = mysql_query($query_story, $story) or die(mysql_error());
$row_story = mysql_fetch_assoc($story);
$totalRows_story = mysql_num_rows($story);

$colname_comments = "-1";
if (isset($_GET['id'])) {
$colname_comments = (get_magic_quotes_gpc()) ? $_GET['id'] :
addslashes($_GET['id']);
}

$query_comments = sprintf("SELECT * FROM comments WHERE id = %s ORDER
BY `datetime` ASC", GetSQLValueString($colname_comments, "int"));
$comments = mysql_query($query_comments, $story) or
die(mysql_error());
$row_comments = mysql_fetch_assoc($comments);
$totalRows_comments = mysql_num_rows($comments);

All works fine if I remove the last few lines, the line causing the
error is the

$query_comments = sprintf("SELECT * FROM comments WHERE id = %s ORDER
BY `datetime` ASC", GetSQLValueString($colname_comments, "int"));

line. Why? What does the error mean?

Bonus points for suggesting a better tool that can generate MYSQL
connections etc other than Dreamweaver which I'm using currently.

Thanks!

20/12/2007, 18h58

== Quote from Rich Bradshaw (Rich.Bradshaw@gmail.com)'s article
> Hi,
> I am using PHP to query two tables in the same database, but am
> getting a "mysql_query(): supplied resource is not a valid MySQL-Link
> resource" error.
> $hostname_story = "mysql.mydomain.com";
> $database_story = "cms";
> $username_story = "user";
> $password_story = "password";
> $story = mysql_pconnect($hostname_story, $username_story,
> $password_story) or trigger_error(mysql_error(),E_USER_ERROR);
> $colname_story = "-1";
> if (isset($_GET['id'])) {
> $colname_story = (get_magic_quotes_gpc()) ? $_GET['id'] :
> addslashes($_GET['id']);
> }
> mysql_select_db($database_story, $story);
> $query_story = sprintf("SELECT * FROM story WHERE id = %s",
> GetSQLValueString($colname_story, "int"));
> $story = mysql_query($query_story, $story) or die(mysql_error());
> $row_story = mysql_fetch_assoc($story);
> $totalRows_story = mysql_num_rows($story);
> $colname_comments = "-1";
> if (isset($_GET['id'])) {
> $colname_comments = (get_magic_quotes_gpc()) ? $_GET['id'] :
> addslashes($_GET['id']);
> }
> $query_comments = sprintf("SELECT * FROM comments WHERE id = %s ORDER
> BY `datetime` ASC", GetSQLValueString($colname_comments, "int"));
> $comments = mysql_query($query_comments, $story) or
> die(mysql_error());
> $row_comments = mysql_fetch_assoc($comments);
> $totalRows_comments = mysql_num_rows($comments);
> All works fine if I remove the last few lines, the line causing the
> error is the
> $query_comments = sprintf("SELECT * FROM comments WHERE id = %s ORDER
> BY `datetime` ASC", GetSQLValueString($colname_comments, "int"));
> line. Why? What does the error mean?
> Bonus points for suggesting a better tool that can generate MYSQL
> connections etc other than Dreamweaver which I'm using currently.
> Thanks!

try adding this:
mysql_select_db($database_story, $story);
right before the last mysql_query and see what happens.

--
POST BY: lark with PHP News Reader ;o)

20/12/2007, 19h01

I did originally have that, but took it out to see if it fixed things,
added it back, now the error is for that line instead...

On Dec 20, 6:58 pm, lark <ham...@sbcglobal.net> wrote:
> == Quote from Rich Bradshaw (Rich.Brads...@gmail.com)'s article
>
>
>
> > Hi,
> > I am using PHP to query two tables in the same database, but am
> > getting a "mysql_query(): supplied resource is not a valid MySQL-Link
> > resource" error.
> > $hostname_story = "mysql.mydomain.com";
> > $database_story = "cms";
> > $username_story = "user";
> > $password_story = "password";
> > $story = mysql_pconnect($hostname_story, $username_story,
> > $password_story) or trigger_error(mysql_error(),E_USER_ERROR);
> > $colname_story = "-1";
> > if (isset($_GET['id'])) {
> > $colname_story = (get_magic_quotes_gpc()) ? $_GET['id'] :
> > addslashes($_GET['id']);
> > }
> > mysql_select_db($database_story, $story);
> > $query_story = sprintf("SELECT * FROM story WHERE id = %s",
> > GetSQLValueString($colname_story, "int"));
> > $story = mysql_query($query_story, $story) or die(mysql_error());
> > $row_story = mysql_fetch_assoc($story);
> > $totalRows_story = mysql_num_rows($story);
> > $colname_comments = "-1";
> > if (isset($_GET['id'])) {
> > $colname_comments = (get_magic_quotes_gpc()) ? $_GET['id'] :
> > addslashes($_GET['id']);
> > }
> > $query_comments = sprintf("SELECT * FROM comments WHERE id = %s ORDER
> > BY `datetime` ASC", GetSQLValueString($colname_comments, "int"));
> > $comments = mysql_query($query_comments, $story) or
> > die(mysql_error());
> > $row_comments = mysql_fetch_assoc($comments);
> > $totalRows_comments = mysql_num_rows($comments);
> > All works fine if I remove the last few lines, the line causing the
> > error is the
> > $query_comments = sprintf("SELECT * FROM comments WHERE id = %s ORDER
> > BY `datetime` ASC", GetSQLValueString($colname_comments, "int"));
> > line. Why? What does the error mean?
> > Bonus points for suggesting a better tool that can generate MYSQL
> > connections etc other than Dreamweaver which I'm using currently.
> > Thanks!
>
> try adding this:
> mysql_select_db($database_story, $story);
> right before the last mysql_query and see what happens.
>
> --
> POST BY: lark with PHP News Reader ;o)

20/12/2007, 21h01

Rich Bradshaw wrote:
> Hi,
>
> I am using PHP to query two tables in the same database, but am
> getting a "mysql_query(): supplied resource is not a valid MySQL-Link
> resource" error.
>
> $hostname_story = "mysql.mydomain.com";
> $database_story = "cms";
> $username_story = "user";
> $password_story = "password";
> $story = mysql_pconnect($hostname_story, $username_story,
A general piece of advice, you shouldn't need to use pconnect

20/12/2007, 21h46

On Dec 20, 9:01 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> Rich Bradshaw wrote:
> > Hi,
>
> > I am using PHP to query two tables in the same database, but am
> > getting a "mysql_query(): supplied resource is not a valid MySQL-Link
> > resource" error.
>
> > $hostname_story = "mysql.mydomain.com";
> > $database_story = "cms";
> > $username_story = "user";
> > $password_story = "password";
> > $story = mysql_pconnect($hostname_story, $username_story,
>
> A general piece of advice, you shouldn't need to use pconnect

I think I'm going to stop using Dreamweaver and go back to nano, it
seems to generate weird code sometimes.

Still would like to know why this doesn't work though!

20/12/2007, 22h54

Rich Bradshaw wrote:
> On Dec 20, 9:01 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> wrote:
>> Rich Bradshaw wrote:
>> > Hi,
>>
>> > I am using PHP to query two tables in the same database, but am
>> > getting a "mysql_query(): supplied resource is not a valid
>> > MySQL-Link resource" error.
>>
>> > $hostname_story = "mysql.mydomain.com";
>> > $database_story = "cms";
>> > $username_story = "user";
>> > $password_story = "password";
>> > $story = mysql_pconnect($hostname_story, $username_story,
>>
>> A general piece of advice, you shouldn't need to use pconnect
>
> I think I'm going to stop using Dreamweaver and go back to nano, it
> seems to generate weird code sometimes.
>
> Still would like to know why this doesn't work though!

OK, let's go back to your original post. You say that:
the line causing the
error is the
$query_comments = sprintf("SELECT * FROM comments WHERE id = %s ORDER
BY `datetime` ASC", GetSQLValueString($colname_comments, "int"));

This is rubbish. This line doesn't even call mysql. The actual error message
should have given you the line number that it occurred on.

My guess is that the actual line giving the error is:
$story = mysql_query($query_story, $story) or die(mysql_error());

Guess what gives me the clue? The error gives the function that was called!

So what's the problem? Look at that line! Look really closely now.

Your mysql connection resource is in a variable called $story. And what do
you do in that line? You wipe out the value of your connection resource and
try to replace it with the query result resource.

20/12/2007, 18h49	#1
Rich Bradshaw Messages: n/a Hébergeur:	Simple but annoying problem when querying multiple tables from samedatabase Hi, I am using PHP to query two tables in the same database, but am getting a "mysql_query(): supplied resource is not a valid MySQL-Link resource" error. $hostname_story = "mysql.mydomain.com"; $database_story = "cms"; $username_story = "user"; $password_story = "password"; $story = mysql_pconnect($hostname_story, $username_story, $password_story) or trigger_error(mysql_error(),E_USER_ERROR); $colname_story = "-1"; if (isset($_GET['id'])) { $colname_story = (get_magic_quotes_gpc()) ? $_GET['id'] : addslashes($_GET['id']); } mysql_select_db($database_story, $story); $query_story = sprintf("SELECT * FROM story WHERE id = %s", GetSQLValueString($colname_story, "int")); $story = mysql_query($query_story, $story) or die(mysql_error()); $row_story = mysql_fetch_assoc($story); $totalRows_story = mysql_num_rows($story); $colname_comments = "-1"; if (isset($_GET['id'])) { $colname_comments = (get_magic_quotes_gpc()) ? $_GET['id'] : addslashes($_GET['id']); } $query_comments = sprintf("SELECT * FROM comments WHERE id = %s ORDER BY `datetime` ASC", GetSQLValueString($colname_comments, "int")); $comments = mysql_query($query_comments, $story) or die(mysql_error()); $row_comments = mysql_fetch_assoc($comments); $totalRows_comments = mysql_num_rows($comments); All works fine if I remove the last few lines, the line causing the error is the $query_comments = sprintf("SELECT * FROM comments WHERE id = %s ORDER BY `datetime` ASC", GetSQLValueString($colname_comments, "int")); line. Why? What does the error mean? Bonus points for suggesting a better tool that can generate MYSQL connections etc other than Dreamweaver which I'm using currently. Thanks!

20/12/2007, 18h58	#2
lark Messages: n/a Hébergeur:	Re: Simple but annoying problem when querying multiple tables from same database == Quote from Rich Bradshaw (Rich.Bradshaw@gmail.com)'s article > Hi, > I am using PHP to query two tables in the same database, but am > getting a "mysql_query(): supplied resource is not a valid MySQL-Link > resource" error. > $hostname_story = "mysql.mydomain.com"; > $database_story = "cms"; > $username_story = "user"; > $password_story = "password"; > $story = mysql_pconnect($hostname_story, $username_story, > $password_story) or trigger_error(mysql_error(),E_USER_ERROR); > $colname_story = "-1"; > if (isset($_GET['id'])) { > $colname_story = (get_magic_quotes_gpc()) ? $_GET['id'] : > addslashes($_GET['id']); > } > mysql_select_db($database_story, $story); > $query_story = sprintf("SELECT * FROM story WHERE id = %s", > GetSQLValueString($colname_story, "int")); > $story = mysql_query($query_story, $story) or die(mysql_error()); > $row_story = mysql_fetch_assoc($story); > $totalRows_story = mysql_num_rows($story); > $colname_comments = "-1"; > if (isset($_GET['id'])) { > $colname_comments = (get_magic_quotes_gpc()) ? $_GET['id'] : > addslashes($_GET['id']); > } > $query_comments = sprintf("SELECT * FROM comments WHERE id = %s ORDER > BY `datetime` ASC", GetSQLValueString($colname_comments, "int")); > $comments = mysql_query($query_comments, $story) or > die(mysql_error()); > $row_comments = mysql_fetch_assoc($comments); > $totalRows_comments = mysql_num_rows($comments); > All works fine if I remove the last few lines, the line causing the > error is the > $query_comments = sprintf("SELECT * FROM comments WHERE id = %s ORDER > BY `datetime` ASC", GetSQLValueString($colname_comments, "int")); > line. Why? What does the error mean? > Bonus points for suggesting a better tool that can generate MYSQL > connections etc other than Dreamweaver which I'm using currently. > Thanks! try adding this: mysql_select_db($database_story, $story); right before the last mysql_query and see what happens. -- POST BY: lark with PHP News Reader ;o)

20/12/2007, 19h01	#3
Rich Bradshaw Messages: n/a Hébergeur:	Re: Simple but annoying problem when querying multiple tables fromsame database I did originally have that, but took it out to see if it fixed things, added it back, now the error is for that line instead... On Dec 20, 6:58 pm, lark <ham...@sbcglobal.net> wrote: > == Quote from Rich Bradshaw (Rich.Brads...@gmail.com)'s article > > > > > Hi, > > I am using PHP to query two tables in the same database, but am > > getting a "mysql_query(): supplied resource is not a valid MySQL-Link > > resource" error. > > $hostname_story = "mysql.mydomain.com"; > > $database_story = "cms"; > > $username_story = "user"; > > $password_story = "password"; > > $story = mysql_pconnect($hostname_story, $username_story, > > $password_story) or trigger_error(mysql_error(),E_USER_ERROR); > > $colname_story = "-1"; > > if (isset($_GET['id'])) { > > $colname_story = (get_magic_quotes_gpc()) ? $_GET['id'] : > > addslashes($_GET['id']); > > } > > mysql_select_db($database_story, $story); > > $query_story = sprintf("SELECT * FROM story WHERE id = %s", > > GetSQLValueString($colname_story, "int")); > > $story = mysql_query($query_story, $story) or die(mysql_error()); > > $row_story = mysql_fetch_assoc($story); > > $totalRows_story = mysql_num_rows($story); > > $colname_comments = "-1"; > > if (isset($_GET['id'])) { > > $colname_comments = (get_magic_quotes_gpc()) ? $_GET['id'] : > > addslashes($_GET['id']); > > } > > $query_comments = sprintf("SELECT * FROM comments WHERE id = %s ORDER > > BY `datetime` ASC", GetSQLValueString($colname_comments, "int")); > > $comments = mysql_query($query_comments, $story) or > > die(mysql_error()); > > $row_comments = mysql_fetch_assoc($comments); > > $totalRows_comments = mysql_num_rows($comments); > > All works fine if I remove the last few lines, the line causing the > > error is the > > $query_comments = sprintf("SELECT * FROM comments WHERE id = %s ORDER > > BY `datetime` ASC", GetSQLValueString($colname_comments, "int")); > > line. Why? What does the error mean? > > Bonus points for suggesting a better tool that can generate MYSQL > > connections etc other than Dreamweaver which I'm using currently. > > Thanks! > > try adding this: > mysql_select_db($database_story, $story); > right before the last mysql_query and see what happens. > > -- > POST BY: lark with PHP News Reader ;o)

20/12/2007, 21h01	#4
Paul Lautman Messages: n/a Hébergeur:	Re: Simple but annoying problem when querying multiple tables from same database Rich Bradshaw wrote: > Hi, > > I am using PHP to query two tables in the same database, but am > getting a "mysql_query(): supplied resource is not a valid MySQL-Link > resource" error. > > $hostname_story = "mysql.mydomain.com"; > $database_story = "cms"; > $username_story = "user"; > $password_story = "password"; > $story = mysql_pconnect($hostname_story, $username_story, A general piece of advice, you shouldn't need to use pconnect

20/12/2007, 21h46	#5
Rich Bradshaw Messages: n/a Hébergeur:	Re: Simple but annoying problem when querying multiple tables fromsame database On Dec 20, 9:01 pm, "Paul Lautman" <paul.laut...@btinternet.com> wrote: > Rich Bradshaw wrote: > > Hi, > > > I am using PHP to query two tables in the same database, but am > > getting a "mysql_query(): supplied resource is not a valid MySQL-Link > > resource" error. > > > $hostname_story = "mysql.mydomain.com"; > > $database_story = "cms"; > > $username_story = "user"; > > $password_story = "password"; > > $story = mysql_pconnect($hostname_story, $username_story, > > A general piece of advice, you shouldn't need to use pconnect I think I'm going to stop using Dreamweaver and go back to nano, it seems to generate weird code sometimes. Still would like to know why this doesn't work though!

20/12/2007, 22h54	#6
Paul Lautman Messages: n/a Hébergeur:	Re: Simple but annoying problem when querying multiple tables from same database Rich Bradshaw wrote: > On Dec 20, 9:01 pm, "Paul Lautman" <paul.laut...@btinternet.com> > wrote: >> Rich Bradshaw wrote: >> > Hi, >> >> > I am using PHP to query two tables in the same database, but am >> > getting a "mysql_query(): supplied resource is not a valid >> > MySQL-Link resource" error. >> >> > $hostname_story = "mysql.mydomain.com"; >> > $database_story = "cms"; >> > $username_story = "user"; >> > $password_story = "password"; >> > $story = mysql_pconnect($hostname_story, $username_story, >> >> A general piece of advice, you shouldn't need to use pconnect > > I think I'm going to stop using Dreamweaver and go back to nano, it > seems to generate weird code sometimes. > > Still would like to know why this doesn't work though! OK, let's go back to your original post. You say that: the line causing the error is the $query_comments = sprintf("SELECT * FROM comments WHERE id = %s ORDER BY `datetime` ASC", GetSQLValueString($colname_comments, "int")); This is rubbish. This line doesn't even call mysql. The actual error message should have given you the line number that it occurred on. My guess is that the actual line giving the error is: $story = mysql_query($query_story, $story) or die(mysql_error()); Guess what gives me the clue? The error gives the function that was called! So what's the problem? Look at that line! Look really closely now. Your mysql connection resource is in a variable called $story. And what do you do in that line? You wipe out the value of your connection resource and try to replace it with the query result resource.

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email