More help needed with microsoft access

[JAKO]

Hi... I just want to know is there a php command that can transform a word from a phrase into another word... For example in one of my column from one of my database... I have a phrase with two words... (example John Wayne)... Is there any command that can be used to tell that whenever there is a word John in my column (example: John Wayne, John Travolta, etc) it will automatically change the word John to Johnny.... Can somebody help me?????

[Garethp]

mysql_query("UPDATE `TABLE`

SET `Columname`='Johnny'

WHERE `Columname`='John'");

[JAKO]

Thanks for the reply..... Example below is a name column from my microsoft access table... Do we have a php code that can change the first name from John to Johnny while maintaining the last name....??? Could somebody please help me....

 

----------------------------------------

|              Name                                |

----------------------------------------

|        John Wilkinson                          |

|        John Abraham                          |

|        John Graham                            |

|        Johnny Wayne                          |

----------------------------------------

[Garethp]

Try

 

$Look = mysql_query("SELECT * FROM `Table` WHERE `Name` REGEXP 'John'");

 

while($Search = mysql_fetch_array($Look))

{

$Temp = str_replace('John', 'Johnny', $Search['Name']);

$ID = $Search['RowID'];

mysql_query("UPDATE `Table`

SET `Name`='$Temp'

WHERE `RowID`='$ID'");

}

[TeNDoLLA]

<?php
$sql = "UPDATE persons SET name = REPLACE(name, 'John', 'Johnnnnny')";
mysql_query($sql) or die(mysql_error());

[JAKO]

Hi... I tried this but I got error....  Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in D:\xampp\htdocs\testis2.php on line 21... Could Sombeody help me?????

 

<?

//sambung ke database microsoft access

$dbc=odbc_connect("guestbook","","");

if (!$dbc)

{exit("Connection Failed: ".$dbc);}

$query="SELECT * FROM comments";

$rs=odbc_exec($dbc,$query);

if(!$rs)

{exit("Error in SQL");}

echo '<h3>MS Access Powered Guest Book</h3>';

while(odbc_fetch_row($rs))

{

$e_name=odbc_result($rs,"name");

$comment=odbc_result($rs,"comment");

$e_date=odbc_result($rs,"entry_date");

 

// Tukar perkataan

 

$Look = mysql_query("SELECT * FROM `comments` WHERE `name` REGEXP 'John'");

 

while($Search = mysql_fetch_array($Look))

{

$Temp = str_replace('John', 'Johnny', $Search['name']);

$ID = $Search['RowID'];

mysql_query("UPDATE `comments`

SET `name`='$Temp'

WHERE `RowID`='$ID'");

}

 

//tukar lowercase ke uppercase

$str_name = $Temp;

$str_name = strtoupper($Temp);

 

$str_comment = $comment;

$str_comment = strtoupper($str_comment);

 

$str_date = $e_date;

$str_date = strtoupper($str_date);

 

 

 

 

// display database

echo '<b>Name:</b>'.$str_name.'<br>';

 

echo '<b>Comments</b>'.$str_comment.'<br /> <b>Date:</b>'.$str_date.'<br />

<hr/>';

}

odbc_close($dbc);

echo"</table>"

 

?>

[Adam]

That's because they're giving you MySQL functions when you're using Access. To be honest, I've not touched Access in quite a while so I'm a little rusty with the SQL capability. Do some reasearch on Google and see what kind of string replacement functions are available, then perform an update like shown below - if possible with Access. You can use odbc_exec to perform queries.

[trq]

Your using ms access, you can't use the mysql_* functions to access it.

 

Simply execute the query that TeNDoLLA posted, Garethp's is a good example of how NOT to do it.

[Garethp]

What's wrong with my example? It's a little bloated, sure, and I'm learning from the other one, but wouldn't it have worked?

[trq]

Yeah it would have worked, with a minimum of double the queries required.

[Adam]

You're extracting and looping through the data, using PHP to replace the text, then updating each row seperately. Waste of resources when you can achieve the same result with just one simple update query.

[JAKO]

Thank you for all of your opinion...  This is what I have managed so far.... It still got some errors...

 

This is a sample of my php....

 

<?

//to connect with the Microsoft Access Database

$dbc=odbc_connect("guestbook","","");

if (!$dbc)

{exit("Connection Failed: ".$dbc);}

$query="SELECT * FROM comments";

$query = "UPDATE comments SET name = REPLACE(name, 'John', 'Johnnnnny')"; //<------- to change the word John to Johnny

$rs=odbc_exec($dbc,$query);

if(!$rs)

{exit("Error in SQL");}

echo '<h3>MS Access Powered Guest Book</h3>';

while(odbc_fetch_row($rs))

{

$e_name=odbc_result($rs,"name");

$comment=odbc_result($rs,"comment");

$e_date=odbc_result($rs,"entry_date");

 

 

// display database

echo '<b>Name:</b>'.$e_name.'<br>';

 

echo '<b>Comments</b>'.$comment.'<br /> <b>Date:</b>'.$e_date.'<br />

<hr/>';

}

odbc_close($dbc);

echo"</table>"

 

?>

 

Below is my current database using Microsoft Access. I was hoping to change the name John to Johnny

 

----------------------------------------

|              Name                                |

----------------------------------------

|        John Wilkinson                          |

|        John Abraham                          |

|        John Graham                            |

|        Johnny Wayne                          |

----------------------------------------

 

It should turn out something like this...

 

----------------------------------------

|              Name                                |

----------------------------------------

|        Johnny Wilkinson                      |

|        Johnny Abraham                      |

|        Johnny Graham                        |

|        Johnny Wayne                          |

----------------------------------------

 

Instead of the above result... I got this error....

 

Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Undefined function 'REPLACE' in expression., SQL state 37000 in SQLExecDirect in D:\xampp\htdocs\testis2.php on line 8

Error in SQL

 

Can you guys give me your opinion or answers....