4images Forum & Community
General / Allgemeines => Programming => Topic started by: MrAndrew on September 15, 2010, 06:33:11 PM
-
Good day
I have this code
$sql = "SELECT SUM(image_hits) AS sum
FROM ".IMAGES_TABLE;
$row = $site_db->query_firstrow($sql);
$sum = (isset($row['sum'])) ? $row['sum'] : 0;
$total_hits = "".$lang['total_hits']."<B> ".$row['sum']."</B>\n";
$site_template->register_vars("total_hits", $total_hits);
unset($total_hits);
But this code extract hits from all database... I need to extract total hits of one user.
-
$user_id = 1; //replace with your user id
$sql = "SELECT SUM(image_hits) AS sum
FROM ".IMAGES_TABLE."
WHERE user_id = $user_id";
-
This code give me the next error:
DB Error: Bad SQL Query: SELECT user_t_images, user_name, user_id, SUM(image_hits) AS sum FROM 5images_users, 5images_images WHERE user_level >= '2' ORDER BY user_t_images DESC LIMIT 10
Column 'user_id' in field list is ambiguous
$sql = "SELECT user_t_images, user_name, user_id, SUM(image_hits) AS sum FROM ".USERS_TABLE.", ".IMAGES_TABLE."
WHERE user_level >= '".USER."'
ORDER BY user_t_images DESC
LIMIT 10";
Help me please
-
It's because you need an unique alias (see extra "i" and "u" below) for each table and it's fields when used two or more tables in the same query.
$sql = "SELECT i.user_t_images, u.user_name, u.user_id, SUM(i.image_hits) AS sum
FROM ".USERS_TABLE." u, ".IMAGES_TABLE." i
WHERE u.user_level >= '".USER."' AND u.user_id = i.user_id
ORDER BY i.user_t_images DESC
LIMIT 10";
-
Right! Thanks V@no, will do that now
Ohhh, i have this error :cry:
DB Error: Bad SQL Query: SELECT i.user_t_images, u.user_name, u.user_id, SUM(i.image_hits) AS sum FROM 5images_users u, 5images_images i WHERE u.user_level >= '2' AND u.user_id = i.user_id ORDER BY i.user_t_images DESC LIMIT 10
Unknown column 'i.user_t_images' in 'field list'
-
user_t_images is not part of 4images, I don't know where it came from in your code...
-
I`m very tired. I`m trying and trying, trying and trying. But nothing to give me a sum :cry: For one user this is not a problem. But to show list with 10 users impossible! Please, please help me!!! :cry: :cry: :cry: :cry:
Code below nothing to show... Not errors, not results...
$tot_hits = "";
while ($row = $site_db->fetch_array($result)) {
$user_id = $row['user_id'];
$sql = "SELECT SUM(image_hits) AS sum, user_id
FROM ".IMAGES_TABLE.", ".USERS_TABLE."
user_id = $user_id
ORDER BY user_id DESC
LIMIT 10";
$result = $site_db->query($sql);
$sum = (isset($row['sum'])) ? $row['sum'] : 0;
$tot_hits = "".$lang['total_hits']."<B> ".$row['sum']."</B>\n";
}
$site_template->register_vars("tot_hits", $tot_hits);
unset($tot_hits);
-
you put sql query inside "while", it should be outside.
-
$tot_hits = "";
$sql = "SELECT SUM(image_hits) AS sum, user_id
FROM ".IMAGES_TABLE.", ".USERS_TABLE."
user_id = $user_id
ORDER BY user_id DESC
LIMIT 10";
$result = $site_db->query($sql);
while ($row = $site_db->fetch_array($result)) {
$user_id = $row['user_id'];
$sum = (isset($row['sum'])) ? $row['sum'] : 0;
$tot_hits = "".$lang['total_hits']."<B> ".$row['sum']."</B>\n";
}
$site_template->register_vars("tot_hits", $tot_hits);
unset($tot_hits);
This give me next and nothing result:
Notice: Undefined variable: user_id in /home/users2/d/drandrew/domains/skyphotos.ru/includes/page_header.php on line 863
DB Error: Bad SQL Query: SELECT SUM(image_hits) AS sum, user_id FROM 5images_images, 5images_users user_id = ORDER BY user_id DESC LIMIT 10
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= ORDER BY user_id DESC LIMIT 10' at line 3
-
$sql = "SELECT SUM(image_hits) AS sum, user_id
FROM ".IMAGES_TABLE."
WHERE user_id = $user_id
ORDER BY user_id DESC
LIMIT 10";
-
$tot_hits = "";
$sql = "SELECT SUM(image_hits) AS sum, user_id
FROM ".IMAGES_TABLE."
WHERE user_id = $user_id
ORDER BY user_id DESC
LIMIT 10";
$result = $site_db->query($sql);
while ($row = $site_db->fetch_array($result)) {
$user_id = $row['user_id'];
$sum = (isset($row['sum'])) ? $row['sum'] : 0;
$tot_hits = "".$lang['total_hits']."<B> ".$row['sum']."</B>\n";
}
$site_template->register_vars("tot_hits", $tot_hits);
unset($tot_hits);
Notice: Undefined variable: user_id in /home/users2/d/drandrew/domains/skyphotos.ru/includes/page_header.php on line 863
DB Error: Bad SQL Query: SELECT SUM(image_hits) AS sum, user_id FROM 5images_images WHERE user_id = ORDER BY user_id DESC LIMIT 10
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY user_id DESC LIMIT 10' at line 4
Error again :cry: :cry:
-
You need specify in $user_id variable what user you want to get data for. So on top of your code you must have something like:
$user_id = 123;
-
Ok. I fully completed my code. That`s will be easy then i thought!
$sql = "SELECT SUM(image_hits) AS sum
FROM ".IMAGES_TABLE."
WHERE user_id = $user_id";
$result = $site_db->query($sql);
while ($row = $site_db->fetch_array($result)) {
$sum = (isset($row['sum'])) ? $row['sum'] : 0;
$tot_hits = "".$lang['total_hits']." <B>".$row['sum']."</B>\n";
}
$site_template->register_vars("tot_hits", $tot_hits);
unset($tot_hits);
This had not be in page_header!
-
are you trying show to a logged in user sum of image hits for his images?
In that case you might want consider use $user_info['user_id'] instead of $user_id...
-
V@no, I wanted to show sum of total images in the profile of user profile! I can do this MOD now! Do it?
Thanks!
-
Oh, now I understand :)
In that case scratch my previous post ;) $user_id - is what you need.
You can simplify your code with with:
$sql = "SELECT SUM(image_hits) AS sum
FROM ".IMAGES_TABLE."
WHERE user_id = $user_id";
$result = $site_db->query_firstrow($sql);
$tot_hits = $lang['total_hits']." <B>".($result['sum'] ? $result['sum'] : 0)."</B>\n";
$site_template->register_vars("tot_hits", $tot_hits);
unset($tot_hits);
-
I have this code. Work fine! But...
$most_viewed_name .= "";
$most_viewed_name .= time() - 60 * 60 * 24 * 7;
$sql = "SELECT i.image_name as name, SUM(i.image_hits) AS hit_sum
FROM ".IMAGES_TABLE." i
WHERE i.image_date >= ".$most_viewed_name."
GROUP BY name ORDER BY hit_sum DESC
LIMIT 5";
$result = $site_db->query($sql);
$sum = (isset($row['hit_sum'])) ? $row['hit_sum'] : 0;
while ($row = $site_db->fetch_array($result)) {
$most_viewed_name .= "<tr>";
$most_viewed_name .= "<td align=\"left\">";
$most_viewed_name .= '<a href ="'.ROOT_PATH.'/search.htm?search_fields=image_name&search_keywords='.$row['name'].'" title="'.$row['hit_sum'].' views today"><b>'.$row['name'].'</b></a>';
$most_viewed_name .= "</td>";
$most_viewed_name .= "</tr>";
}
$site_template->register_vars(array(
"most_viewed_name" => $most_viewed_name,
));
unset($most_viewed_name);
(http://drandrew.jino.ru/5.jpg)
What is the number and how to delete it??? (Yellow mark)
-
$most_viewed_name .= "";
$time= time() - 60 * 60 * 24 * 7;
$sql = "SELECT i.image_name as name, SUM(i.image_hits) AS hit_sum
FROM ".IMAGES_TABLE." i
WHERE i.image_date >= ".$time."
GROUP BY name ORDER BY hit_sum DESC
LIMIT 5";
.
.
.
mfg Andi
-
Fine! Thanks! :D