Geek asks and answers

Empty SUM () with dynamic value

If I replaced the first% s with only 244, I will receive the amount without any problems. But when using a dynamic value in this case, $ shot, it does not seem to get anything, and neither my request is executed, like other results are in order (e.g. story.id, stories.title).

$query = sprintf("
SELECT 
stories.id, 
stories.title,  
stories.timestamp, 
stories.text, 
users.name, 
users.avatar, 
users.id AS idus,
(SELECT sum(reviews.amount) FROM reviews WHERE reviews.storyid='%s') AS reviews 

FROM stories INNER JOIN users ON stories.uid=users.id WHERE stories.id = '%s'",
    mysql_real_escape_string($shot),
    mysql_real_escape_string($shot));

The shooting comes from here:

$shot = $_GET['shot'];
+5
source share
3 answers

I would write something like this.

SELECT 
stories.id, 
stories.title,  
stories.timestamp, 
stories.text, 
users.name, 
users.avatar, 
users.id AS idus,
SUM(reviews.amount) as reviews
FROM stories 
INNER JOIN users ON stories.uid=users.id 
INNER JOIN reviews ON stories.id = reviews.storyid
WHERE stories.id = '%s'"

This has nothing to do with your question, but a lot has to do with optimization.

+1
source

Given that an identifier is always numeric,

Do it $shot = (int)$_GET['shot'];(the% d specifier treats the variable as an integer, but you might need it somewhere else).

Then replace:

reviews.storyid='%s' with reviews.storyid=%d

and

WHERE stories.id = '%s' with WHERE stories.id = %d

mysql_real_escape_string (?).

0

echo $query;

, , , , .

.

$shot = mysql_real_escape_string($shot);
$query = "SELECT 
stories.id, 
stories.title,  
stories.timestamp, 
stories.text, 
users.name, 
users.avatar, 
users.id AS idus,
(SELECT sum(reviews.amount) FROM reviews WHERE reviews.storyid='" . $shot . "') AS reviews 

FROM stories INNER JOIN users ON stories.uid=users.id WHERE stories.id = '" . $shot . "'";
0

More articles:


All Articles