confused about sql join

Question

i have this VIEW MODAL it views the data from the table by ID my problem is if i add an update (as you can see in the image) it doesnt show to in the table updatedby and update: i tried joining in mysql but it only fetch the dateupdated column in my 2nd table. here is my second add_update table and this is my first table document table the foreign key is document_id as you can see.

here is my query in mysql

$query = "SELECT document.document_id, document.document_name, document.document_type,
   document.document_from,document.document_to,document.document_recieved, document.document_date,document.document_dater,
   document.document_status,document.document_signatories,document.document_remarks,document.document_encoded, add_update.document_date_update,
   add_update.document_update,add_update.document_updatedby FROM document LEFT JOIN add_update ON document.document_id = add_update.document_id WHERE
    document.document_id = '".$_POST["document_id"]."'";
  $result = mysqli_query($connection, $query);

Your code is [wide open to SQL injection](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Machavity, Feb 14 '18 at 02:56
Yes but thats not his concern. Give a solution query then you talk about the escaping issue on its code — Kenneth Sunday, Feb 14 '18 at 03:01
May be In your view have some typo in "document_update" & "document_update" please check .. — Mahesh K S, Feb 14 '18 at 03:41

score 0 · Answer 1 · answered Feb 14 '18 at 03:32

0

I believe you have to trace the code. Firstly you can do

print_r($result)

to check whether the query contains all the columns. And then if it already gives you the right result please check your form, to check whether it outputs the right variable.

It is also a good practice to use parameter binding:

$query = $mysqli->prepare('SELECT document.document_id,  
  document.document_name, 
  document.document_type, 
  document.document_from,
  document.document_to,
  document.document_recieved, 
  document.document_date,
  document.document_dater,    
  document.document_status,
  document.document_signatories,
  document.document_remarks,
  document.document_encoded,
  add_update.document_date_update, 
  add_update.document_update, 
  add_update.document_updatedby 
  FROM document 
  LEFT JOIN add_update ON document.document_id = add_update.document_id 
  WHERE
  document.document_id = ?');
$query->bind_param('s', $_POST["document_id"]);
$result = $query->execute();

answered Feb 14 '18 at 03:32

shulha yahya

151
1
2
6

I guess, you should use "AND document.document_id = ?');" instead of WHERE. – Don'tDownvoteMe Feb 14 '18 at 03:36
putting document id inside "where condition" will give you results of rows which only match the ID. While putting it inside "join condition", will give you all rows, however the row which doesn't match the document id will not be joined to add_document table. – shulha yahya Feb 14 '18 at 03:45
@VoltzV please also put your mysqli connection $mysqli = new mysqli("host", "my_user", "my_password", "dbname"); – shulha yahya Feb 14 '18 at 03:47
in the parent table (which is the 'document' table) i have document_id as the primary key and in the child table (which is the 'add_update' table) i have the document_id as the foreign key. they have matching document_id but my query is not working. – VoltzV Feb 14 '18 at 03:47
hi @VoltzV, would you explain me what the meaning of the query doesnt work? Could you show us the print_r($result) – shulha yahya Feb 14 '18 at 03:53
in my add_update table every time i add an update the foreign key (document_id) becomes "0" – VoltzV Feb 14 '18 at 06:18

confused about sql join

1 Answers1