Sql INNER JOIN无法正常工作，如何使用Jquery / Ajax在html表单上显示上传的文件

Question

I want to get results from 2 table using SQL INNER JOIN but it's only showing 1st result not others one. If i select other data then it's showing following error message:

Warning: Invalid argument supplied for foreach() in D:\software installed\xampp\htdocs\contact-management\getContactDetails.php on line 14
[]

Description of my functionality:

I have 2 table in my database.

1. contact_details:

   cdid, family_name, given_name, work_phone, mobile_phone, email, email_private, cid
   

2. contact_docs:

   docid, cdid, file_name, file_des
   

I'm showing all contacts (only family_name) from database. After select each contacts then it's showing/loading all contact details using Jquery/ajax which is showing on a HTML FORM. So now it's showing contact_details data on the html input type = text field. But I also need show contact_docs data which is actually files.

So when I send a request to the server using jQuery/ajax I've to use SQL inner join query to get the result from both table based on id (cdid). But unfortunately my inner join query is not working properly. It's not showing all contact details data on the html form if I select different contacts. Only showing 1st result.

My questions are:

1. How do I fix this inner join query?

2. How do I show the uploaded file on the form (file link would be better) when whole form is load with all data from 2 table ?

Note: I can successfully edit/insert the data to database but issue about showing the data with file.

This is my code:

Html page:

<div id="showContactsDetails">
<h2>Individual Record Details</h2>
    <div style=" visibility:hidden;" id="visiable">    
    <span class="mandatory"><sub>* Required</sub></span>
    <!--success update -->  
    <div id="success"></div>

<form action="" method="post" enctype="multipart/form-data" id="all_contact_details">
<table width="450" border="0" cellspacing="0" cellpadding="0">  
  <input type="hidden" name="cdid" id="cdid"/>
  </tr>     
<tr>
    <td>Company Name</td>
    <td><input type="text" name="company_name" id="company_name" disabled="disabled"/></td>
  </tr>
  <tr>
    <td>Family name</td>
    <td><input type="text" name="family_name" id="family_name"/></td>
  </tr>
  <tr>
    <td>Given name</td>
    <td><input type="text" name="given_name" id="given_name"/></td>
  </tr>
  <tr>
    <td>Work Phone</td>
    <td><input type="text" name="work_phone" id="work_phone"/></td>
  </tr>
  <tr>
    <td>Mobile Phone</td>
    <td><input type="text" name="mobile_phone" id="mobile_phone"/></td>
  </tr>
  <tr>
    <td>Email address</td>
    <td><input type="text" name="email" id="email"/></td>
  </tr>
  <tr>
    <td>Private email address</td>
    <td><input type="text" name="email_private" id="email_private"/></td>
  </tr>
  <tr>
    <td>Upload your document</td>
    <td><input type="text" name="file_des_1" id="file_des1" placeholder="short description of your document" class="shor"/><span class="mandatory"><sup>*</sup></span></td>
  <tr>
    <td></td>  
    <td align="left"><input name="file1" type="file" id="file" class="file"/></td>
    <span class="file_des_1"></span>
  </tr>

  <tr>
    <td></td>
    <td><input type="text" name="file_des_2" id="file_des2" placeholder="short description of your document" class="shor"/><span class="mandatory"><sup>*</sup></span></td>
  <tr>
    <td></td>  
    <td align="left"><input type="file" name="file2" id="file_2" class="file"/></td>
  </tr>

  <tr>
    <td></td>
    <td><input type="text" name="file_des_3" id="file_des3" placeholder="short description of your document" class="shor"/><span class="mandatory"><sup>*</sup></span></td>
  <tr>
    <td></td>  
    <td align="left"><input type="file"  name="file3" id="file_3" class="file"/></td>
  </tr>

  <tr>
    <td></td>
    <td><input type="text" name="file_des_4" id="file_des4" placeholder="short description of your document" class="shor"/><span class="mandatory"><sup>*</sup></span></td>
  <tr>
    <td></td>  
    <td align="left"><input type="file"  name="file4" id="file_4" class="file"/></td>
  </tr>

  <tr>
    <td>&nbsp;</td>
    <td><input type="button" name="submit" value="Update Details" class="submit" id="upload"/></td>
  </tr>  
</table>
</form> 
</div> 
</div><!--showContactsDetails-->

jQuery/Ajax page:

//edit the form....................
<script>
$('body').on('click', '#upload', function(e){
    e.preventDefault();
    var formData = new FormData($(this).parents('form')[0]);


    $.ajax({
        url: 'editContactDetails.php',
        type: 'POST',
        xhr: function() {
            var myXhr = $.ajaxSettings.xhr();
            return myXhr;
        },
        success: function(data){                    
            $("#success").html(data);
            document.getElementById("all_contact_details").reset();


        },
        data: formData,
        cache: false,
        contentType: false,
        processData: false
    });
});    

//load the html form with all contact details..................................................
function getDetails(id) {
        id = id;        
        $.ajax({        
                url: 'getContactDetails.php',
                type: 'post',             
                data: {'id' : id},
                dataType : 'json',
                success: function( res ) {
                    document.getElementById("visiable").style.visibility = "visible";
                    console.log(res);
                    $.each( res, function( key, value ) {
                        console.log(key, value);
                        $('input[type=text][name='+key+']').val(value);
                        $('input[type=hidden][name='+key+']').val(value);
                        $('textarea[name='+key+']').val(value);

                    });

                }            
        });
    }


</script>

Php page (getContactDetails.php):

<?php
ob_start();
require_once("config.php");
$id  = (int) $_POST['id'];

$sql = mysql_query("SELECT contact_details.cdid, contact_details.family_name, contact_docs.file_name FROM contact_details INNER JOIN contact_docs ON contact_details.cdid = contact_docs.cdid WHERE contact_docs.cdid = '$id'");

$res =  mysql_fetch_array($sql);
$data = array();
foreach( $res as $key => $value ) { 
    $data[$key] = $value;   
}
echo json_encode($data);
?>

I'm a new in web development field. So may be my code is wrong that's why your help is greatly appreciated :)

Answer 1

I think you want something like this... (using deprecated API)

<?php
require_once("config.php");

$id  = (int) $_GET['id'];

$query = "
"SELECT c.cdid
      , c.family_name
      , d.file_name 
   FROM contact_details c
   JOIN contact_docs d 
     ON c.cdid = d.cdid 
  WHERE d.cdid = $id
";

$result = mysql_query($query);

$data = array();

while ($res =  mysql_fetch_array($result)){
foreach( $res as $key => $value ) {
    $data[$key][] = $value;
}
}
echo json_encode($data);   

?>