combine two phpmysql queries

Question

I want to combine these two php mysql queries and want to get one resultset array. first query gives all members and postby related posts and second query gives all admin related posts with member_id is 0 and post_by with 0 and admin_id is not 0 and super_admin_post 0.

Database:
1) wall_post_master this my first database fields.
wp_id, wp_cat_id, wl_text, wp_img, datetime, displevel, mg_id, member_id, admin_id, family_id, post_by, photo_id, post_category, generation_id, visible_to, highlight, super_admin_post

2) admin_user_master this my second database fields.
user_id, family_id, first_name, last_name, password, email_id, user_level

Here my conditions to show wallpost data:
1)if mg_id exits with multiple member_id relates to comma.
2)if visible_to exits with multiple member_id relates to comma.
3)if member_id exits with current member_id.
4)if member_id exits in post_by.
5)if member_id is 0 and post_by is 0 and super_admin_post = 0 then i want to display as admin post.

First Query:

$grpsearch = "";
foreach ($arrseltran as $grpid) {
    $grpsearch .= " OR ( FIND_IN_SET('" . $grpid . "', wp.mg_id) AND displevel = 3)";
}
if($time == ""){ $time .= " WHERE"; }else{ $time .=" AND"; }
$time .= "
    wp.family_id = " . $_SESSION['logft']['family_id'] . " AND
    wp.post_category = 0 AND  
    ( 
        wp.member_id = " . $_SESSION['logft']['member_id'] . " OR 
        wp.post_by = " . $_SESSION['logft']['member_id'] . " OR 
        FIND_IN_SET('" . $_SESSION['logft']['member_id'] . "', wp.visible_to) " . $grpsearch . " OR 
        displevel = 1 
    )";

$timeline = "SELECT wp.*, 
wp.member_id, 
wp.datetime AS TimeSpent,
wp_cat.font_color,
photo.photo_path, 
mm.first_name,
mm.middle_name,
mm.last_name,
mm1.first_name AS to_first_name,
mm1.middle_name AS to_middle_name, 
mm1.last_name AS to_last_name
FROM wall_post_master wp 
INNER JOIN 
    wallpost_cat_master wp_cat ON wp_cat.wp_cat_id = wp.wp_cat_id
INNER JOIN 
    member_master mm ON mm.member_id = (CASE WHEN (wp.post_by = 0) THEN wp.member_id ELSE wp.post_by END)
INNER JOIN 
    member_master mm1 ON mm1.member_id = wp.member_id
LEFT JOIN 
    photo_master photo ON photo.photo_id = mm.photo_id
" . $time ." ORDER BY Timespent DESC";

Second Query:

$timeline1 = "SELECT wpa.*, 
wpa.family_id, 
wpa.datetime AS TimeSpent,
wp_cat.font_color,
wpa.wp_img,
c.family_id,
c.family_name,
c.editor_photo,
aum.first_name,
aum.last_name
FROM wall_post_master wpa 
INNER JOIN 
    wallpost_cat_master wp_cat ON wp_cat.wp_cat_id = wpa.wp_cat_id
INNER JOIN 
    config c ON c.family_id = wpa.family_id
INNER JOIN
    admin_user_master aum ON aum.family_id = wpa.family_id
where 
    aum.user_level = 1 and 
    wpa.admin_id <> 0 and 
    wpa.family_id='".$_SESSION['logft']['family_id']."' and 
    wpa.member_id=0 and 
    wpa.post_by=0  and 
    wpa.super_admin_post=0 
ORDER 
  BY Timespent DESC";

i tryed union but not working at all because first query depend on member_id or post_by viseversa (CASE WHEN (wp.post_by = 0) THEN wp.member_id ELSE wp.post_by END), second query if member_id and post_by both are 0 then its family admin post display to all members so i want to join both the query in one resultset with decending timestamp. — Rocky, Aug 23 '14 at 10:47
Could you show some sample data, and what you're trying to get as the result of the combined query? — Barmar, Aug 23 '14 at 10:48

score 0 · Answer 1 · answered Aug 23 '14 at 10:03

0

Use concept of Join itself

select a.col1,a.col2,b.col1,b.col3 from first_table as a, second_table as b where a.col1=condition and b.col1=condition

answered Aug 23 '14 at 10:03

arunrc

628
2
14
26

score 0 · Answer 2 · answered Aug 23 '14 at 10:03

0

you can use mysqli function:

mysqli_multi_query()

or

mysqli::multi_query()

http://php.net/manual/en/mysqli.multi-query.php

full example from link:

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query  = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";

/* execute multi query */
if ($mysqli->multi_query($query)) {
    do {
        /* store first result set */
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                printf("%s\n", $row[0]);
            }
            $result->free();
        }
        /* print divider */
        if ($mysqli->more_results()) {
            printf("-----------------\n");
        }
    } while ($mysqli->next_result());
}

/* close connection */
$mysqli->close();
?>
Procedural style

<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query  = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";

/* execute multi query */
if (mysqli_multi_query($link, $query)) {
    do {
        /* store first result set */
        if ($result = mysqli_store_result($link)) {
            while ($row = mysqli_fetch_row($result)) {
                printf("%s\n", $row[0]);
            }
            mysqli_free_result($result);
        }
        /* print divider */
        if (mysqli_more_results($link)) {
            printf("-----------------\n");
        }
    } while (mysqli_next_result($link));
}

/* close connection */
mysqli_close($link);
?>

answered Aug 23 '14 at 10:03

mwafi

3,946
8
56
83

What's the benefit of multi query over just doing two queries? – Barmar Aug 23 '14 at 10:08
you save connection and authentication time, (and this is long time compared with query process time) – mwafi Aug 23 '14 at 10:09
Authentication only happens when you call `new mysqli`, so that's irrelevant. – Barmar Aug 23 '14 at 10:10
Anyway, what I think he's actually looking for is how to combine them into one query using `UNION`. – Barmar Aug 23 '14 at 10:11
@Barmar no, you authenticate each time you send query to DB – mwafi Aug 23 '14 at 10:13
DB authenticate you each time you send new query – mwafi Aug 23 '14 at 10:14
What about SQL Injection? I believe it is easier to inject something if they use `mysqli::multi_query()` – Bluedayz Aug 23 '14 at 10:31
@Bluedayz no, if you validate it before send query – mwafi Aug 23 '14 at 10:32
You can still attack a database even though only prepared statements are used. It should be even easier if you use `mysqli::multi_query()` http://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection – Bluedayz Aug 23 '14 at 10:35

score 0 · Answer 3 · answered Aug 23 '14 at 10:22

0

if you match up the names of the fields selected in both queries you caan also use UNION

http://dev.mysql.com/doc/refman/5.0/en/union.html

http://www.mysqltutorial.org/sql-union-mysql.aspx

answered Aug 23 '14 at 10:22

KevInSol

2,560
4
32
46

combine two phpmysql queries

3 Answers3