Lose the last contact after a mysql_fetch_array in loop

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
brewmiser
Forum Commoner
Posts: 74
Joined: Mon Aug 18, 2003 12:50 pm
Location: Dallas, TEXAS

Lose the last contact after a mysql_fetch_array in loop

Post by brewmiser »

Ok here is the problem. I am pulling data from a MySQL database and it is loseing the last item in a for loop. Basically if it finds 5 contacts to display it will count them properly using num_results, but when I try to display it, it only show's 4 of them !


Code: Select all

<?php  //Looking for contacts and bulding table for contacts!
$contact_result = mysql_query("SELECT * FROM contact WHERE company_id = '".($company&#1111;"company_id"])."' ORDER BY contact.last_name ASC") or die("<b>MySQL Error:</b> ".mysql_errno()." : ".mysql_error());

$contact = mysql_fetch_array($contact_result);
$num_results = mysql_num_rows($contact_result);
		
if (!($contact&#1111;"company_id"])=="") //If company_id exists, then build list
&#123;
  //Create phone number for display
  if ((!$contact&#1111;"direct_phone"])=="")
  &#123;
      $db_direct_phone = ($contact&#1111;"direct_phone"]);
      $new_direct_phone = "(".substr($db_direct_phone, -10, 3).") ".substr($db_direct_phone, -7, 3)."-".substr($db_direct_phone, -4, 4);
  &#125;
?>

<tr bgcolor="#D3D3D3"><td colspan=2><font face="Arial, Helvetica" size="2" color="Black"><b>Company Contacts</b></font></td></tr>
<tr>
  <td colspan=2 width=100%>
    <table border=0 width=100%>
       <tr>
	<td width=175><font face='Arial,Helvetica' size='2' 
color='Black'><b>Name</b></td>
<td><font face='Arial,Helvetica' size='2' color='Black'><b>Direct Phone/Extension</b></td>
	<td><font face='Arial,Helvetica' size='2' color='Black'><b>Email</b></td>
       </tr>

<?php	
echo $num_results . " contact(s) in the database for his company.";
for ($i=0; $i < $num_results; $i++)
&#123;
    $row = mysql_fetch_array($contact_result);
?>
<tr><td><font face='Arial,Helvetica' size='2' color='Black'>
<a href='view_results.php?id_contact=<?php echo ($row&#1111;'contact_id']); ?>'>
<?php  echo ($row&#1111;"first_name"])." ".($row&#1111;"last_name"]); ?> </a>
</font></td>
<td><font face='Arial,Helvetica' size='2' color='Black'>

<?php 
if((!$row&#1111;"direct_phone"])=="")
&#123;
    $db_direct_phone = ($row&#1111;"direct_phone"]);
    $new_direct_phone = "(".substr($db_direct_phone, -10, 3).") ".substr($db_direct_phone, -7, 3)."-".substr($db_direct_phone, -4, 4);
    echo $new_direct_phone;
&#125;
else if((!$row&#1111;"extension"])=="")&#123; echo " Ext ". ($row&#1111;"extension"])." "; &#125; 
?>
</font></td>
<td><font face='Arial,Helvetica' size='2' color='Black'>

<?php 
if (!$row&#1111;"email"]=="")&#123;echo "<a href='mailto: " . ($row&#1111;"email"]) . "'>Email</a>";
&#125; 
?>

</font></td>
</tr>
<?php
  &#125;
&#125;
?>
</table>
</td>
</tr>
It is long and messy right now, but it should display properly. Like I said I get the correct count in the num_results, but it always drops the last data row in my loop when I try to display it!! There is alot of html mixed in there with it, so it might get confusing. If I need to repost this in a cleaner fashion, please let me know. I have saw this problem on other forums, but no one had a solution.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

this line is the problem

Code: Select all

for ($i=0; $i < $num_results; $i++)
it is saying while $i is less than the number of results. If you have 5 results, the 5th will equate to false becuase 5 ISN'T less than 5.

Replace that line with

Code: Select all

for ($i=0; $i <= $num_results; $i++)
this says, while $i is less than OR equal to number of results.

Mark
User avatar
brewmiser
Forum Commoner
Posts: 74
Joined: Mon Aug 18, 2003 12:50 pm
Location: Dallas, TEXAS

Post by brewmiser »

Ok, I tried that, and still got the same thing. It counts 4 and only shows 3. Any other suggestions?
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

not the best way of doing it really, here is a better way, replace the for line with this

Code: Select all

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
and delete

Code: Select all

$row = mysql_fetch_array($contact_result);
Mark
User avatar
brewmiser
Forum Commoner
Posts: 74
Joined: Mon Aug 18, 2003 12:50 pm
Location: Dallas, TEXAS

Post by brewmiser »

Mark, I am about to give up. I just don't understand. I have almost exactly the same code in another file and it works fine.

I tried what you said, and when I did, it returned nothing. I did change $result to $contact_result (because that is my query variable) and still got nothing. :roll:
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Try this code:

Code: Select all

<?php 

// separate the SQL query from the function call and specify exactly which columns need to be returned
$sql = "SELECT company_id, direct_phone, extension, first_name, last_name, email FROM contact WHERE company_id = '".$company['company_id']."' ORDER BY contact.last_name ASC";
$results = mysql_query($sql) or die('<b>MySQL Error:</b> '.mysql_errno().' : '.mysql_error().'<p>'.$sql.'</p>');

$num_results = mysql_num_rows($results);

// check that there are results
if ($num_results > 0) {

?>
<!-- set-up the table -->
<tr bgcolor="#D3D3D3">
	<th colspan="2">
		<!--	You should always have a sans-serif option in the font face
				for those people who have neither Arial nor Helvetica.
				(Using CSS would cut down on all that repetitive typing) -->
		<font face="Arial, Helvetica, sans-serif" size="2" color="#000000">Company Contacts</font>
	</th>
</tr>
<tr>
	<td colspan="2"><font face="Arial, Helvetica, sans-serif" size="2" color="#000000">
		<?php echo $num_results; ?> contact(s) in the database for this company.
	</font></td>
</tr>
<tr>
	<td colspan="2" width="100%">
		<table border="0" width="100%">
		<tr>
			<th width="175"><font face="Arial, Helvetica, sans-serif" size="2" color="#000000">Name</font></th>
			<th><font face="Arial, Helvetica, sans-serif" size="2" color="#000000">Direct Phone/Extension</font></th>
			<td><font face="Arial, Helvetica, sans-serif" size="2" color="#000000">Email</font></th>
		</tr>

<?php
	// loop through the results
	while ($row = mysql_fetch_assoc($results)) {

		// check that there is an ID (although if you're using this as
		// a primary key it should be a forgone conclusion
		if (!empty($row['company_id'])) {

			// determine the phone number
			if (!empty($row['direct_phone'])) {
				$dp_area_code = substr($row['direct_phone'], -10, 3);
				$dp_number    = substr($row['direct_phone'], -7, 3).'-'.substr($row['direct_phone'], -4, -4);
				$phone_number = '('.$dp_area_code.')'.$dp_number;
			} elseif (!empty($row['extension'])) {
				$phone_number = $row['extension'];
			} else {
				$phone_number = '&nbsp;';
			}

			// format the email address
			$email = (!empty($row['email'])) ? '<a href="mailto:'.$row['email'].'">Email</a>' : '&nbsp;';

?>

		<tr>
			<td><font face="Arial, Helvetica, sans-serif" size="2" color="#000000">
				<a href="view_results.php?id_contact=<?php echo $row['contact_id']; ?>"><?php echo $row['first_name'].' '.$row['last_name']; ?></a>
			</font></td>
			<td><font face="Arial, Helvetica, sans-serif" size="2" color="#000000">
				<?php echo $phone_number; ?>
			</font></td>
			<td><font face="Arial, Helvetica, sans-serif" size="2" color="#000000">
				<?php echo $email; ?>
			</font></td>
		</tr>
<?php
		} // end check on contact_id
	} // end while loop
?>
		</table>
	</td>
</tr>

<?php

} // end check on number of results

?>
Mac
User avatar
brewmiser
Forum Commoner
Posts: 74
Joined: Mon Aug 18, 2003 12:50 pm
Location: Dallas, TEXAS

Post by brewmiser »

WOW, thanks twigletmac, that worked. Can you explain to me why my code would not work?

I am really new at writing code, I have been doing it for about 6 months. I have been doing basic webpages for 5 years now, but nothing to complicated. This is my first real webpage/php/mysql project, and it is for the engineering firm that I work for.

I am going to get my style sheets working, but I need to learn a little more about them as well.

Thanks again.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

It was mainly because you had this line

Code: Select all

$contact = mysql_fetch_array($contact_result);
which returned the first result from the result set, so that when you did:

Code: Select all

$row = mysql_fetch_array($contact_result);
in the for loop, you started with the second record and thus would always see one less displayed than had been returned.

If you're not sure how something in the code I gave you works, just post back and I'll try and give a more detailed explanation than the notes I added in at the time. Hopefully it has shown you a simpler way of looping through a result set.

Mac
Post Reply