Select counter (*) from database
I have this SQL query that works as it should when I run it in phpMyAdmin.
SELECT COUNT( * ) , LENGTH( Number ) AS Numbers
FROM `history_2015-07-22`
WHERE Number NOT LIKE '123%'
OR LENGTH( Number ) <50
GROUP BY Numbers
ORDER BY TIME = '2015-07-22 00:00:01' ASC
Now I want to make a simple php page where I want to display the query results in the browser, but I can't figure out how to replicate this exactly. So I did this:
$result = $pdo->prepare("SELECT COUNT( * ) , LENGTH( Number ) AS Numbers
FROM `history_2015-07-22`
WHERE Number NOT LIKE '123%'
OR LENGTH( Number ) <50
GROUP BY Numbers
ORDER BY TIME = '2015-07-22 00:00:01' ASC ");
$result->execute();
foreach ($result as $Numbers)
{
echo '<div class="container">
'.$Numbers['COUNT(*)'].'
'.$Numbers['LENGTH(Number)'].'
</div>';
}
What I want to echo is Count
and Length
. I'm sure this is very simple, which I missed but cannot figure out.
+3
source to share
3 answers
First, can you explain what you are trying to do with the SQL query exactly?
From what I understand, you can try this:
$result = $pdo->prepare("SELECT COUNT( * ) AS ct_all, LENGTH( `Number` ) AS Numbers
FROM `history_2015-07-22`
WHERE `Number` NOT LIKE ('123%')
AND Numbers < 50
GROUP BY Numbers
ORDER BY `TIME` ASC");
$result->execute();
$results = $result->fetchAll();
foreach ($results as $row) {
echo '<div class="container">';
echo $row['ct_all'] . ' // ';
echo $row['Numbers'];
echo '</div>';
}
+6
source to share
$result = $pdo->prepare("SELECT COUNT( * ) as cnts, LENGTH( Number ) AS num
FROM `history_2015-07-22`
WHERE Number NOT LIKE '123%'
OR LENGTH( Number ) <50
GROUP BY num
ORDER BY TIME = '2015-07-22 00:00:01' ASC ");
$result->execute();
foreach ($result as $Numbers)
{
echo '<div class="container">
'.$Numbers['cnts'].'
'.$Numbers['num'].'
</div>';
}
+4
source to share
Take a look. I have pointed out some problems.
$result = $pdo->prepare("SELECT COUNT( * ) AS ct_all, LENGTH( `Number` ) AS Numbers
FROM `history_2015-07-22`
WHERE `Number` NOT LIKE ('123%')
AND Numbers < 50
GROUP BY Numbers
ORDER BY `TIME` ASC");
$result->execute();
// the problem is..
// you are trying to fetch $result but here $result is just executing
// you cannot retrive anything unless you didn't declare it
/* here $result has nothing in it; is just executed first
* declare it
*/
$result = $result->execute();
/* Then Fetch it
And then You Can Use You Fetch Var with index to retrive data.
e.g */
$allData = $result->fetchAll();
foreach ($allData as $SingleData)
{
// here you must place indexes of your Query
// e.g $SingleData['id'] or $SingleData[0]
echo '<div class="container">
'.$SingleData['COUNT(*)'].'
'.$SingleData['LENGTH(Number)'].'
</div>';
}
+1
source to share