SitePoint Sponsor

User Tag List

Page 2 of 3 FirstFirst 123 LastLast
Results 26 to 50 of 64
  1. #26
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I didn't, and I seriously doubt it's required as the are just place holder for values.

    I've had a quick read of the manual and came up with the following...
    Code:
    DELIMITER $$
    
    DROP FUNCTION IF EXISTS `distance`$$
    
    CREATE FUNCTION `distance`(userLat float, userLong float, targetLat float, targetLong float)
        RETURNS float(11)
    DETERMINISTIC
    NO SQL
    BEGIN
        RETURN (3958.75 * acos(sin(userLat/57.2958) * sin(targetLat/57.2958) + cos(userLat/57.2958) * cos(targetLat/57.2958) * cos(targetLong/57.2958 - userLong/57.2958)));
    END$$
    
    DELIMITER ;
    [Attached a text copy]

    A procedure or function is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise. If neither DETERMINISTIC nor NOT DETERMINISTIC is given in the routine definition, the default is NOT DETERMINISTIC.
    NO SQL indicates that the routine contains no SQL statements.
    I have to reiterate again though, I'm no SQL guy and I'm hacking away at this too.
    Attached Files Attached Files
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  2. #27
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again for the help SB.

    I tried that, and again get an error

    PHP Code:
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$

    DROP FUNCTION IF EXISTS `distance`$$

    CREATE FUNCTION `distanc' at line 1 

  3. #28
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh Good Lord!!! I think you may have cracked it!!!!

    I think it may have just worked. There was no massive 'EH' noise from the computer, when I returned the END$$ part






    Really appreciate aaaaall your help with this SB.
    Last edited by invision2; Feb 10, 2009 at 09:26. Reason: checked 'routines' in pma, and 'distance' is in there :)

  4. #29
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Maybe you should post over the SQL forum and direct their attention to this thread, it's no doubt a very easy thing to fix.

    Unless of course, your outburst meant it worked.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  5. #30
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again for your help and patience SB.

    I've tried running the script below but get errors on lines 12, 19 and 29 due to the mysql_fetch_assoc() function.

    Is there something obvious I'm doing wrong with the following:

    PHP Code:
    <?php

    if (isset($_POST['submitted'])) {

        require_once(
    'database-connector.inc.php');

        
    $sSQL sprintf("SELECT long, lat FROM airports WHERE name = '%s'",
            
    mysql_real_escape_string($_POST['startingAirport'])
        );
        
        
    $rResult mysql_query($sSQL);
        
    $aStartingAiport mysql_fetch_assoc($rResult);
        
        
    $sSQL sprintf("SELECT long, lat FROM airports WHERE name = '%s'",
            
    mysql_real_escape_string($_POST['destinationAirport'])
        );
        
        
    $rResult mysql_query($sSQL);
        
    $aDestinationAiport mysql_fetch_assoc($rResult);
        
        
    $sSQL sprintf("SELECT DISTANCE(%s, %s, %s, %s) AS theDistance;",
            
    $aStartingAiport['latitude'],
            
    $aStartingAiport['longitude'],
            
    $aDestinationAiport['latitude'],
            
    $aDestinationAiport['longitude']
        );
        
        
    $rResult mysql_query($sSQL);
        
    $aDistance mysql_fetch_assoc($rResult);
        
            
    printf('The distance between %s Airport and %s Airport is %s',
            
    $_POST['startingAirport'],
            
    $_POST['destinationAirport'],
            
    $aDistance['theDistance']
        );
        
    }
    ?>

    <form action="airport.php" method="POST">

        <label for="startingAirport">
            <select id="startingAirport" name="startingAirport">
                <option value="2">Aberdeen</option>
            </select>
        </label>
        
        <label for="destinationAirport">
            <select id="destinationAirport" name="destinationAirport">
                <option value="1">Glasgow</option>
            </select>
        </label>
        
        <input type="submit" value="Submit!" />
        <input type="hidden" name="submitted" value="TRUE" />

    </form>
    Code MySQL:
    CREATE TABLE IF NOT EXISTS `airports` (
      `id` smallint(6) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) NOT NULL DEFAULT '',
      `long` varchar(20) NOT NULL DEFAULT '',
      `altitude` varchar(25) NOT NULL DEFAULT '',
      `lat` varchar(20) NOT NULL DEFAULT '',
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
     
    INSERT INTO `airports` (`id`, `name`, `long`, `altitude`, `lat`) VALUES
    (1, 'Glasgow', '042559W', '0026', '555219N'),
    (2, 'Aberdeen', '021201W', '0215', '571215N');


    I've had a good look through but can't seem to think why this issue.
    Is it because I'm submitting an index value and not the actual airport name?


    Really appreciate any further help you can give, you'e already been a huuuuuge help.

  6. #31
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    What's the error?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  7. #32
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again SB!

    PHP Code:
    Warningmysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\wamp\www\airport.php on line 12

    Warning
    mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\wamp\www\airport.php on line 19

    Warning
    mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\wamp\www\airport.php on line 29
    The distance between 2 Airport 
    and 1 Airport is 

  8. #33
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    In the SQL queries your searching for matching names, yet your form sends over the ID's of the selected airports.

    That should be a good starting point.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  9. #34
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK.

    I've now changed my HTML to:

    HTML Code:
    <label for="startingAirport">
    		<select id="startingAirport" name="startingAirport">
    			<option value="Aberdeen">Aberdeen</option>
    		</select>
    	</label>
    	
    	<label for="destinationAirport">
    		<select id="destinationAirport" name="destinationAirport">
    			<option value="Glasgow">Glasgow</option>
    		</select>
    	</label>
    But still get the same errors:

    PHP Code:
    Warningmysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\wamp\www\airport.php on line 12

    Warning
    mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\wamp\www\airport.php on line 19

    Warning
    mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\wamp\www\airport.php on line 29
    The distance between Aberdeen Airport 
    and Glasgow Airport is 

  10. #35
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    You're either not connecting to the database or there is an error returned when your query the databse.

    Try echo'ing out mysql_error() after each query.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  11. #36
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think 'LONG' may be a keyword in MySQL

  12. #37
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?php
    $rResult 
    mysql_query($sSQL);
    echo 
    mysql_error();
    ?>
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  13. #38
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, almost there me reckons

    PHP Code:
    <?php

    if (isset($_POST['submitted'])) {

        require_once(
    'database-connector.inc.php');

        
    $sSQL sprintf("SELECT longi, lat FROM airports WHERE name = '%s'",
            
    mysql_real_escape_string($_POST['startingAirport'])
        );
        
        
    $rResult mysql_query($sSQL);
        
    $aStartingAiport mysql_fetch_assoc($rResult);
        
        
    $sSQL sprintf("SELECT longi, lat FROM airports WHERE name = '%s'",
            
    mysql_real_escape_string($_POST['destinationAirport'])
        );
        
        
    $rResult mysql_query($sSQL);
        
    $aDestinationAiport mysql_fetch_assoc($rResult);
        
        
    $sSQL sprintf("SELECT DISTANCE(%s, %s, %s, %s) AS theDistance;",
            
    $aStartingAiport['lat'],
            
    $aStartingAiport['longi'],
            
    $aDestinationAiport['lat'],
            
    $aDestinationAiport['longi']
        );
        
        
    $rResult mysql_query($sSQL);
        echo 
    mysql_error();
        
    $aDistance mysql_fetch_assoc($rResult);
        
            
    printf('The distance between %s Airport and %s Airport is %s',
            
    $_POST['startingAirport'],
            
    $_POST['destinationAirport'],
            
    $aDistance['theDistance']
        );
        
    }
    ?>

    <form action="airport.php" method="POST">

        <label for="startingAirport">
            <select id="startingAirport" name="startingAirport">
                <option value="Aberdeen">Aberdeen</option>
            </select>
        </label>
        
        <label for="destinationAirport">
            <select id="destinationAirport" name="destinationAirport">
                <option value="Glasgow">Glasgow</option>
            </select>
        </label>
        
        <input type="submit" value="Submit!" />
        <input type="hidden" name="submitted" value="TRUE" />

    </form>
    My error message:
    PHP Code:
    Unknown column '571215N' in 'field list'
    Warningmysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\wamp\www\airport.php on line 30 
    MySQL:
    CREATE TABLE IF NOT EXISTS `airports` (
    `id` smallint(6) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL DEFAULT '',
    `longi` varchar(20) NOT NULL,
    `altitude` varchar(25) NOT NULL DEFAULT '',
    `lat` varchar(20) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

    --
    -- Dumping data for table `airports`
    --

    INSERT INTO `airports` (`id`, `name`, `longi`, `altitude`, `lat`) VALUES
    (1, 'Glasgow', '042559W', '0026', '555219N'),
    (2, 'Aberdeen', '021201W', '0215', '571215N');

  14. #39
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    If you're getting that error from the DISTANCE call, it's because you haven't created the function yet so it thinks the logs and lats are field names.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  15. #40
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, that's odd. As when I look in my phpmyadmin under 'Routines'. I have one listed there called 'distance':

    PHP Code:
    DROP FUNCTION `distance`//
    CREATE DEFINER=`root`@`localhost` FUNCTION `distance`(userLat floatuserLong floattargetLat floattargetLong floatRETURNS float
        NO SQL
        DETERMINISTIC
    BEGIN
        
    RETURN (3958.75 acos(sin(userLat/57.2958) * sin(targetLat/57.2958) + cos(userLat/57.2958) * cos(targetLat/57.2958) * cos(targetLong/57.2958 userLong/57.2958)));
    END 

  16. #41
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)


    So you managed to create it? A head's up would have been nice, you know a geek cant sleep with erroneous programming errors still out there!

    Can you call the distance function supplying the data manually in PMA?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  17. #42
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My sincere apologies. I thought you knew heheh.

    PHP Code:
    // The query and error message
    SELECT DISTANCE(571215N021201W555219N042559W) AS theDistance;Unknown column '571215N' in 'field list' 
    Can you call the distance function supplying the data manually in PMA?
    Oh, I'm not so sure how to....sorry, brain is collapsing all around me

  18. #43
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    You need to supply 'floats' to the function, at present you are supplying strings due the text in there.

    Code:
    SELECT DISTANCE( 57.1215, 021.201, 55.5219, 42.559  )  AS theDistance
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  19. #44
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good shout SB.

    Think my schema needs a rework I major suck at this stuff

    PHP Code:
    theDistance
    822.27941894531 

  20. #45
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT CONCAT(DISTANCE( 57.1215, -2.1201, 55.5219, -4.2559 ), ' miles') AS theDistance
    
    #137.504 miles
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  21. #46
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, so if Glasgow is:

    Latitude : 55&#176;52' 19" N
    Longitude : 4&#176;25' 59" W

    and Aberdeen is:

    Latitude: 57&#176;12' 34" N
    Longitude : 2&#176;12' 12" W

    then my sql table should be:

    PHP Code:
    1     Glasgow     4.2559     W     0026     55.5219     N
    2     Aberdeen     2.1201     W     0215     57.1215     N 
    Just read your previous post - cool!

    Now I need to fix it so if it's W or S then it adds a - symbol before it

  22. #47
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Those longs should go in as minus values, but apart from that, spot on me thinks!
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  23. #48
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Man you've no idea how helpful you have been. Well, I'm sure you do, but it's still huge!

    I'm not cut out for building apps like this. Can do CMS for web site, but I'm mince with this Thanks for all your help SB, apologies for being a bit slow at times.

  24. #49
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    No worries.

    Don't put yourself down, you've picked up quite a bit here. The next time someone asks you if you can work out the distance between two longs and lats you can say quite confidently 'Yup!'.

    Let me have my tea before you create another topic though, I'm starting to feel weak.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  25. #50
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hehehe, no worries. I thought I'd give you time for tea, supper and breakers before I posted my next topic

    Yes, I've been having some confidence/ability issues of late.
    I have the ability but not the confidence - long story. But it's getting better.


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
  •