[SOLVED] PHP code not fetching all values from mysql db

[snorky]

I have a mysql database and PHP code that are working great .... except that the code does not fetch the values from 2 of the fields.

+---------+---------------+------+-----+---------+----------------+

| Field  | Type          | Null | Key | Default | Extra          |

+---------+---------------+------+-----+---------+----------------+

| id      | int(11)      |      | PRI | NULL    | auto_increment |

| lname  | varchar(50)  | YES  |    | NULL    |                |

| bldg    | varchar(50)  | YES  |    | NULL    |                |

| room    | varchar(20)  | YES  |    | NULL    |                |

| phone  | varchar(50)  | YES  |    | NULL    |                |

| fax    | varchar(9)    | YES  |    | NULL    |                |

| affil  | varchar(6)    | YES  |    | NULL    |                |

+---------+---------------+------+-----+---------+----------------+

 

/*  fetch the data, one row at a time  */

while($row=mysql_fetch_array($rpt1))
{
$id=	$row["id"];
$lname=	$row["lname"];
$bldg=	$row["bldg"];
$room=	$row["room"];
$phone=	$row["phone"];
$fax=	$row["fax"];	
$affil=	$row["affil"];

/*  then print the report  */

echo  
"<tr>
	<td>" . $id . "</td>
	<td>" . $lname . "</td>
	<td>" . $bldg . "</td>
	<td>" . $room . "</td>
	<td>" . $phone . "</td>
	<td>" . $fax . "</td>
	<td>" . $affil . "</td>
</tr>" ;
}

 

No matter what values are in "room" and "fax", the fetch _array does not fetch the values for those two fields in any row. It must be a "fetch" problem because I have tweaked the code to move items around. No matter what I do, it is only "room" and "fax" that are wrong (empty) and they are always wrong.

 

I even tried commenting out the actual report and built a simple temporary report without the table and with only a few fields. Everything printed correctly except  "room" and "fax"

 

In the real report, all of the other data display as designed. The only errors are the missing data in those two fields.

 

I'm looking for a 'sanity check.' Somewhere in there I've missed a comma or some such stupid error that I can't see. 

 

[mikesta707]

try mysql_fetch_assoc instead of mysql_fetch_array

[hostingon]

Please sent and your query to check it.

 

Also, try just like this directly:

 

while($row=mysql_fetch_array($rpt1))
{
/*  directly print the report  */

echo  
"<tr>
	<td>" . $row[id] . "</td>
	<td>" . $row[lname] . "</td>
	<td>" . $row[bldg] . "</td>
	<td>" . $row[room] . "</td>
	<td>" . $row[phone] . "</td>
	<td>" . $row[fax] . "</td>
	<td>" . $row[affil] . "</td>
</tr>" ;

}

[snorky]

I changed mysql_fetch_array to mysql_fetch_assoc. It made no difference.

 

The upside is that I learned about mysql_fetch_assoc, which I have never used (until now).  Thanks.

[snorky]

As per your suggestion: printing directly from the mysql_fetch_array made no difference.

[mikesta707]

can you post your query?

[snorky]

I don't understand what you're asking.

 

The mysql query is

 

SELECT id,lname,bldg,room, phone, fax,affil  FROM main;

 

It works as expected. All fields display all data (including room and fax). That means that all of the data in all of the fields are in the db.

[mikesta707]

are you sure that those columns don't have null values in your table. If so, and your query works as expected, I don't see a problem in your code. there were no query errors or anything like that correct?

[snorky]

Number of rows : 914

 

Number of rows where room !="" : 747

Number of rows where room ="" : 167

Number of rows where room IS NULL: 0

 

Number of rows where FAX !="" : 58

Number of rows where FAX ="" : 0

Number of rows where room IS NULL: 856

 

However, none of those values impact the results of the mysql_fetch_array. In the report based on the result set for mysql_fetch_array, all rows come up empty  for room and fax.

[mikesta707]

interesting. hmm, i'm at a loss then. The rest of the results are different right? as in row 0 in the table shows up as the first row, row 1 as 2nd row etc. It COULD be only returning 1 row, and the table gets populated with the results from that row, and that row has null or empty values for the fax and room columns. but I don't know how that would even be possible

 

also, it appears that the fax column is all uppercase. try replacing "fax," in your query with "FAX," and check the case of room also. As you probably already know, SQL is case sensitive, so this may be causing problems also

[PFMaBiSmAd]

What does a "view source" in your browser show for the page?

[snorky]

To quote myself (from the original post)

 

"Somewhere in there I've missed a comma or some such stupid error that I can't see." 

 

First, an explanation.

[*]The part that we have discussed is a small piece of a much larger project.

[*]I try to build my code as modular as possible. I break down the big task into smaller tasks. The smaller tasks wind up as small scripts or in some cases, functions. The big task is handled as a 'home' page with a lot of included files. The report procedure was in an include that was included in an include.

 

So the trees got in the way of my seeing the forest (to badly use a metaphor).

 

While pondering replies (all very good, thank you), I realized I had been focused on the lower-level includes. What I had not examined closely: the home page and its first-level includes. The home page had an include that contained the query around which everything else is built.

 

The person who requested this report did not want 'room' or 'fax' to appear.

Mistake #1 was to remove those fields from the original query - instead of omitting them from the report.

Mistake #2 was forgetting that that I removed them way up near the top level.

 

When I looked at the original query way up there on the home page, clear as could be was the answer AND my comment about why those fields were omitted. du-oh!

 

So I put 'room' and 'fax' back into the original query (and fixed the comment) ... and this report works!

 

Thank you all. I learned a bunch of good stuff. And I got a concussion and waffle-like imprints on my face - from beating my head on the keyboard.  :facepalm:

 

The good news: the lady for whom I write a lot of stuff usually sends over something chocolate as appreciation for my work. I can hardly wait!