Jump to content

How to get the name attached to an id in another table mysql php

bambinou1980

By bambinou1980
in Applications

 Share
Go to solution Solved by scootstah,

Recommended Posts

bambinou1980 Member

bambinou1980

Posted
Posted

Hello,

 

I am having a little problem.

 

I have a listing table and would like to output the "name and surname" associated with the reseller id. I can currently output the reseller id, it is working fine but how to get his name from the associated table please(foreign key).

 

Here is my query:

			    <?php
		        // Attempt select query execution
		        if($result = mysqli_query($connection, $sql)){
		        if(mysqli_num_rows($result) > 0){
                echo "<table class='table table-striped'>";
                echo "<tr>";
                echo "<th class='col-md-1 text-center'>id</th>";
                echo "<th class='col-md-1 text-center'>Order ID</th>";
                echo "<th class='col-md-1 text-center'>Company</th>";
                echo "<th class='col-md-1 text-center'>Due Date</th>";
		echo "<th class='col-md-1 text-center'>Product</th>";
                echo "<th class='col-md-1 text-center'>Unit Price</th>";
                echo "<th class='col-md-1 text-center'>Quantity</th>";
		echo "<th class='col-md-1 text-center'>Order Total</th>";
		echo "<th class='col-md-5 text-center'>Requirements</th>";
		echo "<th class='col-md-1 text-center'>Reseller</th>";
		echo "<th class='col-md-1 text-center'>Order Status</th>";
                echo "<th class='col-md-1 text-center'></th>";
				
				
                while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){
			    $id = htmlspecialchars($row["id"]);
				$cust_order_id = $row["cust_order_id"];
				$cust_company = $row["cust_company"];
				$due_date = $row["due_date"];
				$product = $row["product"];
				$quantity = $row["quantity"];
				$price = $row["price"];
				$requirements = $row["requirements"];
				$cust_order_total = $row["cust_order_total"];
				$resellers = $row["resellers_id"];
				$order_status = $row["order_status"];
				
				
                echo "<tr class='text-center'>";
                echo "<td>" . htmlspecialchars($id) . "</td>";
                echo "<td>" . htmlspecialchars($cust_order_id) . "</td>";
                echo "<td>" . htmlspecialchars($cust_company) . "</td>";
                $new_date = date("d-m-Y", strtotime($due_date));
                echo "<td>" . $new_date . "</td>";
		echo "<td>" . htmlspecialchars($product) . "</td>";
                echo "<td>" . htmlspecialchars($price) . "</td>";
		echo "<td>" . htmlspecialchars($quantity) . "</td>";
                echo "<td>" . htmlspecialchars($cust_order_total) . "</td>";
		echo "<td>" . htmlspecialchars($requirements) . "</td>";
		echo "<td>" . htmlspecialchars($resellers) . "</td>";
		echo "<td>" . htmlspecialchars($order_status) . "</td>";
		echo "<td><a class='btn btn-default' href='update-orders.php?order=$id' role='button'>Edit</a><a class='btn btn-danger' href='delete-orders.php?order=$id' onclick=\"return confirm('Are you sure you want to delete this order?');\" role='button'>Delete</a></td>";
                echo "</tr>";
                     }
                echo "</table>";
               // Close result set
               mysqli_free_result($result);
              }else{
              echo "Not Results to Show, get more customers!!";
				}}
			    ?>

20rshhj.jpg

bambinou1980 Member

bambinou1980

Posted
  • Author
Posted

Oups sorry....ahahah

 

Here it is:

I tried this but the sql does not work.....
SELECT 'orders'.*, 'resellers'.'name' FROM 'orders' INNER JOIN 'resellers' ON 'orders'.'resellers_id' = 'resellers'.'id' WHERE 'orders'.'id' = '46';

I am getting
SELECT 'orders'.*, 'resellers'.'name' FROM 'orders' INNER JOIN 'resellers' ON 'orders'.'resellers_id' = 'resellers'.'id' WHERE 'orders'.'id' = 46
 LIMIT 0, 25 


MySQL said: Documentation 
 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.*, 'resellers'.'name' FROM 'orders' INNER JOIN 'resellers' ON 'orders'.'reselle' at line 1
bambinou1980 Member

bambinou1980

Posted
  • Author
Posted (edited)

I also tried this but it is a fail too..it returns nothing....query is ok but nothing is returned at all.

SELECT resellers_id, reseller_name, reseller_surname FROM orders T1 INNER JOIN resellers T2 ON T1.resellers_id = T2.reseller_name
Edited by bambinou1980
  • Solution
scootstah Member

scootstah

Posted
  • Solution
Posted

Oups sorry....ahahah

 

Here it is:

I tried this but the sql does not work.....
SELECT 'orders'.*, 'resellers'.'name' FROM 'orders' INNER JOIN 'resellers' ON 'orders'.'resellers_id' = 'resellers'.'id' WHERE 'orders'.'id' = '46';

I am getting
SELECT 'orders'.*, 'resellers'.'name' FROM 'orders' INNER JOIN 'resellers' ON 'orders'.'resellers_id' = 'resellers'.'id' WHERE 'orders'.'id' = 46
 LIMIT 0, 25 


MySQL said: Documentation 
 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.*, 'resellers'.'name' FROM 'orders' INNER JOIN 'resellers' ON 'orders'.'reselle' at line 1

 

You're using single quotes when you should be using backticks.

 

SELECT 'orders'.*, 'resellers'.'name'
Should be:
SELECT `orders`.*, `resellers`.`name` ...
Single/double quotes are only for values. Backticks are used for databases, tables and columns. Backticks are completely optional though, unless you are using reserved keywords as your table/column names.
bambinou1980 Member

bambinou1980

Posted
  • Author
Posted

Thank ou so much, well spotted, I also had another error

SELECT `orders`.*, `resellers`.`reseller_name` FROM `orders` INNER JOIN `resellers` ON `orders`.`resellers_id` = `resellers`.`id` WHERE `orders`.`id` = '46';

It was "reseller_name" and not "reseller".

 

It is working! Thanks

bambinou1980 Member

bambinou1980

Posted
  • Author
Posted (edited)

Ah, I did not think about this one....now I am a little stuck with a new problem.

As you can see in my first post I already have a while loop using $query as:

$sql = "SELECT id, cust_order_id, cust_company, due_date, product, quantity, price, requirements, cust_order_total, order_status, resellers_id FROM orders ORDER BY id DESC $limit";
$query = mysqli_query($connection, $sql);

I my while loop I am trying to output in my table all that data:

    <?php
		        // Attempt select query execution
		        if($result = mysqli_query($connection, $sql)){
		        if(mysqli_num_rows($result) > 0){
                echo "<table class='table table-striped'>";
                echo "<tr>";
                echo "<th class='col-md-1 text-center'>id</th>";
                echo "<th class='col-md-1 text-center'>Order ID</th>";
                echo "<th class='col-md-1 text-center'>Company</th>";
                echo "<th class='col-md-1 text-center'>Due Date</th>";
		echo "<th class='col-md-1 text-center'>Product</th>";
                echo "<th class='col-md-1 text-center'>Unit Price</th>";
                echo "<th class='col-md-1 text-center'>Quantity</th>";
		echo "<th class='col-md-1 text-center'>Order Total</th>";
		echo "<th class='col-md-5 text-center'>Requirements</th>";
		echo "<th class='col-md-1 text-center'>Reseller</th>";
		echo "<th class='col-md-1 text-center'>Order Status</th>";
                echo "<th class='col-md-1 text-center'></th>";
				
				
                while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){
			    $id = htmlspecialchars($row["id"]);
				$cust_order_id = $row["cust_order_id"];
				$cust_company = $row["cust_company"];
				$due_date = $row["due_date"];
				$product = $row["product"];
				$quantity = $row["quantity"];
				$price = $row["price"];
				$requirements = $row["requirements"];
				$cust_order_total = $row["cust_order_total"];
				$resellers = $row["resellers_id"];
				$order_status = $row["order_status"];
				
				
                echo "<tr class='text-center'>";
                echo "<td>" . htmlspecialchars($id) . "</td>";
                echo "<td>" . htmlspecialchars($cust_order_id) . "</td>";
                echo "<td>" . htmlspecialchars($cust_company) . "</td>";
                $new_date = date("d-m-Y", strtotime($due_date));
                echo "<td>" . $new_date . "</td>";
		echo "<td>" . htmlspecialchars($product) . "</td>";
                echo "<td>" . htmlspecialchars($price) . "</td>";
		echo "<td>" . htmlspecialchars($quantity) . "</td>";
                echo "<td>" . htmlspecialchars($cust_order_total) . "</td>";
		echo "<td>" . htmlspecialchars($requirements) . "</td>";
		echo "<td>" . htmlspecialchars($resellers) . "</td>";
		echo "<td>" . htmlspecialchars($order_status) . "</td>";
		echo "<td><a class='btn btn-default' href='update-orders.php?order=$id' role='button'>Edit</a><a class='btn btn-danger' href='delete-orders.php?order=$id' onclick=\"return confirm('Are you sure you want to delete this order?');\" role='button'>Delete</a></td>";
                echo "</tr>";
                     }
                echo "</table>";
               // Close result set
               mysqli_free_result($result);
              }else{
              echo "Not Results to Show, get more customers!!";
				}}
			    ?>

But, in one of the colum of my table I need the results from the related table of the second query:

$query2 = "SELECT `orders`.*, `resellers`.`reseller_name` FROM `orders` INNER JOIN `resellers` ON `orders`.`resellers_id` = `resellers`.`id` WHERE `orders`.`id` = '46'";

All I am trying to do is output the surname and name of the reseller "echo "<th class='col-md-1 text-center'>Reseller</th>";" in that column, but that data is from the $query2.

 

How can I merge both queries into 1 and output the name and surname of that reseller table  in that column please? "$resellers = $row["resellers_id"];"

 

 

like this:

                while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){
			        $id = htmlspecialchars($row["id"]);     (Table `orders`)
				$cust_order_id = $row["cust_order_id"]; (Table `orders`)
				$cust_company = $row["cust_company"];   (Table `orders`)
				$due_date = $row["due_date"];           (Table `orders`)
				$product = $row["product"];             (Table `orders`)
				$quantity = $row["quantity"];           (Table `orders`)
				$price = $row["price"];                 (Table `orders`)
				$requirements = $row["requirements"];   (Table `orders`)
				$cust_order_total = $row["cust_order_total"];  (Table `orders`)
				$resellers = $row["resellers_id"];             (Table `resellers`)I need to pull the name and surname of the resellers there
				$order_status = $row["order_status"];          (Table `orders`)

I thought it was going to be easy after finding a working query but it is actually becoming more complicated...

Edited by bambinou1980
scootstah Member

scootstah

Posted
Posted

 

SELECT `orders`.*, `resellers`.`reseller_name` FROM `orders` INNER JOIN `resellers` ON `orders`.`resellers_id` = `resellers`.`id` WHERE `orders`.`id` = '46';

 

That is what this will do. This adds the "reseller_name" column to your result set. If you want the surname, you'll have to add that column to the SELECT statement.

SELECT `orders`.*, `resellers`.`reseller_name`, `resellers`.`reseller_surname` FROM `orders` INNER JOIN `resellers` ON `orders`.`resellers_id` = `resellers`.`id` WHERE `orders`.`id` = '46';
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.