upgrading column type in MySQL has no effect

[snowweb]

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

[chazzy]

i would recommend using a blob column, not a text, and use it send attachments.

that's honestly way too much data to be in one cell as free text.

[snowweb]

hmm... I'm confused here!... so are you saying that the text fields in mysql don't work as they are supposed to, since 10kb is less than even one sixth of what a simple text field is supposed to be capable of holding. If this is the case, why do they still have mediumtext and longtext fields?

I guess if this is right then I will have to learn about how to use a blob field earlier than I would have done... although, to be honest, if it's because MySql has a text field bug, I will probably find an alternative database!

Thanks for your reply Chazzy, although I'm reluctant to use blob, just because the MySql text fields don't work as they should!

[NogDog]

You might want to run the memory_get_usage() function (http://www.php.net/memory_get_usage) to see how much memory is allocated by the system for your script.

[snowweb]

You might want to run the memory_get_usage() function (http://www.php.net/memory_get_usage) to see how much memory is allocated by the system for your script.

I just tried adding

echo memory_get_usage() . "\n";

to my script and received this error

Fatal error: Call to undefined function: memory_get_usage()

What did I do wrong there?

Thanks

[NogDog]

PHP version < 4.3.2 ?

You could try:

echo ini_get("memory_limit");

What I'm interested in: http://www.php.net/manual/en/ini.core.php#ini.memory-limit

[snowweb]

PHP version < 4.3.2 ?

You could try:

echo ini_get("memory_limit");

What I'm interested in: http://www.php.net/manual/en/ini.core.php#ini.memory-limit

I see what you're trying to do. I couldn't get either of the functions to work which is probably my fault for just trying to guess how to implement them. However, now I understand what it is you want to know, I have gone to my php.ini and looked up the value for memory_limit. It was "8M" which I assume the 'M' is correct and means "MB"? Anyway, I increased it to "16M".

However, it is still only returning the first 4095 characters of the field 'Ed_Main'.(I restarted the MySQL service before trying it).

I've also received words about the fact that I've been connecting to the database using odbc and some people think that that could be causeing the problem. I have therefore tried to convert my script to using the method they recommend but I'm having difficulty getting it to work since I can't locate a tutorial which explains how set it up without going into looping arrays and stuff.

Please can you look at the code below and tell me if you can spot the problem?

[PHP]
$mysql_access = mysql_connect("localhost", "$un", "$pw");
mysql_select_db($db, $mysql_access);

$result = mysql_query("SELECT * FROM products WHERE ProdID='$item'");


$ProdID=mysql_result($result,"ProdID");
$NameModel=mysql_result($result,"NameModel");
$Manufacturer=mysql_result($result,"Manufacturer");
$Pic_Thumb_100x100=mysql_result($result,"Pic_Thumb_100x100");
$Pic_1=mysql_result($result,"Pic_1");
$Pic_2=mysql_result($result,"Pic_2");
$Tagline=mysql_result($result,"Tagline");
$Ed_Summary=mysql_result($result,"Ed_Summary");
$Ed_Main=mysql_result($result,"Ed_Main");

[PHP]

What is happening is that all of my variables ($ProdID, $NameModel, $Manufacturer, etc) are each being allocated the value "1" instead of the actuall contents held in the corresponding field in the database.

Once I can get that working, I'll be able to retest my script and see if the data is still being truncated or whether it was due to a problem with the ODBC.

Thanks for your patience :)

[chazzy]

mysql_result's a poor choice for fetching an entire row of data.

anyways, you're not passing it an int. in theory, it should error out because you're passing it a string when it's expecting an INT but instead it's just returning 1..little odd but whatever.
the ideal result would be:


$mysql_access = mysql_connect("localhost", "$un", "$pw") or die("Error connecting to database server: ".mysql_error());
mysql_select_db($db, $mysql_access) or die("Error connecting to database: ".mysql_error());//always gotta do some error checking...

$result = mysql_query("SELECT * FROM products WHERE ProdID='$item'");
$resultset = mysql_fetch_assoc($result); //stores all of your results into an array named $resultset.
$ProdID=$resultset['ProdID'];
$NameModel=$resultset['NameModel'];
$Manufacturer=$resultset['Manufacturer'];
$Pic_Thumb_100x100=$resultset['Pic_Thumb_100x100'];
$Pic_1=$resultset['Pic_1'];
$Pic_2=$resultset['Pic_2'];
$Tagline=$resultset['Tagline'];
$Ed_Summary=$resultset['Ed_Summary'];
$Ed_Main=$resultset['Ed_Main'];


Those shouldn't be 1, unless the column name's not set right somewhere...

[snowweb]

Chazzy! your a star!

All working now. The data must have been being truncated due to a limitation with OBDC which we've now taken out of the equation.

Thanks also for patiently explaining the code you rewrote for me. I'm going back to it in a minute to study it thoroughly. It worked and now the entire record is displayed.

Thanks too to everyone else for all your helpful comments and advice (much of which I have followed and learnt from).

Kind regards

pete

[snowweb]

Hi Chazzy,

Hope you don't mind but I need to 'pick your brains' a little more!

I was updating the code on another page to connect using the method which you showed me. The trouble is that it needs to loop a bit and I've got confused with how to do it.

Here is my attempt which returns an error

<?
/* Connecting to a database and retrieve data */
$mysql_access = mysql_connect("localhost", "$un", "$pw") or die("Error connecting to database server: ".mysql_error());
mysql_select_db($db, $mysql_access) or die("Error connecting to database: ".mysql_error());//always gotta do some error checking...


/* Connecting to a database and retrieve data */

$result = mysql_query("SELECT * FROM products");

$resultset = mysql_fetch_assoc($result); //stores all of your results into an array named $resultset.

if (!$resultset)
{exit("Error in SQL");}
echo "<table><tr>";
echo "<th>ProductID</th>";
echo "<th>Manufacturer / Product Name / Model</th></tr>";
while (mysql_fetch_row($result))
{

$ProdID=$resultset['ProdID'];
$NameModel=$resultset['NameModel'];
$Manufacturer=$resultset['Manufacturer'];
echo "<tr><td>$ProdID</td>";
echo "<td>$Manufacturer $NameModel </td></tr>";
}
echo "</table>";
?>

This displays the headers only. Please can you show me what I've done wrong?

Thanks, pete

[chazzy]

I'm rewriting a section of the code from your first fetch assoc to up to your while statement



echo "<table><tr>";
echo "<th>ProductID</th>";
echo "<th>Manufacturer / Product Name / Model</th></tr>";
while ($resultset = mysql_fetch_assoc($result)){


basically you need get the data into the variable for each row in your set. This should work.

[snowweb]

Chazzy, it's still doing the same!

Here's the code I have at present...

<?
/* Connecting to a database and retrieve data */
$mysql_access = mysql_connect("localhost", "$un", "$pw") or die("Error connecting to database server: ".mysql_error());
mysql_select_db($db, $mysql_access) or die("Error connecting to database: ".mysql_error());//always gotta do some error checking...


$result = mysql_query("SELECT * FROM products");

$resultset = mysql_fetch_assoc($result); //stores all of your results into an array named $resultset.

if (!$resultset)
{exit("Error in SQL");}

echo "<table><tr>";
echo "<th>ProductID</th>";
echo "<th>Manufacturer / Product Name / Model</th></tr>";
while ($resultset = mysql_fetch_assoc($result)){

$ProdID=$resultset['ProdID'];
$NameModel=$resultset['NameModel'];
$Manufacturer=$resultset['Manufacturer'];
echo "<tr><td>$ProdID</td>";
echo "<td>$Manufacturer $NameModel </td></tr>";
}
echo "</table>";
?>

sorry.

[chazzy]

Sorry, missed this thread.

Don't do this:


$resultset = mysql_fetch_assoc($result); //stores all of your results into an array named $resultset.

if (!$resultset)
{exit("Error in SQL");}

echo "<table><tr>";
echo "<th>ProductID</th>";
echo "<th>Manufacturer / Product Name / Model</th></tr>";
while ($resultset = mysql_fetch_assoc($result)){

take out the first couple of lines (before the echo) and it should work.

Specifically, you can do one of several things to check if a query worked:

1)

$result = @mysql_query("SELECT * FROM products");
$error = mysql_error();
if(strlen($error) > 0){
//your query failed
}


2)
$result = mysql_query("SELECT * FROM products") or die("Query failed. MySQL Error Number: ".mysql_errno()." \n<br />Error text:".mysql_error());


Hope that helps you.