Page 1 of 1
Joining tables
Posted: Sun Nov 30, 2003 9:04 pm
by smoky989
OK heres what I'm trying to do.
TABLE1
I have a table that has a list of names in it, it has several fileds but I only need to deal with names and status.
TABLE2
I have another table with the same names along with 3 fields with other information.
I want to list the names and 3 other fields of TABLE2 where the status is equal to 1 or 3. This is how my code looks.
Code: Select all
<?php
$db = mysql_connect("localhost", "username", "password");
mysql_select_db ("blah");
$result = mysql_query("select TABLE1.name, TABLE2.show, TABLE2.active, TABLE2.notes from TABLE1, TABLE2 where TABLE1.name = TABLE2.name and TABLE1.status = 1 || TABLE1.status = 3 order by name", $db);
echo "<table border =2><tr><td colspan=4><b><div align=center>Testing</div></b></td></tr>";
while($myrow = mysql_fetch_array($result)){
$name = $myrow["name"];
$show = $myrow["show"];
$active = $myrow["active"];
$notes = $myrow["notes"];
echo "<tr><td width=175>";
echo "$name";
echo "</td><td>";
echo "$show";
echo "</td><td>";
echo "$active";
echo "</td><td>";
echo "$notes";
echo "</td><tr>";
};
echo "</table>"
?>
This isn't working for me. Any suggestions?
Posted: Sun Nov 30, 2003 9:31 pm
by infolock
try this :
Code: Select all
$result = mysql_query("select TABLE1.*, TABLE2.* from TABLE2, TABLE1 where TABLE1.name = TABLE2.name and TABLE1.status = 1 || TABLE1.status = 3 order by name", $db);
echo "<table border =2><tr><td colspan=4><b><div align=center>Testing</div></b></td></tr>";
while($myrow = mysql_fetch_array($result)){
$name = $myrow["table2.name"];
$show = $myrow["table2.show"];
$active = $myrow["table2.active"];
$notes = $myrow["table2.notes"];
echo "<tr><td width=175>";
echo "$name";
echo "</td><td>";
echo "$show";
echo "</td><td>";
echo "$active";
echo "</td><td>";
echo "$notes";
echo "</td><tr>";
};
echo "</table>"
?>
Posted: Sun Nov 30, 2003 9:44 pm
by microthick
Are you getting errors with your query?
I think your query should be:
Code: Select all
$result = mysql_query("select TABLE1.name, TABLE2.show, TABLE2.active, TABLE2.notes from TABLE1, TABLE2 where TABLE1.name = TABLE2.name and (TABLE1.status = 1 or TABLE1.status = 3) order by TABLE1.name", $db);
Posted: Sun Nov 30, 2003 9:52 pm
by infolock
micro, that says to do the same thing i posted, except you are ordering by the names found in table 1

Posted: Sun Nov 30, 2003 10:04 pm
by microthick
infolock wrote:micro, that says to do the same thing i posted, except you are ordering by the names found in table 1

No.
1) By bracketing the OR clause, the query will do what he wants. Before, the AND would have taken precidence, and that's not what he wanted.
2) I don't believe you can use "||" to mean OR in WHERE clauses.
3) Not stating TABLE1 in the order by clause may have caused MySQL to spit up an ambiguity error. I have more experience with MS SQL than MySQL and I know that in MS SQL, it would have errored. Not sure about MySQL.
Posted: Sun Nov 30, 2003 10:14 pm
by infolock
No.
1) By bracketing the OR clause, the query will do what he wants. Before, the AND would have taken precidence, and that's not what he wanted.
2) I don't believe you can use "||" to mean OR in WHERE clauses.
3) Not stating TABLE1 in the order by clause may have caused MySQL to spit up an ambiguity error. I have more experience with MS SQL than MySQL and I know that in MS SQL, it would have errored. Not sure about MySQL
|| is the same as or

i tried this out on my own table in mysql, and it worked the same both ways.
you are right by if you select both tables, it will give an ambiguity error if you don't specify the table1.name, but you could remove table1.* and it would work fine with just name
edit ::
hmmm
Posted: Sun Nov 30, 2003 10:45 pm
by smoky989
Well the parenthesis were a problem but I'm still getting an error.
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource
on line 11 which is the line that the where clause is on. I'm gonna check the syntax again to make sure I have everything like you had it. Thanks
Posted: Sun Nov 30, 2003 11:39 pm
by infolock
try this and see if you can get it to give you an error :
Code: Select all
$result = mysql_query("select TABLE2.* from TABLE2, TABLE1 where TABLE1.name = TABLE2.name and TABLE1.status = 1 || TABLE1.status = 3 order by name", $db) or die(MySQL_Error());
Posted: Mon Dec 01, 2003 12:17 am
by smoky989
ok I did that and the error it gave was
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'where TABLE1.name = TABLE2.name and TABLE1.status =
Posted: Mon Dec 01, 2003 12:28 am
by infolock
did you try switching the queries around ?
like trying this :
Code: Select all
$result = mysql_query("select TABLE1.*, TABLE2.* from TABLE2, TABLE1 where TABLE1.name = TABLE2.name and TABLE1.status = 1 || TABLE1.status = 3 order by TABLE2.name", $db) or die(MySQL_Error());
and
Code: Select all
$result = mysql_query("select TABLE1.name, TABLE2.name, TABLE2.show, TABLE2.active, TABLE2.notes from TABLE2, TABLE1 where TABLE1.name = TABLE2.name and TABLE1.status = 1 || TABLE1.status = 3 order by TABLE2.name", $db) or die(MySQL_Error());
etc..
Posted: Mon Dec 01, 2003 2:17 am
by JAM
Just adding alternative syntax;
Code: Select all
select
TABLE1.name,
TABLE2.name,
TABLE2.show,
TABLE2.active,
TABLE2.notes
from
TABLE1
inner join TABLE2 on TABLE1.name = TABLE2.name
where
TABLE1.status = '1' or TABLE1.status = '3'
order by
TABLE2.name