PHP - Combined Counts

Hi! I'm trying to get working a photo list (library) that I made for my website. I need to query db for somethings and I have to join them all but I'm not very familiarize with this tecquine, neither COUNT/SUM.   First I need to get (all) the images from user_uploads table (imgID, user_id, filename, description, up_time, etc), second get the user info from members table (id, name and usr_img) where id = user_uploads.user_id, third and last make 2 counts to know if the logged in user already liked the img ($_SERVER['user_id']) and get the total likes the current "user_uploads.img_id" have.   I'm already tried many queries, joining, subquery and finally I'm going to try again joining (or inner join). This is what I have:

SELECT user_uploads.* AS uu, members.*, COUNT(img_id, user_id) AS usr_liked, COUNT(img_id) AS total_likes
FROM user_uploads
INNER JOIN members AS m ON m.id = uu.user_id -- get img owner info
INNER JOIN img_likes AS il ON il.img_id = uu.imgID AND il.user_id = ? -- not merge members.id (img owner) with members.id (connected userid) / check if already liked
INNER JOIN img_likes AS ilt ON ilt.img_id = uu.imgID -- get img total likes
-- maybe group by, but i've not idea :(
ORDER BY up_time DESC -- for the user_uploads.* ???

And this is the php code:

if (login_check($mysqli) == true) {
    $user_id = $_SESSION['user_id'];
}
else {
    $user_id = ip2long(get_ip_address());
}

if ($stmt = $mysqli->prepare(" SELECT user_uploads.* AS uu, members.*, COUNT(img_id, user_id) AS usr_liked, COUNT(img_id) AS total_likes
                               FROM user_uploads
                               INNER JOIN members AS m ON m.id = uu.user_id
                               INNER JOIN img_likes AS il ON il.img_id = uu.imgID AND il.user_id = ?
                               INNER JOIN img_likes AS ilt ON ilt.img_id = uu.imgID
                               GROUP BY img_id, user_id
                               ORDER BY up_time DESC")) {
    $stmt->bind_param('i', $user_id);
    $stmt->execute(); // get photos

    //$Items->store_result();
    //$Items->bind_result();

    $Items = $stmt->get_result();
    
    foreach ($Items as $ItemInfo) {
    
        $liked = $ItemInfo['usr_liked'];      // will this work???
        $total_likes = $ItemInfo['total_likes'];
        
        $imgID = $aItemInfo['imgID']; // user_uploads
        
        if ($liked == 0) {
            $like = 'Like';
        }
        else if ($liked == 1) {
            $like = 'Unlike';
        }
        
        $photo_list .= '
        // img
        // button
        <span class="total_likes" id="">' . $totallikes . '</span>
        <a id="' . $total_likes . '" class="likes">' . $like . '</a>
        ';
    }
}

I'm not sure how to do this and I'd appreciate any help.   Thanks in advance!

I'm having some problems with a piece of software I use on a clients website. There is a "related items" function module available, with this module you can filter to show items from the same category OR in the same city. But now my client want's the module to show only items that are located in the same category AND the same city.   My PHP knowledge is just not good enough to solve this myself, that's why I ask for advice here. I already defined the lines of code that manage the filter, but I don't know how to make this from an "OR" filter to an "AND" filter. Can someone help me with this?

// Join over prop mid table if getting related by category
            if($search_cat)
            {
                $cats = ipropertyHTML::getAvailableCats($property->id);
                
                if($cats)
                {
                    $searchwhere[] = 'pm.cat_id IN ('.implode(',', $cats).')';
                }
            }        
            
            // Filter by city.
            if($property->city && $search_city)         $searchwhere[] = 'p.city = '.$db->Quote($property->city);            
            
            if( count($searchwhere)) $query->where('('.implode(' OR ', $searchwhere).')');

I have attached the complete file in case that gives a better insight in the filter function.   Best regards, Niels Attached Files  helper.php   4.79KB   1 downloads

I have the following PHP code...
$query1 = $this->db->query("SELECT `categories`.`cat_id`, `categories`.`cat_name`, `forums`.`forum_name`
                                        FROM `".$this->dbname."`.`categories`
                                        LEFT JOIN `".$this->dbname."`.`forums`
                                        USING (`cat_id`)
                                        ORDER BY `categories`.`cat_order` ASC");

              $i = 0;
                while($row = $this->db->fetch_assoc($query1))
                {
                    $content .= ($i % 2 == 0 ? '<tr class="odd">' : '<tr>');
                    $content .= '<td><p><a href="index.php?cg=cgs&page=forum&do=forums:'.$row['cat_id'].'">'.$row['cat_name'].'</a></p>';
                    $content .= ' - '.$row['forum_name'].'</td>';
                    $content .= '</tr>';
                    $i++;
                }

I'm getting duplicate rows of the first table (categories) being outputted. So I'm currently getting...
Quote

Crikey Games
- Suggestions and Ideas
Crikey Games
- General Discussion
Crikey Games
- Announcements
Realm Battles
- Bugs, Glitches and Exploits
Realm Battles
- Recruiting
Realm Battles
- General Discussion
Realm Battles
- Suggestions and Ideas
Realm Battles
- Battle Grounds
Realm Battles
- Announcements


When I want to be getting...
Quote

Crikey Games
- Suggestions and Ideas
- General Discussion
- Announcements

Realm Battles
- Bugs, Glitches and Exploits
- Recruiting
- General Discussion
- Suggestions and Ideas
- Battle Grounds
- Announcements


Code: [Select]
--
-- Table structure for table `categories`
--

CREATE TABLE IF NOT EXISTS `categories` (
  `cat_id` int(10) NOT NULL AUTO_INCREMENT,
  `cat_name` varchar(50) NOT NULL,
  `cat_order` int(5) NOT NULL DEFAULT '0',
  PRIMARY KEY (`cat_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`cat_id`, `cat_name`, `cat_order`) VALUES
(1, 'Crikey Games', 1),
(2, 'Realm Battles', 2);

-- --------------------------------------------------------

--
-- Table structure for table `forums`
--

CREATE TABLE IF NOT EXISTS `forums` (
  `forum_id` int(10) NOT NULL AUTO_INCREMENT,
  `cat_id` int(10) NOT NULL,
  `forum_name` varchar(50) NOT NULL,
  `forum_description` varchar(225) NOT NULL,
  `forum_order` int(5) NOT NULL DEFAULT '0',
  `forum_locked` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`forum_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

--
-- Dumping data for table `forums`
--

INSERT INTO `forums` (`forum_id`, `cat_id`, `forum_name`, `forum_description`, `forum_order`, `forum_locked`) VALUES
(1, 1, 'General Discussion', 'talk about anything you like', 2, 0),
(2, 1, 'Announcements', '', 1, 0),
(3, 1, 'Suggestions and Ideas', 'if you have a suggestion or idea, share it here', 3, 0),
(4, 2, 'Announcements', '', 4, 0),
(5, 2, 'General Discussion', 'talk about anything Realm Battles related here', 5, 0),
(6, 2, 'Battle Grounds', '', 6, 0),
(7, 2, 'Recruiting', '', 7, 0),
(8, 2, 'Suggestions and Ideas', 'if you have a suggestion or idea for Realm Battles, share it here', 8, 0),
(9, 2, 'Bugs, Glitches and Exploits', 'if you have discovered something that needs to be fixed, share it here', 9, 0);

Not sure whether I'm doing something wrong with the PHP side of things, or the MySQL query.

Also I can't figure out how to ORDER BY `forums`.`forum_order` ASC , when trying to add it to this query.

Thanks in advance,
Ace

I'm trying to have a running total of the number of views an image gets. My test file works perfectly every time. The actual file, image_win.php, does not and after much testing can't see what's wrong. This files STRANGENESS: the first two views count fine, with 1 added the total in the field image_visit. But every time after that 2 is added to the total in image_visit. Very bizarre.

Thanks for taking a look -Allen
Code: [Select]
<?php
////////TEST IMAGE_WIN //////////THIS WORKS
include_once "scripts/connect_to_mysql.php";
include_once "scripts/paths.php";
$art_id = 372;
$QUERY="SELECT user_id FROM artWork WHERE art_id = '$art_id'";
$res = mysql_query($QUERY);
$num = mysql_num_rows($res);
if($num >0){

while($row = mysql_fetch_array($res)){ 
     $owner_id = $row['user_id'];
    }
}
mysql_query("UPDATE userInfo SET image_visit = image_visit +1  WHERE user_id = '$owner_id'"); 
?>

<?php 
//////// image_win.php  /////////THIS DOES NOT WORK
$user_id=$_SESSION['user_id'];
include_once "scripts/connect_to_mysql.php";
include_once "scripts/paths.php";
$image_link = $_GET['image_link'];
$art_id = $_GET['art_id'];
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="viewport" content="width=device-width, height=device-height, target-densityDpi=device-dpi">
<title>Image Window</title>
</head>
<body bgcolor="#000000" leftmargin="0">
<div  align="center" >
<img src="slir/w640-h535/<?php echo $path.$image_link.$postCat; ?>" />
<?php
$QUERY="SELECT user_id FROM artWork WHERE art_id = '$art_id'";
$res = mysql_query($QUERY);
$num = mysql_num_rows($res);
if($num >0){
while($row = mysql_fetch_array($res)){ 
     $owner_id = $row['user_id'];
    }
}
mysql_query("UPDATE userInfo SET image_visit = (image_visit +1)  WHERE user_id = '$owner_id' ");

?>

MOD EDIT: code tags added.