Convert customer id

#1

Hi, i have a script which displays project information and it stores the clients ‘id’ rather than the name, how can I display this is a table format? At the moment I can only get the client_id to display. I have a database called ‘customers’ which stores the nicename etc

$query = "SELECT * FROM `projects` ORDER BY `id` DESC";

$result = mysqli_query($con, $query) or trigger_error("Query Failed! SQL: $query - Error: ". mysqli_error($db), E_USER_ERROR);

if($result) {
while($row = mysqli_fetch_assoc($result)) {

echo ‘

’;
echo ‘’.$row[‘id’] . ‘’;
echo ‘’.$row[‘client_id’] . ‘’;
echo ‘’.$row[‘name’] . ‘’;
#2

You would have to build a table to start with.

#3

hi astonecipher, hope your well…
I have a table called customers which has id & name columns. The client_id I refer to on the projects table is tied back to the ‘id’ within the customer table if this makes sense, I want the code to lookup the customers table and use client_id to check the id in the customer table and display the ‘name’ part

#4

You would JOIN the two tables in a single query to get the related data.

#5 
$query = "SELECT 
             CONCAT(c.last_name, ', ', c.first_name) as `name`
            , p.project_name 
FROM 
   `projects` p 
INNER JOIN 
   customers c 
      ON p.customer_id = c.id 
ORDER BY `id` DESC";
#6

Hi Astonecipher, Im struggling to get this to work for some reason, I used your code as below…

$query = "SELECT c.name, p.project_name, p.id 
FROM 
projects p 
INNER JOIN 
customers c 
ON p.client_id = c.id 
ORDER BY p.id DESC";
$result = mysqli_query($con, $query) or trigger_error("Query Failed! SQL: $query - Error: ". mysqli_error($db), E_USER_ERROR);
    if($result) {
    	while($row = mysqli_fetch_assoc($result)) {        															
    echo '<tr>';
    echo '<td>'.$row['id'] . '</td>';
    echo '<td>'.$row['name'] . '</td>';
    echo '<td>'.$row['project_name'] . '</td>';

I have two tables, 1 is called ‘projects’ which i want to display id, project_name & it has a column called ‘client_id’ - my next table is customers which has id, name.

The client_id (on projects) value references the id value (on customers)

The code isnt displaying an errors so I haven’t a clue where I’m going wrong

Thanks

#7

Aside from not displaying an error, what output are you getting and if you are getting a blank page, what does the ‘view source’ of the page in your browser show? Based on the posted code, you are executing the sql query inside of the html table markup, where any mysqli error won’t be rendered in the table (it should appear outside the table or not all all) AND your use of mysqli_error($db) is incorrect and won’t actually display a query error (will result in php errors about the $db variable.)

I recommend two things -

  1. Separate the concerns in your code. The database specific code, that knows how to execute the query and fetch the data, is a separate concern from the presentation code, that knows how to produce the output from the data. The way to accomplish this separation is to put the database specific code before the start of your html document, fetch the data into an appropriately named php variable, then use that php variable at the appropriate point in the presentation code. Doing this will make it easier to design, write, and test your code/query and will insure that any errors being reported will get handled correctly.

  2. Use exceptions to handle database statement (connection, query, prepare, and execute) errors and in most cases let php catch the exception where it will use its error_reporting, display_errors, and log_errors settings to control what happens with the actual error information. When learning, developing, and debugging code/queries, you would display all php errors (which will now include the database statement errors/exceptions.) When on a live/public server, you would log all php errors. You would then remove all the error handling logic that is in your code now. This will do two things - 1) Simplify your code, and 2) Only display database errors when you need them (by always outputting the database errors on a web page, what the current code is trying to do, you give hackers useful information.) To enable exceptions for the mysqli extension, add the following line of code before the point where you make the database connection -

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
#8

I was just naming columns, so there is the distinct possibility that you query is failing due to columns not existing.

#9

thanks for the feedback guys, I am a little further but still not working 100%. Im able to pull through the project list but the client id/name is only working when the project id value matches the customer id which is incorrect. Multiple projects will have the same client_id so on the table below rows 1,2,4,5,6 & 7 should all read ‘client No.1’

SELECT projects.id, projects.project_name, customers.name FROM projects 
LEFT JOIN customers
ON projects.id = customers.id
ORDER BY projects.id

So this is what its displaying

No. Client Project Options
1 Test project 1
2 project 2
3 subcontract
4 school
5 staircase
6 Client No.1 bal
7 customer rail
8 Test Hill top
#10

^^^ That is joining customer id 1 with project id 1, customer id 2 with project id 2, …

If the projects table has a column client_id (or is it actually customer_id?) it would be projects.client_id like you had in the previous query.

#11

thanks, I’ve altered the code slightly as below

SELECT projects.id, projects.project_name, customers.name FROM projects LEFT JOIN customers ON projects.client_id = customers.id ORDER BY projects.id

This is what the table is now displaying…

No. Client Project Options
1 Test project 1
2 Test project 2
3 Test project 3
4 Test project 4
5 Test project 5
6 Test project 6
7 Test project 7
8 Test project 8

the projects table has the following columns; id, project_name, client_id

The customers table has the following columns; id, name

#12

You need to determine why the output is not as expected. Either the data stored in the table(s) isn’t what you think or your php code isn’t referencing the ‘name’ column in the fetched row(s).

If you had to use LEFT JOIN to get any output, it means that the data values are not matching between the two tables. How did the various id column data get INSERTED into the tables? If you imported a csv file, without trimming the data, and the columns are character/text, rather than integer, you can have spaces, tabs, or new-lines as part of the data values.

What is the current php code? Does running the query directly against your database, using phpmyadmin or a similar tool, produce the correct results?

1 Like
#13

It does sound like there is a break when it inserts the data into the various tables.

SELECT 
    p.id as `Project ID`
 , p.project_name
 , p.client_id as `Project Owner ID`
 , c.id as `Client ID`
 , c.name as `Client Name`
FROM 
    projects p
LEFT JOIN 
    customers c 
ON 
    p.client_id = c.id 
ORDER BY p.id

What do you get back with that?

#14

I inserted the data in phpadmin and I’ve also ran the code in the SQL section which produces the same results i.e no results in the client_id section

#15

hi astonecipher, I ran the query in phpmyadmin and these were the results…

|[Project ID]|[project_name]|[Project Owner ID]|[Client ID]|[Client Name]||
|1|Test project 1|1| NULL | NULL |
|2|Test project 2|1| NULL | NULL |
|3|Test project 3|2| NULL | NULL |
|4|Test project 4|1| NULL | NULL |
|5|Test project 5|1| NULL | NULL |
|6|Test project 6|1| NULL | NULL |
|7|Test project 7|1| NULL | NULL |
|8|Test project 8|3| NULL | NULL |

#16

This is dummy data right?

What do you have in the Customers table currently?

Do you have the foreign key relationships setup? It isn’t a necessity, but it helps. To do that, InnoDB engine is preferred, MyISAM doesn’t allow it.

#17

Hi astonecipher,

Yes its dummy data for now, this is what is in the table now…

Name Options
Test
customer new
Client No.1

InnoDB has been used but I havent set up a foreign key

Thanks

#18

I’m not seeing a primary key? Did you just leave it out of the data presented?

#19 
ALTER TABLE projects
ADD FOREIGN KEY (customerID) REFERENCES customers(id);
#20

Yes theres an ‘id’ column thats not displayed, i ran the query aboe which returned the following;-

1452 - Cannot add or update a child row: a foreign key constraint fails (`f4aj_system`.`#sql-11e137_1d0ed`, CONSTRAINT `#sql-11e137_1d0ed_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `customers` (`id`))
Sponsor our Newsletter | Privacy Policy | Terms of Service