stripping a substring in php/mysql

[Rojen]

I've got what appears to be a buffering challenge.

I have a medium text field the I want to remove part of the data from, say 300-500 characters into the data. Basically I will be stripping everything after a specified string.

Here is what I wrote:

$sql=mysql_query("select * from table1");
$str='zzzzzzzzzzzzzzz --------- ---- -----';

while ($row=mysql_fetch_assoc($sql)) {
$pos=strpos($row[text_field], $str);
$out=trim(substr($row[field],0,$pos);
$id=$row[primary_key_field];

  if($pos!=0)                                                           {
         mysql_query("update table1 set text_field=('.$out.')
         where primary_key_field=('.$id.')");               }
   unset($out, $pos, $id);                                 }

It updates nothing. If I replace $out with a shorter string, say $out1='Bob does great', it works. If I dump the $out to a report. It contains exactly what I want updated to the text field. The other variables similarly contain exactly what I would expect. This leads me to believe that something about the length of the text string in $out is causing the update to not work. I have adjusted a lot of the buffers that I could find to no avail so far.

By the way I prefer the (' to "' when putting a string variable into a mysql query. Works the same, but easier to read. I tried replacing that to a more traditional look and the results are exactly the same and it still works with $out1='Bob does great'.
Any ideas?

Thanks,

[Rojen]

Thought I should include a little more detail:

I have read the manual, and googled. Been on this little challenge for a couple daus on and off now.

I get no error msg that I know of, just updates nothing. I am doing all this as local host on the dev box itself. I am directly executing .php code with no html internal at all.

I do not have a php.conf file (windows XP laptop is my dev box.)

Version info: php 5.0.2
apache 1.3.33
mysql 4.1.7

Variables and Files I have tweaked:

httpd.conf
Timeout 300

php.ini
max_execution_time = 480

max_input_time = 480
memory_limit = 128M

post_max_size = 128M
upload_max_filesize = 128M
max_allowed_packet=128M
default_socket_timeout = 480
mysql.connect_timeout = 120

my.ini
set-variable=key_buffer=64M
set-variable=table_cache=256
set-variable=sort_buffer_size=64M
set-variable=read_buffer_size=1M
set-variable=max_allowed_packet=64M

[laserlight]

$sql = mysql_query("SELECT * FROM table1")
	OR die(mysql_error());
$str = 'zzzzzzzzzzzzzzz --------- ---- -----';

while ($row = mysql_fetch_assoc($sql)) {
	if (($pos = strpos($row['text_field'], $str)) !== false && $pos != 0) {
		$out = trim(substr($row['field'], 0, $pos));
		$id = $row['primary_key_field'];

	mysql_query("UPDATE table1 SET text_field='" . $out . "'"
		. "WHERE primary_key_field='" . $id . "'")
		OR die(mysql_error());
}
}

I get no error msg that I know of, just updates nothing.

Unfortunately, you did not give the exact code you used, so there should be an error message.
$out=trim(substr($row[field],0,$pos);
is wrong since there is no closing parenthesis for trim()

[jkurrle]

This may seem a little simplistic, but why not use EXPLODE to take care of that for you?

Example:

<?php

$data="huge string of data... qqqqqdelimiterfieldqqqqq huge string of more data";

$array=explode("qqqqqdelimiterfieldqqqqq",$data);

$subset=array_shift($array);

?>

This will get you all information up to the first delimiter field. If you wanted to get the second field, just do an array_shift and then set your subset equal to the second array_shift.

[Rojen]

Laserlight you are right. At the time I posted I was behind a firewall with no direct access to the web hence the easy syntax error when I retyped it.

Here is the current version of the code with the or die stuff added too:

$sql=mysql_query("select * from table3")
  OR die(mysql_error());

$string='Very best always';

print "<table border=\"1\"><tr><th>1</th><th>2</th><th>3</th><th>4</th><th>5</th><th>6</th></tr>";

while ($row = mysql_fetch_assoc($sql)) 
        {
        $pos=strpos($row[text_field], $string);
        $out=strtr((trim(substr($row[text_field],0, $pos))),'\'','~') ;
        $ID=$row[primary_key_field];
        if ($pos!=0) 
              {
              mysql_query("UPDATE table3 SET text_field=(' . $out .')
            	              WHERE primary_key_field=(' . $id . ')")
           	              OR die(mysql_error()); 
              }

    print "<tr><td>$ID</td><td> $row[YM_ID]</td><td>$out</td><td>$pos</td><td>$row[text_field]</td></tr>";
    unset($out, $pos, $ID); 
    }

print "</table>"; 			

Thanks for looking at it. It still goes through without error, but not updating. The $string value changes as I test so I can get different length of characters in the selection set.

For kicks and grins I cut and pasted this part of yours -

mysql_query("UPDATE table1 SET text_field='" . $out . "'"

VS - my ('.$out.')

Same results.

The $out line really has a \backslash which gets dropped when cut and pasted into the forum.

jkurrle

Thanks for looking too. My challenge does not seem to be with the data set itself, but rather the updating. I have tried several different ways while doing this with the same result. Though I will admit I did not think of using explode in this. The $out has exactly the selection set I want updated, and if I substitute a smaller data set, it updates fine. I think I will rewrite it using insert and use a replacement table and see how that goes. I avoided that initially because of the amount of data, seemed inefficient to recreate a 400K row table unnecessarily, especially given the large text fields.