SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Loop and query issue

    I have (at least) two issues that are perplexing me.

    1. My loop is not working, it is giving me the one record (the first) 3 times.

    The query works.
    PHP Code:
    $county='Montgomery County';
    mysql_select_db($database_alphabb$alphabb);
    $query_Recordset1 "SELECT * FROM attorney WHERE ('county'||'county_2'||'county_3') = '$county'  ";
    $Recordset1 mysql_query($query_Recordset1$alphabb) or die(mysql_error());
    $row_Recordset1 mysql_fetch_assoc($Recordset1);
    $totalRows_Recordset1 mysql_num_rows($Recordset1); 
    The Loop

    PHP Code:
     <?php 
    $counter 
    0;
    $max 3;

      while ((
    $totalRows_Recordset1 0) && ($counter $max)){ // Show if recordset not empty ?>
        <?php 
    $counter
    ++;

    echo 
    '<br />';
    echo 
    "$row_Recordset1[logo]".'<br />';
    echo 
    '<h4>'"$row_Recordset1[law_firm]".'</h4>';
    echo 
    '<h5>'"$row_Recordset1[fname]"  .  $row_Recordset1[mint] " .  $row_Recordset1[lname]"' ESQ'.'</h5>'.'<br />';
    echo 
    "$row_Recordset1[street]".'<br />'
    .  $row_Recordset1[city]"
    .   $row_Recordset1[state]"
    .   $row_Recordset1[zip]";
    echo 
    '<br /><br />';
    echo 
    "$row_Recordset1[phone]".'<br />';
    echo 
    "<a href=$row_Recordset1[url]>".' Law Firm Web Site'.'</a>'.'<br />';
    echo 
    '<hr />';
    ?>
        <?php }  ?>
    The second issue is really weird in that I removed the first record's 'county' so it should not show up in the result, but it does. I ran the query through phpmyadmin and the first record STILL shows up, even though it has no county listed and should be bypassed by the query. The mysql query gives me 3 results (total in the table)

    The mysql query:

    Code:
    SELECT* 
    FROM attorney
    WHERE (
     'county' ||'county_2' ||'county_3'
    ) ='Montgomery County'
    LIMIT 0 , 30
    Can anyone point me in the right direction?

    Gary

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,034
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?php

    // Code to connect to MySQL and select db goes here


    $county='Montgomery County';

    $escaped_county=mysql_real_escape_string($county);

    $sql="
        SELECT
              logo
            , law_firm
            , fname
            , mint
            , lname
            , street
            , city
            , state
            , zip
            , phone
            , url
        FROM
            attorney
        WHERE
            ('county'||'county_2'||'county_3') = '
    $escaped_county'
    "
    ;
    $result mysql_query($sql) or die(mysql_error());

    // Set up an empty array (avoids errors with foreach loops if no matches)
    $attorneys=array();

    // Keep adding rows from the result set to the attorneys array until there are no more to add
    while ( $row mysql_fetch_assoc($result)) {
        
    $attorneys[]=$row;
    }

    if (empty(
    $attorney)) {
        echo 
    'There was no attorney found in $county';
    } else {
        foreach ( 
    $attorneys AS $attorney ) {
            echo 
    '<br />';
            echo 
    "$attorney[logo]".'<br />';
            echo 
    '<h4>'"$attorney[law_firm]".'</h4>';
            echo 
    '<h5>'"$attorney[fname]"  .  $attorney[mint] " .  $attorney[lname]"' ESQ'.'</h5>'.'<br />';
            echo 
    "$attorney[street]".'<br />'
            
    .  $attorney[city]"
            
    .   $attorney[state]"
            
    .   $attorney[zip]";
            echo 
    '<br /><br />';
            echo 
    "$attorney[phone]".'<br />';
            echo 
    "<a href=$attorney[url]>".' Law Firm Web Site'.'</a>'.'<br />';
            echo 
    '<hr />';
        }
    }

    ?>
    You should consider however migrating over to using either the mysqli_ extenstion or PDO as then you would have available the use of prepared statements.

    As for the query is there 3 county fields in the table (will some attorneys have offices in more then 1 county)?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    and yet another developer is pwned by mysql's backticks

    this --
    Code:
    WHERE (
     'county' ||'county_2' ||'county_3'
    ) ='Montgomery County'
    is never, ever going to be true

    you are comparing constants, i.e. literal strings, and by inspection, you can see they aren't equal

    now, assuming you have three columns called county, county_2, county_3, then you shouldn't put quotes around the column names

    neither should you put those horrible mysql backticks around the column names, either

    backticks are required if the column name contains a special character (e.g. `order#`) or is the same as a reserved word

    best practice says don't use special characters or reserved words for your column names, and yoy'll never have to use those ugly horrible evil backticks

    by the way, the mysql double pipes "or" operator ( || ) is non-standard sql, too

    you should rewrite that WHERE condition like this --
    Code:
    WHERE 'Montgomery County' IN ( county,county_2,county_3 )
    sweet, eh?

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

  4. #4
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for your reply.

    As for the query is there 3 county fields in the table (will some attorneys have offices in more then 1 county)?
    Yes, 3 columns, 3 different locations.

    The query is not returning any results, it should return 2. I get no error messages.

    It returns:

    "There was no attorney found in Montgomery County"

    (Again, very curious, when I put your query into the phpmyadmin, it returns 3, the first record I removed the 'Montgomery County', so it should only return 2).

    I changed the code slightly to keep with the existing funtions

    PHP Code:
    $county='Montgomery County';
    $escaped_county=mysql_real_escape_string($county);
    mysql_select_db($database_alphabb$alphabb);
    $query_Recordset1 "SELECT
              logo
            , law_firm
            , fname
            , mint
            , lname
            , street
            , city
            , state
            , zip
            , phone
            , url
        FROM
            attorney
        WHERE
            ('county'||'county_2'||'county_3') = '
    $escaped_county'
    "
    ;
    $Recordset1 mysql_query($query_Recordset1$alphabb) or die(mysql_error());
    $row_Recordset1 mysql_fetch_assoc($Recordset1);
    $totalRows_Recordset1 mysql_num_rows($Recordset1); 
    And

    PHP Code:

     $result 
    mysql_query($query_Recordset1) or die(mysql_error('Ooops, you died at $result'));

    // Set up an empty array (avoids errors with foreach loops if no matches)
    $attorneys=array();

    // Keep adding rows from the result set to the attorneys array until there are no more to add
    while ( $row_Recordset1 mysql_fetch_assoc($result)) {
        
    $attorneys[]=$row;
    }

    if (empty(
    $attorney)) {
        echo 
    'There was no attorney found in '."$escaped_county";
    } else {
        foreach ( 
    $attorneys AS $attorney ) {
            echo 
    '<br />';
            echo 
    "$attorney[logo]".'<br />';
            echo 
    '<h4>'"$attorney[law_firm]".'</h4>';
            echo 
    '<h5>'"$attorney[fname]"  .  $attorney[mint] " .  $attorney[lname]"' ESQ'.'</h5>'.'<br />';
            echo 
    "$attorney[street]".'<br />'
            
    .  $attorney[city]"
            
    .   $attorney[state]"
            
    .   $attorney[zip]";
            echo 
    '<br /><br />';
            echo 
    "$attorney[phone]".'<br />';
            echo 
    "<a href=$attorney[url]>".' Law Firm Web Site'.'</a>'.'<br />';
            echo 
    '<hr />';
        }

    Did I miss something?

    Again, thank you for your help.

    Gary

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by gwpaul View Post
    The query is not returning any results, it should return 2. I get no error messages.
    see post #3 for the reason
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you both very much for your replies.

    Thank you r937 for the lesson. I did get it to work with the following modifications. I will post incase it might help someone else.

    PHP Code:
    $county='Montgomery County';
    $escaped_county=mysql_real_escape_string($county);
    mysql_select_db($database_alphabb$alphabb);
    $query_Recordset1 "SELECT
              logo
            , law_firm
            , fname
            , mint
            , lname
            , street
            , city
            , state
            , zip
            , phone
            , url
        FROM
            attorney
       WHERE '
    $escaped_county' IN ( county, county_2, county_3 )";
       
    $Recordset1 mysql_query($query_Recordset1$alphabb) or die(mysql_error());
    $row_Recordset1 mysql_fetch_assoc($Recordset1);
    $totalRows_Recordset1 mysql_num_rows($Recordset1); 
    And the loop

    PHP Code:
    $counter = 0;
    $max = 3;

      while ($row_Recordset1 !="") { // Show if recordset not empty 
      
    $counter++;
    do {
    echo '<br />';
    echo "$row_Recordset1[logo]".'<br />';//look this up
    echo '<h4>'. "$row_Recordset1[law_firm]".'</h4>';
    echo '<h5>'. "$row_Recordset1[fname]"  .  " $row_Recordset1[mint] " .  " $row_Recordset1[lname]". ' ESQ'.'</h5>'.'<br />';
    echo "$row_Recordset1[street]".'<br />'
    .  " $row_Recordset1[city]"
    .   " $row_Recordset1[state]"
    .   " $row_Recordset1[zip]";
    echo '<br /><br />';
    echo "$row_Recordset1[phone]".'<br />';
    echo "<a href=$row_Recordset1[url]>".' Law Firm Web Site'.'</a>'.'<br />';
    echo '<hr />';
    }while  ($row_Recordset1 = mysql_fetch_array($Recordset1));
    ?>
        <?php // Show if recordset not empty ?>
    Space Phoenix, your loop was a little thicker than I was used to and I was not able to get it to work.

    Again, thank you both for your help.

    Gary


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
  •