mysql problem

elvatra · August 17, 2013, 8:39am

hi,

i have a mysql question to ask. i have a user table. the user table store all my user information and image. this is all fine im able to do it.

So, i have a small grouping to do. those in the same group of people, i need to group them in one group. so i have added a group_info in my user table. Then, each of my group have 3 rows which each row has 5 people on it. so i add another filed call row_info.

So my question is here. i have aroud 500 people in my user table. how do i query my statement so that im able to show in json encode that how many group i have and in each group, the user able able to display the information like description and name.

example of my silly drawing

Group 1
|
row 1->user 1 -> image, description, caption,remarks
row 1->user 2->image,description, caption, remarks.

i have all this information in mysql database. So how shoud i select the statement out of it???

i know how to json encode it… SO no worries…

richei · August 17, 2013, 4:00pm

maybe something like

$sel = “SELECT * FROM users_table GROUP BY group_no ORDER BY username”;

Now if you’re pre-selecting the group number, then you’d just use a WHERE clause instead of GROUP BY. You could also use a regular select statement without a clause and just order by group number and username (in that order), then use php to display it by group, then by row.

elvatra · August 18, 2013, 2:11am

hi, how about this??

Group 1
|
row 1->user 1 -> image, description, caption,remarks
row 1->user 2->image,description, caption, remarks.
row 1->user 3 -> image, description, caption,remarks
row 1->user 4->image,description, caption, remarks.
row 1->user 5 -> image, description, caption,remarks
row 1->user 6->image,description, caption, remarks.
row 1->user 7 -> image, description, caption,remarks
row 1->user 8->image,description, caption, remarks.

Group 2
|
row 1->user 9 -> image, description, caption,remarks
row 1->user 10->image,description, caption, remarks.

Group 3
|
row 1->user 11 -> image, description, caption,remarks
row 1->user 12->image,description, caption, remarks.

i want to display something like this.

richei · August 18, 2013, 2:41am

easier to just do a basic query selecting the information you want, order it by group and name, then do something like
[php]
$prev_group = “”;
if($prev_group != $row[‘groupno’]) {
// display group number
$prev_group = $row[‘groupno’];
}
// display rest of user info[/php]

elvatra · August 18, 2013, 6:02am

Hi,

after dicussing with my buddy which involve in this small project of it, he told me that we still have some problem on it regarding the database creation.

upon from the user table, group table, he told me that he wanted to additional table for the animated group of people. So i have create a table for gd to render so that my gd render able to know which group of people under which type of animation and under which row.

So how should i do it?? i have no idea on it…

richei · August 18, 2013, 6:59am

Well, having the information in multiple tables can complicate the query, but as long as you have information tying all of your information together, it wouldn’t to hard to set up some joins.

elvatra · August 18, 2013, 7:31am

Hi

can you show me some example of how to join at least 5 tables ? i having problem of finding joining so much table on it. normally i have join 3 tables. LEFT and RIGHT join. but i cant join more then 3.

richei · August 18, 2013, 8:09am

I know what information you’re trying to get out of it, but why do you have 5 tables?

try just regular joins, not left or right joins.

elvatra · August 18, 2013, 4:01pm

i have another set of question.

this is more on how do i random insert value with a condition on php?

as i mention before, i have a group_id. this is not auto increment value. Example.

The group max people allow are 15 people. So after max 15 people in this group, the 16 person and mroe will jump tp next group. So how should i do it in the query side??

richei · August 18, 2013, 8:07pm

Before doing the insert, run a query counting the number of people in each group, get the group id of the first group that doesn’t have 15 people in it.

elvatra · August 20, 2013, 1:28am

hi this is my code regarding the listing

[php]include ‘config.php’;
include ‘opendb.php’;

$Groups = $DB->DBCol(“t121_post_group”, “id”);
$Contition_Group = implode("’,’", $Groups);
$DB->DBSelect(“t111_post”, array(‘id’,‘display_name’,‘caption’,‘position_no’,‘original_photo_url’,‘thumbnail_photo_url’),null,"post_group_id in (’{$Contition_Group}’)", array(“ID”=>“desc”) );

while ($row = $DB->Fetch_Assoc())
$r[] = array_map(‘utf8_encode’, $row);

print (json_encode($r));[/php]

so from this im getting

        row 1->user 1 -> image, description, caption,remarks,group_1
        row 1->user 2->image,description, caption, remarks,group_1
        row 1->user 3 -> image, description, caption,remarks,group_1
        row 1->user 4->image,description, caption, remarks,group_1
        row 1->user 5 -> image, description, caption,remarks,group_1
        row 1->user 6->image,description, caption, remarks,group_1
        row 1->user 7 -> image, description, caption,remarks,group_1
        row 1->user 8->image,description, caption, remarks,group_1

        row 1->user 9 -> image, description, caption,remarks,group_2
        row 1->user 10->image,description, caption, remarks,group_2


        row 1->user 11 -> image, description, caption,remarks, group_3
        row 1->user 12->image,description, caption, remarks, group_3

but i want it to display like this at below

Group 1
|
row 1->user 1 -> image, description, caption,remarks
row 1->user 2->image,description, caption, remarks.
row 1->user 3 -> image, description, caption,remarks
row 1->user 4->image,description, caption, remarks.
row 1->user 5 -> image, description, caption,remarks
row 1->user 6->image,description, caption, remarks.
row 1->user 7 -> image, description, caption,remarks
row 1->user 8->image,description, caption, remarks.

Group 2
|
row 1->user 9 -> image, description, caption,remarks
row 1->user 10->image,description, caption, remarks.

Group 3
|
row 1->user 11 -> image, description, caption,remarks
row 1->user 12->image,description, caption, remarks.

richei · August 20, 2013, 4:04am

Not entirely sure, but you could try

$DB->DBSelect(“t111_post”, array(‘id’,‘display_name’,‘caption’,‘position_no’,‘original_photo_url’,‘thumbnail_photo_url’),null,"post_group_id in (’{$Contition_Group}’)", array(“GROUP” => “desc”, “ID”=>“desc”) );

replace GROUP with whatever your column name is for the group number.

elvatra · August 20, 2013, 9:30am

hi

im able to get it… but my group did not split according to the group number…

[php]<?php

include ‘config.php’;
include ‘opendb.php’;

$Groups = $DB->DBCol(“t111_post”, “id”, 0, null, null, null,
‘post_group_id’);

foreach ($Groups as $GID ) {

$DB->DBSelect("t111_post", array(
    "pid" => 'id',
    "image" => 'original_photo_url',
    "thumbnail" => 'thumbnail_photo_url',        
    "name" => 'display_name',
    "group" => 'post_group_id',
    "caption" => 'caption',
    "position" => 'position_no'), null, "`post_group_id`='{$GID}'", array("ID"=>"ASC"),null,null,null);
$Group = array('photo' => "$GID", 'thumbnail' => "$GID");
while ($row = $DB->Fetch_Assoc())
    $posts[] = array_map('utf8_encode', $row);
$Group['posts'] = $posts;

}

$Result = array(
“Status” => “SUCCESS”,
“count” => count($Groups),
“groups” => $Group);

echo json_encode($Result);
?>[/php]

i would like to know what is the problem…