PDA

View Full Version : mySQL database help!!??!!



nikomou
11-20-2007, 02:32 PM
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
11-20-2007, 02:43 PM
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
11-20-2007, 03:20 PM
i just want to display them!

boogyman
11-20-2007, 03:30 PM
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
11-21-2007, 03:30 PM
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
11-21-2007, 03:58 PM
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
11-21-2007, 04:03 PM
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
11-21-2007, 04:25 PM
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...


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.