Results 1 to 6 of 6

Thread: MYSQL Query Shortened

  1. #1
    Join Date
    May 2007
    Location
    Boston,ma
    Posts
    2,127
    Thanks
    173
    Thanked 207 Times in 205 Posts

    Default MYSQL Query Shortened

    I just started using MYSQL so I'm not sure if I've done something wrong here. The data went into the database but the first character of the first query was removed.

    I executed this query in PHP and it inserted into the database but the first value was shortened by it's first character.

    This was the query I then commented it out to see if it was correct.
    $query = "INSERT INTO Locations (Zip, LAT, LNG, Name, Address) VALUES (01915, 45.5, 55.6, 'Name', 'Address');";
    The output is

    1915<br />45.5<br />55.6<br />Name<br />Address<br />
    PHP Code:
    <?php
    include_once('db_conn.php');
    //$query = "INSERT INTO Locations (Zip, LAT, LNG, Name, Address) VALUES (01915, 45.5, 55.6, 'Name', 'Address');";
    $query "SELECT * FROM Locations";
    // Perform Query
    $result mysql_query($query);
    if (!
    $result) {
        
    $message  'Invalid query: ' mysql_error() . "\n";
        
    $message .= 'Whole query: ' $query;
        die(
    $message);
    }
    // Use result
    // Attempting to print $result won't allow access to information in the resource
    // One of the mysql result functions must be used
    // See also mysql_result(), mysql_fetch_array(), mysql_fetch_row(), etc.
    while ($row mysql_fetch_assoc($result)) {
        echo 
    $row['Zip'] . "<br />";
        echo 
    $row['LAT'] . "<br />";
        echo 
    $row['LNG'] . "<br />";
        echo 
    $row['Name'] . "<br />";
        echo 
    $row['Address'] . "<br />";
    }

    // Free the resources associated with the result set
    // This is done automatically at the end of the script
    mysql_free_result($result);
    ?>
    Corrections to my coding/thoughts welcome.

  2. #2
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    is that first field "INT" type? since it's being treated as a number (not as a string of individual digits), leading zeros will be dropped. switch the field to VARCHAR and you should be fine (just remember to 'quote' the values when you insert them).

  3. The Following User Says Thank You to traq For This Useful Post:

    bluewalrus (02-20-2011)

  4. #3
    Join Date
    May 2007
    Location
    Boston,ma
    Posts
    2,127
    Thanks
    173
    Thanked 207 Times in 205 Posts

    Default

    I didn't know about the leading zeros being removed thanks. Would float stop that as well or is varchar the only way?
    Corrections to my coding/thoughts welcome.

  5. #4
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    Haven't tried, but I don't think FLOAT will prevent it, as mysql is still considering the value "numeric." If you think about it, a zip code is really a string, even though it's comprised solely of digits.

    in php its even further from what you might expect:
    PHP Code:
    <?php
    //try this
    $zip 01915;
    print 
    $zip;
    print 
    "<br>";
    //compared to this
    $zip_str '01915';
    print 
    $zip_str;
    ?>
    the leading zero leads php to evaluate $zip as a numeric value written in octal notation.

  6. The Following User Says Thank You to traq For This Useful Post:

    bluewalrus (03-16-2011)

  7. #5
    Join Date
    May 2007
    Location
    Boston,ma
    Posts
    2,127
    Thanks
    173
    Thanked 207 Times in 205 Posts

    Default

    Thanks, didn't know that I've switched it to varchar and it's working.
    Corrections to my coding/thoughts welcome.

  8. #6
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

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
  •