Php getting total amount of duplicate values from a SQL query
up vote
0
down vote
favorite
I have this query which returns a list of rows as well as the amount of time they are repeated, with a column TotalCount :
SELECT X,
COUNT(*) as TotalCount
FROM table
GROUP BY X HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
I execute this query in PHPMyadmin :
[X] [TotalCount]
A 5
B 6
I try to include the query in my script, but the following gives me '2' :
echo $result->num_rows;
How to get the '11' in PHP?
EDIT:
My query is :
$sql="
SELECT X,
COUNT(*) as TotalCount
FROM table
GROUP BY X HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
";
$result = $conn->query($sql);
echo $result->num_rows;
I am trying to do something like
echo $row[TotalCount];
The initial table is
[X]
A
A
A
A
A
B
B
B
B
B
B
php sql
asked Nov 12 at 1:23
Lama222
82
|
show 3 more comments
up vote
0
down vote
favorite
I have this query which returns a list of rows as well as the amount of time they are repeated, with a column TotalCount :
SELECT X,
COUNT(*) as TotalCount
FROM table
GROUP BY X HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
I execute this query in PHPMyadmin :
[X] [TotalCount]
A 5
B 6
I try to include the query in my script, but the following gives me '2' :
echo $result->num_rows;
How to get the '11' in PHP?
EDIT:
My query is :
$sql="
SELECT X,
COUNT(*) as TotalCount
FROM table
GROUP BY X HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
";
$result = $conn->query($sql);
echo $result->num_rows;
I am trying to do something like
echo $row[TotalCount];
The initial table is
[X]
A
A
A
A
A
B
B
B
B
B
B
php sql
asked Nov 12 at 1:23
Lama222
82
3
What does your table look like?
– Obsidian Age
Nov 12 at 1:26
1
if I understand you correctly when you run the query in phpmyadmin it works fine when you insert the query in php code it only returns 2?
– guradio
Nov 12 at 1:27
1
Hi, you must put the PHP code your are using actually... What is$resultand what representsnum_rowsin it.
– ankabout
Nov 12 at 1:27
guradio in phpmyadmin it works fine because this query has created a column TotalCount with the amount of repetitions. But I do not know how to get this 'displayed' via PHP
– Lama222
Nov 12 at 1:36
I believe the $result will contain the count you're looking for, numrows is a function of it which will return 2 rows, so php is not the issue. do a var_dump($result); you'll see the array
– comphonia
Nov 12 at 1:38
|
show 3 more comments
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have this query which returns a list of rows as well as the amount of time they are repeated, with a column TotalCount :
SELECT X,
COUNT(*) as TotalCount
FROM table
GROUP BY X HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
I execute this query in PHPMyadmin :
[X] [TotalCount]
A 5
B 6
I try to include the query in my script, but the following gives me '2' :
echo $result->num_rows;
How to get the '11' in PHP?
EDIT:
My query is :
$sql="
SELECT X,
COUNT(*) as TotalCount
FROM table
GROUP BY X HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
";
$result = $conn->query($sql);
echo $result->num_rows;
I am trying to do something like
echo $row[TotalCount];
The initial table is
[X]
A
A
A
A
A
B
B
B
B
B
B
php sql
asked Nov 12 at 1:23
Lama222
82
I have this query which returns a list of rows as well as the amount of time they are repeated, with a column TotalCount :
SELECT X,
COUNT(*) as TotalCount
FROM table
GROUP BY X HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
I execute this query in PHPMyadmin :
[X] [TotalCount]
A 5
B 6
I try to include the query in my script, but the following gives me '2' :
echo $result->num_rows;
How to get the '11' in PHP?
EDIT:
My query is :
$sql="
SELECT X,
COUNT(*) as TotalCount
FROM table
GROUP BY X HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
";
$result = $conn->query($sql);
echo $result->num_rows;
I am trying to do something like
echo $row[TotalCount];
The initial table is
[X]
A
A
A
A
A
B
B
B
B
B
B
php sql
php sql
asked Nov 12 at 1:23
Lama222
82
asked Nov 12 at 1:23
Lama222
82
edited Nov 12 at 1:38
asked Nov 12 at 1:23
Lama222
82
asked Nov 12 at 1:23
Lama222
82
asked Nov 12 at 1:23
Lama222
82
82
3
What does your table look like?
– Obsidian Age
Nov 12 at 1:26
1
if I understand you correctly when you run the query in phpmyadmin it works fine when you insert the query in php code it only returns 2?
– guradio
Nov 12 at 1:27
1
Hi, you must put the PHP code your are using actually... What is$resultand what representsnum_rowsin it.
– ankabout
Nov 12 at 1:27
guradio in phpmyadmin it works fine because this query has created a column TotalCount with the amount of repetitions. But I do not know how to get this 'displayed' via PHP
– Lama222
Nov 12 at 1:36
I believe the $result will contain the count you're looking for, numrows is a function of it which will return 2 rows, so php is not the issue. do a var_dump($result); you'll see the array
– comphonia
Nov 12 at 1:38
|
show 3 more comments
3
What does your table look like?
– Obsidian Age
Nov 12 at 1:26
1
if I understand you correctly when you run the query in phpmyadmin it works fine when you insert the query in php code it only returns 2?
– guradio
Nov 12 at 1:27
1
Hi, you must put the PHP code your are using actually... What is$resultand what representsnum_rowsin it.
– ankabout
Nov 12 at 1:27
guradio in phpmyadmin it works fine because this query has created a column TotalCount with the amount of repetitions. But I do not know how to get this 'displayed' via PHP
– Lama222
Nov 12 at 1:36
I believe the $result will contain the count you're looking for, numrows is a function of it which will return 2 rows, so php is not the issue. do a var_dump($result); you'll see the array
– comphonia
Nov 12 at 1:38
3
3
What does your table look like?
– Obsidian Age
Nov 12 at 1:26
What does your table look like?
– Obsidian Age
Nov 12 at 1:26
1
1
if I understand you correctly when you run the query in phpmyadmin it works fine when you insert the query in php code it only returns 2?
– guradio
Nov 12 at 1:27
if I understand you correctly when you run the query in phpmyadmin it works fine when you insert the query in php code it only returns 2?
– guradio
Nov 12 at 1:27
1
1
Hi, you must put the PHP code your are using actually... What is
$result and what represents num_rows in it.– ankabout
Nov 12 at 1:27
Hi, you must put the PHP code your are using actually... What is
$result and what represents num_rows in it.– ankabout
Nov 12 at 1:27
guradio in phpmyadmin it works fine because this query has created a column TotalCount with the amount of repetitions. But I do not know how to get this 'displayed' via PHP
– Lama222
Nov 12 at 1:36
guradio in phpmyadmin it works fine because this query has created a column TotalCount with the amount of repetitions. But I do not know how to get this 'displayed' via PHP
– Lama222
Nov 12 at 1:36
I believe the $result will contain the count you're looking for, numrows is a function of it which will return 2 rows, so php is not the issue. do a var_dump($result); you'll see the array
– comphonia
Nov 12 at 1:38
I believe the $result will contain the count you're looking for, numrows is a function of it which will return 2 rows, so php is not the issue. do a var_dump($result); you'll see the array
– comphonia
Nov 12 at 1:38
|
show 3 more comments
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
The code is behaving correctly, it returns 2 because you have 2 rows. You actually want the values of those rows so you need to fetch the result object.
$sql="
SELECT X,
COUNT(*) as TotalCount
FROM table
GROUP BY X HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
";
$result = $conn->query($sql);
while($row = $result->fetch_array())
echo $row['TotalCount'] . PHP_EOL; // or '<br />' if in a browser;
answered Nov 12 at 1:45
user3783243
2,16311021
Glad to help. You can useTotalCountin yourhavingandorder byas well in place of thecount(*)s.
– user3783243
Nov 12 at 1:49
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53254907%2fphp-getting-total-amount-of-duplicate-values-from-a-sql-query%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
The code is behaving correctly, it returns 2 because you have 2 rows. You actually want the values of those rows so you need to fetch the result object.
$sql="
SELECT X,
COUNT(*) as TotalCount
FROM table
GROUP BY X HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
";
$result = $conn->query($sql);
while($row = $result->fetch_array())
echo $row['TotalCount'] . PHP_EOL; // or '<br />' if in a browser;
answered Nov 12 at 1:45
user3783243
2,16311021
Glad to help. You can useTotalCountin yourhavingandorder byas well in place of thecount(*)s.
– user3783243
Nov 12 at 1:49
add a comment |
up vote
1
down vote
accepted
The code is behaving correctly, it returns 2 because you have 2 rows. You actually want the values of those rows so you need to fetch the result object.
$sql="
SELECT X,
COUNT(*) as TotalCount
FROM table
GROUP BY X HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
";
$result = $conn->query($sql);
while($row = $result->fetch_array())
echo $row['TotalCount'] . PHP_EOL; // or '<br />' if in a browser;
answered Nov 12 at 1:45
user3783243
2,16311021
Glad to help. You can useTotalCountin yourhavingandorder byas well in place of thecount(*)s.
– user3783243
Nov 12 at 1:49
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
The code is behaving correctly, it returns 2 because you have 2 rows. You actually want the values of those rows so you need to fetch the result object.
$sql="
SELECT X,
COUNT(*) as TotalCount
FROM table
GROUP BY X HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
";
$result = $conn->query($sql);
while($row = $result->fetch_array())
echo $row['TotalCount'] . PHP_EOL; // or '<br />' if in a browser;
answered Nov 12 at 1:45
user3783243
2,16311021
The code is behaving correctly, it returns 2 because you have 2 rows. You actually want the values of those rows so you need to fetch the result object.
$sql="
SELECT X,
COUNT(*) as TotalCount
FROM table
GROUP BY X HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
";
$result = $conn->query($sql);
while($row = $result->fetch_array())
echo $row['TotalCount'] . PHP_EOL; // or '<br />' if in a browser;
answered Nov 12 at 1:45
user3783243
2,16311021
answered Nov 12 at 1:45
user3783243
2,16311021
answered Nov 12 at 1:45
user3783243
2,16311021
answered Nov 12 at 1:45
user3783243
2,16311021
2,16311021
Glad to help. You can useTotalCountin yourhavingandorder byas well in place of thecount(*)s.
– user3783243
Nov 12 at 1:49
add a comment |
Glad to help. You can useTotalCountin yourhavingandorder byas well in place of thecount(*)s.
– user3783243
Nov 12 at 1:49
Glad to help. You can use
TotalCount in your having and order by as well in place of the count(*)s.– user3783243
Nov 12 at 1:49
Glad to help. You can use
TotalCount in your having and order by as well in place of the count(*)s.– user3783243
Nov 12 at 1:49
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53254907%2fphp-getting-total-amount-of-duplicate-values-from-a-sql-query%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
What does your table look like?
– Obsidian Age
Nov 12 at 1:26
1
if I understand you correctly when you run the query in phpmyadmin it works fine when you insert the query in php code it only returns 2?
– guradio
Nov 12 at 1:27
1
Hi, you must put the PHP code your are using actually... What is
$resultand what representsnum_rowsin it.– ankabout
Nov 12 at 1:27
guradio in phpmyadmin it works fine because this query has created a column TotalCount with the amount of repetitions. But I do not know how to get this 'displayed' via PHP
– Lama222
Nov 12 at 1:36
I believe the $result will contain the count you're looking for, numrows is a function of it which will return 2 rows, so php is not the issue. do a var_dump($result); you'll see the array
– comphonia
Nov 12 at 1:38