mySQL database help!!??!!

[nikomou]

Hey,

I have 2 tables in a database, which i would need to use together..

tblDeals has details of mobile phones and which tarrif they're available on, and tblTariff has details of the tariffs..

here's an example.

tblDeals
Nokia N95 | Orange | Orange12mxnet200 | Phone discription | More details

tblTariff
Orange12mxnet200 | 200 texts | 500 free mins | other details


I would like to do something similar to
Nokia N95 | Orange | 200 Texts | 500 free mins | other stuff

Would this be possible for multiple records?! e.g.
Nokia N95 | Orange | 200 Texts | 500 free mins | other stuff
Nokia N95 | O2 | 150 Texts | 500 free mins | other stuff
Nokia N95 | Voda | 250 Texts | 300 free mins | other stuff
Nokia N95 | Orange | 200 Texts | 100 free mins | other stuff
Nokia N95 | Orange | 250 Texts | 230 free mins | other stuff
Nokia N95 | Orange | 230 Texts | 400 free mins | other stuff
Nokia N95 | Orange | 130 Texts | 500 free mins | other stuff

does that make sense? thanks.

[boogyman]

are you trying to create a new table combining them together or are you just trying to display information from both tables?

the former would be possible though not very optimized.
the latter needs some type of join condition

SELECT tblDeals.field, tblDeals.field, tblTariff.field, tblTariff.field FROM tblDeals LEFT JOIN tblTariff ON tblDeals._key_ = 'Orange12mxnet200' WHERE _table_._field_ = '_condition_'

[nikomou]

i just want to display them!

[boogyman]

then use the select query I wrote out and substitute your details.

you are also going to need a server-side language, such as php/asp/python.
if you need more help on that search google for documentation and if you have any specific questions we'll be here to help ya

[nikomou]

hey, thanks. that code works great if i was only pulling up one record.. but i want to do something like this...

$dealsaddress = "localhost";
$dealsusername = "xxxx";
$dealspassword = "xxxx";
$dealsdb = "xxxx";
$dealsconn = mysql_connect($dealsaddress, $dealsusername, $dealspassword);
$dealsrs = mysql_select_db($dealsdb, $dealsconn);
$dealssql="SELECT mobdeals.handsetid, mobdeals.tariff, mobdeals.tariffid, tariff.contract FROM mobdeals LEFT JOIN tariff ON tariff.tariffid = 'o2op1500' WHERE mobdeals.handsetid = 'n95'";

$dealsrs = mysql_query($dealssql, $dealsconn);
$dealsj = 0;
for($resultnumber = 1; $dealsrow = mysql_fetch_array($dealsrs); ++$resultnumber) {
// Assigning names

$dealshandsetid = $dealsrow[handsetid];
$dealstariff = $dealsrow[tariff];
$dealstariffid = $dealsrow[tariffid];

$dealscontractlength = $dealsrow[contract];

  echo("$dealshandsetid | $dealstariff | $dealstariffid | $dealscontractlength |<br>"); 
} 
  $dealsj++; 
mysql_close();
?> 

but obviouly $dealscontractlength stays the same.. could i not do something like ON tariff.tariffid = '$dealsrow[tariffid]' ??

[boogyman]

yes you can... what I was trying to show was the generalization of how to create the query... I highlighted in blue the fields that I left entirely generic and in orange I put the value because thats what you had it as... you are free to include / exclude whatever variables you wish, I was just trying to show you the methodology.

[nikomou]

thanks, i used

LEFT JOIN tariff ON tariff.tariffid = mobdeals.tariffid 

and that seems to work great! if i were to do the same but using 3 tables, would i just use LEFT JOIN again?

thanks for all your help.

[boogyman]

well it depends on what type of join you would like, but if its just a simple join like you wanted for the original 2 then yes just use left join

SELECT table1.field FROM table1
     LEFT JOIN table2 ON table2.field = table1.field
     LEFT JOIN table3 ON table3.field = tableX.field
     WHERE tableX = '_condition_'
     ORDER BY table.field ASC/DESC

I added ORDER BY incase you wanted to specify how the results are pulled...

Edit:
As a side note the forums over at mysql.com are not very active, so if you did have a question I would suggest using a different site, although they do have good documentation and guides.