phpmyadmin Question posted in
The official documentation can be found here.

In MySQL database how can I create a different key for a field that repeats more than once in a row by php and json – Phpmyadmin

I created a relationship between a table and another table whose fields are repeated twice in a row and the result was new when the experiment on phpMyAdmin but when the experiment on PHP and printed as Gausson, the results were not displayed as required, where he printed a field twice and did not print the other field

<?php
  include_once("include/config.php");
  header('Content-Type:text/html; charset=utf-8');
  $sql = "
  SELECT po.post_id
       , po.title_post
       , po.user_id
       , po.admin_id 
       , us.id
       , us.fname
       , us.lname 
       , aus.id
       , aus.fname
       , aus.lname 
    FROM postmy po 
    JOIN dataUser us 
    JOIN dataUser aus 
      ON po.user_id = us.id 
     AND po.admin_id = aus.id 
   ORDER 
      BY po.post_id ASC
  ";
$r = mysqli_query($conn,$sql);
$result = array();
 while($row = mysqli_fetch_array($r)){
   array_push($result,array(
  "post_id"=>$row['post_id'],
  "title_post"=>$row['title_post'],
  "user_id"=>$row['user_id'],
  "admin_id"=>$row['admin_id'],
  "us.id"=>$row['id'],
  "us.fname"=>$row['fname'],
  "us.lname"=>$row['lname'],
  "aus.id"=>$row['id'],
  "aus.fname"=>$row['fname'],
  "aus.lname"=>$row['lname']        
 ));    
 }
 echo json_encode(array('result'=>$result));
 mysqli_close($conn);   
?>
Tags:

3

Answers


  1. 0

    An observation:

    This…

    FROM postmy po 
JOIN dataUser us 
JOIN dataUser aus 
  ON po.user_id = us.id 
 AND po.admin_id = aus.id

    …would normally be written like this…

    FROM postmy po 
JOIN dataUser us 
  ON us.id = po.user_id 
JOIN dataUser aus 
  ON aus.id = po.admin_id
    Login or Signup to reply.
  2. 0

    You should use alias for the repated columns 

      $sql = "SELECT po.post_id
      , po.title_post
      , po.user_id
      , po.admin_id 
      , us.id
      , us.fname
      , us.lname 
      , aus.id ausid
      , aus.fname ausfname
      , aus.lname auslname 
    FROM postmy po 
    INNER JOIN dataUser us ON po.user_id = us.id  
    INNER  JOIN dataUser aus  ON po.admin_id = aus.id
   ORDER BY po.post_id ASC";

$r = mysqli_query($conn,$sql);
....


"post_id"=>$row['post_id'],
"title_post"=>$row['title_post'],
"user_id"=>$row['user_id'],
"admin_id"=>$row['admin_id'],
"us.id"=>$row['id'],
"us.fname"=>$row['fname'],
"us.lname"=>$row['lname'],
"aus.id"=>$row['iduas'],
"aus.fname"=>$row['fnameaus'],
"aus.lname"=>$row['lnameaus']

    (and apply the specific ON to each table )

    Login or Signup to reply.
  3. 0

    You need to use column aliases or your PHP will overwrite one of those columns values because an index can only hold one value. Example https://3v4l.org/jnMvj

    SELECT po.post_id
   , po.title_post
   , po.user_id
   , po.admin_id 
   , us.id
   , us.fname
   , us.lname 
   , aus.id as au_id
   , aus.fname as au_fname
   , aus.lname as au_lname
FROM postmy po 
JOIN dataUser us 
JOIN dataUser aus 
  ON po.user_id = us.id 
 AND po.admin_id = aus.id 
ORDER 
  BY po.post_id ASC

    Then access the au_ fields in the fetch.

    while($row = mysqli_fetch_array($r)){
   array_push($result,array(
  "post_id"=>$row['post_id'],
  "title_post"=>$row['title_post'],
  "user_id"=>$row['user_id'],
  "admin_id"=>$row['admin_id'],
  "us.id"=>$row['id'],
  "us.fname"=>$row['fname'],
  "us.lname"=>$row['lname'],
  "aus.id"=>$row['au_id'],
  "aus.fname"=>$row['au_fname'],
  "aus.lname"=>$row['au_lname']        
 ));

    Alternatively you could use the numeric indices but that is volatile in my opinion. If you remove/add a column all indices may need to be changed.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search