Gossamer Forum
Home : Products : Gossamer Links : Discussions :

MySQL insert question

Quote Reply
MySQL insert question
Greetings All:

I wasn't sure whether to post this here or in the MySQL Forum - but I am sure someone will know.

I have searched for Forum and tried to decipher the MySQL docs, but am still unsure how to go about this.

When I first setup links some years ago I created 'Yes' 'No' fields within my tables eg. Do you like oranges 'Yes' or 'No' - selected via radio buttons.

I believe a better way to do it now is simply to change these fields to a different type (integer?) so I will be able to have a '1' for Yes and a '0' for No in the database. There are some real reasons I want to do this..... Now for my question.

What MySQL Monitor command will I need to enter to achieve this? In other words, I want every record in my database in the links table xxx.field where it = Yes to be changed to '1' and where it = No to be changed to '0'. (do I need to do the change to 0 or just delete the 'No'?)

Would I then need to change that field to a different type - integer maybe?

Sorry for my ignorance on this - but I would appreciate some advice.


Regards,



Clint.
--------------------------
http://AffiliatesDirectory.com
The Affiliate Programs Directory
Quote Reply
Re: [Clint] MySQL insert question In reply to
You could try;

UPDATE lsql_Links SET field_name = '1' WHERE field_name = "Yes"

..and;

UPDATE lsql_Links SET field_name = '0' WHERE field_name = "No"

Hope that helps.

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] MySQL insert question In reply to
Andy:

You're a genius. How simple it was.... Thanks very much.

Just one other thing what type of Column Type do you recommend for this type of field, so I can do a <%if abc%><%abc%><%endif%> on the basis that '1' means yes.

Thanks,


Clint.
--------------------------
http://AffiliatesDirectory.com
The Affiliate Programs Directory
Quote Reply
Re: [Clint] MySQL insert question In reply to
No worries :)

I would use a SELECT type;

Values: 1/0
Show: Yes/No

In your HTML pages, you will need to use something like this though;

Code:
<select name="my_field">
<option <%if myfield eq "1"%>selected="yes"<%endif%> value="1">Yes</option>
<option <%if myfield eq "0"%>selected="yes"<%endif%> value="0">No</option>
</select>

Hope that helps.

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] MySQL insert question In reply to
Hi Andy:

I think something went wrong. My current fields are setup as Enum fields. When I ran the queries you advised it changed everything into 0 (or blank actually).

Should I have updated/changed my table definitions BEFORE I ran the query?

No damage done - I'm doing this on my offline database before I do anything to my live site...

Thanks again,


Regards,


Clint.
--------------------------
http://AffiliatesDirectory.com
The Affiliate Programs Directory
Quote Reply
Re: [Clint] MySQL insert question In reply to
Yeah, you need to run the field changes first (i.e set it up as an SELECT (INT)) ... and then run the update queries.

Hope that helps.

Cheer

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!