SitePoint Sponsor

User Tag List

Page 1 of 4 1234 LastLast
Results 1 to 25 of 80

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unknown column 'city' in 'where clause'

    Currently my form can query our club members db by any combination of last name, first name, or gender. We used to be able to query by city as well, but recently I broke something and, when I try to query by city, I get this message: Unknown column 'city' in 'where clause'.

    1. My cities table does have a column called city. That table is the only table with a column called city, so there is no ambiguity.
    2. I've scoured my PHP form process script from top to bottom several times and can find no place where the table and column are called anything but cities and city, respectively.
    3. When I echo my WHERE clause, it looks like this, for example: city = 'Walnut Creek'

    So another way of stating my problem is: If the db table column is called city, and the script that writes to that table refers to that column as city, and echoing the WHERE clause proves that the column name is getting picked up as city.... then what are some of the reasons that the DBMS or PHP would return a message saying: Unknown column 'city' in 'where clause' ?

    Thank you all for your valuable time.
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  2. #2
    Guru in training bronze trophy SoulScratch's Avatar
    Join Date
    Apr 2006
    Location
    Maryland
    Posts
    1,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe it's an issue with whitespace preceding? How about

    LIKE %Walnut Creek%

    Instead of a straight up equality check?
    Cross browser css bugs

    Dan Schulz you will be missed

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SoulScratch, thank you for your reply.
    My understanding is that the issue is not with the name of the city on the right side of the equals sign, but rather with the column name to the left of the equals sign -- the column name city. The error message seems to convey that the system cannot evaluate the column name city. Does that sound right?

    Curtis

  4. #4
    Guru in training bronze trophy SoulScratch's Avatar
    Join Date
    Apr 2006
    Location
    Maryland
    Posts
    1,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok. Have you tried going into your DBMS, doing a select statement using just something simple like

    Code:
    SELECT * from city;
    WITHOUT a where clause? Is the issue ONLY when there's a where clause?
    Cross browser css bugs

    Dan Schulz you will be missed

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Interesting.
    Using phpMyAdmin I can query successfully with all of these:
    1. SELECT * from cities;
    2. SELECT city FROM cities;
    3. SELECT * FROM cities WHERE city = 'Dublin';
    4. SELECT * FROM cities WHERE city_id LIKE '4%';

    That last one returns all the cities whose id's start with the integer 4. They all work great. I suppose that indicates that the issue lies in my script somewhere, eh?
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  6. #6
    Guru in training bronze trophy SoulScratch's Avatar
    Join Date
    Apr 2006
    Location
    Maryland
    Posts
    1,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes. How about posting the actual real full query and sample data, rows which you think should be getting fetched.. but are not getting fetched.
    Cross browser css bugs

    Dan Schulz you will be missed

  7. #7
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When I echo the sql statement with my own first name, last name and gender, but with no city value, it is echoed to my results page as:
    Code:
    SELECT l_name, f_name, city, num, address 
    FROM names 
    LEFT JOIN IDcity ON names.mem_id = IDcity.mem_id 
    LEFT JOIN cities ON IDcity.city_id = cities.city_id 
    LEFT JOIN IDphone ON names.mem_id = IDphone.mem_id 
    LEFT JOIN phone ON IDphone.phone_id = phone.phone_id 
    LEFT JOIN IDemail ON names.mem_id = IDemail.mem_id 
    LEFT JOIN email ON IDemail.email_id = email.email_id 
    WHERE l_name = 'Stallins' 
       AND f_name = 'Curtis' 
       AND gender = 'm' 
    ORDER BY l_name, f_name 
    LIMIT 0, 20
    The row returned and formatted on my results page looks something like this:
    Code:
    Last Name   First Name      City     Phone Number 
    Stallins      Curtis     Pleasanton  123-abc-defg
    That's how it looks if you don't include a value in the city field of my form - the query returns the city value just fine because the SQL statement is pre-programmed to return that value from the cities table, along with l_name, f_name, num and address from other tables (num and address are phone number and email address, respectively). But, when you enter a value into the city field of the form, that's when the script hiccups and returns the error message that titles this thread.

    Weird, eh?
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cstallins View Post
    But, when you enter a value into the city field of the form, that's when the script hiccups and returns the error message that titles this thread.
    thank you for showing us the query that works

    i'm sure this is not going to come as too much of a surprise, but if you want help fixing the query that hiccups, you're going to have to show us that one too

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Guru in training bronze trophy SoulScratch's Avatar
    Join Date
    Apr 2006
    Location
    Maryland
    Posts
    1,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Methinks there's a typo of some sort. I don't think you'll get a definitive answer without posting all the code.
    Cross browser css bugs

    Dan Schulz you will be missed

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sigh, and i'm still waiting until you get a result set for someone with more than one city and more than one phone and more than one email

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you'll need to post your actual php code both here and other forums you have posted it to get this solved.

    The problem is in your PHP code.

  12. #12
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just to make sure I'm hearing you all correctly:
    Are you suggesting that, if I use sufficient line breaks and indenting so that it fits legibly in that tiny <code> window, it's OK to post a script with about 325 lines of PHP? And, I can actually expect someone to read through it??
    Wow...that's weird :-) Let me know if that's ok here, and I'll format it for posting.
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cstallins View Post
    Just to make sure I'm hearing you all correctly:
    well, the php does me no good -- i still want to see the actual constructed mysql query that dies with the message that is in this thread's title

    that will also help pinpoint where in that code monstrosity the problem may actually lie

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    Guru in training bronze trophy SoulScratch's Avatar
    Join Date
    Apr 2006
    Location
    Maryland
    Posts
    1,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think the limit is around 8,000 chars for messages.. I forget but someone will eventually take a look and solve it. I've seen lengthier pastebins from IRC regarding problems.
    Cross browser css bugs

    Dan Schulz you will be missed

  15. #15
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, here is the script that is no longer recognizing the table column called city. Please let me know if you see a typo, or if something puzzles you.
    Curtis
    Code:
    <?php
      session_start();
      if (!($_POST['l_name'] || $_POST['f_name'] || $_POST['city'] 
        || $_POST['gender'] || isset($_GET['pageno']))) {
        include('incl/headerDirectory.htm'); 
        echo"<div id=\"contentMain\"><div id=\"directoryPreso\">
                <h2>The form fields were empty.</h2>
                <p>Please return to 
                <a href=\"directory2.php\">our directory page</a> 
                and select your search criteria.</p>
                </div><!-- end div directoryPreso -->
                </div><!-- end contentMain -->
              "; 
        include('incl/footer.htm'); 
        echo"</div><!-- end wholePage --> </body></html>"; 
        exit(); 
      } /* end if, and end header and general stuff */
    
    $conn = mysql_connect("serverName", "login", "pswrd") or die(mysql_error());
    $db = mysql_select_db("dbName", $conn) or die(mysql_error());
    // SELECT query courtesy r937 
    //(I removed OUTERs to see if the query still works :-)
    $sql = "SELECT l_name, f_name, city, num, address 
      FROM names 
      LEFT JOIN IDcity  ON names.mem_id = IDcity.mem_id  
      LEFT JOIN cities ON IDcity.city_id  = cities.city_id 
      LEFT JOIN IDphone ON names.mem_id = IDphone.mem_id 
      LEFT JOIN phone ON IDphone.phone_id = phone.phone_id 
      LEFT JOIN IDemail ON names.mem_id = IDemail.mem_id 
      LEFT JOIN email ON IDemail.email_id = email.email_id 
      WHERE ";
    
      $orderBy = " ORDER BY l_name, f_name"; // the default sort order
    
      if($_POST['l_name']) { // if the visitor entered a last name
        $l_name = trim(mysql_real_escape_string($_POST['l_name']));
        $l_name = ucfirst(strtolower($l_name));
        $isValid = isValidLastName($l_name); //isValid functions near the bottom
        if(!$isValid) {
          //return the visitor to the form page to try again;
        }
        // if there is a * at the end of l_name, then replace it with % wildcard
        $l_nameLength = strlen($l_name);  
        if(substr_count($l_name, '*', $l_nameLength-1, 1)) {
           $l_name = preg_replace('/[*]/', '%', $l_name);
        }
        $wildCard = hasWildCard($l_name); //hasWildCard function is below
        if($wildCard) {
          $whereClause="l_name LIKE '$l_name'";
        } else { 
            $whereClause="l_name = '$l_name'"; 
           }	
        }
    
      if($_POST['f_name']) { // if the visitor entered a first name
        $f_name = trim(mysql_real_escape_string($_POST['f_name']));
        $f_name = ucfirst(strtolower($f_name));
        $isValid = isValidFirstName($f_name); //isValid functions are below
        if(!$isValid) {
          //return the visitor to the form page; (will write the code later)
        }
        // if there is a * at the end of f_name, then replace it with % wildcard
        $f_nameLength = strlen($f_name);  
        if(substr_count($f_name, '*', $f_nameLength-1, 1)) {
          $f_name = preg_replace('/[*]/', '%', $f_name);
        }
        // Check to see if there's a wild card at the end of f_name.
        $wildCard = hasWildCard($f_name);  //hasWildCard() function id below
        if($wildCard) {
          if($_POST['l_name']) {
            $whereClause.=" AND f_name LIKE '$f_name'";
          } 
          else { 
               $whereClause="f_name LIKE '$f_name'"; 
          }	
        }
        else {	// if f_name has no wild card...
          $hasNickName = nickName($f_name); // does f_name have a nickName?
          if($hasNickName) {
            if(!($_POST['l_name'])) { // if there is a nickName and no last name
              $whereClause = $hasNickName;
            } 
            else { // when there is a nickName and a last name
              $whereClause.= " AND f_name = '$f_name'";
            }
          }
          else {  // if there is no nickName:
            if($_POST['l_name']) {
              $whereClause.=" AND f_name = '$f_name'";
            } 
            else { 
              $whereClause="f_name = '$f_name'"; 
            }	
          }
        }
      } 
    
      //check to see if visitor entered a city name in the form
      if($_POST['city']) {
        $city = trim(mysql_real_escape_string($_POST['city']));
        $city = ucwords(strtolower($city));
        $thisCity = checkCity($city); //checkCity() function near line 248
        if(!($thisCity)) {
          $msg = "Our records indicate that no current club member lives in
                   ".$city.". Carefully check your spelling and adjust your search
                    accordingly."; //I'll use the message later
        }
        if($_POST['l_name'] || $_POST['f_name']) {
          $whereClause .= " AND city = '$city'";
        } 
        else { 
          $whereClause = "city = '$city'"; 
        }
      }
    
      //check to see if visitor is searching by gender
      if($_POST['gender']) {
        $gender = mysql_real_escape_string($_POST['gender']);
        if($_POST['l_name'] || $_POST['f_name'] || $_POST['city']) {
          if($_POST['gender'] != "a") {
            $whereClause .=" AND gender = '$gender'";
          } 
          else { 
            $whereClause .= " AND gender IN ('m', 'f')";
          }
        } 
        else {
          if($gender != "a") {
            $whereClause = "gender = '$gender'";
          }
          else {
            $whereClause = "gender IN ('m', 'f')";
          }
        }
      }
    
    //begin pagination code 
    //links displayed only if more than 20 records returned)
      if (isset($_GET['pageno'])) {
        $pageno = $_GET['pageno'];
      } 
      else {
        $pageno = 1;
      }
    	
      if(!(isset($_SESSION['whereClause']))) {
        $_SESSION['whereClause'] = $whereClause;
      }
      else { // if $_SESSION['whereClause'] already has a value (possibly
               // empty...)
        if($_SESSION['whereClause'] != $whereClause) {
           // if safe, update the SESSION variable with the new value of
           // whereClause
          if( $whereClause != "") { 
            $_SESSION['whereClause'] = $whereClause; 
          } 
        }
      }
    	
      $_SESSION['f_name'] = $f_name;  //these three are defined
      $_SESSION['l_name'] = $l_name;  //so that the form fields
      $_SESSION['city'] = $city;      //can retain their values
    	
      $query = "SELECT count(*) FROM names WHERE
                  ".$_SESSION['whereClause'];
      $result = mysql_query($query, $conn) or die(mysql_error());
      $query_data = mysql_fetch_row($result);
      $numrows = $query_data[0];
      $rows_per_page = 20;
      $lastpage = ceil($numrows/$rows_per_page);
      $pageno = (int)$pageno;
      if ($pageno > $lastpage) {$pageno = $lastpage;}
        if ($pageno < 1) {$pageno = 1;}
          $limit = " LIMIT ".($pageno - 1) * $rows_per_page.", ".$rows_per_page;
    
    /*********************************************
    * Here's where the main SELECT statement is constructed  *
    *********************************************/
      $_SESSION['sql'] = $sql . $_SESSION['whereClause'] . $orderBy . $limit;
    
      $sql_result = mysql_query($_SESSION['sql'], $conn) or die(mysql_error());
      $num_rows = mysql_num_rows($sql_result);
    	
      if ($num_rows == 0) {
        include('incl/headerDirectory.htm'); 
        echo"<div id=\"contentMain\"><div id=\"directoryPreso\">
              <h3>Sorry about that...</h3><p style=\"border-bottom:none\">
              We found no members that satisfy your search. 
              Please return to <a href=\"directory2.php\">our directory page</a> 
              and perhaps try different search criteria, or use fewer criteria 
              to broaden your search.</p>
            ";
        echo"</div><!-- end div direcPreso --></div><!-- end contentMain -->"; 
        include('incl/footer.htm');
        echo"</div><!-- end wholePage --></body></html>";
        exit();
      }
    	
      if ($sql_result) { //this if closes about 62 lines below here...
        if ($num_rows == 1) {$member = "member";} 
        else {$member = "members";}  //for outputting number of records below.
        include('incl/headerDirectory.htm'); 
        echo"<div id=\"contentMain\"><div id=\"directoryPreso\">
             <h1>We found ".$numrows." ".$member."</h1><p id=\"theNames\">
             Click a <span style=\"color:blue; text-decoration:underline\">blue first
             name</span> to e-mail that person.</p>
            ";
    
        // Print pagination links only if there are more than $rows_per_page rows
        if($numrows > $rows_per_page) {
          echo"<p style=\"text-align:center; border-bottom:0\">";
          if($pageno == 1) {
            echo "First&nbsp;&nbsp;Prev&nbsp;&nbsp;";
          } 
          else {
            echo "<a href='{$_SERVER['PHP_SELF']}?pageno=1'>First</a>
                  &nbsp;&nbsp;
                 ";
            $prevpage = $pageno - 1;
            echo "<a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>Prev</a>
                     &nbsp;&nbsp;
                   ";
          }
          echo " (Page $pageno of $lastpage) ";
          if($pageno == $lastpage) {
            echo " Next Last ";
          } 
          else {
            $nextpage = $pageno + 1;
            echo "&nbsp;<a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>
                    Next</a>&nbsp;&nbsp;
                   ";
            echo "<a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>Last</a>";
          }
          echo "</p>";
        } 
    
        // Print the four column headings of the query results table.
        echo"<table id=\"presoTable\"><tr><th>Last Name</th><th>First
               Name</th><th>City</th><th>Phone Number</th></tr>
              ";  	
        //init color flag; alternate color by li.odd/even defined in css
        $rowColor = "odd";	
        while ($row = mysql_fetch_array($sql_result)) {
          //if the person has an email address in the db, link it to their first name
          if($row['address']!= NULL) { 
            $email_fName = 
                "<a href=\"mailto:".$row['address']."\">".$row['f_name']."</a>";
          }
          else { //if the person has no email address, just print their first name
            $email_fName = $row['f_name']; 
          } // print first and last name, city and phone to the results table.
          echo "<tr class=\"$rowColor\"><td>".$row['l_name']."</td><td>".
                  $email_fName."</td><td>".$row['city']."</td> <td>".$row['num'].
                  </td></tr>
                 ";
          $rowColor = ($rowColor == "odd") ? "even" : "odd";
        } //close the table and write the Return link to the form page.
        echo "</table><p id=\"return\">Return to the <a href=\"directory2.php\">
                 Directory page.</a></p>
                ";   
      } //this closes the big if($sql_result) construct
      echo"</div><!-- end directoryPreso div --></div>
              <!-- end contentMain -->
            ";
      include('incl/footer.htm');echo"</div></body></html>";
    
      function isValidLastName($alphaIn) {
        if(preg_match('/[A-z]+[ \'-]?([ A-z]*){0,3}[*%]?/', $alphaIn)) {
           return 1;
        }
        else return 0;
      }
    
      function isValidFirstName($alphaIn) {
        if(preg_match('/[A-z]+[\'-]?[A-z]*[*%]?/', $alphaIn)) {
          return 1;
        }
        else return 0;
      }
    
      function hasWildCard($nameIn) {
        if(preg_match('/^[A-z \'-]+%/', $nameIn)) {
          return 1;
        } 
        else return 0;
      }
    	
      function checkCity($cityIn) {
        $cityIn = ucwords(strtolower($cityIn));
        $cities = array('Alamo', 'Antioch', 'Atascadero', ... about fifty cities here);
        if(!(in_array($cityIn, $cities))) {
          return false;
        }
      }
    
      function nickName($nameIn) {
        $arr[0] = array(Bob, Robert, Rob, Bobby); 
        $arr[1] = array(Chuck, Charles, Charlie); 
        $arr[2] = array(Chris, Christopher);
        .
        . about seventy arrays used for finding and returning names similar to the
          name the visitor entered in the First Name field of the form.
    
        $arr[75] = array(Curtis, Curt, Kurt);
    
        $whereString = "f_name IN ('"; // initialize whereString
        for($i=0; $i < 76; $i++) {        // and search each of the mini arrays,
                                                 //  looking for a match to f_name
          $arrLen = count($arr[$i]);
          for($j=0; $j < $arrLen; $j++) {
            if($arr[$i][$j] == $nameIn) { // if you find a match, append each name
                                                   // in that mini array to $whereString
              for($k=0; $k < $arrLen; $k++) { 
                if($k < $arrLen - 1) {
                  $whereString .= $arr[$i][$k]."', '";
                }
                else { // After the final name is added to the IN clause, properly  
                  $whereString .= $arr[$i][$k]."')";
                  return $whereString;						
                }
              }
            }
          }
        } // this one closes for($j=0;...
      } // this one closes function nickName()
    ?>
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    one thing i did notice while perusing your code (i don't actually do php myself) is that you seem to be doing a SELECT COUNT(*) query with your WHERE clause, apparently for paging purposes

    you don't need to do that -- look up the FOUND_ROWS function instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    It would be equally helpful to see the structure of all these tables.

    PS I assume city is where they live, or is it something else?
    I'm asking because it is often easier to have the city in the names table, and accept a slightly denormalised table for less hassle when querying. Especially as it is unusual in a club database to have two cities with the same name, thus needing a unique id to differentiate them. Unless you have members from different countries of course, where a separate id would possibly be required.

    PPS wouldn't a phone's id just be the phone number - they do tend to be just a bit unique in my experience.

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Dr John View Post
    PPS wouldn't a phone's id just be the phone number - they do tend to be just a bit unique in my experience.
    i lolled at that one

    the classic "put an auto_increment PK on everything that moves" strategy

    curtis is trying to implement several many-to-many relationships, hence the need for the intermediate linking tables

    whether a username is actually related to more than one city might be a question for the business logic, but it's easy to see that a username might have multiple phones

    in this case, i would obviously use the phone number as the key, and thus the "linking" table would not actually need to link to the phones table (which would disappear), thus the "linking" table would just be a simple one-to-many table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you all for contributing.
    Addressing your recent posts, most recent first:
    I. r937 said: curtis is trying to implement several many-to-many relationships, hence the need for the intermediate linking tables. Yes, I've designed the db tables (seven in all) in this way more to practice and hone strict and proper normalization skills than to make things 'simple'. Heck, I could put city, phone and email in the main table along with last name, first name and gender and be done with it, and it would work perfectly well. But I want to learn to do it right -- no matter how small this project actually is (a bike club with about 350 names, 320 phone numbers, 280 email addresses, 48 cities, and 2 genders :-D ). This is a playground for me to develop my skills, not a mission-critical app. Still, I want to fix this "city" thing and make it work like it did before I broke out the city, email and phone to their own tables a couple weeks ago...

    II. No user name is related to more than one city. But I'll goof off with that later (after I fix this bug) so that r937 can show me how to resolve the "returning more than one row per person" problem he told me about.

    III. I can delete the phone table and use phone numbers as the keys to the phone numbers, but I'll do that after I / we fix this bug.

    IV. For Dr. John: Here's the structure of all seven tables (I hope this is what you need):
    1. names table // main table, w/ each member's ID value
    mem_id smallint(4) PK auto_increment //this is the member's id
    l_name varchar(20)
    f_name varchar(16)
    gender varchar(1)

    2. cities table
    city_id smallint(4) auto_increment PK
    city varchar(20) Unique

    3. IDcity table // links to cities table
    mem_id smallint(4) PK
    city_id smallint(3)

    4. email table
    email_id smallint(4) PK auto_increment
    address varchar(40) Unique

    5. IDemail table // links to email table
    mem_id smallint(4) PK
    email_id smallint(4)

    6. phone table
    phone_id smallint(4) PK auto_increment
    num varchar(12) Unique //phone number must be of form 123-456-0909

    7. IDphone table // links to phone table
    mem_id smallint(4)
    phone_id smallint(4)

    Yes, sir, it is easier to just put the city in the names table and accept a slightly less normalized structure (by extension the same could be said for phone and email, no?), but, again, I am doing it this way to learn how the big boys do it.

    V. r937 said: the php does me no good -- i still want to see the actual constructed mysql query that dies with the message that is in this thread's title
    Well, I appear to have organized my script in a manner such that I can't get the script to echo the query before the error message is generated. Can you tell me where in the script I need to insert that echo statement? I'll keep trying on my end...

    Thanks to everyone who is following my issue. I really appreciate everyone's patience.
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  20. #20
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Make the following change in your script and then run the form filling out the city field:

    Code:
    * Here's where the main SELECT statement is constructed  *
    *********************************************/
      $_SESSION['sql'] = $sql . $_SESSION['whereClause'] . $orderBy . $limit;
    
      $sql_result = mysql_query($_SESSION['sql'], $conn) or die(mysql_error());
      $num_rows = mysql_num_rows($sql_result);
    to

    Code:
    * Here's where the main SELECT statement is constructed  *
    *********************************************/
      $_SESSION['sql'] = $sql . $_SESSION['whereClause'] . $orderBy . $limit;
    
      $sql_result = mysql_query($_SESSION['sql'], $conn);
      echo $sql_result;

    and then paste back the value of $sql_result as echoed to your browser.

  21. #21
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The three lines in my script now look like this:
    Code:
    $_SESSION['sql'] = $sql . $_SESSION['whereClause'] . $orderBy . $limit;
    $sql_result = mysql_query($_SESSION['sql'], $conn);
    echo $sql_result;
    But my browser received only this:
    Unknown column 'city' in 'where clause'
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  22. #22
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I added these two lines of code just below my if($_POST['gender']) { construct and just ahead of the if (isset($_GET['pageno'])) { construct:
    Code:
    $sql = $sql.$whereClause.$orderBy;
    echo $sql;
    The message sent to my monitor was:
    SELECT l_name, f_name, city, num, address
    FROM names
    LEFT JOIN IDcity ON names.mem_id = IDcity.mem_id
    LEFT JOIN cities ON IDcity.city_id = cities.city_id
    LEFT JOIN IDphone ON names.mem_id = IDphone.mem_id
    LEFT JOIN phone ON IDphone.phone_id = phone.phone_id
    LEFT JOIN IDemail ON names.mem_id = IDemail.mem_id
    LEFT JOIN email ON IDemail.email_id = email.email_id
    WHERE city = 'Richmond'
    ORDER BY l_name, f_name
    Unknown column 'city' in 'where clause'
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  23. #23
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Check this code out, not 100&#37; sure but think this could be it.

    Code:
          echo "<tr class=\"$rowColor\"><td>".$row['l_name']."</td><td>".
                  $email_fName."</td><td>".$row['city']."</td> <td>".$row['num'].
                  </td></tr>
                 ";
    I count 11 double quote marks, should be an even number. Use an editing program that highlights syntaxes in different colour. I use EditPlus. When I cut and pasted it was the first thing that jumped out at me.

    You need an opening " before the final </td></tr>

  24. #24
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If, as you say, you wish to do it the correct way, then you drop the tables email and phone table.

    Next, edit the table IDemail to use address varchar(40) instead of email_id smallint(4), AND you then declare a joint primary key of address and mem_id, because at the present moment your current PK means you can only record one email per person - you haven't solved the many to many properly, and I see it as a one to many.

    Next, edit the table IDphone to use num varchar(12) instead of phone_id smallint(4), AND again declare a joint primary key of num and mem_id. Again, you are limited to one phone number per person if mem_id is used as the primary key.

    It is now properly normalised, with NO artificial fields added as primary keys, and allows multiple phone numbers and emails to be associated with a given member. Your existing tables phone and email both contain THREE superkeys and two candidate keys, because you invented an unnecessary artificial primary key in both cases. One hint is that you had to declare the phone number unique (which suggests it might be a superkey and candidate key in this case - (not always of course)).

    There is a mania for people to use an artificial auto-incrementing PK when they have a perfectly good natural PK already.

    It then simplifies your queries considerably.

    this is how the big boys do it

  25. #25
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    guelphdad, I count 12 double quotes in that echo line. At any rate, I swapped in single quotes for doubles for all instances except those that are escaped as XHTML, so the line now looks like this:
    Code:
    echo '<tr class=\"$rowColor\"><td>'.$row['l_name'].'</td>
    <td>'.$email_fName.'</td><td>'.$row['city'].'</td><td>'.$row['num'].
    '</td></tr>';
    Dr. John, thank you very much for the advice that will help me grow my data design and SQL skills. I'm writing all that down and will revisit all of it after we solve this problem of mine. I'll do my due diligence and discover what the heck a super key and candidate key are...

    In the mean time, I'm still troubleshooting my PHP and so far cannot find the flaw in the code that is the source of my city problem: Unknown column 'city' in 'where clause'
    Curtis
    Was it summer when the river ran dry, or was it just another dam?


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
  •