Weird case with empty values

[jjsanders]

Dear all,

I am trying to get some data from my mysql databse. This goes pretty well, here's my query:

$db =Zend_Db_Table_Abstract::getDefaultAdapter();
$sql = "SELECT * FROM evenementen LEFT JOIN inschrijvingen ON ";
$sql.= "inschrijvingen.evenement_id= evenementen.evenement_id;";
$result = $db->fetchAll($sql);

When I dump my result, this is what I get: http://paste2.org/p/40914

And this is what worries me:["evenement_id"] => NULL,
Evenement_id is always filled, since it is my primary key.

So I tested the same query straight on my database with phpMyAdmin and this is what I got:
http://sanders-albek.nl/pics/result.JPG
And as you can see, what probebly happends is that a evenement_id wich is in my evenementen table always filled, gets overwritten by an empty one from my second table (inschrijvingen)
How can I resolve this problem?
       


--
Thanks

J Sanders

[Joseph Chereshnovsky]

Hello,

It's not Zend Framework problem but MySQL related.

Any way you don't need to use wildcard (*) in JOIN statements in this case.
Specify your tables and fields explicitly.

The example:

SELECT
          evenementen.evenement_id
        , evenementen.evenement_naam
        , evenementen.datum_aanvang
        , evenementen.tijd_aanvang
        ... and so on...
        , inschrijvingen.persoon_id
        , inschrijvingen.status_id
FROM
        evenementen

LEFT JOIN inschrijvingen ON inschrijvingen.evenement_id =
evenementen.evenement_id;

I can't guess the structure of your tables but I hope you've got the point.

Thanks,
Joseph

-----Original Message-----
From: Jigal sanders [mailto:jigalroecha@...]
Sent: Thursday, June 19, 2008 3:37 PM
To: fw-db@...
Subject: [fw-db] Weird case with empty values

Dear all,

I am trying to get some data from my mysql databse. This goes pretty well,
here's my query:

$db =Zend_Db_Table_Abstract::getDefaultAdapter();
$sql = "SELECT * FROM evenementen LEFT JOIN inschrijvingen ON ";
$sql.= "inschrijvingen.evenement_id= evenementen.evenement_id;";
$result = $db->fetchAll($sql);

When I dump my result, this is what I get: http://paste2.org/p/40914

And this is what worries me:["evenement_id"] => NULL,
Evenement_id is always filled, since it is my primary key.

So I tested the same query straight on my database with phpMyAdmin and this
is what I got:
http://sanders-albek.nl/pics/result.JPG
And as you can see, what probebly happends is that a evenement_id wich is in
my evenementen table always filled, gets overwritten by an empty one from my
second table (inschrijvingen)
How can I resolve this problem?



--
Thanks

J Sanders

[Bill Karwin]

jjsanders wrote:

And as you can see, what probebly happends is that a evenement_id wich is in
my evenementen table always filled, gets overwritten by an empty one from my
second table (inschrijvingen)
How can I resolve this problem?

One solution is to specify an alias for the second instance of the column:

SELECT e.*, i.evenement_id AS evenement_id_from_i
FROM evenementen AS e
 LEFT JOIN inschrijvingen AS i USING (evenement_id)

Note this means you must spell out all columns from i.  
You can't give an alias for one column and simultaneously use a wildcard for the others.

Another solution would be to list all columns from i explicitly, excluding evenement_id.

Regards,
Bill Karwin

[Bill Karwin]

Bill Karwin wrote:

Another solution would be to list all columns from i explicitly, excluding evenement_id.

I thought of a third solution:  fetch the results as a plain array instead of an associative array.  That's the reason the column from the right table is overwriting the entry from the left table.

You can specify the Zend_Db fetch mode as FETCH_NUM, to make it return each row as a plain array indexed by integer.

See the documentation for more on fetch modes:
http://framework.zend.com/manual/en/zend.db.html#zend.db.adapter.select.fetch-mode

Regards,
Bill Karwin

[Joseph Chereshnovsky]

Hello,

Please refer to MySQL manual to build the query you need:
http://dev.mysql.com/doc/refman/5.0/en/join.html

Thanks,
Joseph


P.S.  Also use aliases for table names to simplify your query:

SELECT
        e.evenement_id
    , e.evenement_naam
    , e.datum_aanvang
    , e.tijd_aanvang
    , i.persoon_id
    , i.status_id
    , s.omschrijving
FROM
        evenementen as e
    , statussen as s
      LEFT JOIN inschrijvingen as i ON i.evenement_id = e.evenement_id



-----Original Message-----
From: Jigal sanders [mailto:jigalroecha@...]
Sent: Friday, June 20, 2008 1:32 PM
To: Joseph Chereshnovsky
Subject: Re: [fw-db] Weird case with empty values

Ok thanks and what do I do if I want an extra table like:

SELECT evenementen.evenement_id, evenementen.evenement_naam,
evenementen.datum_aanvang, evenementen.tijd_aanvang,
inschrijvingen.persoon_id, inschrijvingen.status_id, statussen.omschrijving
FROM evenementen, statussen LEFT JOIN inschrijvingen ON
inschrijvingen.evenement_id = evenementen.evenement_id ?

On Thu, Jun 19, 2008 at 3:14 PM, Joseph Chereshnovsky <Joseph007@...>
wrote:

> Hello,
>
> It's not Zend Framework problem but MySQL related.
>
> Any way you don't need to use wildcard (*) in JOIN statements in this
case.

> Specify your tables and fields explicitly.
>
> The example:
>
> SELECT
>          evenementen.evenement_id
>        , evenementen.evenement_naam
>        , evenementen.datum_aanvang
>        , evenementen.tijd_aanvang
>        ... and so on...
>        , inschrijvingen.persoon_id
>        , inschrijvingen.status_id
> FROM
>        evenementen
>
> LEFT JOIN inschrijvingen ON inschrijvingen.evenement_id =
> evenementen.evenement_id;
>
> I can't guess the structure of your tables but I hope you've got the

point.

>
> Thanks,
> Joseph
>
> -----Original Message-----
> From: Jigal sanders [mailto:jigalroecha@...]
> Sent: Thursday, June 19, 2008 3:37 PM
> To: fw-db@...
> Subject: [fw-db] Weird case with empty values
>
> Dear all,
>
> I am trying to get some data from my mysql databse. This goes pretty well,
> here's my query:
>
> $db =Zend_Db_Table_Abstract::getDefaultAdapter();
> $sql = "SELECT * FROM evenementen LEFT JOIN inschrijvingen ON ";
> $sql.= "inschrijvingen.evenement_id= evenementen.evenement_id;";
> $result = $db->fetchAll($sql);
>
> When I dump my result, this is what I get: http://paste2.org/p/40914
>
> And this is what worries me:["evenement_id"] => NULL,
> Evenement_id is always filled, since it is my primary key.
>
> So I tested the same query straight on my database with phpMyAdmin and

this

> is what I got:
> http://sanders-albek.nl/pics/result.JPG
> And as you can see, what probebly happends is that a evenement_id wich is
> in
> my evenementen table always filled, gets overwritten by an empty one from
> my
> second table (inschrijvingen)
> How can I resolve this problem?
>
>
>
> --
> Thanks
>
> J Sanders
>
>

--
Met vriendelijke groet,

Jigal Sanders
A.J. Ernststraat 739
1082 LK Amsterdam
Mobiel: 06-42111489