SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 28
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    Eastland
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Will this code allow me to ORDER BY "distance"?

    My code returns rows of information like model numbers, company name, city, state... The last column of each row displays the distance in miles between the company listed in the row and the logged in (SESSION) company according to their zip codes.
    I am able to ORDER BY state and/or company name but I need the results to be ordered by the distance in miles with the closest distance listed first.
    The code is rather long so I've divided it up to, hopefully, make it easier to read. Thanks for any assistance.

    Here is my initial query:
    PHP Code:
    $val=$_POST["tranwild"]; // From submitted form 

    $query "SELECT *,date, DATE_FORMAT( `date` , '%m/%d/%Y' ) AS formatted FROM transfers WHERE modelnum LIKE '".$val."%' ORDER BY state ASC, coname ASC"
    $result=mysql_query($query); 
    if (!
    $result) die('In order to complete a wildcard search....'); 
    Here is the function to calculate the distance:
    PHP Code:
    $zipTable 'zip_codes'

    // Function to calculate the distance of the zip codes 

    function calcDist($lat_A$long_A$lat_B$long_B) { 

    $distance sin(deg2rad($lat_A)) 
    sin(deg2rad($lat_B)) 
    cos(deg2rad($lat_A)) 
    cos(deg2rad($lat_B)) 
    cos(deg2rad($long_A $long_B)); 

    $distance = (rad2deg(acos($distance))) * 69.09

    return 
    $english_format_number number_format($distance0'.'''); 


    Here is the while loop with the html removed:
    PHP Code:
    while ($fields = mysql_fetch_array($result)) { 
    ?> 

    <?=$fields['modelnum']?> 
    <?=$fields['coname'].', '?> 
    <?=$fields['city'].', '.$fields['state']?> 
    <?=$fields['formatted']?>
    And here is the rest of the code:

    PHP Code:
    // $content will be echoed for errors and the distance when applicable 

    global $content

    // Check for zip code one to exist, for length, and validity 

    $one $_SESSION['zip']; 
    if(!
    is_numeric($one)) $content .= 'Zip code one is invalid.<br/>'
    elseif(
    strlen($one) < 5$content .= 'Zip code one is not long enough.<br/>'
    $zipCount 1

    // Same thing for zip code two 

    $two $fields['zip']; 
    if(!
    is_numeric($two)) $content .= 'Zip code two is invalid.<br/>'
    elseif(
    strlen($two) < 5$content .= 'Zip code two is not long enough.<br/>'
    elseif(isset(
    $zipCount)) $zipCount++; 

    else 
    $two ''

    $result1 mysql_query("SELECT latitude,longitude 
    FROM 
    $zipTable 
    WHERE zip = '
    $one
    LIMIT 1"
    ); 

    $result2 mysql_query("SELECT latitude,longitude 
    FROM 
    $zipTable 
    WHERE zip = '
    $two
    LIMIT 1"
    ); 

    if(
    mysql_num_rows($result1) == 0$content .= 'Zip code one not found in the database.<br/>'
    else { 
    $row mysql_fetch_row($result1); 
    $lat_A $row[0]; 
    $long_A $row[1]; 

    if(
    mysql_num_rows($result2) == 0$content .= 'Zip code two not found in the database.<br/>'
    else { 
    $row mysql_fetch_row($result2); 
    $lat_B $row[0]; 
    $long_B $row[1]; 



    // If both zips were found in the database, calculate the distance! 

    $content ' 'calcDist($lat_A$long_A$lat_B$long_B) . ' miles.'

    echo 
    $content
    If this code will not allow me to order the results by distance, I'd appreciate it if you could point me in a new direction. Thanks.

  2. #2
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    do you test with large amount of zip codes?

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can do this with PHP coding not a SQL statement. Loop through your result set from the SQL query and add it to a PHP array while calculating your distance function on the values from each row and assigning the integer for distance to a variable. Once you have this in a mutidimensional array you can use array_mutilsort to sort by the distance variable. Here is some sample code to give you an idea.

    PHP Code:
    while ($fields mysql_fetch_array($result)) {
        
    //enter code to calculate the distance and assign it to a variable. In this example the variable would be $distance
        
    $data[] = array('modelnum' => $fields[modelnum], 'coname' => $fields[coname], 'city' => $fields[city], 'state' => $fields[state], 'distance' => $distance); 


    foreach (
    $data as $key=>$row) { 
        
    $distance[$key] = $row['distance']; 


    array_multisort($distanceSORT_ASC$fields); 

    foreach (
    $fields as $key=>$value) { 
        foreach (
    $fields[$key] as $innerkey=>$innervalue) { 
        print 
    $innerkey.": ".$innervalue."<br>"
        } 


  4. #4
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    Eastland
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks pworley. I'm getting some errors but I'll keep working.
    The errors are:
    Warning: array_multisort(): Argument #3 is expected to be an array or a sort flag in /page.php on line xxx.

    Warning: Invalid argument supplied for foreach() in /page.php on line xxx.

  5. #5
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There are some errors in my code. Replace the $fields variable in the array_multisort with $data and the $fields variable with $data in the foreach statement.
    PHP Code:
    while ($fields mysql_fetch_array($result)) { 
        
    //enter code to calculate the distance and assign it to a variable. In this example the variable would be $distance 
        
    $data[] = array('modelnum' => $fields[modelnum], 'coname' => $fields[coname], 'city' => $fields[city], 'state' => $fields[state], 'distance' => $distance); 


    foreach (
    $data as $key=>$row) { 
        
    $distance[$key] = $row['distance']; 


    array_multisort($distanceSORT_ASC$data); 

    foreach (
    $data as $key=>$value) { 
        foreach (
    $data[$key] as $innerkey=>$innervalue) { 
        print 
    $innerkey.": ".$innervalue."<br>"
        } 


  6. #6
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    Eastland
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your time. I've inserted your code and get the correct number of rows but there is no distance calculation. Here's an example of one of the rows:
    modelnum: L2800DT-R
    coname: Harvy's Tractor & Supply
    city: Amarillo
    state: TX
    distance:

    As you can tell I'm pretty much a newbie but I'm still working on it.

  7. #7
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    While you are looping through your database results you will need to perform your distance calculation. I will incorporate your code in with mine.

    PHP Code:
    while ($fields mysql_fetch_array($result)) { 
        
    //enter code to calculate the distance and assign it to a variable. In this example the variable would be $distance

    $result1 mysql_query("SELECT latitude,longitude 
    FROM 
    $zipTable 
    WHERE zip = '
    $one
    LIMIT 1"
    ); 

    $result2 mysql_query("SELECT latitude,longitude 
    FROM 
    $zipTable 
    WHERE zip = '
    $two
    LIMIT 1"
    ); 

    if(
    mysql_num_rows($result1) == 0$content .= 'Zip code one not found in the database.<br/>'
    else { 
    $row mysql_fetch_row($result1); 
    $lat_A $row[0]; 
    $long_A $row[1]; 

    if(
    mysql_num_rows($result2) == 0$content .= 'Zip code two not found in the database.<br/>'
    else { 
    $row mysql_fetch_row($result2); 
    $lat_B $row[0]; 
    $long_B $row[1]; 



    // If both zips were found in the database, calculate the distance! 

    $distance calcDist($lat_A$long_A$lat_B$long_B); 


        
    $data[] = array('modelnum' => $fields[modelnum], 'coname' => $fields[coname], 'city' => $fields[city], 'state' => $fields[state], 'distance' => $distance); 


    foreach (
    $data as $key=>$row) { 
        
    $distance[$key] = $row['distance']; 


    array_multisort($distanceSORT_ASC$data); 

    foreach (
    $data as $key=>$value) { 
        foreach (
    $data[$key] as $innerkey=>$innervalue) { 
        print 
    $innerkey.": ".$innervalue."<br>"
        } 


  8. #8
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    Eastland
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm getting close. It is calculating the correct distance but the order is not ASC, and I still get this warning:
    Warning: array_multisort(): Argument #1 is expected to be an array or a sort flag in ....

  9. #9
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try changing the following code from
    PHP Code:
    foreach ($data as $key=>$value) { 
        foreach (
    $data[$key] as $innerkey=>$innervalue) { 
        print 
    $innerkey.": ".$innervalue."<br>"
        } 

    to

    PHP Code:
    foreach ($distance as $key=>$value) { 
        foreach (
    $distance[$key] as $innerkey=>$innervalue) { 
        print 
    $innerkey.": ".$innervalue."<br>"
        } 


  10. #10
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    Eastland
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No change...same results except there is a Warning: Invalid argument supplied for foreach() in ..... along with the prior Warning: in the previous test.

  11. #11
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is some test code that i have verified to work.

    PHP Code:
    //simulate results from a database thrown in a mutidimensional PHP array

    $data[] = array('modelnum' => "model133"'coname' => "Coname133"'city' => "City133"'state' => "State133"'distance' => "133");
    $data[] = array('modelnum' => "model66"'coname' => "Coname66"'city' => "City66"'state' => "State66"'distance' => "66");
    $data[] = array('modelnum' => "model33"'coname' => "Coname33"'city' => "City33"'state' => "State33"'distance' => "33"); 
    $data[] = array('modelnum' => "model99"'coname' => "Coname99"'city' => "City99"'state' => "State99"'distance' => "99"); 
     


    foreach (
    $data as $key=>$row) { 
        
    $distance[$key] = $row['distance']; 


    array_multisort($distanceSORT_ASC$data); 

    foreach (
    $data as $key=>$value) { 
        foreach (
    $data[$key] as $innerkey=>$innervalue) { 
        print 
    $innerkey.": ".$innervalue."<br>"
        } 

    This produces the following output

    modelnum: model33
    coname: Coname33
    city: City33
    state: State33
    distance: 33
    modelnum: model66
    coname: Coname66
    city: City66
    state: State66
    distance: 66
    modelnum: model99
    coname: Coname99
    city: City99
    state: State99
    distance: 99
    modelnum: model133
    coname: Coname133
    city: City133
    state: State133
    distance: 133

  12. #12
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    Eastland
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your time and patience.
    This is what I get
    Warning: array_multisort(): Argument #1 is expected to be an array or a sort flag in /page.php on line 182
    modelnum: model133
    coname: Coname133
    city: City133
    state: State133
    distance: 133
    modelnum: model66
    coname: Coname66
    city: City66
    state: State66
    distance: 66
    modelnum: model33
    coname: Coname33
    city: City33
    state: State33
    distance: 33
    modelnum: model99
    coname: Coname99
    city: City99
    state: State99
    distance: 99

    and these results are repeated 7 times which is the number of rows returned from the search query.

  13. #13
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What version of PHP are you using? Also, could you post the exact code you are using in the page.php file? Maybe I can see what is going on.

  14. #14
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    Eastland
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Version 4.0
    And here is the code, sorry for all the html and for not being formatted for this forum.

    <body>

    <form name="tranwild" action="<?$_SERVER['PHP_SELF']?>" method="post">
    <b><font face="Arial" size="2">Wildcard Search For Available Transfers</b></font><br />
    <font face="arial" color="blue" size="1">Do not use the ( * ) symbol with this search.&nbsp; Simply enter what you need.<br />Entries are not case sensitive.</font><br />

    <input type="text" name="tranwild" style='border-width:1; width:"100"; border-color:#000000'>&nbsp;&nbsp;
    <input type="submit" value="Go" style='border-width:1; border-color:#000000' onClick='check_fields();'>
    </form>

    <?
    //================== PROCESS TRANSFERS WILDCARD SEARCH

    if (isset($_POST['tranwild'])) { // checks to see if the tranwild input field is set - if so, execute the block below.


    $val=$_POST["tranwild"]; // From submitted form

    $query = "SELECT *, DATE_FORMAT( `date` , '%m/%d/%Y' ) AS formatted FROM transfers WHERE modelnum LIKE '".$val."%'";
    $result=mysql_query($query);
    if (!$result) die('In order to complete a wildcard search, you must enter your data in the search field. Please try your search again.');

    ?>
    <div align="center">
    <table border="0" cellpadding="0" style="border-collapse: collapse" width="100%" height="20">
    <tr>
    <td align="left"><font face="Arial" size="2" color="black">
    <?if(mysql_num_rows($result) == 0) echo 'No record found.<br/>';
    ?>
    </td>
    </tr>
    </table>
    </div>
    <?
    $zipTable = 'zip_codes';

    // Function to calculate the distance of the zip codes

    function calcDist($lat_A, $long_A, $lat_B, $long_B) {

    $distance = sin(deg2rad($lat_A))
    * sin(deg2rad($lat_B))
    + cos(deg2rad($lat_A))
    * cos(deg2rad($lat_B))
    * cos(deg2rad($long_A - $long_B));

    $distance = (rad2deg(acos($distance))) * 69.09;

    return $english_format_number = number_format($distance, 0, '.', '');

    }

    while ($fields = mysql_fetch_array($result)) {

    $bg = ($bg == "#FFF9F0") ? "#FFFFFF" : "#FFF9F0";
    ?>
    <div align="center">
    <table border="1" cellpadding="0" style="border-collapse: collapse" width="700">
    <tr>

    <td align="center" width="130" valign="middle" bgcolor=<? echo "$bg" ?>>
    <?
    // $content will be echoed for errors and the distance when applicable
    global $content;

    // Check for zip code one to exist, for length, and validity

    $one = $_SESSION['zip'];
    if(!is_numeric($one)) $content .= 'Zip code one is invalid.<br/>';
    elseif(strlen($one) < 5) $content .= 'Zip code one is not long enough.<br/>';
    $zipCount = 1;

    // Same thing for zip code two

    $two = $fields['zip'];
    if(!is_numeric($two)) $content .= 'Zip code two is invalid.<br/>';
    elseif(strlen($two) < 5) $content .= 'Zip code two is not long enough.<br/>';
    elseif(isset($zipCount)) $zipCount++;

    else $two = '';

    $result1 = mysql_query("SELECT latitude,longitude
    FROM $zipTable
    WHERE zip = '$one'
    LIMIT 1");

    $result2 = mysql_query("SELECT latitude,longitude
    FROM $zipTable
    WHERE zip = '$two'
    LIMIT 1");

    if(mysql_num_rows($result1) == 0) $content .= 'Zip code one not found in the database.<br/>';
    else {
    $row = mysql_fetch_row($result1);
    $lat_A = $row[0];
    $long_A = $row[1];

    if(mysql_num_rows($result2) == 0) $content .= 'Zip code two not found in the database.<br/>';
    else {
    $row = mysql_fetch_row($result2);
    $lat_B = $row[0];
    $long_B = $row[1];
    }
    }

    // If both zips were found in the database, calculate the distance!

    $distance = calcDist($lat_A, $long_A, $lat_B, $long_B);


    $data[] = array('modelnum' => "model133", 'coname' => "Coname133", 'city' => "City133", 'state' => "State133", 'distance' => "133");
    $data[] = array('modelnum' => "model66", 'coname' => "Coname66", 'city' => "City66", 'state' => "State66", 'distance' => "66");
    $data[] = array('modelnum' => "model33", 'coname' => "Coname33", 'city' => "City33", 'state' => "State33", 'distance' => "33");
    $data[] = array('modelnum' => "model99", 'coname' => "Coname99", 'city' => "City99", 'state' => "State99", 'distance' => "99");
    }

    foreach ($data as $key=>$row) {
    $distance[$key] = $row['distance'];
    }

    array_multisort($distance, SORT_ASC, $data);

    foreach ($data as $key=>$value) {
    foreach ($data[$key] as $innerkey=>$innervalue) {
    print $innerkey.": ".$innervalue."<br>";
    }
    }
    }
    ?>
    </td>
    </tr>
    </table>
    </div>

    </body>

  15. #15
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try replacing with this modified code.
    PHP Code:
    <body>

    <form name="tranwild" action="<?$_SERVER['PHP_SELF']?>" method="post">
    <b><font face="Arial" size="2">Wildcard Search For Available Transfers</b></font><br />
    <font face="arial" color="blue" size="1">Do not use the ( * ) symbol with this search.&nbsp; Simply enter what you need.<br />Entries are not case sensitive.</font><br />

    <input type="text" name="tranwild" style='border-width:1; width:"100"; border-color:#000000'>&nbsp;&nbsp;
    <input type="submit" value="Go" style='border-width:1; border-color:#000000' onClick='check_fields();'>
    </form>

    <?
    //================== PROCESS TRANSFERS WILDCARD SEARCH 

    if (isset($_POST['tranwild'])) { // checks to see if the tranwild input field is set - if so, execute the block below.


    $val=$_POST["tranwild"]; // From submitted form

    $query "SELECT *, DATE_FORMAT( `date` , '%m/%d/%Y' ) AS formatted FROM transfers WHERE modelnum LIKE '".$val."%'";
    $result=mysql_query($query);
    if (!
    $result) die('In order to complete a wildcard search, you must enter your data in the search field. Please try your search again.');

    ?>
    <div align="center">
    <table border="0" cellpadding="0" style="border-collapse: collapse" width="100%" height="20">
    <tr>
    <td align="left"><font face="Arial" size="2" color="black">
    <?if(mysql_num_rows($result) == 0) echo 'No record found.<br/>';
    ?>
    </td>
    </tr>
    </table>
    </div>
    <?
    $zipTable 
    'zip_codes';

    // Function to calculate the distance of the zip codes

    function calcDist($lat_A$long_A$lat_B$long_B) {

    $distance sin(deg2rad($lat_A))
    sin(deg2rad($lat_B))
    cos(deg2rad($lat_A))
    cos(deg2rad($lat_B))
    cos(deg2rad($long_A $long_B));

    $distance = (rad2deg(acos($distance))) * 69.09;

    return 
    $english_format_number number_format($distance0'.''');

    }

    while (
    $fields mysql_fetch_array($result)) {

    $bg = ($bg == "#FFF9F0") ? "#FFFFFF" "#FFF9F0"
    ?>
    <div align="center">
    <table border="1" cellpadding="0" style="border-collapse: collapse" width="700">
    <tr>

    <td align="center" width="130" valign="middle" bgcolor=<? echo "$bg?>>
    <?
    // $content will be echoed for errors and the distance when applicable
    global $content;

    // Check for zip code one to exist, for length, and validity

    $one $_SESSION['zip'];
    if(!
    is_numeric($one)) $content .= 'Zip code one is invalid.<br/>';
    elseif(
    strlen($one) < 5$content .= 'Zip code one is not long enough.<br/>';
    $zipCount 1;

    // Same thing for zip code two

    $two $fields['zip'];
    if(!
    is_numeric($two)) $content .= 'Zip code two is invalid.<br/>';
    elseif(
    strlen($two) < 5$content .= 'Zip code two is not long enough.<br/>';
    elseif(isset(
    $zipCount)) $zipCount++;

    else 
    $two '';

    $result1 mysql_query("SELECT latitude,longitude
    FROM 
    $zipTable
    WHERE zip = '
    $one'
    LIMIT 1"
    );

    $result2 mysql_query("SELECT latitude,longitude
    FROM 
    $zipTable
    WHERE zip = '
    $two'
    LIMIT 1"
    );

    if(
    mysql_num_rows($result1) == 0$content .= 'Zip code one not found in the database.<br/>';
    else {
    $row mysql_fetch_row($result1);
    $lat_A $row[0];
    $long_A $row[1];

    if(
    mysql_num_rows($result2) == 0$content .= 'Zip code two not found in the database.<br/>';
    else {
    $row mysql_fetch_row($result2);
    $lat_B $row[0];
    $long_B $row[1];
    }
    }

    // If both zips were found in the database, calculate the distance!

    $distance calcDist($lat_A$long_A$lat_B$long_B); 


    $data[] = array('modelnum' => "$fields[modelnum]"'coname' => "$fields[coname]"'city' => "$fields[city]"'state' => "$fields[state]"'formatted' => "$fields[formatted]"'distance' => "$distance"); 


    foreach (
    $data as $key=>$row) { 
    $distancetmp[$key] = $row['distance']; 


    array_multisort($distancetmpSORT_ASC$data); 

    foreach (
    $data as $key=>$value) { 
    foreach (
    $data[$key] as $innerkey=>$innervalue) { 
    print 
    $innerkey.": ".$innervalue."<br>"

    }
    }
    ?>
    </td>
    </tr>
    </table>
    </div>

    </body>

  16. #16
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    Eastland
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It worked!
    Now, since the returned values are contained in the $data[] array, how can these rows be formatted in html?
    Something like this;

    <div align="center">
    <table border="1" cellpadding="0" style="border-collapse: collapse" width="550">
    <tr>
    <td width="90" valign="top" bgcolor=<? echo "$bg" ?>>
    <b><font face="Arial" size="2">
    <a href="javascript:;" onClick="MM_openBrWindow('http://www.website/page.php?model_number=<?=$fields['modelnum']?>','','scrollbars=yes,width=250,height=350')">
    <?=$fields['modelnum']?></a></font></b></td>
    <td width="220" valign="top" bgcolor=<? echo "$bg" ?>><font face="Arial" size="2"><?=$fields['coname']?></font></td>
    <td width="120" valign="top" bgcolor=<? echo "$bg" ?>><font face="Arial" size="2"><?=$fields['city']?></font></td>
    <td width="50" valign="top" bgcolor=<? echo "$bg" ?>><font face="Arial" size="2"><?=$fields['state']?>.</font></td>
    <td width="70" valign="top" bgcolor=<? echo "$bg" ?>><font face="Arial" size="2"><?=$fields['formatted']?><br /></font>
    </td>
    </tr>
    </table>
    </div>

  17. #17
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this.

    PHP Code:
    <body> 

    <form name="tranwild" action="<?$_SERVER['PHP_SELF']?>" method="post"> 
    <b><font face="Arial" size="2">Wildcard Search For Available Transfers</b></font><br /> 
    <font face="arial" color="blue" size="1">Do not use the ( * ) symbol with this search.&nbsp; Simply enter what you need.<br />Entries are not case sensitive.</font><br /> 

    <input type="text" name="tranwild" style='border-width:1; width:"100"; border-color:#000000'>&nbsp;&nbsp; 
    <input type="submit" value="Go" style='border-width:1; border-color:#000000' onClick='check_fields();'> 
    </form> 

    <? 
    //================== PROCESS TRANSFERS WILDCARD SEARCH 

    if (isset($_POST['tranwild'])) { // checks to see if the tranwild input field is set - if so, execute the block below. 


    $val=$_POST["tranwild"]; // From submitted form 

    $query "SELECT *, DATE_FORMAT( `date` , '%m/%d/%Y' ) AS formatted FROM transfers WHERE modelnum LIKE '".$val."%'"
    $result=mysql_query($query); 
    if (!
    $result) die('In order to complete a wildcard search, you must enter your data in the search field. Please try your search again.'); 

    ?> 
    <div align="center"> 
    <table border="0" cellpadding="0" style="border-collapse: collapse" width="100%" height="20"> 
    <tr> 
    <td align="left"><font face="Arial" size="2" color="black"> 
    <?if(mysql_num_rows($result) == 0) echo 'No record found.<br/>'
    ?> 
    </td> 
    </tr> 
    </table> 
    </div> 
    <? 
    $zipTable 
    'zip_codes'

    // Function to calculate the distance of the zip codes 

    function calcDist($lat_A$long_A$lat_B$long_B) { 

    $distance sin(deg2rad($lat_A)) 
    sin(deg2rad($lat_B)) 
    cos(deg2rad($lat_A)) 
    cos(deg2rad($lat_B)) 
    cos(deg2rad($long_A $long_B)); 

    $distance = (rad2deg(acos($distance))) * 69.09

    return 
    $english_format_number number_format($distance0'.'''); 



    while (
    $fields mysql_fetch_array($result)) { 

    $bg = ($bg == "#FFF9F0") ? "#FFFFFF" "#FFF9F0"
    ?> 
    <div align="center"> 
    <table border="1" cellpadding="0" style="border-collapse: collapse" width="700"> 
    <tr> 

    <td align="center" width="130" valign="middle" bgcolor=<? echo "$bg?>
    <? 
    // $content will be echoed for errors and the distance when applicable 
    global $content

    // Check for zip code one to exist, for length, and validity 

    $one $_SESSION['zip']; 
    if(!
    is_numeric($one)) $content .= 'Zip code one is invalid.<br/>'
    elseif(
    strlen($one) < 5$content .= 'Zip code one is not long enough.<br/>'
    $zipCount 1

    // Same thing for zip code two 

    $two $fields['zip']; 
    if(!
    is_numeric($two)) $content .= 'Zip code two is invalid.<br/>'
    elseif(
    strlen($two) < 5$content .= 'Zip code two is not long enough.<br/>'
    elseif(isset(
    $zipCount)) $zipCount++; 

    else 
    $two ''

    $result1 mysql_query("SELECT latitude,longitude 
    FROM 
    $zipTable 
    WHERE zip = '
    $one
    LIMIT 1"
    ); 

    $result2 mysql_query("SELECT latitude,longitude 
    FROM 
    $zipTable 
    WHERE zip = '
    $two
    LIMIT 1"
    ); 

    if(
    mysql_num_rows($result1) == 0$content .= 'Zip code one not found in the database.<br/>'
    else { 
    $row mysql_fetch_row($result1); 
    $lat_A $row[0]; 
    $long_A $row[1]; 

    if(
    mysql_num_rows($result2) == 0$content .= 'Zip code two not found in the database.<br/>'
    else { 
    $row mysql_fetch_row($result2); 
    $lat_B $row[0]; 
    $long_B $row[1]; 



    // If both zips were found in the database, calculate the distance! 

    $distance calcDist($lat_A$long_A$lat_B$long_B); 


    $data[] = array('modelnum' => "$fields[modelnum]"'coname' => "$fields[coname]"'city' => "$fields[city]"'state' => "$fields[state]"'formatted' => "$fields[formatted]"'distance' => "$distance"); 


    foreach (
    $data as $key=>$row) { 
    $distancetmp[$key] = $row['distance']; 


    array_multisort($distancetmpSORT_ASC$data); 

    foreach (
    $data as $key=>$value) { 
    foreach (
    $data[$key] as $innerkey=>$innervalue) { 
    //End PHP to start HTML
    ?>
    <div align="center">
    <table border="1" cellpadding="0" style="border-collapse: collapse" width="550">
    <tr>
    <td width="90" valign="top" bgcolor=<?=$bg?>
    <b><font face="Arial" size="2">
    <a href="javascript:;" onClick="MM_openBrWindow('http://www.website/page.php?model_number=<?=$fields['modelnum']?>','','scrollbars=yes,width=250,height=350')">
    <?=$fields['modelnum']?></a></font></b></td>
    <td width="220" valign="top" bgcolor=<? echo "$bg?>><font face="Arial" size="2"><?=$fields['coname']?></font></td>
    <td width="120" valign="top" bgcolor=<? echo "$bg?>><font face="Arial" size="2"><?=$fields['city']?></font></td>
    <td width="50" valign="top" bgcolor=<? echo "$bg?>><font face="Arial" size="2"><?=$fields['state']?>.</font></td>
    <td width="70" valign="top" bgcolor=<? echo "$bg?>><font face="Arial" size="2"><?=$fields['formatted']?><br /></font>
    </td>
    </tr>
    </table>
    </div>

    <?php
    //Start processing PHP again.



    ?> 
    </td> 
    </tr> 
    </table> 
    </div> 

    </body>

  18. #18
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    Eastland
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's hard to say what the results are doing. I'll post the url if it will help you.

    Also, while testing the code I noticed that if you enter a search for a model for which there is no record in the db, I get this:

    No record found.



    Warning: Invalid argument supplied for foreach() in /page.php on line 163

    Warning: array_multisort(): Argument #1 is expected to be an array or a sort flag in /page.php on line 167

    Warning: Invalid argument supplied for foreach() in /page.php on line 169
    Last edited by drb10; Apr 5, 2005 at 12:40.

  19. #19
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, either that or post any errors that might be there.

    Ahh, yes we will have to put something in there that can count the rows from the MySQL query and if they are equal to zero it will skip the while loop.

    When you put in a zip code, does it process and spit out the HTML or does it not send anything?

  20. #20
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    Eastland
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's the url .
    http://www.theorangelist.com/codes/query_tranwild.php

    Just enter either upper or lowercase L or T

    This is the modified <tr>

    <tr>
    <td width="90" valign="top" bgcolor=<?=$bg?>>
    <b><font face="Arial" size="2">
    <a href="javascript:;" onClick="MM_openBrWindow('http://www.website/page.php?model_number=<?=$fields['modelnum']?>','','scrollbars=yes,width=250,height=350')">
    <?=$fields['modelnum']?></a></font></b></td>
    <td width="220" valign="top" bgcolor=<? echo "$bg" ?>><font face="Arial" size="2"><?=$fields['coname']?></font></td>
    <td width="120" valign="top" bgcolor=<? echo "$bg" ?>><font face="Arial" size="2"><?=$fields['city']?></font></td>
    <td width="50" valign="top" bgcolor=<? echo "$bg" ?>><font face="Arial" size="2"><?=$fields['state']?>.</font></td>
    <td width="100" valign="top" bgcolor=<? echo "$bg" ?>><font face="Arial" size="2"><?=$fields['formatted']?><br /></font>
    <td width="90" valign="top" bgcolor=<? echo "$bg" ?>><font face="Arial" size="2"><?=$distance?>&nbsp;miles<br /></font></td>
    </tr>

  21. #21
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try this.

    UPDATED!!! Too many mistakes...try this

    PHP Code:
    <body> 

    <form name="tranwild" action="<?$_SERVER['PHP_SELF']?>" method="post"> 
    <b><font face="Arial" size="2">Wildcard Search For Available Transfers</b></font><br /> 
    <font face="arial" color="blue" size="1">Do not use the ( * ) symbol with this search.&nbsp; Simply enter what you need.<br />Entries are not case sensitive.</font><br /> 

    <input type="text" name="tranwild" style='border-width:1; width:"100"; border-color:#000000'>&nbsp;&nbsp; 
    <input type="submit" value="Go" style='border-width:1; border-color:#000000' onClick='check_fields();'> 
    </form> 

    <? 
    //================== PROCESS TRANSFERS WILDCARD SEARCH 

    if (isset($_POST['tranwild'])) { // checks to see if the tranwild input field is set - if so, execute the block below. 


    $val=$_POST["tranwild"]; // From submitted form 

    $query "SELECT *, DATE_FORMAT( `date` , '%m/%d/%Y' ) AS formatted FROM transfers WHERE modelnum LIKE '".$val."%'"
    $result=mysql_query($query); 
    if (!
    $result) die('In order to complete a wildcard search, you must enter your data in the search field. Please try your search again.'); 

    ?> 
    <div align="center"> 
    <table border="0" cellpadding="0" style="border-collapse: collapse" width="100%" height="20"> 
    <tr> 
    <td align="left"><font face="Arial" size="2" color="black"> 
    <?if(mysql_num_rows($result) == 0) echo 'No record found.<br/>'
    ?> 
    </td> 
    </tr> 
    </table> 
    </div> 
    <? 
    $zipTable 
    'zip_codes'

    // Function to calculate the distance of the zip codes 

    function calcDist($lat_A$long_A$lat_B$long_B) { 

    $distance sin(deg2rad($lat_A)) 
    sin(deg2rad($lat_B)) 
    cos(deg2rad($lat_A)) 
    cos(deg2rad($lat_B)) 
    cos(deg2rad($long_A $long_B)); 

    $distance = (rad2deg(acos($distance))) * 69.09

    return 
    $english_format_number number_format($distance0'.'''); 


    ?>
    <div align="center"> 
    <table border="1" cellpadding="0" style="border-collapse: collapse" width="550"> 
    <?php
    while ($fields mysql_fetch_array($result)) { 

    $bg = ($bg == "#FFF9F0") ? "#FFFFFF" "#FFF9F0"
    ?> 
    <div align="center"> 
    <table border="1" cellpadding="0" style="border-collapse: collapse" width="700"> 
    <tr> 

    <td align="center" width="130" valign="middle" bgcolor=<? echo "$bg?>
    <? 
    // $content will be echoed for errors and the distance when applicable 
    global $content

    // Check for zip code one to exist, for length, and validity 

    $one $_SESSION['zip']; 
    if(!
    is_numeric($one)) $content .= 'Zip code one is invalid.<br/>'
    elseif(
    strlen($one) < 5$content .= 'Zip code one is not long enough.<br/>'
    $zipCount 1

    // Same thing for zip code two 

    $two $fields['zip']; 
    if(!
    is_numeric($two)) $content .= 'Zip code two is invalid.<br/>'
    elseif(
    strlen($two) < 5$content .= 'Zip code two is not long enough.<br/>'
    elseif(isset(
    $zipCount)) $zipCount++; 

    else 
    $two ''

    $result1 mysql_query("SELECT latitude,longitude 
    FROM 
    $zipTable 
    WHERE zip = '
    $one
    LIMIT 1"
    ); 

    $result2 mysql_query("SELECT latitude,longitude 
    FROM 
    $zipTable 
    WHERE zip = '
    $two
    LIMIT 1"
    ); 

    if(
    mysql_num_rows($result1) == 0$content .= 'Zip code one not found in the database.<br/>'
    else { 
    $row mysql_fetch_row($result1); 
    $lat_A $row[0]; 
    $long_A $row[1]; 

    if(
    mysql_num_rows($result2) == 0$content .= 'Zip code two not found in the database.<br/>'
    else { 
    $row mysql_fetch_row($result2); 
    $lat_B $row[0]; 
    $long_B $row[1]; 



    // If both zips were found in the database, calculate the distance! 

    $distance calcDist($lat_A$long_A$lat_B$long_B); 


    $data[] = array('modelnum' => "$fields[modelnum]"'coname' => "$fields[coname]"'city' => "$fields[city]"'state' => "$fields[state]"'formatted' => "$fields[formatted]"'distance' => "$distance"); 


    foreach (
    $data as $key=>$row) { 
    $distancetmp[$key] = $row['distance']; 


    array_multisort($distancetmpSORT_ASC$data); 

    foreach (
    $data as $key=>$value) { 
    //End PHP to start HTML 
    ?> 
    <tr> 
    <td width="90" valign="top" bgcolor=<?=$bg?>
    <b><font face="Arial" size="2"> 
    <a href="javascript:;" onClick="MM_openBrWindow('http://www.website/page.php?model_number=<?=$data[$key]['modelnum']?>','','scrollbars=yes,width=250,height=350')"> 
    <?=$data[$key]['modelnum']?></a></font></b></td> 
    <td width="220" valign="top" bgcolor=<?=$bg?>><font face="Arial" size="2"><?=$data[$key]['coname']?></font></td> 
    <td width="120" valign="top" bgcolor=<?=$bg?>><font face="Arial" size="2"><?=$data[$key]['city']?></font></td> 
    <td width="50" valign="top" bgcolor=<?=$bg?>><font face="Arial" size="2"><?=$data[$key]['state']?>.</font></td> 
    <td width="70" valign="top" bgcolor=<?=$bg?>><font face="Arial" size="2"><?=$data[$key]['formatted']?><br /></font> 
    </td> 
    </tr> 

    <?php 
    //Start processing PHP again. 


    ?> 
    </table> 
    </div> 
    </td> 
    </tr> 
    </table> 
    </div> 

    </body>

  22. #22
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    Eastland
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I made couple minor modifications and it looks pretty good. Did you go to the site?
    If so, please notice that the results table appears beveled on the edges like the rows are stacking with each loop through the db. I'm working on that.

    Also, if I type in K or P I still get the same errors. Letters like L, T, M, G, are represented in my db. You might go to the url as I'm not the best at explaining things.

    You wrote: "When you put in a zip code, does it process and spit out the HTML or does it not send anything?" There is no zip code entered. The wildcard search is based on the first letter of a certain model and the script computes the distance from the logged in members zip code to the zip code returned in each row of the array of results. Hope I said that right!
    Let me again say how much I appreciate your willingness to help.

  23. #23
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    Eastland
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I fixed the appearance of the resulting rows but not much progress on the error messages.

  24. #24
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Post your updated code and I will add the appropriate lines to eliminate the database errors.

  25. #25
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    Eastland
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The only changes I made were to remove a couple of <div> tags I had laying around and the following additions:
    foreach ($data as $key=>$value) {
    //End PHP to start HTML
    $bg = ($bg == "#FFF9F0") ? "#FFFFFF" : "#FFF9F0"; //ALTERNATING COLOR ROWS

    AND
    <tr>
    <td width="100" valign="top" bgcolor=<?=$bg?>>
    <b><font face="Arial" size="2">
    <a href="javascript:;" onClick="MM_openBrWindow('http://www.theorangelist.com/codes/viewtransfers.php?model_number=<?=$data[$key]['modelnum']?>','','scrollbars=yes,width=250,height=350')">
    <?=$data[$key]['modelnum']?></a></font></b></td>
    <td width="220" valign="top" bgcolor=<?=$bg?>><font face="Arial" size="2"><?=$data[$key]['coname']?></font></td>
    <td width="120" valign="top" bgcolor=<?=$bg?>><font face="Arial" size="2"><?=$data[$key]['city']?></font></td>
    <td width="45" valign="top" bgcolor=<?=$bg?>><font face="Arial" size="2"><?=$data[$key]['state']?>.</font></td>
    <td width="85" valign="top" bgcolor=<?=$bg?>><font face="Arial" size="2"><?=$data[$key]['formatted']?><br /></font>
    <td width="70" valign="top" bgcolor=<?=$bg?>><font face="Arial" size="2"><?=$data[$key]['distance']?>&nbsp;miles<br /></font>
    </td>
    </tr>
    I added the last column to the table row to allow for the distance in miles.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •