COUNT(*) returning 0 ?

[eluser]dplgngr[/eluser]
@codex

It returned this 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'

Yes the fields are exactly like that. Check the screenshot below.

[eluser]codex[/eluser]
I'd say loose the spaces. As you can see it screws up the queries:

Code:

`Transaction` Status

[quote author="dplgngr" date="1256068473"]@codex

It returned this 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'

Yes the fields are exactly like that. Check the screenshot below.[/quote]

[eluser]dplgngr[/eluser]
[quote author="codex" date="1256068648"]I'd say loose the spaces. As you can see it screws up the queries:

Code:

`Transaction` Status

[/quote]

How can i do that? Sorry

[eluser]codex[/eluser]
Rename your fields to have no spaces but underscores and no special characters:

transaction_status, settlement_whatever


[quote author="dplgngr" date="1256069214"][quote author="codex" date="1256068648"]I'd say loose the spaces. As you can see it screws up the queries:

Code:

`Transaction` Status

[/quote]

How can i do that? Sorry[/quote]

[eluser]sl3dg3hamm3r[/eluser]
Or you can try to deny backticks. From the user guide:

$this->db->where() accepts an optional third parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks.

Code:

$this->db->where('MATCH (field) AGAINST ("value")', NULL, FALSE);

But it is really bad practice to have blanks in your field-names. I would rename that, too.

[eluser]dplgngr[/eluser]
@codex and sl3dg3hamm3r

Yes i agree it is really a bad practice to use spaces on field names.
I really hope im the one who named these table fields.

Im gonna try your suggestion sl3dg3hamm3r and if it wont work im gonna have to cross my fingers and see if my senior allows me to change the field names.

[eluser]codex[/eluser]
Yes, I could have suggested preventing backticks, but bad practice is bad practice. TO should fix that first ;-)

[quote author="sl3dg3hamm3r" date="1256070130"]Or you can try to deny backticks. From the user guide:

$this->db->where() accepts an optional third parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks.

Code:

$this->db->where('MATCH (field) AGAINST ("value")', NULL, FALSE);

But it is really bad practice to have blanks in your field-names. I would rename that, too.[/quote]

[eluser]bretticus[/eluser]
If you separate your where statements, you may get the multi-word column names escaped properly (although I agree, it's a bad idea to use them in the first place.)

Code:

$this->db->select('COUNT(*) AS total_count', FALSE); //escape backticking here
$this->db->where('Transaction Status', 'Settled Successfully');
$this->db->where('Settlement Amount', '9.95');
        
$query = $this->db->get('authorize_net');
$result = $query->row(); //get a row
        
$this->total_count_dvd = $result->total_count; //get the total_count coumn.

If that doesn't work, just use $this->db->query("YOUR SQL");

[eluser]InsiteFX[/eluser]
I want to know where he is getting 'Settled Successfully' from could be a wrong type?

Enjoy
InsiteFX

[eluser]dplgngr[/eluser]
Hi guys thank you for your help.
I got it right now.

There was an issue with the connection on the database.
I was connecting to the same database structure but with different data values in it.

Sorry for the bother.
But, YES all of the suggested workaround by you guys worked properly on a field name with a space.

THanks a lot.