querying from 2 tables with strange results

[treyhunsucker]

What I want is to run a query that select the complete.a2 field and echos the value, where dn_records.number='5553331111'

Whats happening is all the values for complete.a2 are echoing back, even if dn_records.number isn't equal to '5553331111'

I am getting same results using the below php script as I am usnig the MySQL Query Browser.

<?php
mysql_connect( 'localhost', 'user', 'pass' )
or die( "Error! Could not connect to database: " . mysql_error() );

mysql_select_db( 'customers' )
or die( "Error! Could not select the database: " . mysql_error() );

$result = mysql_query("select complete.a2, dn_records.number from complete, dn_records where dn_records.number='5553331111'")
or die(mysql_error());

while($row = mysql_fetch_array( $result )) {
    echo "<tr><td>";
    echo $row["a2"];
    echo "</td></tr>";
}

?>

Results Example:

mysql> select complete.a2, dn_records.number from complete, dn_records where dn_records.number='5553331111';
+-------------------+------------+
| a2 | number |
+-------------------+------------+
| company1 | 1111111111 |
| company2 | 2222222222 |
| company3 | 5553331111 |
+-------------------+------------+
3 rows in set (0.01 sec)

 

[sleipnir214]

This is because you are not relating the two tables in your query. Typically with the type of query you're running, you'd want a query like:

[tt]SELECT
complete.a2,
dn_records.number
FROM
complete,
dn_records
WHERE
[red]complete.some_column = dn_records.some_column
AND[/red]
dn_records.number='5553331111'[/tt]

Where the where-clause in red relates the two tables by specifying a column in each table that should be equal to the other.







Want the best answers? Ask the best questions! TANSTAAFL!

 

[treyhunsucker]

Thank you that worked