COUNT(*) returning 0 ? - Printable Version

COUNT(*) returning 0 ? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: COUNT(*) returning 0 ? (/showthread.php?tid=23720)

Pages: 1 2

COUNT(*) returning 0 ? - El Forum - 10-20-2009

[eluser]dplgngr[/eluser]
I get a 0 when i tried to use COUNT to retrieve the number of data.

Code:
$query = $this->db->query('SELECT COUNT(*) AS total_count FROM `authorize_net` where `Transaction Status` = "Settled Successfully" and `Settlement Amount` = 9.95');

$row = $query->row_array();

echo $row['total_count'];

the result printed 0 whereas when i query it using the phpmyadmin it returned the right amount.

Can anybody help me with this asap?
Thanks!

COUNT(*) returning 0 ? - El Forum - 10-20-2009

[eluser]sl3dg3hamm3r[/eluser]
And what do you get if you replace your query on behalf of testing with:

SELECT 'Test' AS total_count;

If you get 'Test' then it was definitely 0 returned by the DB...

COUNT(*) returning 0 ? - El Forum - 10-20-2009

[eluser]dplgngr[/eluser]
[quote author="sl3dg3hamm3r" date="1256062264"]And what do you get if you replace your query on behalf of testing with:

SELECT 'Test' AS total_count;

If you get 'Test' then it was definitely 0 returned by the DB...[/quote]

I get this:

Code:
Error Number: 1054

Unknown column 'Test' in 'field list'

SELECT `Test` AS total_count;

And here is the phpmyadmin query result to clarify that the query should return a definite number.

COUNT(*) returning 0 ? - El Forum - 10-20-2009

[eluser]sl3dg3hamm3r[/eluser]
Uh sorry, my fault, thought this syntax works with MySQL... can't remember the right syntax.

COUNT(*) returning 0 ? - El Forum - 10-20-2009

[eluser]dplgngr[/eluser]
I tried it the other way:

Code:
$this->db->select('COUNT(*) AS total_count');

$this->db->from('authorize_net');

$this->db->where('`Transaction Status`="Settled Successfully" and `Settlement Amount`=9.95');

$query = $this->db->get();

$result = $query->result();

$this->total_count_dvd = $result;

But it returned an error:

Code:
A Database Error Occurred

Error Number: 1064

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 'Status`="Settled Successfully" and `Settlement Amount`=9.95' at line 3

SELECT COUNT(*) AS total_count FROM (`authorize_net`) WHERE `Transaction` Status`="Settled Successfully" and `Settlement Amount`=9.95

COUNT(*) returning 0 ? - El Forum - 10-20-2009

[eluser]rogierb[/eluser]
Loose the double quotes.

Code:
$this->db->where("`Transaction Status`='Settled Successfully' and `Settlement Amount`=9.95");

COUNT(*) returning 0 ? - El Forum - 10-20-2009

[eluser]dplgngr[/eluser]
[quote author="rogierb" date="1256064181"]Loose the double quotes.

Code:
$this->db->where("`Transaction Status`='Settled Successfully' and `Settlement Amount`=9.95");

[/quote]

Code:
$this->db->select('COUNT(*) AS total_count');

$this->db->from('authorize_net');

$this->db->where('`Transaction Status`="Settled Successfully" and `Settlement Amount`=9.95');

$query = $this->db->get();

$result = $query->result();

$this->total_count_dvd = $result;

I dont see any double quotes.

COUNT(*) returning 0 ? - El Forum - 10-20-2009

[eluser]rogierb[/eluser]
around "Settled Successfully". Mysql does not support them.

COUNT(*) returning 0 ? - El Forum - 10-20-2009

[eluser]dplgngr[/eluser]
[quote author="rogierb" date="1256065850"]around "Settled Successfully". Mysql does not support them.[/quote]

Code:
$this->db->where('`Transaction Status`="Settled Successfully" and `Settlement Amount`=9.95');

Can you suggest the exact code?

I tried this one:

Code:
$this->db->select('COUNT(*) AS total_count');

$this->db->from('authorize_net');

$this->db->where('`Transaction Status`=`Settled Successfully` and `Settlement Amount`=9.95');

$query = $this->db->get();

$result = $query->result();

$this->total_count_dvd = $result;

but it returned an error:

Code:
A Database Error Occurred

Error Number: 1064

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 'Status`=`Settled Successfully` and `Settlement Amount`=9.95' at line 3

SELECT COUNT(*) AS total_count FROM (`authorize_net`) WHERE `Transaction` Status`=`Settled Successfully` and `Settlement Amount`=9.95

COUNT(*) returning 0 ? - El Forum - 10-20-2009

[eluser]codex[/eluser]
Try this:

Code:
$this->db->select('COUNT(*) AS total_count');

$this->db->from('authorize_net');

$this->db->where('Transaction Status', 'Settled Successfully');

$this->db->where('Settlement Amount', '9.95');

$query = $this->db->get();

$result = $query->result();

$this->total_count_dvd = $result;

But are your fields really called Transaction Status and Settlement Amount? That can't be right...

[quote author="dplgngr" date="1256067135"][quote author="rogierb" date="1256065850"]around "Settled Successfully". Mysql does not support them.[/quote]

Code:
$this->db->where('`Transaction Status`="Settled Successfully" and `Settlement Amount`=9.95');

Can you suggest the exact code?

I tried this one:

Code:
$this->db->select('COUNT(*) AS total_count');

$this->db->from('authorize_net');

$this->db->where('`Transaction Status`=`Settled Successfully` and `Settlement Amount`=9.95');

$query = $this->db->get();

$result = $query->result();

$this->total_count_dvd = $result;

but it returned an error:

Code:
A Database Error Occurred

Error Number: 1064

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 'Status`=`Settled Successfully` and `Settlement Amount`=9.95' at line 3

SELECT COUNT(*) AS total_count FROM (`authorize_net`) WHERE `Transaction` Status`=`Settled Successfully` and `Settlement Amount`=9.95

[/quote]