Combining the contents of two array tables

Question

Combining the contents of two array tables

I am attempting to retrieve the status of all users from my website. Here is my table https://i.stack.imgur.com/2I3xi.png

When another user adds me, their username is added to user1. If I add another user, my username gets added into user1 and theirs into user2.

Therefore, I need to gather all the IDs from user1 and user2 where accepted is 1.

This is how I do it:

$id = $_SESSION['user_id'] ;
$yes = '1';
$query = $db2->prepare("SELECT * FROM friend_requests WHERE (user1='".$id."' OR user2='".$id."') AND accepted = '".$yes."'");
$query->execute();
foreach ($query as $row) {
    print $row['user1'] . "\t";
    print $row['user2'] . "\t";

}

Now that I have $row['user2'] and $row['user1'], is there a way to combine them into one so I can simply use a pdo loop to select status where id equals that?

I want to retrieve all statuses where id = $row['user1'] or $row['user2'], but I prefer not to use OR in my pdo select statement.

php mysql pdo

Answer 1

Answer №1

You have the option to modify your SQL query in the following way:

"SELECT id, (CASE WHEN user1 = $id THEN user2 ELSE user1 END) as user, time FROM friend_requests WHERE (user1 = $id OR user2 = $id) AND accepted = 1"

By implementing this change, you will receive only the approved friends associated with the user identified by the ID $ID.

To see the demonstration of this approach, visit:

UPDATE: It is advisable to bind parameters to safeguard against SQL injection, although this topic extends beyond the current discussion.

UPDATE 2: Regarding your subsequent inquiry, there are two alternative methods available - either conduct two separate loops or utilize fetchAll() if the table size is relatively small compared to the memory capacity.

$id = $_SESSION['user_id'] ;
$query = $db2->prepare("SELECT (CASE WHEN user1 = :userid THEN user2 ELSE user1 END) as user FROM friend_requests WHERE (user1 = :userid OR user2 = :userid) AND accepted = 1");
$query->bindParam(':userid', $id, PDO::PARAM_INT);
$query->execute();
$friends = $query->fetchAll(PDO::FETCH_ASSOC);
$friends[] = array('user'=>$id);
foreach ($friends as $k=>$friend) {
    echo $friend . "\t";
}

Answer 2

You have the option to modify your SQL query in the following way:

"SELECT id, (CASE WHEN user1 = $id THEN user2 ELSE user1 END) as user, time FROM friend_requests WHERE (user1 = $id OR user2 = $id) AND accepted = 1"

By implementing this change, you will receive only the approved friends associated with the user identified by the ID $ID.

To see the demonstration of this approach, visit:

UPDATE: It is advisable to bind parameters to safeguard against SQL injection, although this topic extends beyond the current discussion.

UPDATE 2: Regarding your subsequent inquiry, there are two alternative methods available - either conduct two separate loops or utilize fetchAll() if the table size is relatively small compared to the memory capacity.

$id = $_SESSION['user_id'] ;
$query = $db2->prepare("SELECT (CASE WHEN user1 = :userid THEN user2 ELSE user1 END) as user FROM friend_requests WHERE (user1 = :userid OR user2 = :userid) AND accepted = 1");
$query->bindParam(':userid', $id, PDO::PARAM_INT);
$query->execute();
$friends = $query->fetchAll(PDO::FETCH_ASSOC);
$friends[] = array('user'=>$id);
foreach ($friends as $k=>$friend) {
    echo $friend . "\t";
}

Answer 3

Answer №2

If you're looking for a way to combine results from two tables, you can employ the UNION method in your SQL query:

<?php

$id = $_SESSION['user_id'] ;
$yes = '1';
$query = $db2->prepare("SELECT id,user2 AS `user` FROM `friend_requests` WHERE user1=:user1 AND accepted={$yes} UNION SELECT id,user1 AS `user` FROM `friend_requests` WHERE user2=:user2 AND accepted={$yes}");
$query->bindParam(':user1', $id, PDO::PARAM_INT);
$query->bindParam(':user2', $id, PDO::PARAM_INT);

$query->execute();
foreach ($query as $row) {
    print $row['user'] . "\t";
}

To see this method in action, you can check out this example on SqlFiddle.

Answer 4

If you're looking for a way to combine results from two tables, you can employ the UNION method in your SQL query:

<?php

$id = $_SESSION['user_id'] ;
$yes = '1';
$query = $db2->prepare("SELECT id,user2 AS `user` FROM `friend_requests` WHERE user1=:user1 AND accepted={$yes} UNION SELECT id,user1 AS `user` FROM `friend_requests` WHERE user2=:user2 AND accepted={$yes}");
$query->bindParam(':user1', $id, PDO::PARAM_INT);
$query->bindParam(':user2', $id, PDO::PARAM_INT);

$query->execute();
foreach ($query as $row) {
    print $row['user'] . "\t";
}

To see this method in action, you can check out this example on SqlFiddle.

Combining the contents of two array tables

Answer №1

Answer №2

Similar questions

PHP functioning on one server yet malfunctioning on a different server

How can Laravel 4's Eloquent be used to implement 'offset' instead of 'page' in the ->paginate() method?

Utilizing PHP for loading a fresh webpage within a specific div element of an existing page through AJAX

Tips for rounding small decimal numbers in PHP

"Encountered error converting array to string while attempting to swap out two elements

Error 404 occurred after transferring the website from a Linux/Apache server to a Windows 2008

Passing array data from JavaScript to PHP using POST method in Joomla 3.x

Resolving the Node.js and MySQL duplication problem

php code for locating a substring within a larger string

Upon submission of the form, trigger an email to be sent and simultaneously open a

How can I generate a daily report for an Employee that includes all dates within a specified range, even for dates when no data was entered?

"Utilize the power of the ajaxform jQuery plugin to automatically reset form fields

``There seems to be an issue with the redirect header function in the PHP

Obtaining values from a jQuery slider for the price range

Creating personalized flash messages in CakePHP

Submitting a specific group of form inputs using ajax involves selecting the desired elements within the form

Integrating PHP code into a React.js application can provide

Updating a dropdown select in jQuery using AJAX and returning an empty string when it changes

A guide on combining two parameters using jQuery in a link for CodeIgniter

Refreshing the page triggers the callback function that retrieves the checkboxes selected in a Kendo treeview component