php with mysql query with a CSV type of dataset

[monkeytooth]

IM trying to put together a function but first I dont know where to begin with it. I am a little lost, I have been working on this one project for about 72 hours straight with no sleep.. so im burnt out

 

anyway.

 

I wanna store data in a particular column in a CSV format they are settings for a particular search function I am trying to build to filter the results pending on a persons choice. Theres 7 choices currently

 

 

I am storing the data like var1:var2:var3:var4:var5:var6:var7

I am trying to build this into an existing table structure with over 400,000+ and growing as we speak unfortunately so I am kind of limited to my structural changes of the DB table, hence the concept of using a now null column thats already in it for a CSV style data set.

 

What I am trying to figure out now or wrap my head around is how would I query my DB

would I pull the values out in the CSV format and use explode on them and a series of if or foreach loops? Is there a way I can construct a query with a series of multiple wildcards?

 

I am open to any suggestions.

[monkeytooth]

Im guessing maybe my idea is out of sorts? not possible? or other? does anyone know of a way I can handle the data i need to without bogging down my query on site later?

[Ken2k7]

It's just bad DB design.

[monkeytooth]

Yea, that I figure.. unfortunately, an I am far from a fan of doing it that way.. I am however open to suggestions as to how to fix my issue with this.. you think maybe I should just add the rows an say screw it? my only concern is the many elements of the site that do inserts, an other things.

 

Or the new features with the rows some will return blank or NULL.. eh.. Im sure its all simple stuff I just got 1001 things running through my head with a current project that im for a lack of better words just stupid right now. I think i spread myself to thin on my project load recently

[Muddy_Funster]

Could you break down exactly what it is you are trying to do a bit more?  I'm getting mixed up and can't figure if your trying to pull data out of the database or add it in.  As long as all your current INSERTS and UPDATES use explicit column names then you shouldn't have an issue adding columns to the table.

[monkeytooth]

Over all, filter my results I guess you can say.

Someone types in a keyword for the query. To help filter it down within specific types or categories we want to offer them the option of checking check boxes with filter options.

 

7 Check boxes at most currently.

Which when we store the data on the table since im limited due to this DB not being something fresh to work with and vastly populated I want to take a column thats no longer in use and use it to store the 7 values of the checkboxes. The values of the checkboxes are always static, yes/no so an example of how I would store the data upon entry is

 

yes:yes:yes:no:yes:no:no

 

and when a user comes along and wants to do a search same concept applies to that of when we stored it.. user clicks query follows its normal route.

 

but what I am trying to figure out is how to build the query.. cause taking the above example i know I could query specifically for this combination but what if the user wants to search based on the above with the changes below

 

yes:no:yes:no:yes:no:yes

 

now I might be thinking way out of scope with this maybe I should be thinking of matching things so I match user input Vs stored. But see im thinking of theres more to it for some reason i just cant place my finger on what the more is yet.. but for some reason my mind wont let go of the idea that this could be just that easy..

 

Now what these things are more indepth

 

They are item condition's. We are acting as a medium for people to come sell used items,

so the check boxes are like

Cracks

Worn

Torn

Missing Part

 

nothing specific other than a generalization.

[Muddy_Funster]

so you already have columns like

 || cracked | worn | water_damage | **unused** ||

and you want the unused column to store what eactly?  a concatenated value from the previous columns or are you thinking along the lines

$qry = "UPDATE tablename SET **unused** = ".$var1.":".$var2.":".$var3....

  I can't see the logic in storing a search filter in the actual table it's self, much easier to just script it in my opinion.

[monkeytooth]

unfortunately no, no columns like that exist. this is a database that was designed some time ago by someone else.. what I am trying to do is expand the service based on some requests made. So the unfortunate side is that the table is already massively populated. With that said I took note that when going through things I noticed nowhere on the site or current service is there a specific column thats built into that tables structure being used. So what I want to do is exploit that if possible by setting values as mentioned in it. Rather then make columns and all else cause I know or at least I think I know that if I expand the structure of the table with more columns places that have insert values can or will fail as the columns in there queries no longer match the columns of the table. Im trying to avoid that mostly. and I don't want to really create a second table to match it cause then would cause me to run a second query searching for the filter to then match with the items.. and that will bog it down more than I would prefer. Thats why im mostly looking for ideas or that to find out that im gonna have to do what I dont really wanna do. Either way.

[Muddy_Funster]

So where is the information currently sitting that you are going to filter on (this scratched, dented etc.)  is it in a descriptoin field or simmilar?  If you can describe what you currently have to refference it would help a lot.  Also, going back to your Insert worry - it will only be a problem if the query has been structured as "INSERT INTO tablename VALUES (...)" if the colum names have been explicitly used then there won't be a problem eg "INSERT INTO tablename (col1, col2, col3....) VALUES (...)".

[monkeytooth]

alright, existing structure is:

 

[iD][iSBN][author][title][itemnumber][listingID][datestart][dateexpire][dateend][stockinfo][active][comment][price][description]

 

the comment column is no longer in use, not sure if it ever was to be honest with you.

So I want to use the column for comments to store the idea of "yes:yes:yes:no:yes:no:no" in rather than make new columns on the existing structure of the table (which could break other parts of the site unintentionally)  or create a new table specific to them and then from here on in add to it with referencing itemnumber title isbn author cause then im quering one table to get this refrence to the other to then query the other table.. for other information be it through a JOIN clause or otherwise.

 

So I guess before going all out and building one way or the other I just want to figure out if i store "yes:yes:yes:no:yes:no:no" something like that in a single column on a table, is there a way I can construct a query with the help of php.. to then query the DB that would be looking for similar refrences.. lets say in a case a user desides to search checking the boxes for worn pages and ripped pages.. indicating upon checking the refrence to worn and ripped that they dont want to see results with ripped pages or worn pages, but everything else, thats fine...

[Muddy_Funster]

There are a couple of ways it can be done, probably an array set using nested if's on your checkbox input would be the most likely approach, it does however meen that someone (and if your life goes anything like mine this meens you) will have the mind numbing task of entering the right yes:yes:no:yes.... combination in the column against each of your 400,000+ entrys.  On top of that it's pretty bad practice.  Given you don't want to edit the current table I would strongly suggest linking a second table to it, call it status or somthing, and cross refference on the ID number primary key in your current table as a forign key in your new table which you can set to cascade so any new entries in the main table auto populate the ID field into the new one.  Doing this will help break out of this restrictive workspace youve been stuck in as you can then use the addition table to store any further info for the records that you want (assuming that from day one you use absolute refferencing for your INPUT statements).