How to process data with a one to many relation from MySQL into PHP -


i using following query return data single habit.

a habit can have many steps , many comments.

when run following query repeats data habit every step , comment there is.

        select          habit_id, habit_user_id, habit_name, habit_category_id, habit_description, habit_target_user, habit_up_votes, habit_down_votes,          step_id, step_description,          comment_id, comment_user_id, comment_description         habits, steps, comments          habit_id = ?          , habit_id = step_habit_id         , habit_id = comment_habit_id         order step_order

example output (the ids should enough idea of going on):

habit_id    step_id    step_description    comment_id    comment_description 1           1          x                1             works great! 1           1          x                2             awful! 1           1          x                3             nice job 1           2          y           1             works great! 1           2          y           2             awful! 1           2          y           3             nice job

i want able take returned data , have in 1 array.

array("habit_id" => 1, "step_id" => array(1, 2), "comment_id" => array(1, 2, 3));

the ways can think of doing either:

executing 3 seperate queries 1 habit data, 1 steps habit , 1 comments habit.

or

by using above query , constructing new array habit data , looping through rows , constructing new array steps , comments while @ same time making sure there no duplicates added.

this sounds way inefficient, can suggest better way either modifying query provide php more workable data or trick in php. did @ 1 point consider concatenating data arrays steps , comments within query, thought should php's job manipulate data in such way.

the reason why want data in way return used in angularjs application.

the query fine is. it's returning enough data iterate through , build array want. 

$data = array();  // type of loop here. whatever returns data query while($row=mysql_fetch_array($result)) {     $data['habit_id'] = $row['habit_id'];      // in_array check ensures there no dupes     if (!in_array($row['step_id'], $data['step_id'])) {         $data['step_id'][] = $row['step_id'];     }     if (!in_array($row['comment_id'], $data['comment_id'])) {         $data['comment_id'][] = $row['comment_id'];     } }

this going more efficient 3 separate queries. make sure database indexed. , check you're using indexes running query explain.


-

Comments

Post a Comment

Popular posts from this blog

OpenCV OpenCL: Convert Mat to Bitmap in JNI Layer for Android -

there several posts converting mat bitmap using utils.mattobitmap() function. i'm assuming function can called in java layer after importing utils class. i want transfer data memory address pointed uint32_t* bmpcontent; in code below. jniexport void jnicall java_com_nod_nodcv_nodcvactivity_runfilter( jnienv *env, jclass clazz, jobject outbmp, jbytearray indata, jint width, jint height, jint choice, jint filter) { int outsz = width*height; int insz = outsz + outsz/2; androidbitmapinfo bmpinfo; if (androidbitmap_getinfo(env, outbmp, &bmpinfo) < 0) { throwjavaexception(env,"gaussianblur","error retrieving bitmap meta data"); return; } if (bmpinfo.format != android_bitmap_format_rgba_8888) { throwjavaexception(env,"gaussianblur","expecting rgba_8888 format"); return; } uint32_t* bmpcontent; if (androidbitmap_lockpixels(env, outbmp,(void**)&bmpcontent)...
Read more

android - org.xmlpull.v1.XmlPullParserException: expected: START_TAG {http://schemas.xmlsoap.org/soap/envelope/}Envelope -

i trying hit soap api in application login purpose , getting above mentioned error , can 1 tell me going wrong , first time soap apis. here code hit soap :- vector<object> args = new vector<object>(); hashtable<string, object> hashtable = new hashtable<string, object>(); hashtable.put("email", "mss.siddhart28@gmail.com"); hashtable.put("password", "siddharth"); args.add(hashtable); soapserializationenvelope envelope = new soapserializationenvelope( soapenvelope.ver11); envelope.dotnet = true; envelope.xsd = soapserializationenvelope.xsd; envelope.enc = soapserializationenvelope.enc; (new marshalhashtable()).register(envelope); httptransportse androidhttptransport = new httptransportse( "http://tempuri.org/", 15000); androidhttptransport.debug = true; string result = ""; try { soapobject request = new soapobject("http:...
Read more

python - How to remove the Xframe Options header in django? -

i have made page has iframe . inside iframe want show multiple different links article facebook, or news, or youtube video or other possible url. but, due xframe header, unable so. referred following link: https://docs.djangoproject.com/en/1.8/ref/clickjacking/ , django xframeoptionsmiddleware (x-frame-options) - allow iframe client ip but didn't help. my settings.py file's middleware_classes is: middleware_classes = ( 'django.contrib.sessions.middleware.sessionmiddleware', 'django.middleware.common.commonmiddleware', 'django.middleware.csrf.csrfviewmiddleware', 'django.contrib.auth.middleware.authenticationmiddleware', 'django.contrib.auth.middleware.sessionauthenticationmiddleware', 'django.contrib.messages.middleware.messagemiddleware', 'django.middleware.clickjacking.xframeoptionsmiddleware', ) from http://django-secure.readthedocs.org/en/latest/middleware.html , found using ...
Read more