MySQL not returning all fields

[LinusIT]

I've just setup my own Apache Server on Server 2008, everything is working fine apart from certain data isn't being returned. Basically I have my intranet site setup on a LinkStation box and want it moving to a dedicated server, everything works perfectly on the LinkStation.

I've copied the files from the LinkStation to the new server and also imported the .sql exports I made of the database. I can see all the data is there in phpMyAdmin.

The problem spans across many pages but maybe sorting one will help me fix the rest.

Example 1:

I've got a table that stores usernames, telephone numbers, extensions etc. All the data is being returned apart from the telephone number, this is setup to varchar in MySQL. The data is there but is showing up blank when viewing the page.

Example 2:

I've got a table that stores prices, all the price fields are int. Again, I can see the data in phpMyAdmin but not on the page.

Example 3:

Same as example 2 but this page is to edit the pricelist, in each input field it now shows <?=$row['field_name']?> instead of the result.

All the code works perfectly on the LinkStation but now I've copied it across I'm having errors. It's probably something I've missed when setting apache/php/mysql but this is my first time at doing so.

Any pointers gratefully received.

[penders]

...but now I've copied it across I'm having errors

Are you actually getting any errors reported? (error_reporting and display_errors set to report everything?)

Same / different versions of Apache and MySQL?

[LinusIT]

I've got error reporting turned on and it's returning no errors.

Linkstation

Lighttpd 1.4.23
MySQL 5.0.70

Server

Apache 2.2.15
PHP 5.3.2
MySQL 5.5.12

[brotherhood of LAN]

Is there a JOIN of multiple tables when outputting to PHP? If so and you are using the MYSQL_ASSOC flag in PHP for your MySQL function, try MYSQL_NUM. This would be significant if the joined tables share a column name.

[LinusIT]

There aren't any join statements, just simple queries.

SQL:

$staff_sql="SELECT * FROM users WHERE users_type = 1 ORDER BY users_firstname ASC";
$staff_result = mysql_query($staff_sql) or die(mysql_error());

HTML:

<table cellpadding="0" cellspacing="0" id="data">
<tr class="redheader">
<th class="w120">Name</th>
<th class="w120">Telephone</th>
<th class="w80">Email</th>
</tr>

<?php while($staffrow=mysql_fetch_array($staff_result)) { ?>
<tr>
<?php
$user_firstname = $staffrow['users_firstname'];
$user_lastname = $staffrow['users_lastname'];
?>
<td><?php echo $user_firstname . " " . $user_lastname;?></td>
<td><?=$staffrow['users_telephone'];?></td>
<?php
$user_email = $staffrow['users_email'];
?>
<td><a href="mailto:<?php echo $user_email; ?>"><?php echo $user_email; ?></a></td>
</tr>
<?php }?>
</table>

This works perfectly on the LinkStation but on the Server it's showing everything but the telephone number.

[LinusIT]

Thinki I've just noticed where the problem lies! Name & Email are echoed where the telephone number isn't. I'll do a test and see what happens.

[penders]

Name & Email are echoed where the telephone number isn't.

<td><?php echo $user_firstname . " " . $user_lastname;?></td>
<td><?=$staffrow['users_telephone'];?></td>

Ah, SHORT_OPEN_TAGS might not be enabled (in php.ini) on your new server?

[LinusIT]

It was that, I've changed all my code to <?php now.

I have one problem remaining, again it works on the LinkStation but not on my server. The error I'm getting is:

Notice: Undefined variable: row in C:\www\webroot\intranet\scrapped-cars\insert.php on line 167

Code:

<select id="manufacturer_id" name="manufacturer_id" class="w140">
<option selected="selected" value="0">Select A Make</option>
<? while($row=mysql_fetch_array($result1)) { ?>
<option value="<?php echo $row['manufacturer_id']; ?>"><?php echo $row['manufacturer_name']; ?></option>
<? } ?>
</select>

It's a drop down menu populated from a table in my database. I can't for the life of me see why it isn't working.

Any ideas?

[penders]

<? while($row=mysql_fetch_array($result1)) { ?>

I thought you'd said you'd changed all your code to

<?php

? :)

[rocknbil]

This is also why I detest dropping in and out of PHP on principle, it makes things so much harder to debug.

<select id="manufacturer_id" name="manufacturer_id" class="w140">
<option selected="selected" value="0">Select A Make</option>
<?php
// Why it's broken? Make sure $result 1 is working. I don't see
$result1 = mysql_query($select) or die("Cannot execute query");
while($row=mysql_fetch_array($result1)) {
echo "<option value=\"" . $row['manufacturer_id'] . "\">" . $row['manufacturer_name'] . "</option>\n";
}
?>
</select>

Better yet, what if there's no results, or if you want to set selected based on what's in post?

<?php 
$mfg_list=null; 
// your select statement 
$result1 = mysql_query($select) or die("Cannot execute query"); 
while($row=mysql_fetch_array($result1)) { 
 $mfg_list .= "<option value=\"" . $row['manufacturer_id']; 
 if (isset($_POST['manufacturer_id']) and ($_POST['manufacturer_id']==$row['manufacturer_id'])) { 
 $mfg_list .= ' selected="selected"'; 
 } 
 $mfg_list .= "\">" . $row['manufacturer_name'] . "</option>\n"; 
} 
if ($mfg_list) { 
 echo <select id=\"manufacturer_id\" name=\"manufacturer_id\" class=\"w140\"> 
 <option value=\"0\">Select A Make</option> 
 $mfg_list 
 </select>  
 "; 
} 
else { echo "<em>(No manufacturers found)</em>"; } 
?> 

No need to set selected on first list item. Leverage that . . .