option

[missippi]

Hi,

I have a database with a field called entryTerm that contains for example Fall 2002, I put the value into a variable that I would like to split the value into two variables so I can do something like the code below. Any ideas?

I have to option boxes:

<select name=&quot;entryTerm&quot;>
<option value=&quot;Fall&quot; <? if ($entryTerm == &quot;Fall&quot; {print(&quot; Selected&quot;}?> > Fall </option>
<option value=&quot;Winter&quot; <? if ($entryTerm == &quot;Winter&quot; {print(&quot; Selected&quot;}?> > Winter </option>
<option value=&quot;Spring&quot; <? if ($entryTerm == &quot;Spring&quot; {print(&quot; Selected&quot;}?> > Spring </option>
<option value=&quot;Summer&quot; <? if ($entryTerm == &quot;Summer&quot; {print(&quot; Selected&quot;}?> > Summer</option>
</select>

<select name=&quot;year&quot;>
<option value=&quot;1999&quot; <? if ($entryTerm == &quot;1999&quot; {print(&quot; Selected&quot;}?> > 1999 </option>
<option value=&quot;2000&quot; <? if ($entryTerm == &quot;2000&quot; {print(&quot; Selected&quot;}?> > 2000</option>
<option value=&quot;2001&quot; <? if ($entryTerm == &quot;2001&quot; {print(&quot; Selected&quot;}?> > 2001</option>
<option value=&quot;2002&quot; <? if ($entryTerm == &quot;2002&quot; {print(&quot; Selected&quot;}?> > 2002</option>
</select>



MAO

 

[KarveR]

Split the field into two fields &quot;season&quot; and &quot;year&quot; - you can then use a date field for year (searchable sortable etc) and group by season to load you selects correctly. ***************************************
Party on, dudes!

 

[missippi]

Thanks but I guess my question is how do I split them??

MAO

 

[KarveR]

I can't see quite where this is leading or why you'd want to split this, would it not be easier to create the drop down of years and drop down of seasons manually? as there are only 4 seasons it seems daft to try and pull it from a db.
If its to be submitted back as a query you can then combine them to make the search criteria.

If you are using Mysql you'd need to use an ereg to split off the season.
As long as the year is always 4 characters you can use SELECT right(&quot;entryTerm&quot;, 4)
you can't do this for the season as you have a differnet number of chars depending on season. ***************************************
Party on, dudes!

 

[missippi]

Thanks again. I didn't set the database up I am working from some elses crappy design. In the database there is a entery term (eg. Fall 2002) that I am pulling from each students records that they entered and someone concatenated together. The fields are combined at present. I am displaying the information in a report that is being viewed. It is hard to explain but the fields need to be split to display the information correcty in the option boxes. I agree it makes little sense but I am just trying to pull data into drop down boxes they way that I was told to. I have little say in the matter. I have already expressed my thought of bad design.

MAO

 

[KarveR]

Is it mysql?
Maybe I can throw in a ereg to pull the season for you. ***************************************
Party on, dudes!

 

[missippi]

Yes mysql

MAO

 

[KarveR]

Hi, try this in your query:

SELECT @lnth:=length(entryTerm),left(entryTerm,(@lnth-4))AS season,right(entryTerm,4) AS year FROM <your db> where etc etc.....

This should return 3 columns, lnth (ignore), season and year. Now you are able to put season into one select and year in another.It may be wise to add distinct() to these to ensure you only get unique results (no duplicated years or seasons).
Alternatively just use the

SELECT right(entryTerm,4) AS year FROM <your db>

and create the season drop down manually.
Whatever this should give you all possible options.

Hope this will help, sorry for the delay its my first day back at work and I been kinda busy

*Add distinct() to avoid duplicated records
SELECT @lnth:=length(entryTerm),distinct(left(entryTerm,(@lnth-4)))AS season,distinct(right(entryTerm,4)) AS year FROM <your db> where etc etc..... ***************************************
Party on, dudes!

 

[KarveR]

Oh btw I didnt ereg/preg_match (PHP) as mysql will do this quicker up to about 150,000 records and if you build a web page with a table that big, dont expect the server to return it in a hurry ***************************************
Party on, dudes!

 

[KarveR]

Duh! brain fully back into gear,
SELECT distinct @lnth:=length(entryTerm),left(entryTerm,(@lnth-4))AS season,right(entryTerm,4) AS year FROM <your db> where etc etc..... ***************************************
Party on, dudes!