Joning tables

[yandoos]

Hello I was hoping some help with joining tables....

 

I have joined tables but have found strange results so i assume I am doing it wrong. Whats happening is that even though I am pulling the query from the product table if there are NULL data in the mem_id field the row is not being pulled. I would add this is using a php while loop, using phpmyadmin and pure mysql it works.

SELECT * FROM product
join distribution ON product.pid = distribution.pid
join plus_signup ON distribution.mem_id = plus_signup.mem_id

Have a look at this phpmyadmin screen shot.

 

 

The records for pid 3, and 4 are not being outputted when I used a php while loop. I need it so that all the records will be pulled even if the mem_id is null. Is there a way I can do this?

 

Thank you

Edited July 29, 2015 by yandoos

[Barand]

use LEFT JOIN

[yandoos]

Sorry I was using left join. The results are the same as in the attachment and on page. I was trying all the other joins to see if it would work to no avail.

[yandoos]

<?php
$per_page='10';

if (isset($_GET['page'])) {

$page = $_GET['page'];

}else {

$page='1';}

  // Page will start from 0 and Multiple by Per Page
$start_from = ($page-1) * $per_page; 
	 
	 
$connection = mysqli_connect($dbhost_name, $username, $password, $database);
 $finder1 = mysqli_query($connection,"SELECT * FROM product left join distribution ON product.pid = distribution.pid
 left join plus_signup ON distribution.mem_id = plus_signup.mem_id LIMIT $start_from, $per_page")  or die (mysql_error());
 
  $connection = mysqli_connect($dbhost_name, $username, $password, $database);
 $counter1 = mysqli_query($connection,"SELECT * FROM product left join distribution ON product.pid = distribution.pid
 left join plus_signup ON distribution.mem_id = plus_signup.mem_id")  or die (mysql_error());
 
  // Count the total records
$total_records = mysqli_num_rows($counter1);

//Using ceil function to divide the total records on per page
$total_pages = ceil($total_records / $per_page);
 
 }

echo '<table class="admintable" width="1000" style="padding:0px 10px 0px 10px;" border="0">'; 


while($builder1 = mysqli_fetch_array($finder1))
    {    

$pid = mysqli_real_escape_string($connection, $builder1['pid']);
echo $pid; echo '<br/>';

     
    echo'<tr><td valign="top">'; ?>
    
    
    <table border="0"><tr><td width="100">
    <form action="update_image.php" enctype="multipart/form-data" method="post">	
     <input name="pid" type="hidden" value="<?php echo $builder1['pid'];?>">	   
     Name:  </td><td><input name="productname" type="text" value="<?php echo $builder1['productname'];?>"></td></tr><tr>
      <td>Price: </td><td><input name="price" type="text" value="<?php echo $builder1['price'];?>"></td></tr><tr>
      <td> Stock: </td><td><input name="stock" type="text" value="<?php echo $builder1['stock'];?>"></td></tr><tr>
      
      <?php 
      
       
	  
	   echo '<tr><td colspan="2">';
	   
	   
	   if ($builder1['name'] != NULL){
	   
	    echo 'Product assigned to: <a href="http://dusousbois.co.uk/distributions.php?id=';
	    echo $builder1['pdid'];
	    echo '">';
	    echo $builder1['name'];
	    echo '</a>';
	  
	  
	   } else if ($builder1['name'] == NULL){
	   echo 'Product ready for assigment'; 
	   }
	   
	  
	  
  
	  
	  
	    echo'</td></tr>';
    
      ?>
      
      
      
      
       <td colspan="2">Description:<br/><textarea name="description" cols="40" rows="8"><?php echo $builder1['description'];?></textarea></td></tr><tr>   
       <td><input name="submit" type="Submit" value="Edit Product"/></td>
       
       <td>  <?php if ($builder1['stock'] == '1') {
       echo $pid;
        
	  echo '<a href="delete_products.php?id=';
	  echo $builder1['pid'];
	  
	  echo'">Delete</a>';
	  } else if ($builder1['stock'] == '0') {
	  echo 'This product can not be deleted as it has been assigned to a gallery';
	  
	  }  
	  
	  echo '</td></tr>';}
	  
	  
	  echo'
	  </table>'; 

Here is a snipped of the code in question. The problem is that the $pid is not outputting when the mem_id is NULL

[Barand]

Don't use "SELECT * ", especially when using joins. Specify the fields you want. Where you have the same field in two table (like pid) specify the table or alias with the field eg SELECT product.pid, .....

 

You only need to connect once per script unless you are connecting to two servers.

 

 

$pid = mysqli_real_escape_string($connection, $builder1['pid']);

real_escape_string() is for sanitizing input BEFORE using it in a query, not afterwards.

[yandoos]

Thank you that has done it - specifying the fields.

 

 Thank you for explaining about real_escape_string() too, I didn't know that.

 

Thank you very much