Query on 2 Arrays
-
Hi there, I have 2 MySQL databases on 2 different servers and using php am pulling an array from each of them as follows... From Server 1:
$query_Recordset1 = "SELECT login.`id`, login.username, tbl_countries.Country
FROM login, tbl_countries
WHERE login.Country = tbl_countries.id ";
$Recordset1 = mysql_query($query_Recordset1, $NU_Conn) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);From Server 2:
$query_Recordset2 = "SELECT newusb.ID, newusb.artist, newusb.title, tbl_ply_newusb.Week_14
FROM newusb, tbl_ply_newusb
WHERE tbl_ply_newusb.Song_ID = newusb.ID
ORDER BY tbl_ply_newusb.Week_14 DESC";
$Recordset2 = mysql_query($query_Recordset2, $Chartmenu) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);Between the 2 arrays I am trying to find the most played artists by Country, my thought is to have some sort of join between the artist name as the artist.id does not exist in the second array. Do I create a 3rd array? if so how? Any help on how to bring in a 3rd array based on the 2 existing arrays would be appreciated. Many Thanks Ray
-
Hi there, I have 2 MySQL databases on 2 different servers and using php am pulling an array from each of them as follows... From Server 1:
$query_Recordset1 = "SELECT login.`id`, login.username, tbl_countries.Country
FROM login, tbl_countries
WHERE login.Country = tbl_countries.id ";
$Recordset1 = mysql_query($query_Recordset1, $NU_Conn) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);From Server 2:
$query_Recordset2 = "SELECT newusb.ID, newusb.artist, newusb.title, tbl_ply_newusb.Week_14
FROM newusb, tbl_ply_newusb
WHERE tbl_ply_newusb.Song_ID = newusb.ID
ORDER BY tbl_ply_newusb.Week_14 DESC";
$Recordset2 = mysql_query($query_Recordset2, $Chartmenu) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);Between the 2 arrays I am trying to find the most played artists by Country, my thought is to have some sort of join between the artist name as the artist.id does not exist in the second array. Do I create a 3rd array? if so how? Any help on how to bring in a 3rd array based on the 2 existing arrays would be appreciated. Many Thanks Ray
how two tables are related?
-
how two tables are related?
Hi, The tables are related only by username. Regards Ray
-
Hi there, I have 2 MySQL databases on 2 different servers and using php am pulling an array from each of them as follows... From Server 1:
$query_Recordset1 = "SELECT login.`id`, login.username, tbl_countries.Country
FROM login, tbl_countries
WHERE login.Country = tbl_countries.id ";
$Recordset1 = mysql_query($query_Recordset1, $NU_Conn) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);From Server 2:
$query_Recordset2 = "SELECT newusb.ID, newusb.artist, newusb.title, tbl_ply_newusb.Week_14
FROM newusb, tbl_ply_newusb
WHERE tbl_ply_newusb.Song_ID = newusb.ID
ORDER BY tbl_ply_newusb.Week_14 DESC";
$Recordset2 = mysql_query($query_Recordset2, $Chartmenu) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);Between the 2 arrays I am trying to find the most played artists by Country, my thought is to have some sort of join between the artist name as the artist.id does not exist in the second array. Do I create a 3rd array? if so how? Any help on how to bring in a 3rd array based on the 2 existing arrays would be appreciated. Many Thanks Ray
I am having trouble understanding what your table fields mean. I am assuming that Week_14 is the number of times a song was played in Week_14? In that case, you will want to use MAX(Week_14) with GROUP BY Country to get what the greatest Week_14 is for each Country (it will give you one row per country). I would need to know more about the table structure to give you more specific help than that.