Help with table extract!

russellbcv

Hello

I am tying to link the username with a partiular table which contains an A to Z list of rates for that user.

MySQL set-up>

Table 1 = "ratetableuk"
- username
- ratetable

Table 2 = "rate1"
- destination
- standard
- evening
- weekend

Tables 3, 4 & 5 are exactly the same format as Table 2 but with different rates.

In Table 1, I have assigned a rate to each username i.e

username = 1213411
ratetable = rate1

username = 1213412
ratetable = rate3

etc. etc....

Does anybody have any idea how I can link the username with the appropriate rates?

Yours

Russell

Frederick

You need to add an id field to the Table 2 so it can reference Table 1.

Table 1 = "ratetableuk"
- table1ID
- username
- ratetable

Table 2 = "rate1"
- table2ID
- table1ID
- destination
- standard
- evening
- weekend

russellbcv

I'm not sure how this would work?

How would this work?

mrhappiness

Are rate1, rate2, rate3, rate4 and rate5 structured the same way?
if yes you should change your db layout:
- user table
- rate table
- relation table with user_id and corresponding rate_ids (one entry for each relation)

then you shold inform yourself about JOINs in mysql

russellbcv

Do you mean ?

Table 1 =
- temp1
- username
- ratateble

Table 2 =
- temp2
- temp1
- destination
- standard
- evening
- weekend

Table 3 =
- temp3
- temp1
- destination
- standard
- evening
- weekend

Table 4 =
- temp4
- temp1
- destination
- standard
- evening
- weekend

etc, etc....

mrhappiness

for example... 🙂

what do you store in those rate tables?

russellbcv

each ratetable contains the following:

Destination (i.e Germany)
Standard (i.e 3.7)
Evening (i.e 3.7)
Weekend (i.e 3.7)

A to Z list of destinations with different rates

mrhappiness

Then: Why do you have different tables?

Or in other words: Only have one rate table as described above

russellbcv

Each ratetable have different rates.

i.e

Ratetable1
Germany
3.7
3.7
3.7

Ratetable2
Germany
4.6
4.6
4.6

Ratetable3
Germany
5.0
5.0
5.0

mrhappiness

And how do you know which table you have to use?

russellbcv

I have assigned to each user the appropriate rates.

i.e

username = 1213411
ratetable = rateable1

username = 1213412
ratetable = rateable3

username = 1213413
ratetable = rateable2

etc, etc...

mrhappiness

All you SELECTs would be much easier if you had only one rate table
and I personally don't see any reason why you need to have that much rate tables...

russellbcv

ok, say had just the 1 table. Would this work?

destination, tableref,standard,evening,weekend

i.e.
germany,temp1,3.7,3.7,3.7
germany,temp2,3.7,3.7,3.7
germany,temp3,3.7,3.7,3.7
uk,temp1,3.7,3.7,3.7
uk,temp2,3.7,3.7,3.7
uk,temp3,3.7,3.7,3.7
spain,temp1,3.7,3.7,3.7
spain,temp2,3.7,3.7,3.7
spain,temp3,3.7,3.7,3.7

and In Table 1, I have the following:-

username,ratetable

i.e.
1213411,temp2
1213412,temp3
1213413,temp1

russellbcv

How would I extract the data for each user?

Does this look ok?

 <?php
$conn = @mysql_connect( "localhost", "xxx", "xxx" )
             or die( "Could not connect" );
$rs = @mysql_select_db( "cpanel", $conn )
            or die( "Could not select database" );
$sql = "select * from ratetables where username = '" . $_SESSION['username'] . "'"; 
$rs = mysql_query( $sql, $conn )
            or die( "Could not execute query" );
$list = "<table border=\"0\" cellpadding=\"5\">";
$list .= "<tr><th>Destination</th>";
$list .= "<th>Standard</th>";
$list .= "<th>Evening</th>";
$list .= "<th>Weekend</th></tr>";
while( $row = mysql_fetch_array( $rs ) )
{
  $list .= "<tr>";
  $list .= "<td>".$row["destination"]."</td>";
  $list .= "<td>".$row["standard"]."</td>";
  $list .= "<td>".$row["evening"]."</td>";
  $list .= "<td>".$row["weekend"]."</td>";
  $list .= "</tr>";
}
$list .= "</table>";
echo( $list );
?>
</p>

russellbcv

This will not work as I am only selecting * from a table which doesnt include the username.

$sql = "select * from ratetables where username = '" . $_SESSION['username'] . "'";

Does anybody know how I can do this?

mrhappiness

my suggestions

create these tables:

CREATE TABLE country (
  country_id int(10) NOT NULL auto_increment,
  country_name varchar(30) NOT NULL default '',
  PRIMARY KEY  (country_id),
  UNIQUE KEY country_name (country_name)
);

CREATE TABLE rating (
  rating_id int(10) NOT NULL auto_increment,
  user_id int(10) NOT NULL default '0',
  country_id int(10) NOT NULL default '0',
  standard float NOT NULL default '0',
  evening float NOT NULL default '0',
  weekend float NOT NULL default '0',
  PRIMARY KEY  (rating_id),
  UNIQUE KEY user_id (user_id,country_id)
);

CREATE TABLE userdata (
  user_id int(10) NOT NULL auto_increment,
  user_name varchar(30) NOT NULL default '',
  PRIMARY KEY  (user_id),
  UNIQUE KEY user_name (user_name)
);

fill them with the following data

INSERT INTO country (country_id, country_name) VALUES (1, 'UK');
INSERT INTO country (country_id, country_name) VALUES (2, 'Germany');
INSERT INTO country (country_id, country_name) VALUES (3, 'Spain');

INSERT INTO rating (rating_id, user_id, country_id, standard, evening, weekend) VALUES (1, 1, 1, 2, 2, 2);
INSERT INTO rating (rating_id, user_id, country_id, standard, evening, weekend) VALUES (2, 1, 2, 4, 4, 4);
INSERT INTO rating (rating_id, user_id, country_id, standard, evening, weekend) VALUES (3, 2, 1, 4.5, 4.6, 4.7);
INSERT INTO rating (rating_id, user_id, country_id, standard, evening, weekend) VALUES (4, 2, 3, 5.2, 5.4, 5.6);
INSERT INTO rating (rating_id, user_id, country_id, standard, evening, weekend) VALUES (5, 2, 2, 8.7, 3.9, 2.4);

INSERT INTO userdata (user_id, user_name) VALUES (1, 'foo');
INSERT INTO userdata (user_id, user_name) VALUES (2, 'bar');

have a look at this SELECT

SELECT
  u.user_name,
  c.country_name,
  r.standard, r.evening, r.weekend
FROM
  userdata u
  INNER JOIN rating r ON u.user_id = r.user_id
    INNER JOIN country c ON r.country_id = c.country_id

is there anything you can't do with my suggested database layout?

russellbcv

Wouldnt my table format work?

I just need to find the method for extracting the rates for each user.

mrhappiness

Originally posted by russellbcv
ok, say had just the 1 table. Would this work?

destination, tableref,standard,evening,weekend

i.e.
germany,temp1,3.7,3.7,3.7
germany,temp2,3.7,3.7,3.7
germany,temp3,3.7,3.7,3.7
uk,temp1,3.7,3.7,3.7
uk,temp2,3.7,3.7,3.7
uk,temp3,3.7,3.7,3.7
spain,temp1,3.7,3.7,3.7
spain,temp2,3.7,3.7,3.7
spain,temp3,3.7,3.7,3.7

and In Table 1, I have the following:-

username,ratetable

i.e.
1213411,temp2
1213412,temp3
1213413,temp1

??

This tableformat?
I don't understand it 🙂

What do temp1, temp2 and temp3 specify?
What reason does the column tableref exist for?
What reason does the column ratetable exist for?

russellbcv

This is my actual table setup

Table 1 "ratetables"
- ref
- destination
- standard
- evening
- weekend

Table 2 "ratetableuk"
- username
- ref

"ref" being the ratetable identifier (below)

[ref]
- DD44UKGOLD
- DD44UKCPS
- DD44UK1XXX
- DD44UK1XXX10pc

I have 4 different ratetables.

I have provided you below with actual table 1 examples:-

DD44UKGOLD,Germany,3.7,3.7,3.7
DD44UKCPS,Germany,4.0,4.0,4.0
DD44UK1XXX,Germany,5.0,5.0,5.0
DD44UK1XXX10pc,Germany,5.2,5.2,5.2

DD44UKGOLD,Spain,2.0,2.0,2.0
DD44UKCPS,Spain,2.0,2.0,2.0
DD44UK1XXX,Spain,2.0,2.0,2.0
DD44UK1XXX10pc,Spain,5.2,5.2,5.2

DD44UKGOLD,UK,2.0,2.0,2.0
DD44UKCPS,UK,2.0,2.0,2.0
DD44UK1XXX,UK,2.0,2.0,2.0
DD44UK1XXX10pc,UK,2.0,2.0,2.0

Does this make sense?

mrhappiness

Originally posted by russellbcv
"ref" being the ratetable identifier (below)

that would equal my user_id column in my ratetable but using an int is somewhat faster in comparison to a string
(and i don't need to have another column in my usertable)

I have 4 different ratetables.

What for?

Does this make sense?

I don't think so since you have multiple tables storing identical data