selecting data from multiple tables

[sliquid]

Im using ms Access and PHP to write / display data from the DB.

my question:
Is there some kind of difference between selecting info from the first table in a DB than there is for the second table?

I started with one table, called customers, and I can write and pull data from it just fine.

I took the page that pulls data from the customers table, and I made a copy, and changed "customers" to "distributors" in the sql request

I created a table in the same DB, called distributors. most of the columns are the same, i.e firstname, lastname, phone, etc.

the first page works just fine pulling data from the first table, "customers".
the copied page does not work.

There is no error, it returns my column headers, but no data is pulled.

so is there some kind of syntax change to select info from a second table?

heres the code for the page that works.

<?php 
include("dbconnection.php"); 
$search_results = FALSE; 
if (isset($_POST['Submit'])) { 
    $search = $_POST['search']; 
    $date = $_POST['date']; 
$query = "SELECT firstname,lastname,city,state,email FROM customers WHERE firstname = '$search' OR lastname = '$search' OR city = '$search' OR state = '$search' OR email = '$search' AND date >= '$date' ORDER BY date asc"; 

   $result = odbc_exec($cnx, $query); 
$i = 0; 
$color1 = "#CADEFA"; // gainsboro 
$color2 = "#5F7183"; // light slate gray 
$search_results = '<div id=\"table1\"> 
<center> 
<TABLE CELLPADDING=0 CELLSPACING=1 width=\"100%\"><tr align=center bgcolor=lightsteelblue> 
<th><font face=\"Tahoma\" size=\"1\"><b>First Name:</b></font></th> 
<th><font face=\"Tahoma\" size=\"1\">Last Name:</font></th> 
<th><font face=\"Tahoma\" size=\"1\">Customer City:</font></th> 
<th><font face=\"Tahoma\" size=\"1\">Customer State:</font></th> 
<th><font face=\"Tahoma\" size=\"1\">Email:</font></th> 
</tr>'; 
while ($row = odbc_fetch_array($result)) { 
$color = ($i%2==0 || $i==0)?$color1:$color2; 
/* 
echo "<p>SQL: $query</p>"; 
if ($result === false) 
  echo "<p>odbc_exec returned false, msg: " . odbc_errormsg() . "</p>"; 
else echo "<p>odbc_exec returned " . odbc_num_rows($result) . " rows.</p>"; 
*/ 

$search_results .= " 
<tr align=center bgcolor=".$color."> 
<td><font face=\"Tahoma\" size=\"1\">$row[firstname]</font></td> 
<td><font face=\"Tahoma\" size=\"1\">$row[lastname]</font></td> 
<td><font face=\"Tahoma\" size=\"1\">$row[city]</font></td> 
<td><font face=\"Tahoma\" size=\"1\">$row[state]</font></td> 
<td><font face=\"Tahoma\" size=\"1\"><a href=\"mailto:$row[email]\">$row[email]</a></font></td> 
</tr>"; 
$i++; // for alternating colors per row... 
} // End of WHILE loop 
$search_results .= " 
</table> 
</center> 
</div>"; 

} 

?>

and the one that doesnt

<?php 
include("dbconnection.php"); 
$search_results = FALSE; 
if (isset($_POST['Submit'])) { 
    $search = $_POST['search']; 
    $date = $_POST['date']; 
$query = "SELECT company,firstname,phone,fax,website,email FROM distributors WHERE company = '$search' OR firstname = '$search' OR lastname = '$search' OR city = '$search' OR state = '$search' OR email = '$search' OR store_type = '$search' AND date >= '$date' ORDER BY date asc"; 

   $result = odbc_exec($cnx, $query); 
$i = 0; 
$color1 = "#CADEFA"; // gainsboro 
$color2 = "#5F7183"; // light slate gray 
$search_results = '<div id=\"table1\"> 
<center> 
<TABLE CELLPADDING=0 CELLSPACING=1 width=\"100%\"><tr align=center bgcolor=lightsteelblue> 
<th><font face=\"Tahoma\" size=\"1\"><b>Company:</b></font></th> 
<th><font face=\"Tahoma\" size=\"1\">Contact:</font></th> 
<th><font face=\"Tahoma\" size=\"1\">Phone:</font></th> 
<th><font face=\"Tahoma\" size=\"1\">Fax:</font></th> 
<th><font face=\"Tahoma\" size=\"1\">Website:</font></th> 
<th><font face=\"Tahoma\" size=\"1\">Email:</font></th> 
</tr>'; 
while ($row = odbc_fetch_array($result)) { 
$color = ($i%2==0 || $i==0)?$color1:$color2; 
/* 
echo "<p>SQL: $query</p>"; 
if ($result === false) 
  echo "<p>odbc_exec returned false, msg: " . odbc_errormsg() . "</p>"; 
else echo "<p>odbc_exec returned " . odbc_num_rows($result) . " rows.</p>"; 
*/ 

$search_results .= " 
<tr align=center bgcolor=".$color."> 
<td><font face=\"Tahoma\" size=\"1\">$row[company]</font></td> 
<td><font face=\"Tahoma\" size=\"1\">$row[firstname]</font></td> 
<td><font face=\"Tahoma\" size=\"1\">$row[phone]</font></td> 
<td><font face=\"Tahoma\" size=\"1\">$row[fax]</font></td> 
<td><font face=\"Tahoma\" size=\"1\"><a href=\"$row[website]\">$row[website]</a></font></td>
<td><font face=\"Tahoma\" size=\"1\"><a href=\"mailto:$row[email]\">$row[email]</a></font></td> 
</tr>"; 
$i++; // for alternating colors per row... 
} // End of WHILE loop 
$search_results .= " 
</table> 
</center> 
</div>"; 

} 

?>

[chrisranjana]

$query = "SELECT company,firstname,phone,fax,website,email FROM distributors WHERE company = '$search' OR firstname = '$search' OR lastname = '$search' OR city = '$search' OR state = '$search' OR email = '$search' OR store_type = '$search' AND date >= '$date' ORDER BY date asc";

Did you try to run the query manually on the database and find that there are some rows returned or not ?

[r937]

you have this --

 WHERE firstname = '$search' 
    OR lastname = '$search' 
    OR city = '$search' 
    OR state = '$search' 
    OR email = '$search' 
   AND date >= '$date'

since ANDs take precedence over ORs, this is evaluated as --

 WHERE firstname = '$search' 
    OR lastname = '$search' 
    OR city = '$search' 
    OR state = '$search' 
    OR (
       email = '$search' 
   AND date >= '$date' 
       )

whereas what you probably want is this --

 WHERE (
       firstname = '$search' 
    OR lastname = '$search' 
    OR city = '$search' 
    OR state = '$search' 
    OR email = '$search' 
       )
   AND date >= '$date'

tip: whenever you mix ANDs and ORs, hardcode the parentheses exactly the way you want the expressions evaluated