How to query an array in PHP and return individual rows/items for each element

Question

Im coding a social web application and Im having some problems. I will ask just one of them as Im unaware of the rules of this forum, i.e, whether I can post more than one problem. I am learning PHP pretty much as I code. I have a table users that has a field called pal_array that holds the user_id's of people who are your pals. Im my example, my pal array has 2 user_id's.

I want to be able to query all of a user's pals and be able to show their details e.g thumb pics, names etc. I dont know how to to this properly.

My code from pals.php:

<?php
//Establish the Web Intersect Profile Interaction Token here
if(!isset($_SESSION['wipit']))//Check to see if session wipit is set yet
{
    session_register('wipit');//Be sure to register the session if it is not yet set
}
$thisRandNum = rand(9999999999999,999999999999999999);
$_SESSION['wipit'] = base64_encode($thisRandNum);
?>
<script type="text/javascript" charset="utf-8">
//jquery functionality for toggling member interaction containers
function toggleInteractContainers(x)
{
    if($('#'+x).is(":hidden"))
    {
        $('#'+x).slideDown(200);
    }else
    {
        $('#'+x).hide();
    }
    $('.interactContainers').hide();
}
// Pal accepting 
var palRequestURL = "request_as_pal.php";
var thisRandNum = "<?php echo $thisRandNum; ?>";
function acceptPalRequest (x) {
    $.post(palRequestURL,{ request: "acceptPal", reqID: x, thisWipit: thisRandNum } ,function(data) {
            $("#req"+x).html(data).show();
    });
}
function denyPalRequest (x) {
    $.post(palRequestURL,{ request: "denyPal", reqID: x, thisWipit: thisRandNum } ,function(data) {
           $("#req"+x).html(data).show();
    });
}
// End Pal accepting 
// Pal removal
function removeAsPal(a,b) {
    $("#remove_pal_loader").show();
    $.post(palRequestURL,{ request: "removePal", mem1: a, mem2: b, thisWipit: thisRandNum } ,function(data) {
        $("#remove_friend").html(data).show().fadeOut(12000);
    }); 
}
// End Pal removal 
</script>
<?php require_once('Connections/connections.php'); ?>
<?php
//query username
$user_id = $_SESSION['UserSession'];
mysql_select_db($database_connections, $connections);
$query_user_info = "SELECT username FROM users WHERE user_id='$user_id'";
$user_info = mysql_query($query_user_info, $connections) or die(mysql_error());
$row_user_info = mysql_fetch_assoc($user_info);

//code for displaying all your pals
$query_pal_array = "SELECT pal_array FROM users WHERE user_id='$user_id'";
$pal_array_result = mysql_query($query_pal_array, $connections) or die(mysql_error());
$row_pal_array = mysql_fetch_assoc($pal_array_result);

$pal_array = $row_pal_array['pal_array']; 

$palList = "";
if($pal_array !="")
{
    $palArray = explode(",",$pal_array);
    $palCount = count($palArray);
    $palArray = array_slice($palArray,0,15);
    $i = 0;//how many times we loop over
    foreach($palArray as $key =>$array_value)
    {
        $i++;
    //increment by one
        $palList = "$array_value";      
    }
}
else
{
    $palCount = "0";
}
?>
<table width="500" border="0">
    <tr>
    <td height="20"><div class="heading_text_18"><?php echo $row_user_info ['username']; ?>'s&nbsp;pals <?php echo $palCount ?></div>  </td>
    </tr>
    <tr>
      <td class="interactionLinksDiv" align="right" style="border:none;"><a href="#" onclick="return false" 
      onmousedown="javascript: toggleInteractContainers('pal_requests');">Pal Requests</a></td>
    </tr>
    <tr>
      <td height="5"></td>
    </tr>
</table>
<div class="interactContainers" id="pal_requests">
<?php
//container for accepting/rejecting pal requests
$pal_requests = "SELECT * FROM pal_requests WHERE mem2='$user_id' ORDER BY pal_request_id ASC LIMIT 50";
$pal_request_query = mysql_query($pal_requests) or die(mysql_error());
$pal_request_num_rows = mysql_num_rows($pal_request_query);
if($pal_request_num_rows < 1)
{
    echo '&nbsp;You have no Pal requests at this time.';
    exit();
}
else
{
    while($row_pal_query = mysql_fetch_array($pal_request_query))
    {
    $request_id = $row_pal_query["pal_request_id"];
    $mem1 = $row_pal_query["mem1"];
    $query_user = "SELECT user_first_name, user_last_name, picture_thumb_url FROM users LEFT JOIN picture ON users.user_id = picture.user_id
    AND picture.avatar=1 WHERE users.user_id='$mem1' LIMIT 1";
    $user_info = mysql_query($query_user, $connections) or die(mysql_error());
    while ($row = mysql_fetch_array($user_info)){ $requesterFirstName = $row["user_first_name"]; $requesterLastName = $row["user_last_name"]; }
    {
        if(!empty($row["picture_thumb_url"]))
        {
            $avatar = '<a href="user_view.php?user_id2=' . $mem1 . '"><img src="/NNL/User_Images/' . $row["picture_thumb_url"] . '" width="50" height="50" border="0"/></a>';
        }
        else
        {
            $avatar = '<a href="user_view.php?user_id2=' . $mem1 . '"><img src="/NNL/Style/Images/default_avatar.png" width="50" height="50" border="0"/></a>';
        }
    echo '<hr />
    <table width="100%" cellpadding="5">
     <tr>
       <td width="17%" align="left"><div style="overflow:hidden; height:50px;">'. $avatar .'</div></td>
       <td width="83%"><a class="ordinary_text_12_blue "href="user_view.php?user_id2=' . $mem1 . '">'. $requesterFirstName .' '.   $requesterLastName .'</a> 
       wants to be your Pal<br /><br />
       <span id="req' . $request_id . '">
       <a class="ordinary_text_12" href="#" onclick="return false" onmousedown="javascript:acceptPalRequest(' . $request_id . ');" >Accept</a>
       &nbsp; &nbsp; OR &nbsp; &nbsp;
       <a class="ordinary_text_12" href="#" onclick="return false" onmousedown="javascript:denyPalRequest(' . $request_id . ');" >Deny</a>
       </span></td>
     </tr>
    </table>';    
    }
}            
}
?> 
</div> 
<?php
//get pal avatars
$query_pal_info = "SELECT users.user_id, user_first_name, user_last_name, username, picture_thumb_url, avatar FROM users LEFT JOIN picture ON users.user_id = picture.user_id
AND picture.avatar=1 WHERE users.user_id = $array_value";
$pal_info  = mysql_query($query_pal_info , $connections) or die(mysql_error());
$totalRows_pal_info  = mysql_num_rows($pal_info );
echo $totalRows_pal_info; 

echo "\n<table>";
$i = 5;
while ($row_pal_info  = mysql_fetch_assoc($pal_info))
{
if($i==5) echo "\n\t<tr>";
$thumbnail_user = $row_pal_info['picture_thumb_url'] != '' ? $row_pal_info['picture_thumb_url'] : '../Style/Images/default_avatar.png';
echo "<td width='100' height='100' align='center' valign='middle'><a href = 'user_view.php?user_id2=$array_value'>
  <img src='/NNL/User_Images/$thumbnail_user' border='0'/></a></td>\n";
$i--;
if($i==0) {
echo "\n\t</tr>\n\t<tr>";
$i = 5;
} 
}
if($i!=5) echo "\n\t\t<td colspan=\"$i\"></td>\n\t</tr>";
echo "\n</table>";
?>

Near the bottom, I have this query

$query_pal_info = "SELECT users.user_id, user_first_name, user_last_name, username, picture_thumb_url, avatar FROM users LEFT JOIN picture ON users.user_id = picture.user_id
AND picture.avatar=1 WHERE users.user_id = $array_value";

The variable $array_value holds the array of the user_id's of pals. How will I be able to show individual pals? I also have a question as to why anything below my interactContainers div will not show.

Thanks in advance

Richard Marskell - Drackir · Accepted Answer · 2011-02-01 18:39:05Z

1

Important
Before I answer the question, I have to make sure that you understand the insecurity of what you're doing. You REALLY need to go and read about SQL injection and re-evaluate how you are designing your queries. You should be escaping ALL of your values (including ones coming out of the database).

Answer
Ok, so that said, what you should do, is have a pal table which simply links users to other users. The fields would be user_id and pal_id. Both of which are foreign keys of the user_id field in the user table.

You can then write a query like this to get the pal information:

SELECT usr.*
FROM pal
INNER JOIN users
ON pal.pal_id = users.user_id
WHERE pal.user_id = XXX (User's ID here)

The array method of storing "pals" is going to get really old, really quick once you start wanting to do more elaborate things (like finding pals of pals and such).

You can do the query as you have it now if you REALLY want by using the IN keyword (though I recommend changing your DB structure; that will also eliminate the headache of escaping this string). Assuming that your $pal_array variable has a list of user ids separated by a comma: $query_pal_info = "SELECT users.user_id, user_first_name, user_last_name, username, picture_thumb_url, avatar FROM users LEFT JOIN picture ON users.user_id = picture.user_id AND picture.avatar=1 WHERE users.user_id IN ($pal_array)";

answered Feb 1, 2011 at 18:39

Richard Marskell - Drackir

13.4k3 gold badges37 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kinyanjui Kamau Over a year ago

Thanks for the reply Drackir. As I said im a beginner and really appreciate your input. I now see why I should escape all my values after reading your link. I intend to make more elaborate queries like you said e.g pals you may know etc. Your last solution works, although I'll have to change my database and have a pal table instead of my array...its seems better :)

Richard Marskell - Drackir Over a year ago

@Kinyanjui Kamau: Glad I could help. :) I recommend looking into PDO for handling your queries. It has support for parameterized queries which basically handles the escaping for you (properly!).

Kinyanjui Kamau Over a year ago

Hello, Drackir. Changed my DB structure and did it the right way, which is as you suggested. Had a query problem I would like you to look at if you have the time. Its about querying pal information...link

user180100 · Accepted Answer · 2011-02-01 18:38:30Z

0

you need to join your array with a comma and use a IN.

answered Feb 1, 2011 at 18:38

user180100

Collectives™ on Stack Overflow

How to query an array in PHP and return individual rows/items for each element

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related