Loop and query issue

[gwpaul]

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.

$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 
$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:

SELECT* 
FROM attorney
WHERE (
 'county' ||'county_2' ||'county_3'
) ='Montgomery County'
LIMIT 0 , 30

Can anyone point me in the right direction?

Gary

[SpacePhoenix]

<?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)?

[r937]

and yet another developer is pwned by mysql's backticks

this --

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 --

WHERE 'Montgomery County' IN ( county,county_2,county_3 )

sweet, eh?

[gwpaul]

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

$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

 $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

[r937]

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

see post #3 for the reason

[gwpaul]

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.

$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

$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