PHP - Mysql Queries

Hello, in short, here is my problem:

There are 2 tables:

a table called postcodes, which contains UK postcodes
and a table called wp_bp_xprofile_data (i want to integrate this search with the Buddypress plugin's tables) which contains the user data.

I tried searching this, but to be honest, i didn't quite know how to go about it..



Here is what i have:

$query1 = "SELECT value, user_id, (SQRT(POW((b.x - a.x), 2) + POW((b.y - a.y), 2))/1000) * 0.621 AS distance
    FROM postcodes a, postcodes b, wp_bp_xprofile_data
    WHERE a.outcode = '"$postcode"' AND b.outcode = wp_bp_xprofile_data.value
    HAVING (distance < '"$area"')
    ORDER BY distance asc ";


$result1=mysql_query($query1) or die(mysql_error());

        echo "<p style=\"font-size:10px;line-height:14px;color:#888;\">Straight line distances shown.<br>";
        echo "<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\" width=\"100%\" ><tr>";

        // display results
        while ($list = mysql_fetch_array($result1)) {
        $user_postcode = $list['value'];
        $distance = $list['distance'];
        $distance = round($distance);

   echo "<td align=\"right\">$distance miles away</td></tr>";
   echo "<tr><td colspan=\"3\" align=\"left\" width=\"130px\">$user_postcode </td>";
   echo "<td align=\"right\"><a href=\"blahblah\">Directions using Google Maps</a></td>";
   echo "<tr><td align=\"left\">&nbsp;</td><tr>";
        echo "</tr>";
}
echo "</table>";


Code: ("wp_bp_xprofile_data") [Select]
id field_id user_id value
1 1          1 admin
8 3          3 NW10
6 1          3 Test User 1
9 4          3 IT, Web Development
11 1          4 Test User 2
12 3          4 HA5
13 4          4 Test, It, Some Work
Code: ("postcodes") [Select]
outcode x y latitude longitude
B10 392900 804900 57 -2
AB11 394500 805300 57 -2
AB12 393300 801100 57 -2
AB13 385600 801900 57 -2
AB14 383600 801100 57 -2


Here is an example of what i need:

field_id "3" corresponds to a postcode. My script so far, can search a postcode and return results with the postcodes sorted by distance. What i'm trying to do, is also get the username on the result (i.e the 'value' column again..).

in short, im trying to search a postcode, find it on wp_bp_xprofile_data, associate it with the correspondent user, and return both.  Someone told me i need to INNER JOIN, but im lost..



The end result im looking for is if i type the search: "NW9 1AA"

It returns:

Test User 1
NW10
 

Test User 2
HA5   
(the code sorts them by distance. So far i can get it to return the postcode, but cannot associate the relevant name.

Can anyone help me with this?
I hope this is in the right forum.

Thanks for your attention.

I was wondering if it is possible to perform the following query.  Say I have a list of names each with an id number ranging from 1 to 10.  Is it possible to perform a query where you list all of these records while repeating one records.  For example could I do a query that lists record number 6 then 10, 9, 8, 7, 6 etc.  Or another example would be listing record number 10, then 10, 9, 8, 7, 6 etc?

Thanks for any help. 

   // Check for this userID if exsist in cp_credits
   $sql = "SELECT * FROM `cp_credits` WHERE account_id = $userid";
   
   // Execute Query
   $r = mysqli_query($dbc, $sql);
   
   
   // If there are result in $r
   if($r){
      // Print a message indicating success or not:
      while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)){
      $user_balance           = $row['balance'];
      $user_last_donation_date = $row['last_donation_date'];
   }
   }
   else{
   $user_balance = "nothing";
   $user_last_donation_date = "nothing";
   }

but either of my else statement does not work. Whats wrong with this?

Hi,
I would appreciate any help with this at all that you guys can give me.   I am trying to get a list of unique users from a mysql table, then get the records for each of those user.  I am then converting to a .csv and sending it through an email.  I have the following code and I always only get the same record.  The file name changes to the correct user but the data in the .csv is always only the first user.  Thanks in advance.
Code: [Select]
echo "getusers";
$server = "localhost";
$link = mysql_connect("$server", "$user", "$password");

mysql_select_db("$database");

$getusers = "select distinct(`User`) from realtordata";
$result1 = mysql_query($getusers);
$numusers = mysql_num_rows($result1);
$userarray[$numusers];
// $s = 0;
$userarray[$numusers];
$uservar = $userarray[$s];
echo "<table width = 5% border = '2'";
while ($row = mysql_fetch_array($result1))
{
$userarray[$s] = $row['User'];

// echo $userarray[$s];
$csv_file_name = "$uservar.csv";
echo "<tr><td>$userarray[$s] </td></tr>";
// $s++;
$mySQL_query = "SELECT  *  FROM `realtordata` where `User` = '$uservar'   order by `Status`";
$result = mysql_query($mySQL_query);
$numrecs = mysql_num_rows($getdata);
echo "<table  border = '1'";
echo "<th> User </th><th>Date Added</th><th>First Name</th><th>Last Name </th><th> Status</th><th> Loan Timeframe</th><th>Need Purchase Realtor</th>
<th> Real Estate Agent Name</th><th>Real Estate Agent Phone</th><th>Need Selling Realtor</th><th>Agent Company or Broker</th>
<th> Agent Email</th><th>Agent City</th><th>Agent State</th>";
while($row = mysql_fetch_array($result))
{
$s++;
echo "$uservar";
$userarray[$s] = $row['User'];
$user = $userarray[$s];
$date = $row['Date Added'];
$fname = $row['First Name'];
$lname = $row['Last Name'];
$timeframe = $row['Loan Timeframe'];
$needpurchaserealtor = $row['Need Purchase Realtor'];
$agentname = $row['Real Estate Agent Name'];
$agentphone = $row['Real Estate Agent Phone'];
$needsellingrealtor = $row['Need Selling Realtor'];
$agentcompanyorbroker = $row['Real Estate Agent Company or Broker'];
$agentemail = $row['Real Estate Agent Email'];
$agentcity = $row['Real Estate Agent City'];
$agentstate = $row['Real Estate Agent State'];

echo "<tr><td> $user </td>";
echo "<td> $date </td>";
echo "<td> $fname </td>";
echo "<td> $lname </td>";
echo "<td> $timeframe </td>";
echo "<td> $needpurchaserealtor </td>";
echo "<td> $agentname </td>";
echo "<td> $agentphone </td>";
echo "<td> $needsellingrealtor </td>";
echo "<td> $agentcompanyorbroker </td>";
echo "<td> $agentemail </td>";
echo "<td> $agentcity </td>";
echo "<td> $agentstate </td></tr>";


// Reuseable CSV file variables
$csv_contain = '"';
$csv_separate = ",";
$csv_end_row = "\n";
/*
// run the MySQL query and check to see if results were returned*/
$result = mysql_query($mySQL_query);
if (!$result) {
  die("ERROR: Invalid query \n MySQL error: " . mysql_error() . "\n Your query: " . $query);
}
echo "Step 3: MySQL query ran successfully. \n\n";

// store the number of columns from the results
$columns = mysql_num_fields($result);

// Build a header row using the mysql field names
$header_row = '';
for ($i = 0; $i < $columns; $i++) {
  $column_title = $csv_contain . stripslashes(mysql_field_name($result, $i)) . $csv_contain . $csv_separate;
  $header_row .= $column_title;
}
$csv_file .= $header_row . $csv_end_row; // add header row to CSV file

// Build the data rows by walking through the results array one row at a time
$data_rows = '';
while ($row = mysql_fetch_array($result)) {
  for ($i = 0; $i < $columns; $i++) {
    // clean up the data; strip slashes; replace double quotes with two single quotes
    $data_rows .= $csv_contain . preg_replace('/"/', '\'\'', stripslashes($row[$i])) . $csv_contain . $csv_separate;
  }
  $data_rows .= $csv_end_row; // add data row to CSV file
}
$csv_file .= $data_rows; // add the data rows to CSV file

echo "Step 4: CSV file built. \n\n";

/*
--------------------------------------
    SEND EMAIL WITH ATTACHMENT
    This requires the use of complex variable parsing {curly braces surrounding variable}
      For more information see: "PHP: Complex Variables in Strings" at Tinsology.net
      http://tinsology.net/2009/06/php-complex-variables-in-strings/

*/
// start setting up the email header
$headers = "From: ".$email_from;

// create boundary string
// boundary string must be unique using MD5 to generate a pseudo random hash
$random_hash = md5(date('r', time()));
$mime_boundary = "==Multipart_Boundary_x{$random_hash}x";

// set email header as a multipart/mixed message
// this allows the sending of an attachment combined with the HTML message
$headers .= "\nMIME-Version: 1.0\n" .
"Content-Type: multipart/mixed;\n" .
" boundary=\"{$mime_boundary}\"";

// multipart boundary for the HTML message
$email_message = "This is a multi-part message in MIME format.\n\n" .
"--{$mime_boundary}\n" .
"Content-Type:text/html; charset=\"iso-8859-1\"\n" .
"Content-Transfer-Encoding: 7bit\n\n" .
$email_html_msg . "\n\n";

// encode CSV file with MIME base64
// required for sending it as an email attachment
$data = chunk_split(base64_encode($csv_file));

// multipart boundary for the email attachment
$email_message .= "--{$mime_boundary}\n" .
"Content-Type: application/octet-stream;\n" .
" name=\"{$csv_file_name}\"\n" .
"Content-Disposition: attachment;\n" .
" filename=\"{$csv_file_name}\"\n" .
"Content-Transfer-Encoding: base64\n\n" .
$data . "\n\n";

// end the multipart message
$email_message .= "--{$mime_boundary}--\n";

// try to send the email and verify the results
$sendit = @mail($email_to, $email_subject, $email_message, $headers);
if(!$sendit) {
  die("ERROR: The Email could not be sent.");
}
echo "Step 5: Email sent with attachment. \n\n";
}

}