CODEWITHSUNDEEP
phpmysqlmysqli
Vipul Singh
Vipul Singh

Reputation: 163

get data from two tables and collect them in array and count rows of one table result

I had two tables named fixture_list and OneRecord where fixture_list has 3 columns named Team1, Team2, player whereas OneRecord has columns like Team1, Team2, id, status so here I want to select all the data of fixture_list and put them into array whereas I want to count the number of rows based on this where condition fixture_list.Team1=OneRecord.Team1 and fixture_list.Team2=OneRecord.Team2 from OneRecord, actually I am not able to distinguish between selecting the data and putting it into the array and counting the number of rows because I just want to count the rows of one record only. I know things like joins and mysqli_multi_query() can be performed but do not know how...I want to perform this thing in single query please help.

<?php

require_once('connect.php');

$sql = "select * from fixture_list";

if($res = mysqli_query($con,$sql)){
$result = array();

 while($row = mysqli_fetch_array($res)){

array_push($result,
array('players'=>$row[3],
 'team1_name'=>$row[1],
 'team2_name'=>$row[2]
  ));
  }
mysqli_free_result($res);
}
echo json_encode (array("list"=>$result));
mysqli_close($con);
  ?>

Upvotes: 0

Views: 79

Answers (1)

Rolfie
Rolfie

Reputation: 110

You can use a sub_query to do this. I don't understand your question perfectly (try using capitals and punctuation marks), but this should be probably the format to use.

$sql = "SELECT `fixture_list`.`team1_name`,
               `fixture_list`.`team2_name`,
       (
          SELECT count(`OneRecord`.`Team1`) as `total` 
          FROM `OneRecord`
          WHERE `OneRecord`.`Team1` = `fixture_list`.`Team1`
          AND `OneRecord`.`Team2` = `fixture_list`.`Team2`
       )  as `Total_Players`
FROM `fixture_list`
GROU BY `fixture_list`.`id`";

$result = array(); // keep this outside the while loop to ensure the array is made
if($res = mysqli_query($con,$sql)){
  while($row = mysqli_fetch_array($res, MYSQLI_ASSOC)){
    $result[] = $row;
  }
}

This would give you a $result array as follows:

array(
 'team1_name' => 'TEAMNAME1',
 'team2_name' => 'TEAMNAME2',
 'Total_Players'  => INTEGER_VALUE
)

The INTEGER_VALUE is the number of rows from the subquery. You can edit the WHERE part of the subquery to your liking

Then you can do this to create a json object:

echo json_encode($result);

This will echo it, which is ideal if you use it with an Ajax function for example.

Upvotes: 1

Related Questions