Querying multiple databases

Anon

I have a problem with my query:

Here's my query and result, what's wrong with it?

<?
$query = "SELECT * FROM 'benjobee_graffiti','benjobee_skatepics' WHERE filename='$action'";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)) //this is the line with the mysql error on it
{
echo "<p align=\"center\"><img src=\"images/" . $row['filename'] . "\"><br>" . $row['name'] . " by " . $row['artist'] . " - " . $row['description'] . "";

$page= $page;

if ($page == 'fitilatest')
{
echo "<br><a href=\"fiti.php\">back</a></p>";
}
if ($page == 'fitiall')
{
echo "<br><a href=\"allfiti.php\">back</a></p>";
}
if ($page == 'type')
{
echo "<br><a href=\"fititype.php?type=" . $row['type'] . "\">back</a></p>";
}
if ($page == 'skatelatest')
{
echo "<br><a href=\"skate.php\">back</a></p>";
}
if ($page == 'skateall')
{
echo "<br><a href=\"allskate.php\">back</a></p>";
}
}
?>

bfranco

I would like to know what's the text of the error you're getting.

Anyway, you're not checking if the query executed ok... maybe you should do:

$result = mysql_query($query);
if ($result)
{
while($row = mysql_fetch_array($result))
{
//yadda
}
}
else
print("Error executing query.");

Anon

Warning: Supplied argument is not a valid MySQL result resource in /usr/local/psa/home/vhosts/sanddesign.co.uk/httpdocs/benjobee/pic.php on line 21

is the error I get, line 21 is the line I indicated

bfranco

ok, as I told you in the previous message, you should check if the $result is a valid result identifier (obviously it isn't).

I think your query is wrong... are you aware that you're asking for the cartesian product of all of the records from one of the tables, and all of the records from the other where the field filename meets the condition?? I mean, you are not specifing any relation between both tables (like a join or something). I don't think that's what you want.

anyway, try the code this way:

$query = "SELECT * FROM 'benjobee_graffiti','benjobee_skatepics' WHERE filename='$action'";
$result = mysql_query($query);

//There was an error??
if (!$result)
printf("<h1>%s</h1>", mysql_error();
else
{
//No errors??? let's check how many rows did we get
printf("</h1>Query returned %d rows.</h1>", mysql_num_rows());
}

this will give you a hint of what's going on with your query.

Anon

Well you are trying to join 2 tables...which 2 columns are pointing to each other?

a sample query would look something like this.

$query = "SELECT * FROM 'benjobee_graffiti','benjobee_skatepics' WHERE benjobee_graffiti.rowid=benjobee_skatepics.rowid and benjobee_graffiti.filename='$action'";

That is a correct join if rowid is the same value for both tables.

Hope this helps.

Cheers

Anon

Tanks for the idea, but that is not what I'm trying to do: I'm trying to query both databases for filename='$action'. I keep getting errors camplaining that 'filename' is ambiguous. I found that ambiguous means having more than one meaning, but filename doesn't have the same value! Help!

_benjobee

bfranco

first of all, we're not talking bout databases here, it's just one database with 2 (or more) tables. There's a table called 'benjobee_graffiti' and another one called 'benjobee_skatepics'.

and what you're trying to do is wrong. You cannot do that in one query. Send us the tables structure (from the mysql console do a
describe benjobee_skatepics
and
describe benjobee_graffiti

and send us the results.

Anon

Database phoenixbarn - table benjobee_graffiti
SQL-query : [Edit]
describe benjobee_graffiti

Database phoenixbarn - table benjobee_graffiti
Showing records 0 - 4 (4 total)
SQL-query : [Edit]
describe benjobee_skatepics

Those are the descriptions 'MyDBAdmin' gave me, and yes I was using the worng terminology. something I often do with php! Are you sure I can't just query both table, because I think I saw it done some where, but I understand why it thinks 'filename' is ambiguous...

_benjobee

Anon

Help at all?