0

I have two tables the first one researchprojects which containes Publications and those are comma separated numberes, in the other table publi i have Pub_id where the same values are not comma separted, and other columns like Pub_year my question is how can i link those two tables and output Pub_id ,Pub_year values ?

researchprojects table

Publications
800, 900, 1000
1000, 1200, 1300

publi table

Pub_id  Pub_year
800     2015
900     2016
1000    2017
1200    2016
1300    2012
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
usethe23
  • 155
  • 6

3 Answers3

0

Here is exactly how you can do it :

LINKING TWO TABLES IN MYSQL :

It's very easy in order to link two tables in MySQL.You need to have at least one column (field) in each table similar to each other to relate them to each other.

For Example :

In your case you need to have your both table structures like this way :

Table : researchprojects => id,publications

Table : publi => publi_id,publi_year

YOUR CASE:

As in your case you have all id's mentioned in your one row as : 

    researchprojects 
         |
         V
---------------------------
publications
800,900,1000
---------------------------

       publi 
         |
         V
---------------------------
publi_id     publi_year
800          2012
800          2013
900          2014
900          2015
1000         2016
1000         2017
---------------------------

CODE TO SHOW THE TABLES DATA OUTPUT:

<?php
// Assuming that $publications will hold all fetched values from researchprojects table
// Also assuming that $publi will hold all fetched data from publi table
$publi_ids = explode(",",$publications[0]['publications']);
//Now you have all ids in array in $publi_ids
//So moving forward with the code

foreach ($publi_ids  as $publi_id) {
echo "<h1>".$publi_id."</h1>";
$id = $publi_id;
$query = mysqli_query($mysqli,"SELECT * FROM publi WHERE publi_id='$id'");

$publi_data = array();
while ($rows = mysqli_fetch_array($query)) {
  $publi_data[] = $rows;
}

foreach ($publi_data as $publi_row) {
echo "Year :".$publi_row['publi_year']."</br>";
echo "</br></br></br>";
}
echo "</br></br></br></br>";  
} 
?>

OUTPUT :

800
Year : 2012
Year : 2013

900
Year : 2014
Year : 2015

1000
Year : 2016
Year : 2017
Umair Shah
  • 2,305
  • 2
  • 25
  • 50
  • Thank you for your answer but the the id of publications table and the publi_id of publi table don't have the same values because publications have comma separted values so how can i link them ? – usethe23 May 06 '16 at 09:28
  • Well..Instead I think you need to add one publications per row in your publications table,that way it's better putting all of them in one row is not a good practice and you will have alot of problems coming in your coding next then..! – Umair Shah May 06 '16 at 09:31
  • You are right, it would've been much easier but i can't separate the values because they are related to other columns so the structure can't be changed i have to stick with the comma separated values unfortunately – usethe23 May 06 '16 at 09:37
0

I've solved this ! 

$query = "SELECT * FROM researchprojects";
$result = mysqli_query($con, $query);
while ($val = mysqli_fetch_array($result))
{
  $Id = $val['Id'];
  $Publications = $val['Publications'];
  $Publication_seprated = explode(',', $Publications);

  foreach($Publication_seprated as $Publication_seprated)
  {
  $query2 = "SELECT * FROM publi WHERE Pub_id = '$Publication_seprated'";
  $result2 = mysqli_query($con, $query2);
  while ($val2 = mysqli_fetch_array($result2))
        {
           $pub_id = $val2['pub_id'];
           $pub_year = $val2['pub_year'];

           echo $pub_year;
          } // while loop


    } // foreach

   } // while loop
usethe23
  • 155
  • 6
-2

Create an array which will store pub_id and pub_year in array in the form of key and value pair

<?php 

$pub = [800 => 2015, 900 => 2016, 1000 => 2017];
foreach ($pub as $pubId => $pubYear) 
{
    echo "<td>{$pubId}, {$pubYear}</td>";
}

?>
Deepak Adhikari
  • 419
  • 2
  • 4