How to view SQL generated by Query Builder? |
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
(08-09-2020, 12:34 PM)richb201 Wrote: $sql= "SELECT COUNT(*) FROM business_components WHERE email= ? AND campaign= ? AND 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 )
|
Welcome Guest, Not a member yet? Register Sign In |