make a count with two tables

Question

make a count with two tables

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

ok I already know the topic of the while and all that I know how to get everything from a table, Now what I want is the following: I have two tables one " friends " another " main " in this case I do not want to print anything, the table friends contains the id of the users who are example friends "of" and "for" the other table only contains a field id_user without counting the auto increment there is an action that saves the id of the users in the "main" table what necito is to make a query that tells me how many id there is in the "main" table that are my friends example of the code for better understanding

my query

<?php
    include"conexion.php";
     $yo=$_SESSION["id"];
           //hago una consulta en la tabla amigos donde de sea mi id 
      $ami=mysqli_query($conexion,"SELECT * FROM amigos WHERE de='$yo' ");

      while  ( $obtener_amigos_id=mysqli_fetch_assoc($ami)){
          //aqui saco todos los id que son mis amigos, nose si esta consulta while 
          este correcta a sinceridad
        $id_de_amigos=$obtener_amigos_id["para"];
        }
          //otra consulta en la tabla principal para saver cuantos amigos mios hay aqui
           //tengo 9 amigos y en esta tabla hay 5 amigos mios
        $mostrar_cantidad_amigos= mysqli_query($conexion,"SELECT * FROM principal 
        WHERE id_user='".$id_de_amigos["para"]."' ");

         $cantida_id_amigos=mysqli_num_rows($mostrar_id_amigos);

       //solo me arroja uno y tengo 9 amigos, de esos 9 el resultado esperado es 5
            echo $cantida_id_amigos;
     ?>

I would appreciate a help because I am not receiving the expected result

php mysqli

asked by andy gibbs 03.06.2018 в 05:58

source

2 answers

0

enter the description of the image here your code was useful to me I just made some changes and got what I wanted just modify it like this:

  <?php
      include"conexion.php";
       $yo=$_SESSION["id"];

        $sql_1=mysqli_query($conexion,"SELECT * FROM main_zone WHERE id_user!='$yo' ");
        //Variable que contará el número de amigos.
        $contadorAmigos = 0;
        while  ( $row = mysqli_fetch_assoc($sql_1)){
            $sql_2 = mysqli_query($conexion,"SELECT * FROM amigos WHERE de ='$yo' AND para='".$row["id_user"]."' ");
            //Si se encuentra una ID para ese usuario se suma uno al contador.
            if ($sql_2) {
                $contadorAmigos++;

            }
        }
        echo $contadorAmigos;
        //obtuve 5 el valor esperado ya que hay 5  conectados en esta tabla
    ?>

answered by 03.06.2018 в 15:39

Android Studio 3.1.2 Emulator: Process finished with exit code -1073741511 (0xC0000139) Wordpress Cloudflare Cache - I have to press f5 every time I enter my page

score 0 · Accepted Answer

You can try with:

<?php
include"conexion.php";
    $yo=$_SESSION["id"];

    $sql_1=mysqli_query($conexion,"SELECT * FROM amigos WHERE de='$yo' ");
    //Variable que contará el número de amigos.
    $contadorAmigos = 0;
    while  ( $row = mysqli_fetch_assoc($sql_1)){
        $sql_2 = mysqli_query($conexion,"SELECT * FROM principal WHERE id_user='".$row["para"]."' ");
        //Si se encuentra una ID para ese usuario se suma uno al contador.
        if ($sql_2) {
            $contadorAmigos++;
        }
    }

    echo $contadorAmigos;
?>

What I do not understand is that you do not COUNT() from the SQL query to know the number of friends you have.

SELECT COUNT(*) AS num_amigos FROM amigos WHERE de=$yo;

This query instead of returning the records would return something type:

And that number can be extracted with:

$row = mysqli_fetch_assoc($sql)
$numero = $row['num_amigos'];