Page 1 of 1

Update query question

Posted: Wed Nov 02, 2005 11:50 am
by bramnaus
feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]


I got a table which i use to store html-code. In this code there are url of pictures with capital letters. Now i changed the directories of some of the picture folders (i took out the capitals). Since i use a unix server the pictures in the html-code will be visable and errors occur.
Now i what to write a script that replaces the capitals in the urls in the html codes. Everthing is working correct untill the update query.

Code: Select all

<?php 
$search= "Media";  
$replace= "media";  
              
$query_Recordset_check_content = "SELECT * FROM $tabel_content WHERE content LIKE '%".$search."%'";      
$Recordset_check_content = mysql_query($query_Recordset_check_content, $connect) or die(mysql_error());  
$row_Recordset_check_content = mysql_fetch_assoc($Recordset_check_content);  
$totalRows_Recordset_check_content = mysql_num_rows($Recordset_check_content);  

do{  
    $content = $row_Recordset_check_content['content'];  
    $content_id = $row_Recordset_check_content['id'];  
    $content = str_replace($search, $replace, $content);  
    echo $content;  
    mysql_query("UPDATE $tabel_content SET content = '".$content."' WHERE id = '".$content_id."'"); 
    // mysql_query("UPDATE $tabel_content SET content = '$z' WHERE id = '$content_id'");     
} while($row_Recordset_check_content = mysql_fetch_assoc($Recordset_check_content));          
?>
The echo $content shows the html-code and pictures are visable, so the str_replace function worked. But the update query didn't work. What am i doing wrong? Any suggestion?

Thx for any help


feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

Re: Update query question

Posted: Wed Nov 02, 2005 11:59 am
by RobertGonzalez
bramnaus wrote: Change this...

Code: Select all

<?php 
    mysql_query("UPDATE $tabel_content SET content = '".$content."' WHERE id = '".$content_id."'"); 
?>
to this...

Code: Select all

<?php 
    mysql_query("UPDATE $tabel_content SET content = '$content' WHERE id = $content_id"); 
?>
See if that does anything.

nope

Posted: Wed Nov 02, 2005 12:20 pm
by bramnaus
Nope, used that one before, but got a reply at an other forum to use

Code: Select all

<?php 
    mysql_query("UPDATE $tabel_content SET content = '".$content."' WHERE id = '".$content_id."'"); 
?>

Posted: Wed Nov 02, 2005 1:02 pm
by RobertGonzalez
Run this and see what echo's out of the first $content and the second one. If they are not different MySQL will not update and it will return zero affected rows. Also I threw in an error trapper to see what MySQL is telling you about your query.

Code: Select all

<?php
$query_Recordset_check_content = "SELECT * FROM $tabel_content WHERE content LIKE '%".$search."%'";      
$Recordset_check_content = mysql_query($query_Recordset_check_content, $connect) or die(mysql_error());  
$row_Recordset_check_content = mysql_fetch_assoc($Recordset_check_content);  
$totalRows_Recordset_check_content = mysql_num_rows($Recordset_check_content);  

while ($row_Record_set_content)
{
    $content = $row_Recordset_check_content['content'];  
    echo $content; // Just for testing
    $content_id = $row_Recordset_check_content['id'];  
    $content = str_replace($search, $replace, $content);  
    echo $content; // Is this echo different that the first? MySQL will not update if they are not different
    if (!mysql_query("UPDATE $tabel_content SET content = '".$content."' WHERE id = '".$content_id."'"))
    {
        echo mysql_error();
    }
}
?>

got an SQL syntax eror

Posted: Wed Nov 02, 2005 2:36 pm
by bramnaus
Okay, i get this error somewhere between all html-code:

You have an error in your SQL syntax near 'Comic Sans MS'">

Could it be that there are some " in the html-codes which are the caurse of the error?

Found a solution

Posted: Wed Nov 02, 2005 2:50 pm
by bramnaus
The html-code had some dubble quotes ("") which caursed the syntax error. I searched php.net for htmlspecialchars function and found a usefull made function:

Code: Select all

<?php
$search = "Media"; 
$replace = "media"; 

function unhtmlentities ($string) {
   $trans_tbl =get_html_translation_table (HTML_ENTITIES );
   $trans_tbl =array_flip ($trans_tbl );
   return strtr ($string ,$trans_tbl );
}
             
$query_Recordset_check_content = "SELECT * FROM $tabel_content WHERE content LIKE '%".$search."%'";       
$Recordset_check_content = mysql_query($query_Recordset_check_content, $connect) or die(mysql_error());   
$row_Recordset_check_content = mysql_fetch_assoc($Recordset_check_content);   
$totalRows_Recordset_check_content = mysql_num_rows($Recordset_check_content);   
echo $totalRows_Recordset_check_content;
do
{ 
    $content = $row_Recordset_check_content['content'];   
    echo $content; // Just for testing 
    $content_id = $row_Recordset_check_content['id'];   
    $content = str_replace($search, $replace, $content); 
	$content = unhtmlentities (addslashes (trim ($content)));  
    echo $content; // Is this echo different that the first? MySQL will not update if they are not different 
    if (!mysql_query("UPDATE $tabel_content SET content = '".$content."' WHERE id = '".$content_id."'")) 
    { 
        echo mysql_error(); 
    } 
} while($row_Recordset_check_content = mysql_fetch_assoc($Recordset_check_content));
?>
This did the trick!