add value to result from mysql query that will be JSON encoded in PHP?

Question

I would like to add a value to each row that I get from my query depending on if a row exist in another table. Is there a smart way to achieve this?

This is the code I have:

$sth = mysql_query("SELECT tbl_subApp2Tag.*, tbl_tag.* FROM tbl_subApp2Tag LEFT JOIN tbl_tag ON tbl_subApp2Tag.tag_id = tbl_tag.id WHERE tbl_subApp2Tag.subApp_id = '".$sub."' ORDER BY tbl_tag.name ASC");
if(!$sth) echo "Error in query: ".mysql_error();
while($r = mysql_fetch_assoc($sth)) {
    $query = "SELECT * FROM tbl_userDevice2Tag WHERE tag_id='".$r['id']."' AND userDevice_id='".$user."'";
    $result = mysql_query($query) or die(mysql_error());
    if (mysql_num_rows($result)) {
        $r['relation'] = true;
        $rows[] = $r; //Add 'relation' => true to this row
    } else {
        $r['relation'] = false;
        $rows[] = $r; //Add 'relation' => false to this row
    }
}
print json_encode($rows);

Where the //Add ... is, is where I would like to insert the extra value. Any suggestions of how I can do this? I'm still a beginner in PHP so if there are anything else that I have missed please tell me.

EDIT: Second query was from the wrong table. This is the correct one.

this can be done by sql much better

k102
– k102

2012-02-24 11:18:32 +00:00
Commented Feb 24, 2012 at 11:18 — k102
– k102, Commented Feb 24, 2012 at 11:18

Simon at The Access Group · Accepted Answer · 2012-02-24 11:39:06Z

Edited Edited below query to reflect new information because I don't like leaving things half-done.

$sth = mysql_query("
    SELECT
        tbl_subApp2Tag.*,
        tbl_tag.*,
        ISNULL(tbl_userDevice2Tag.userDevice_id) AS relation

    FROM tbl_subApp2Tag
    LEFT JOIN tbl_tag
        ON tbl_tag.id = tbl_subApp2Tag.tag_id

    LEFT JOIN tbl_userDevice2Tag
        ON tbl_userDevice2Tag.tag_id = tbl_tag.id
        AND tbl_userDevice2Tag.userDevice_id = '".$user."'

    WHERE tbl_subApp2Tag.subApp_id = '".$sub."'
    ORDER BY tbl_tag.name ASC
");

Though the above feels like the LEFT JOIN on tbl_tag is the wrong way around, but it's hard to tell as you are vague on your eventual aim. For example, if I was to assume the following

Tags will always exist
subApp2Tag will always exist
You want to know if a record in tbl_userDevice2Tag matches the above

Then I would do the following instead. The INNER JOIN means that it won't worry about records in tbl_tag that are not on the requested subApp_id which in turn will limit the other joins.

$sth = mysql_query("
    SELECT
        tbl_subApp2Tag.*,
        tbl_tag.*,
        ISNULL(tbl_userDevice2Tag.userDevice_id) AS relation

    FROM tbl_tag

    INNER JOIN tbl_subApp2Tag
        ON tbl_subApp2Tag.tag_id = tbl_tag.id
        AND tbl_subApp2Tag.subApp_id = '".$sub."'

    LEFT JOIN tbl_userDevice2Tag
        ON tbl_userDevice2Tag.tag_id = tbl_tag.id
        AND tbl_userDevice2Tag.userDevice_id = '".$user."'

    ORDER BY tbl_tag.name ASC
");

Your Common Sense · Accepted Answer · 2012-02-24 11:18:51Z

1

you have to do all the job in a single query.
Why can't you just $r['append'] = "value"; before adding $r to the array?

answered Feb 24, 2012 at 11:18

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Collectives™ on Stack Overflow

add value to result from mysql query that will be JSON encoded in PHP?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related