Getting double result sets from MySQL 4.0.10

using INNODB, PHP 4.3.0, Apache 2, Linux

[lars stecken]

Hi there,

I am experiencing a weired problem trying to retrieve data through PHP from a MySQL table. A simple SELECT always shows every field twice. ADODB and regular PHP-mysql functions give the same result...

Does anybody know about a similar problem and how to fix it? May be the combination of all the latest versions of Apache, PHP, MySQL?

I am quite clueless on this one. Any suggestion would help!

Thanks a bunch,

Stefan

[hakre]

hi lars stecken,

didn't had the same problem, but maybe it's not an error of the db, it might be an error of the sql query or the php script like fetching the same recordset twice. stickymail me if you don't want to post details herin.

[ukgimp]

lars

Are you joining tables or similar. It might be something like you have two tables and you need to alter you SQL.

For example

SELECT * FROM x, y WHERE id = 1

may give two sets of results. So you need to join the tables:

SELECT * FROM x, y WHERE id = 1
AND x.id = y.id

[lars stecken]

No, I am not using any JOINS. I am aware that you get the multiplied results if you don't do the right JOINS.

But while I am typing this I get an idea. I am trying to work with FOREIGN KEY CONSTRAINTS for the first time and there is at least one other table "connected" (with ON DELETE CASCADE) to the table I am retrieving data from. But that would be really stupid if this screwed up my SELECT...

I am going to try this out, but it doesn't seem very likely.

Greets,
Stefan

[Robber]

It could be the php function you are using - if you use mysql_fetch_array then the array contains duplicate data - you will get the data returned as a standard numeric index based array and also as an associative array (all combined in a single array), so if you just did a foreach on it to get all values out you would effectively pull out all values twice.

One other thing, I dont think mysql supports foreign key constraints - you can put them in the sql statement but they will be effectively ignored (I think!).

[lars stecken]

Hi Robber,
thank you for your reply.
This sounds quite the way it could be. I think it would really be helpful to know about the "insides" of the functions you use ;-)

I always thought that if you use mysql_fetch_array it would give you ONLY the associative array. I'll give it a try! But do you by coincidence know the equivalent function in ADODB for that?

Referring to foreign keys: I am using InnoDB and you don't have to specify anything in the SELECT statement. You have to tell the "connected" tables what the relevant action is if something happens in the parent table. I tried this out and it worked perfectly. It can save you a lot of work when you have many parent/child tables . The trigger options are ON DELETE and ON UPDATE.
Thanks again,
greets Stefan

[Robber]

You can use it like this:

$row = mysql_fetch_array($queryResult, MYSQL_NUM)

to give you a normal array, or

$row = mysql_fetch_array($queryResult, MYSQL_ASSOC)

to give an associative array.

Im afraid I cant help with the adodb.

Cheers