Change code to name

[Abi2702]

I would like change the code in one table to a name in another table and display the name only using php.

[Barand]

Give us a clue! We need more than that if we're to help.

Perhaps give examples of

• what you have now, and
• what you want

[mac_gyver]

i'm going to guess the answer involves a JOIN query or the number 42?

[Moorcam]

Change code as in???

This part confuses me. Based on what you are actually looking to change it is hard for anyone to help really.

[Abi2702]

<?php $db = mysqli_connect("localhost","admin123","mawfadmin","utility system"); if(!$db) { die("Connection failed: " . mysqli_connect_error()); } /*function get_total_row($connect) { $query = " SELECT * FROM tbl_webslesson_post "; $statement = $connect->prepare($query); $statement->execute(); return $statement->rowCount(); } $total_record = get_total_row($connect);*/ $limit = '5'; $page = 1; if($_POST['page'] > 1) { $start = (($_POST['page'] - 1) * $limit); $page = $_POST['page']; } else { $start = 0; } $query = " SELECT invoice_id, service_provider, reg_name, acc_no, acc_name, invoice_no, invoice_date, invoice_amount, eft_no, pay_status FROM invoices "; if($_POST['query'] != '') { $query .= ' where service_provider LIKE "%'.str_replace(' ', '%', $_POST['query']).'%" '; } $query .= 'ORDER BY invoice_id ASC '; $filter_query = $query . 'LIMIT '.$start.', '.$limit.''; $statement = $db->prepare($query); $statement->execute(); $total_data = $statement->rowCount(); $statement = $connect->prepare($filter_query); $statement->execute(); $result = $statement->fetchAll(); $total_filter_data = $statement->rowCount(); $output = ' <label>Total Records - '.$total_data.'</label> <table class="table table-striped table-bordered"> <tr> <th>ID</th> <th>Service Provider</th> <th>Region</th> <th>Account no</th> <th>Account name</th> <th>Invoice no</th> <th>Invoice date</th> <th>Invoice amount</th> <th>EFT no</th> <th>Pay Status</th> <th>User</th> </tr> '; if($total_data > 0) { foreach($result as $row) { $output .= ' <tr> <td>'.$row["invoice_id"].'</td> <td>'.$row["service_provider"].'</td> <td>'.$row["reg_name"].'</td> <td>'.$row["acc_no"].'</td> <td>'.$row["acc_name"].'</td> <td>'.$row["invoice_no"].'</td> <td>'.$row["invoice_date"].'</td> <td>'.$row["invoice_amount"].'</td> <td>'.$row["eft_no"].'</td> <td>'.$row["pay_status"].'</td> <td>'.$row["user_name"].'</td> if ($result->num_rows > 0) { // output data of each row while($row = $result->fetch_assoc()) { echo "Name: " . $row["name"]. "<br>"; } } else { echo "0 results"; }</td> </td> <td> // displaying data in option menu </td> </tr> '; } } else { $output .= ' <tr> <td colspan="2" align="center">No Data Found</td> </tr> '; } $output .= ' </table> <br /> <div align="center"> <ul class="pagination"> '; $total_links = ceil($total_data/$limit); $previous_link = ''; $next_link = ''; $page_link = ''; //echo $total_links; if($total_links > 4) { if($page < 5) { for($count = 1; $count <= 5; $count++) { $page_array[] = $count; } $page_array[] = '...'; $page_array[] = $total_links; } else { $end_limit = $total_links - 5; if($page > $end_limit) { $page_array[] = 1; $page_array[] = '...'; for($count = $end_limit; $count <= $total_links; $count++) { $page_array[] = $count; } } else { $page_array[] = 1; $page_array[] = '...'; for($count = $page - 1; $count <= $page + 1; $count++) { $page_array[] = $count; } $page_array[] = '...'; $page_array[] = $total_links; } } } else { for($count = 1; $count <= $total_links; $count++) { $page_array[] = $count; } } for($count = 0; $count < count($page_array); $count++) { if($page == $page_array[$count]) { $page_link .= ' <li class="page-item active"> <a class="page-link" href="#">'.$page_array[$count].' <span class="sr-only">(current)</span></a> </li> '; $previous_id = $page_array[$count] - 1; if($previous_id > 0) { $previous_link = '<li class="page-item"><a class="page-link" href="javascript:void(0)" data-page_number="'.$previous_id.'">Previous</a></li>'; } else { $previous_link = ' <li class="page-item disabled"> <a class="page-link" href="#">Previous</a> </li> '; } $next_id = $page_array[$count] + 1; if($next_id >= $total_links) { $next_link = ' <li class="page-item disabled"> <a class="page-link" href="#">Next</a> </li> '; } else { $next_link = '<li class="page-item"><a class="page-link" href="javascript:void(0)" data-page_number="'.$next_id.'">Next</a></li>'; } } else { if($page_array[$count] == '...') { $page_link .= ' <li class="page-item disabled"> <a class="page-link" href="#">...</a> </li> '; } else { $page_link .= ' <li class="page-item"><a class="page-link" href="javascript:void(0)" data-page_number="'.$page_array[$count].'">'.$page_array[$count].'</a></li> '; } } } $output .= $previous_link . $page_link . $next_link; $output .= ' </ul> </div> '; echo $output; ?>

[Abi2702]

I have this code to fetch the data from the database

[Abi2702]

<?php $servername = "localhost"; $username = "admin123"; $password = "mawfadmin"; $dbname = "utility system"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = "SELECT name FROM admins inner join invoices on admins.user_code = invoices.user_name"; $result = $conn->query($sql); if ($result->num_rows > 0) { // output data of each row while($row = $result->fetch_assoc()) { echo "Name: " . $row["name"]. "<br>"; } } else { echo "0 results"; } $conn->close(); ?>

[Abi2702]

And this code to change the code to the name. I want to integrate the two files into the fetch php file. But i dont know how. 

[Moorcam]

When posting code use the <> button to add code as it will format it better for reading.

[mac_gyver]

@Abi2702, the only technically relevant parts are the two sql queries -

$query = "SELECT invoice_id, service_provider, reg_name, acc_no, acc_name, invoice_no, invoice_date,
 invoice_amount, eft_no, pay_status
 FROM invoices";

and -

$sql = "SELECT name
 FROM admins
 inner join invoices on admins.user_code = invoices.user_name";

wouldn't you add the admins name column to the list of columns you are SELECTing and JOIN the invoices query with the admins table ON the same join condition being used in the second query?

i recommend that when you write JOIN queries that you use table alias names and that you prefix every column reference with the corresponding alias, even when it is not necessary to make the query work, in order to prevent mistakes and to make it clear to anyone reading the sql query what you are attempting to do.

i also recommend that you rename the invoices user_name column to user_code, since it is not holding user_name values.

[Abi2702]

How will the query look like? Please give an example. Im new at this.

[mac_gyver]

1 hour ago, Abi2702 said:

Im new at this

that's why you need to make an attempt at doing this yourself, so that you will actually learn the meaning of the words and syntax you are using.