data parsing in query [Intermediate ~> Advance mysql user please help]

I was doing conditions in a single query
here's what i've come up

"SELECT DISTINCT store_type FROM stores
WHERE NOT EXISTS (SELECT * FROM cities_stores
WHERE cities_stores.store_type = stores.store_type)";

here's the question
i want to parse a string from other table row where the string is formatted by php function "serialize($string)".. is there any way to parse the serialized string to mysql compatible type.?

So much thanks...

 

Hi, you can use REGEXP to test input string or [var]char field against regular expression match.
Regards
p.p.: also LIKE but REGEXP/RLIKE is better in this case.
Edit: parsing can be done via string functions
Note: mysql has not preg_replace!
Maybe you'll provide some input and desired output

 

I would do something like this:

SELECT store_type FROM stores
WHERE store_type NOT IN (
SELECT store_type FROM cities_stores)
GROUP BY store_type;

As far as the parsing question, I'm not clear on exactly what you are trying to accomplish. Are you saying there is a serialized string stored in the database?

 

yep.. alright here's the exact algorithm of what im trying to query..

- i have a quiz with more than 300 items per quiz
- its paginated so im only showing 5 questions per page
- BUT it is random generated --> (order by rand() limit 0,5)
- now once the user has finished that page it automatically updates the row and insert the answers WITH respects with the ITEM number.
here is the string format of the answer
q1:a;q9:d;q250:c; .....

where question_item:user_answer;......

now to the next page, again im querying random items BUT now here's what im trying to do with the query, all items that have already taken must restrict the query from selecting it randomly.. meaning items taken must not show again when random.

i want to execute this without dealing into php to lessen memory load to the server, and besides some quizzes are about 1,000+ items and 50+ users are using it simultaneously.

 

remember that i have to parse the string format of the answers and execute query from that string..
stored procedures are also welcome, i just cant get the correct algorithm process of that.

no php's please, i've already made that in php and convinced that i should replace it by pure mysql function and query.

Thanks

 

up

 

so a user just can have 1 row in the answers table right??
i think the simple INSTR() function will help in this case, you can just search the question_item + ':' string in the answer string, if the function return is 0 means that the question was never been answered.

SELECT * FROM question, answers
where INSTR(answers.useranswer, question.question_item || ':' ) = 0 AND
	answers.userid = 'theid'

Code (markup):

logicly i think it'll work but i don't know about the Load..


note : use CONCAT() function if the || operator doesn't worked..