SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2005
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    selecting data from multiple tables

    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 Code:
    <?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==|| $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 Code:
    <?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==|| $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>"




    ?>

  2. #2
    Web development Company chrisranjana's Avatar
    Join Date
    Jan 2001
    Location
    chennai , tamil nadu , India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $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 ?
    Chris, Programmer/Developer,
    www.chrisranjana.com

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you have this --
    Code:
     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 --
    Code:
     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 --
    Code:
     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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •