replace multiple keyword string with + in place of spaces ???

[Maven4Champ]

Is it possible to somehow look at a field value...Let's call it game_title.

Now, in this game_title there could be values such as this:
The Legend of Zelda
Wii Sports
Call of Duty 3
Aragorn

//etc...

Now, I have a longtext field called search_string which is set to:

http://www.google.com/search?q=

What I want to do is to grab the game_title and instead of:
The Legend of Zelda
//turn it into
The+Legend+of+Zelda
//essentially replacing the ' ' with '+' where ' ' exists.

Then through either php or mysql, I will concatenate those two values to equal:

http://www.google.com/search?q=The+Legend+of+Zelda

---
IS THIS POSSIBLE?

 

[guelphdad]

Yes, you would use REPLACE in MySQL to do that.

 

[vacunita]

Or you could use [blue]str_replace()[/blue] from PHP before passing the value on to Google.

$google_term=str_replace(" ","+",$value_from_DB);

http://mx2.php.net/manual/en/function.str-replace.php

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[johnwm]

?? PHP ?? Am I in the wrong forum here?

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[vacunita]

The OP mentioned he was going to Use PHP to pass the value to google, so i suggested he do the replacement there also. and not from Within Mysql

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[Maven4Champ]

Well guys I was able to do what I needed by using a few methods since my ultimate goal was to create a search friendly url that users could click on.

I created a field in the table called search_string and populated that with the initial string (e.g.

http://google.com?search=q)

I then added another field called game_keyword and used did an update statement to update the table with all of the words in the game title. I then did an update statement on that field and replaced ' ' with '+' on any fields.

Finally, I added a third column to the table that concat'd the the search_string and game_keyword = search_url and returns these results:

http://freemywii.com/forums/wiigames.php

Notice on the Gamespot search that its an exact replica of whatever the game title was plus the search url for the desitnation site.

Thanks for all your help guys!

 

[Maven4Champ]

Speaking of which,

other than doing the keyword ' ' / '+' replacement and then the concat to field3 manually, does anyone know of way this could be automated so that ON UPDATE, it does this. I think that would be a trigger maybe but I am not sure?

 

[guelphdad]

You shouldn't be storing redundant data, the solution should be to use a simple REPLACE as I suggested when querying your data and then you would USE CONCAT to append the search term to the string your are passing to google. You've just created an unnecessary threefold increase in your data.

 

[Maven4Champ]

I totally agree guelphdad. However, I didn't want querying to be slow as it already is for other tables thus I wanted to be able to just look at one field for the already existent data instead of performing functions and sql queries to merge the data together in an effort to provide what was needed. Now that the data is populated, I could very well kill two of the fields, thus decreasing my allocated space for this site feature.

I guess my final question would be - if it can be done better now that I have the data populated, what would that look like? While I am not new to SQL, I am new to consistent and efficient database development and utilizing MySQL for doing certain things that would be a "cost-cutter" so to speak. Any advice is appreciated.

 

[Maven4Champ]

I think I will start a new thread on the trigger/stored proc question as maybe that will more benficial to discussion.