Welcome Guest, Not a member yet? Register   Sign In
MYSQL longtext return null value
#1

(This post was last modified: 12-11-2020, 02:55 PM by ReivaxBird.)

Hello,

I have an issue with my codes. I use CodeIgniter 4.
I try to do a select on a table with a join. In the table Appointments, a field 'notes' is a longtext.
The query works fine for all other column except for the longtext field which return NULL everytimes (I check on MYSQL and it's not NULL).

Here is my codes :
Code:
$db = \Config\Database::connect();

$builder = $db->table('Appointments');

                $query = $builder->select('appointment')
                    ->distinct()
                    ->join("Clients", "Appointments.clientid = Clients.id")
                    ->where('Appointments.branchid', session('branchid'))
                    ->where('Appointments.appointment BETWEEN STR_TO_DATE(\'' . $datefrom . '\',\'%Y-%m-%d\') AND STR_TO_DATE(\'' . $dateto . '\',\'%Y-%m-%d\')')
                    ->get();

                $MISdetails = $query->getResultArray();

foreach ($MISdetails as $MIS) {
echo $MIS['notes'];
}

I used the $MIS['notes'] which return null (other value are not null).

Do you have any idea how to handles longtext column ?

My database configuration use MySQLi (I try also PDO).

Regards,

Xavier
Reply
#2

@ReivaxBird,

Can you show the generated SQL? Also, did you try to run the generated SQL in MySQL directly?
Reply
#3

Hello,

Yes of course :
one example is here :
Code:
SELECT *
FROM `Appointments`
JOIN `Clients` ON `Appointments`.`clientid` = `Clients`.`id`
WHERE `Appointments`.`branchid` = '1'
AND `Appointments`.`appointment` = STR_TO_DATE('2020-12-11','%Y%m%d')
AND CAST(Clients.createdate as Date) = STR_TO_DATE('2020-12-11','%Y%m%d');

There is a field notes which is the longtext.
And sorry i make a mistake in copying my code in my previous subject. The good code is :

Code:
$queryAppt = $builderxls->join("Clients", "Appointments.clientid = Clients.id")
Code:
                            ->where('Appointments.branchid', session('branchid'))
Code:
                            ->where('Appointments.appointment = STR_TO_DATE(\'' . $MISdate['appointment'] . '\',\'%Y-%m-%d\')')
Code:
                            ->where('CAST(Clients.createdate as Date) = STR_TO_DATE(\'' . $MISdate['appointment'] . '\',\'%Y-%m-%d\')')
Code:
                            ->get();


I play that on mysql and there is no error, I have other value OK (so there is some row). Just the following code which give me null for Appt['notes'].
Code:
$Appt = $queryAppt->getResultArray();

In the query I recover the notes field with data on mysql. (MariaDb).
Reply
#4

Hello,

I did some test and It seems if it's something with the join.

Code:
//direct query
        echo "db : ";
        $db = \Config\Database::connect();
       
        $builder = $db->table('Clients');
        //$builder->select('Appointments.notes');
        $builder->join("Appointments", "Appointments.clientid = Clients.id AND Appointments.id = Clients.lastappointmentid")->where('Clients.branchid', session('branchid'));
        $array = $builder->get()->getResultArray();
        foreach($array as $b){
            echo $b['notes'];
        }
       
        //direct query
        echo "db2 : ";
        $db = \Config\Database::connect();
       
        $builder = $db->table('Appointments');
        //$builder->select('Appointments.notes');
        $builder->join("Clients", "Appointments.clientid = Clients.id AND Appointments.id = Clients.lastappointmentid")->where('Clients.branchid', session('branchid'));
        $array = $builder->get()->getResultArray();
        foreach($array as $d){
            echo $d['notes'];
        }

I try to do the same queries with two different ways.
I have two tables :
Clients which does not have the notes field (which is a longtext).
And Appointments which does contains notes field.

There is only one line in Appointments and Client.

My first try is with Clients as main table join to Appointments : Its work !
My second try is with Appointments as main table join to Clients : It does not work !

here the result :

Quote:model :db : test note où je dit plein de trucdb2 :


Why I do not retrieve the longtext field in one case but I do in the other case ?

Regards,

Xavier
Reply
#5

@ReivaxBird,

I have a some recommendation. Sometimes it is easier to get the query working directly in MySQL and then convert it to code. Also, you might want to consider doing a left join.
Reply
#6

(This post was last modified: 12-14-2020, 02:47 AM by ReivaxBird.)

Hello,

Thanks for the answer.
But I already do the query in MYSQL and it's works fine. There is no reason in MYSQL to not have a return of the longtext only and to have a return of other fields. (And I try it, it works).
Then I do not want to do a left join but an inner join. I do not want have a result in the case of a missing row in one of the tables.

I do not understand why do :
SELECT * from APPOINTMENTS
INNER JOIN Clients ON ....

Differ of doing that :
SELECT * from Clients
INNER JOIN Appointments ON ...

In mysql there is no difference (except for the order of the fields).
I do not understand why exchange the both table change the behaviour. (In the first case it does not work, and it works in the second case). The notes field is owned by Appointments table.

I find a solution with this exchange but I do not understand why it's working i one way and not the other Smile

Regards,

Xavier
Reply
#7

Why working with longtext but not "text" for example? Isn't longtext too much?
Reply
#8

@ReivaxBird,

Try using the query binding way instead (https://codeigniter.com/user_guide/datab...y-bindings). Maybe it will work.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB