How to view SQL generated by Query Builder?

I'd like to make sure that Query Builder is generating exactly the SQL that I need. Is there some way to echo the sql string it has built?

proof that an old dog can learn new tricks

Well I think I found this one.

$this->db->get_compiled_select()

---

In looking at the codeigniter4 Query Builder docs. I see that the way to load the Query Builder is to use

Config\Database::connect();
$builder = $db->table('users');

Clearly this is for a single table, users. I have over 20 tables but this makes sense. I could open the 4 or 5 tables I need.

But in Codeigniter3, I don't see this same "table" function. So again I wonder how do i tell Query Builder 3 which table I want to do the from on?

proof that an old dog can learn new tricks

To try to solved this I created the query by hand:
$sql="SELECT COUNT(*) FROM employees WHERE qualified=yes AND employee_title='$title' AND email=$_SESSION[userid] AND campaign=$_SESSION[campaign]";
            $query = $this->db->query($sql);

This returns the error: "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 '@gmail.com AND campaign=Apple' at line 1"

I am wondering if the "@" in the email address is the problem? How can I escape that?

I tried $query = $this->db->query(escape($sql));

But I get: Call to undefined function escape()

In searching for an answer on stackoverflow I see this:

"Change your PHP version back to PHP 5.6 and it will working fine."

I am running php 7. Could this be the issue? I am not sure I want to go back. 

proof that an old dog can learn new tricks

@richb201,

Have you tried running the query directly on the database to make sure it works? Also, are you sure that in your query where it says email=$_SESSION[userid] that maybe it should be email='$_Session[userid]'?


Also, don't forget that you can use query binding (https://codeigniter.com/user_guide/datab...y-bindings) instead of the query builder.

PHP, good idea to run the query directly. I ran it within phpMyAdmin. Seems I modified one of the columns, qualified from a digit to a string. I had assumed all of the fields set to 1 would convert to "yes". They did not and once I saw that in the phoMyAdmin, it became clear that was the issue. Thx.

Just read through the document you recommended:
>>Also, don't forget that you can use query binding (https://codeigniter.com/user_guide/datab...y-bindings) instead of the query builder.

sounds perfect for my needs. thanks again, Rich

proof that an old dog can learn new tricks

$sql= "SELECT COUNT(*) FROM business_components WHERE email= ? AND campaign= ? AND IUS= ?";
$query =$this->db->query($sql, [ $_SESSION['userid'],$_SESSION['campaign'],1]);
$res=$query->result();

This works except I don't know how to get the result

I tried this 'num_IUS' => $res[0]. The value in $res[0] is COUNT(*)="2". 2 is the correct answer but how do I get that into num_IUS?

proof that an old dog can learn new tricks

$sql= "SELECT COUNT(*) FROM business_components WHERE email= ? AND campaign= ? AND IUS= ?";
$query =$this->db->query($sql, [ $_SESSION['userid'],$_SESSION['campaign'],1]);
$res=$query->result();

This works except I don't know how to get the result

I tried this 'num_IUS' => $res[0]. The value in $res[0] is COUNT(*)="2". 2 is the correct answer but how do I get that into num_IUS?

Set num_IUS = $res[0];

PHP Code:

$db->getLastQuery() 


What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

thank you

proof that an old dog can learn new tricks