SQL query not working properly

PHP
1

okay i have his sorry for all the bloat…

<?php

/*
$query = sprintf("SELECT 
						members.memberID,
						members.salutation,
						members.firstName,
						members.middleName,
						members.lastName,
						members.suffix,
						members.company,
						 
						addresses.address1,
						addresses.address2,
						addresses.city,
						addresses.state,
						addresses.postalCode,
						addresses.country,
						addresses.memberID,
						
						email.email,
						email.memberID,
						 
						phonenumbers.phoneNumber, 
						phonenumbers.memberId,
						
						subscriptions.year,
						subscriptions.memberID
					FROM 
						members,
						addresses,
						email,
						phonenumbers,
						subscriptions
					WHERE
						subscriptions.year = '%s'
							AND
						subscriptions.memberID = members.memberID
							AND
						subscriptions.memberID = addresses.memberID
							AND
						subscriptions.memberID = email.memberID
							AND
						subscriptions.memberID = phonenumbers.memberID
					ORDER BY 
						members.lastName,
						members.firstName,
						members.company
					LIMIT
						0, 10
						", mysql_real_escape_string($year));
*/

define('HOST', 'localhost');
	define('USERNAME', 'asdf');
	define('PASSWORD', 'asdf');
	define('DATABASE', 'asdf');

function db_connect()
	{
	  	$connection = @mysql_connect(HOST,USERNAME,PASSWORD);
		if (!$connection)
	  	{
	   		return false;
	  	} 
	 	if(!mysql_select_db(DATABASE, $connection)) 
	  	{
		  	return false;
	  	}
		return $connection;
	}
	function result_to_array($result)
	{
	  	$result_array = array();
		for ($i=0; $row = mysql_fetch_array($result) ; $i++)
		{
		   	$result_array[$i] = $row; 
		}
		return $result_array;
	}

function find_members($year)
	{
		$connection = db_connect();
			
	    $query = sprintf("SELECT 
						members.memberID,
						members.salutation,
						members.firstName,
						members.middleName,
						members.lastName,
						members.suffix,
						members.company,
						 
						addresses.address1,
						addresses.address2,
						addresses.city,
						addresses.state,
						addresses.postalCode,
						addresses.country,
						addresses.memberID,
						
						email.email,
						email.memberID,
						 
						phonenumbers.phoneNumber, 
						phonenumbers.memberId,
						
						subscriptions.year,
						subscriptions.memberID
					FROM 
						members,
						addresses,
						email,
						phonenumbers,
						subscriptions
					WHERE
						subscriptions.year = '%s'
							AND
						subscriptions.memberID = members.memberID
							AND
						subscriptions.memberID = addresses.memberID
							AND
						subscriptions.memberID = email.memberID
							AND
						subscriptions.memberID = phonenumbers.memberID
					ORDER BY 
						members.lastName,
						members.firstName,
						members.company
LIMIT 0, 10
						", mysql_real_escape_string($year));
			
		$result = mysql_query($query);	
		$number_of_members = mysql_num_rows($result);
		/*echo $number_of_members;	
		if ($number_of_members == 0) 
		{
			 return false;	
		}
		
		$result = result_to_array($result);

		return array('result' => $result);*/
		
	}
	$year = 2010;
	$members = find_members(2010);
	?>
	<p>This is working correctly</p>
	<?php echo $number_of_members; ?>
<?php if($members['result']): ?>
<table border="1">
<tr>
<th>Member ID</th>
<th>Salutation</th>
<th>First Name</th>
<th>Middle Name</th>
<th>Last Name</th>
<th>Suffix</th>
<th>Company</th>
<th>Address 1</th>
<th>Address 2</th>
<th>City</th>
<th>State</th>
<th>Postal Code</th>
<th>Country</th>
<th>First Class</th>
<th>Email Address</th>
<th>Phone Number</th>
</tr>
	<?php foreach($members['result'] as $member): ?>
		<tr>
<td><?php echo $member['memberID']; ?></td>
<td><?php echo $member['salutation']; ?></td>
<td><?php echo $member['firstName']; ?></td>
<td><?php echo $member['middleName']; ?></td>
<td><?php echo $member['lastName']; ?></td>
<td><?php echo $member['suffix']; ?></td>
<td><?php echo $member['company']; ?></td>
<td><?php echo $member['address1']; ?></td>
<td><?php echo $member['address2']; ?></td>
<td><?php echo $member['city']; ?></td>
<td><?php echo $member['state']; ?></td>
<td><?php echo $member['postalCode']; ?></td>
<td><?php echo $member['Country']; ?></td>
<td><?php echo '0'; ?></td>
<td><?php echo $member['email']; ?></td>
<td><?php echo $member['phonenumber']; ?></td>
</tr>

 	<?php	endforeach; ?>
</table> 
<?php endif; ?>

my problem is its a huge query so Im trying to limit it to so many at a time… its supposed to have over 5000 results… anyway the only limit that works is limit 0, 10 if you do anything else 5, 10 it doesnt work 0, 50 doesnt work… only 0, 10 works…

and when I do 0, 10 the query returns blake firstName, middleName, lastName, and a few others… and when I do a print_r() on the $result it shows them blank as well and there is most def data in the database and there is also no typos for that…

basically im in a heap of mess…

2
  1. Try running the query in PHPMyAdmin and see if it gives the correct results.
  2. Also, echo out the value of $query in your script and see if it is exactly as you expect it to be.
  3. Change

$result = mysql_query($query);

to


$result = mysql_query($query) or die('SQL error ' . mysql_error() . ' in query: ' . $query);

It’ll give you some info in case of an error.

3

What errors if any is MySQL giving you?

Can I suggest a change to one of your php functions?

function result_to_array($result) {
    $result_array=array();
    while ( $row = mysql_fetch_array($result)) {
        $result_array[] = $row;
    }
    return $result_array;
}

Also can I suggest that you switch to using the join syntax?