Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2006
    Posts
    13

    Unanswered: upgrading MySQL column type has no effect: Moved to PHP

    I've created a web page in .php which receives it's content from a MySQL database, it is all working nicely and looking good except that a field which I have called Ed_Main is now about 65kb and now does not display all the text stored in it.

    I'm using PHPMyadmin to administer MySQL.
    MySQL version 4.0 (CGI setup)
    MySQL installed on local machine (high-end Pentium laptop)
    IIS installed on local machine
    OS - XP Pro SP2
    Browser: IE6

    I was creating my content in an external application then I 'Browsed' in PHPMyadmin for the relevant record and pasted it into the field in PHPMyadmin directly. I did it this way as I've not yet created any input pages for updating the web site.

    The field in PHPMyadmin seems to accept my entire text and when I navigate back to that field again after saving the record, indeed, the text was saved. So I then check my .php page to view the out put and find that my content has been truncated. It seems to make no difference whether I add more text, it still truncates in the same place, so I assume it is a size issue (although, isn't it odd that my ENTIRE text seems to be stored ok in the database, since I've been back via PHPMyadmin to look at it!)

    Anyway, I then changed the field type using PHPMyadmin from 'Text' to 'MediumText' and saved my changes. The situation is still identical, although PHPMyadmin indicates that the change was successful. I have also run the repair option on the database with no improvement.

    As I said, my text is currently about 65kb (although I need to add about another 40kb to it), so the MediumText field which holds about 16mb, should have no bother holding it.

    Does anyone know the answer to this? I spent the entire day searching online and can find nothing of any help!

    Thanks

    peter

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    changing the data type shouldn't have made any difference text is 2^16 (65Kb), medium text 2^24 (16.7Mb), unless ou are using unicode

    But you state that when you check the value in the db its correct, but re-opening the row in phpMyAdmin its truncated - sounds to me more lilely to be a flaw in PHPMyAdmin than MySQL.

    I'd check using another way of putting the info into / retrieveing from MySQL. Persoanlly I wouldn't want to store that amount of data in one column unless there was no other option.

  3. #3
    Join Date
    Jan 2006
    Posts
    13
    Thanks healdem, actually, I have a confession to make.. I've just pasted the full text into Notepad and checked the file size. It's actually 7kb. Sorry my previous method of discerning it was obviously wrong!

    However, that kind of confirms what you are saying. I will see if I can create an input page to insert the data to the database, maybe that will help.

    Regards, and thanks for your quick reply.

    Peter

  4. #4
    Join Date
    Jan 2006
    Posts
    13
    Quote Originally Posted by healdem
    changing the data type shouldn't have made any difference text is 2^16 (65Kb), medium text 2^24 (16.7Mb), unless ou are using unicode

    But you state that when you check the value in the db its correct, but re-opening the row in phpMyAdmin its truncated - sounds to me more lilely to be a flaw in PHPMyAdmin than MySQL.

    I'd check using another way of putting the info into / retrieveing from MySQL. Persoanlly I wouldn't want to store that amount of data in one column unless there was no other option.
    I've just reread your message and it looks like I didn't explain properly, I see you make a distinction between checking the value in the db and looking at it in PHPMyAdmin. I only know how to look at it in PHPMyAdmin but since each time I look at it there, it is able to show me the entire text that I stored (not truncated), even after closing my browser and connecting to the database, I'm assuming that MYSQL has successfully stored my data, otherwise it would not be there when I returned to PHPMyAdmin. It is when I try to view it using my new .php web page, that the code is truncated.

    As you suggested, I have found an alternative method of putting the data in to the database. I have created an import form (thanks for you help with that too by the way!).

    This is what is happening now. When I try to submit the form with my large data in one of the textareas, nothing happens and the form just sits there. The status bar says done. I've waited a considerable time, but still nothing happens. If I then start deleting some of the text in the textarea, when there is only a few lines left, it will submit ok and is added to the database.

    I was wondering if it was because I was using 'GET' method, so I changed every instance of the word 'GET' to 'POST' but since I've done that, although the form now submits with all the text in it, I get the following error message (and a lot of similar errors below that)

    Notice: Undefined index: namemodel in C:\Documents and Settings\Peter Snow\My Documents\websites\Our Web Sites\Onwards\website 1.0\insert.php on line 29
    Line 29 is
    PHP Code:
    $NameModel=$_POST['namemodel']; 
    I rekon, that if I can fix that I'm nearly there!

    Any ideas?

    Thanks, pete

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I don't know what you get with the windows MySQL package. On *NIX systems you get the command line monitor MySQL which interacts directly with MySQL - ie you cna type in the SQL & see exactly whats returned. - it may not be of much use resolving your 7/65Kb question. You can also get the MySQL Administrator & MySQL Query Browser.

    Generally to check the sort of problem you were having I'd knock up a quick & dirty PHP from, or modify the existing one and debug soem data

    eg
    $DebugMessage = "Length of bigcolumn:".strlen($row['bigcolumn']);
    $DebugMessage .= "Value of bigcolumn:".$row['bigcolumn'];
    //ad other debugmessags as required.....
    die ($DebugMessage);

    The fact you are geting an error 'undefined index' suggests that the $_Post array doens't have a value for the index you have requested.

    HTH

  6. #6
    Join Date
    Jan 2006
    Posts
    13
    OK, thanks for you help. You've been great. I can now enter data into the database using my form. It seems that it was something to do with a bug in PHPMyAdmin that was preventing it from storing the full text correctly. There is also a bug in my very old version of Homesite concerning the sending and retrieving of values using the 'post' command in the internal preview window (even though that is powered by the IE engine. I was able to confirm this by testing it in an external instance of IE which was able to carry out the insert operation.

    Unfortunately, this has uncovered another very strange problem!

    When I went to view the database contents, the data has been stored in the wrong columns. I've gone through my code with a fine toothcomb and can see no errors which might cause this. I will post the code below, and you will see that I have some diagnostics built in, which show that the information (at least at that point) appears to be about to be stored in the correct columns.

    I have 'checked', 'repaired' and 'flushed' the database and tried again. I have also restarted my PC several times too (although so far, it has always been very stable).

    These are a list of the columns in my table called 'products' and along side them is the name of the columns data that is wrongly stored there:


    NameModel - correct
    Manufacturer - correct
    Tagline - CONTAINS Ed_Main
    Ed_Summary - CONTAINS Tagline
    Ed_main - CONTAINS Ed_Summary

    The rest of the columns are store correctly.

    This is the diagnostic text that was output on the screen because of the diagnostic code in insert.php

    Values
    submit = Submit Query
    namemodel = 1Name/Model
    manufacturer = Manufacturer
    tagline = Tagline
    ed_summary = Ed Summary
    ed_main = Main Editorial
    features = Features
    specs = Specs
    options = Options
    pic_thumb_100x100 = Thumbnail Picture
    pic_1 = Picture 1
    pic_2 = Picture 2
    costprice = 1
    retailprice = 2
    supplierid = 3
    curr_stock = 4
    minstock = 5
    maxstock = 6
    The text to the left of the '=' sign is the Post HTTP header field name and to the right of the '=' is the contents of the form.

    Looking at that, it is all good so far, as I typed in each text field some text to identify which field it should be in. EG. I typed 'Main Editorial' in the field called 'Ed_main'.

    However, when I check the actual contents of the database the data is stored in the wrong columns still.

    Here is the full code of the page holding the form:

    HTML Code:
    <html>
              <head>
                   <title>Admin</title>
              </head>
         <body>
    
              <form action="insert.php" method="post">
                   <input type="Submit" name="submit" />
    
                   Name/Model: <input type="text" name="namemodel" /><br />
                   Manufacturer: <input type="text" name="manufacturer" /><br />
                   Tagline: <input type="text" name="tagline" /><br />
                   Ed Summary: <textarea cols="20" rows="10" name="ed_summary"></textarea><br />
                   Main Editorial: <textarea cols="60" rows="20" name="ed_main"></textarea><br />
                   Features: <textarea cols="20" rows="20" name="features"></textarea><br />
                   Specs: <textarea cols="20" rows="10" name="specs"></textarea><br />
                   Options: <textarea cols="20" rows="10" name="options"></textarea><br />
                   Thumbnail Picture: <input type="text" name="pic_thumb_100x100" /><br />
                   Picture 1: <input type="text" name="pic_1" /><br />
                   Picture 2: <input type="text" name="pic_2" /><br />
                   Cost Price: <input type="text" name="costprice" /><br />
                   Retail Price: <input type="text" name="retailprice" /><br />
                   Supplier ID: <input type="text" name="supplierid" /><br />
                   Current Stock: <input type="text" name="curr_stock" /><br />
                   Minimum Stock: <input type="text" name="minstock" /><br />
                   Maximum Stock: <input type="text" name="maxstock" /><br />
    
                   <input type="Submit" name="submit" />
              </form>
         </body>
    </html>

    Here is the code for insert.php

    PHP Code:

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
    <head>
        <title>Untitled</title>
    </head>

    <body>
    <?php
    $username
    ="removed for forum";
    $password="removed for forum";
    $database="myodbc_store";


    if (
    $username="removed for forum") {

      
    // process form
    echo "Values <br>";
      while (list(
    $name$value) = each($HTTP_POST_VARS)) {

        echo 
    "$name = $value<br>\n";

      }}

    $ProdID='';
    $NameModel=$_POST['namemodel'];
    $Manufacturer=$_POST['manufacturer'];
    $Tagline=$_POST['tagline'];
    $Ed_Summary=$_POST['ed_summary'];
    $Ed_Main=$_POST['ed_main'];
    $Features=$_POST['features'];
    $Specs=$_POST['specs'];
    $Options=$_POST['options'];
    $Pic_Thumb_100x100=$_POST['pic_thumb_100x100'];
    $Pic_1=$_POST['pic_1'];
    $Pic_2=$_POST['pic_2'];
    $CostPrice=$_POST['costprice'];
    $RetailPrice=$_POST['retailprice'];
    $SupplierID=$_POST['supplierid'];
    $Curr_Stock=$_POST['curr_stock'];
    $MinStock=$_POST['minstock'];
    $MaxStock=$_POST['maxstock'];
     
    $conn=odbc_connect($database,$username,$password);

    $query "INSERT INTO products VALUES ('$ProdID', '$NameModel', '$Manufacturer', '$Tagline', '$Ed_Summary', '$Ed_Main', '$Features', '$Specs', '$Options', '$Pic_Thumb_100x100', '$Pic_1', '$Pic_2', '$CostPrice', '$RetailPrice', '$SupplierID', '$Curr_Stock', '$MinStock', '$MaxStock')";

    $resultset=odbc_exec($conn,$query) or die ("Error inserting data<BR>$query");

    /* <!-- where:-
    $sql is the variable containing the SQL to be applied
    $cnn is the variable containing a valid MySQL resource
    $resultset is the return from the function
     --> */
     
    odbc_close($conn);
    ?>

    </body>
    </html>
    Again, I really appreciate your help!

    Thanks, pete

  7. #7
    Join Date
    Jan 2006
    Posts
    13

    Thread closed - New Thread started

    ...because the problem has evolved and nolonger is represented by the original posts title.

Posting Permissions

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