[SOLVED] Query not given what I want

[salami1_1]

Hi Guys,

 

I'm working on MySql version: MySQL client version: 4.1.22

 

Query:

SELECT logindb.aantallogs, orderdb.sell_price,orderdb.sell_curr FROM ics_orders orderdb, ics_logins logindb WHERE logindb.product_link = orderdb.id AND gebruikt = 1 ORDER BY logindb.aantallogs ASC

 

Table layout:

CREATE TABLE ics_logins (

  id SMALLINT UNSIGNED NOT NULL auto_increment,

  username VARCHAR(40)  NOT NULL,

  pass VARCHAR(40) NOT NULL,

  aantallogs SMALLINT NOT NULL,

  gebruikt TINYINT UNSIGNED DEFAULT '0' NOT NULL,

  type char(15),

  product_link CHAR(20) NOT NULL,

  orderlink INT UNSIGNED, 

  PRIMARY KEY (id)

);

ALTER TABLE ics_logins ADD INDEX icsLogin_index (username,aantallogs,gebruikt,product_link);

#product_link is the unique product_name from product db

#orderlink is id from ics_orders (1 to many)

 

CREATE TABLE ics_orders (

  id INT UNSIGNED NOT NULL auto_increment,

  selldatum CHAR(16),

  cust_email VARCHAR(60) NOT NULL,

  payp_orderid CHAR(25),

  cust_name char(100),

  cust_ip CHAR(16),

  cust_country CHAR(5),

  sell_price FLOAT(7,2) NOT NULL,

  sell_curr CHAR(10) NOT NULL,

  affiliate char(40),

  note text,

  PRIMARY KEY (id)

);

ALTER TABLE ics_orders ADD INDEX icsOrders_index (cust_email,cust_country,sell_curr,affiliate);

 

Now in the order table there are 2 currencies EUR and USD (mostly EUR just couple USD)

but with the query I only get the results with EUR and not USD..

I checked if perhaps the linking was wrong (mysql doesn't support foreign key so that is not defined in create table statements) but that is all fine.

 

I'm probably doing something wrong but I even took my last semester database design lectures with it but couldn't find the mistake..

 

If anybody can point me in the right direction?

Thanks.

 

Best regards,

[Mchl]

Could it be, that gebruikt = 1 only where currency is EUR? Other than that I can not see anything that would limit the query.

 

And yes, MySQL does support foreign keys since ver. 3.22.34

 

edit

 

Why are you using

logindb.product_link = orderdb.id

 

instead of

logindb.orderlink = orderdb.id

?

[BloodyMind]

Could it be, that gebruikt = 1 only where currency is EUR? Other than that I can not see anything that would limit the query.

 

And yes, MySQL does support foreign keys since ver. 3.22.34

 

edit

 

Why are you using

logindb.product_link = orderdb.id

 

instead of

logindb.orderlink = orderdb.id

?

 

Indeed just use InnoDB Engine so it supports transactions

[Mchl]

Foreign keys and transactions are two veru different things, but yes InnoDB supports both.

[salami1_1]

Aaaah thanks!

 

I was using the wrong Fk constraint indeed! Changing product_link with orderlink did the trick!

 

Are you sure Mysql supports Fk? Maybe they can disable that on public hostings? Because tried with;

CREATE TABLE ics_newsreactions (

  id SMALLINT UNSIGNED NOT NULL auto_increment,

  postlinkid SMALLINT UNSIGNED NOT NULL,

  bericht text NOT NULL,

  datum CHAR(35) NOT NULL,

  poster_naam CHAR(20) NOT NULL,

  poster_email VARCHAR(30) NOT NULL,

  posterip CHAR(16) NOT NULL,

  active TINYINT(1) UNSIGNED NOT NULL,

  seckey CHAR(20) NOT NULL,

  PRIMARY KEY (id)

  #FOREIGN KEY (postlinkid) REFERENCES ics_news(id);

);

 

(complete other table) but it gave me errors with the foreign key (so commented out now)

I'm not using Inno because Inno does not support index and as I'm on shared hosting I need all the efficiency I can get.

 

Best regards,

[corbin]

Foreign keys and transactions are two veru different things, but yes InnoDB supports both.

 

 

 

I think he was referring to the little thing that MyISAM does not support foreign keys.  (Last I checked, anyway.)

[salami1_1]

I think he was referring to the little thing that MyISAM does not support foreign keys.  (Last I checked, anyway.)

 

yeah correct, I know its not true but I see InnoDB as a different think as when I say Mysql, as MyIsam is standard.

[Mchl]

I'm not using Inno because Inno does not support index

 

Who told you that?

[ashishag67]

hey salami1_1

 

creating database is a pain in a neck if done with coding I would suggest use phpmyadmin... it comes with a bundle in wampserver which consist of apache, php , mysql, postgre this will solve your problems with foreign key as well..

 

check this website: http://www.mysqlandphp.net

[salami1_1]

I'm not using Inno because Inno does not support index

 

Who told you that?

 

Its not true then? Don't remember exactly where I got that from but read it somewhere..

If its not true then why is the MyIsam always standard? Because in that case it looks like InnoDB is better in every way

 

@ashishag67

yes I also use phpmyAdmin but its not a full solution. PHPmyadmin is not a solution to design databases more for maintaining them.

[Mchl]

For many MySQL versions InnoDB was in fact inferior to MyISAM in terms of performance. Nowadays however they go head to head, with InnoDB often having an edge, where amount of queries is not very high. MyISAM is still a better choice, when you have a lot (like hundreds per second) SELECT queries, and a lot less INSERT and UPDATE queries.

 

Also some shared hosts don't like InnoDB, as it requires more memory (so guess what? thay can have less clients on same hardware!)

 

Actually InnoDB has been set as a default storage engine for Windows installations (since somewhere in 5.0 I suppose).

[salami1_1]

For many MySQL versions InnoDB was in fact inferior to MyISAM in terms of performance. Nowadays however they go head to head, with InnoDB often having an edge, where amount of queries is not very high. MyISAM is still a better choice, when you have a lot (like hundreds per second) SELECT queries, and a lot less INSERT and UPDATE queries.

 

Also some shared hosts don't like InnoDB, as it requires more memory (so guess what? thay can have less clients on same hardware!)

 

Actually InnoDB has been set as a default storage engine for Windows installations (since somewhere in 5.0 I suppose).

 

thanks for the explanation, maybe I should switch my stuff to InnoDB then.. I'll have a closer look at that when I have the time. Thanks for the help!

[Mchl]

Just make sure, your host supports it. I was dismayed recently, when I found out my newly bought hosting account has no InnoDB support (and will not have, whatever I do - unless I buy dedicated server with them)