Why does not this query work in codeigniter, and in the IDE of ms Access it works without problem? (I think it's the JOIN)

Question

Why does not this query work in codeigniter, and in the IDE of ms Access it works without problem? (I think it's the JOIN)

Navigation

#1 by (0 votes)

0

I have a query that I can not work inside codeigniter, removing parts of the query, it seems that the error is in the JOIN, but I do not understand why, and even less understand, that when performing the query in PostMan, for example, it does not no error, simply the result remains "white" (Using simple queries the result is correct)

$query = $this->db->query("SELECT F_STO.ARTSTO, F_STO.ACTSTO, F_STO.MINSTO, F_ART.DESART
                      FROM F_STO RIGHT JOIN F_ART ON F_STO.ARTSTO = F_ART.CODART
                      WHERE F_STO.ACTSTO < F_STO.MINSTO AND F_STO.ARTSTO LIKE 'T%'
                      ");

The connection is to an MS Access database (not for pleasure ...) using the ODBC driver.

If instead of using result_array () to see the value I use this code:

$query = $this->db->query("SELECT F_STO.ARTSTO, F_STO.ACTSTO, F_STO.MINSTO, F_ART.DESART, F_ART.CODART
                                                        FROM F_STO
                                                        RIGHT JOIN F_ART ON F_STO.ARTSTO = F_ART.CODART
                                                        WHERE F_STO.ACTSTO < F_STO.MINSTO AND F_STO.ARTSTO LIKE 'T%'
                                                        ");
$respuesta = array(

  'error' => FALSE,
  'stock' => $query->num_rows()
);
$this->response($respuesta);

If that gives me the number of rows in the result, which in this case is 1.

But if I do it like this:

$query = $this->db->query("SELECT F_STO.ARTSTO, F_STO.ACTSTO, F_STO.MINSTO, F_ART.DESART, F_ART.CODART
                          FROM F_STO
                          RIGHT JOIN F_ART ON F_STO.ARTSTO = F_ART.CODART
                          WHERE F_STO.ACTSTO < F_STO.MINSTO AND F_STO.ARTSTO LIKE 'T%'
                          ");

    $respuesta = array(

      'error' => FALSE,
      'stock' => $query->result_array()
    );
    $this->response($respuesta);

It does not show me the 'error', 'false', it is totally blank.

Thank you very much, I hope you can help me out.

EDITED: SOLUTION

The query was correct, the problem was when generating the JSON, I do not know because POSTMAN did not give me any wrong JSON error, but from the Firefox inspector I marked

SyntaxError: JSON.parse: unexpected end of data at line 1 column 1 of the JSON data

Reviewing the contents of the database I noticed that some rows had a

"

It was not contained in quotation marks, but it had to be in the base because it refers to the size of a thread, for example 2 "NPT.

Removing the quotes to make the test has already formed the JSON correctly.

Now I can only investigate how to replace the quotes before forming the JSON, but that's another story.

Thanks

json codeigniter query join ms-access

asked by NeWsPi 11.06.2018 в 08:02

source

1 answer

Rails and Datatables Edit the page rescued with curl before executing it with curl_exec

score 0 · Answer 1

You have solved your question, seeing your consulta I think it would be easier for you to make use of the querys that you provide codeigniter and that you have something much cleaner.

Example:

public function tufuncion()
{
    $this->db->select('f.ARTSTO, f.ACTSTO, f.MINSTO, f.DESART');
    $this->db->from('F_STO f');
    $this->db->join('F_ART a','f.ARTSTO = a.CODART','right outer');
    $this->db->where('f.ACTSTO < f.MINSTO');
    $this->db->like('f.ARTSTO', 'T', 'after');

    $aResult = $this->db->get();
    if(!$aResult->num_rows() == 1)
    {
        return false;
    }
    return $aResult->result_array();
}

For more information about the querys in the codeigniter documentation .