looping through a database & exploding each field

[Malevolence]

Hi there,

Got a system where pages within my CMS have 'flags' and so, in the database there is a single field for all of these flags. The flags basically decipher things like whether a page is a draft or not, whether it should be displayed in the nav-bar, whether it should be displayed in the sidebar and whether it should appear in search results. Each numeric input is delimited by commas (e.g. 0,0,1,1).

 

Initially it was simple, all I had to do for one page request is find that page (using the pID in the DB as it's WHERE field), explode the flags field via commas and it was easy to process from there for one page, however the nav-bar display one is a tricky one (likewise with the search & sidebar). What I will need to do is find every field that has a 1 for the second & third items in each array for every exploded field in the table. I would imagine MySQL isn't capable of this, so I guess its down to PHP, however I am no good with the more technical loops.

 

here's some example data:

Table name = fs_pages
pgID, pgURI, pgName, pgSummary, pgBody, pgUser, pgTimestamp, pgFlags

 

Grab all of the info using MySQLi Query (the easy bit) unless you can explode fields with a query.

Loop through all of the rows, exploding the pgFlags fields and finding all of the rows where $examplearray[1] == 1

 

Thanks in advance,

Dan

[.josh]

Is your list of numbers are always single digits, so you know the substring position of each number?  If so, you might be able to make us of mysql's substring

[Malevolence]

I'm not much of a MySQL person if I'm honest, Never realised how powerful MySQL is, but a lot of people say try to utilise mysql's features before calling upon php to haul the workload.

 

Could you explain how this query would be written, and yes the numbers are always single digits. Remember I'll need to loop through each row using this substring thing.

[.josh]

Okay say your string is

 

0,1,1,3,2

 

with mysql's substring, the first character position in the string is 1 (not 0 like with most things).  So you know that your numbers will be in positions 1,3,5,7,9,etc... so if you want to select all rows where the 2nd and 3rd numbers are 1 (from your OP), you would do something like this:

 

select * from fs_pages where substring(pgFlags,3,1) = 1 and substring(pgFlags,5,1) = 1

 

 

 

[Malevolence]

Will try that tomorrow evening. I still don't get how even though 2nd and 3rd are next to each other, yet in mysql's substring, they are odd... (3 & 5) but yeah, I'll see how it turns out.

Thanks!

[.josh]

because there is a comma between them. The comma counts as a character position in the string.

 

Character Position:123456789

String:0,1,1,3,2

[Malevolence]

Couldn't explain it better, am trying now (will replace this post)