lookup query help

General PHP Help
#1

Hi all,

I’m trying to better my current code and instead of entering a full contact name on an order I add their ID number instead and lookup their full name etc. The only real issue I have is displaying the name on an update page, for instance if I goto update ‘CONTACT A’ (ID 1) my current code will only display ‘1’ rather than the name…

Does anyone know how to do this?

Here’s my current working code…

[php]<?php
$supplier = “SELECT name FROM fms_tbl_contacts WHERE client_id=’{$_SESSION[‘client_id_of_user’]}’ ORDER BY name ASC”;
$result = mysqli_query ($db, $supplier);

echo “”;
echo ‘’;
echo htmlspecialchars($row[‘supplier’]);
echo ‘’;
echo “SELECT…”;
while($r = mysqli_fetch_array($result)){
echo ‘’;
echo htmlspecialchars($r[‘name’]);
echo ‘’;
}
echo “”;
?>[/php]

thanks

#2

Howdy Dan, welcome back…

So, normally, what I would do is to set up the options to make the value the ID number and display the name
as the text. You would need to change the query to pull the id and name instead of just the name itself. Like:
echo ‘’ . htmlspecialchars($r[‘name’]) . ‘’;
(Note, why use three echo lines for one display?)

Then, you know the id of the contact select and can use it in your next query for whatever you need…

#3

Hi ErnieAlex,

Thanks for your comment, I was thinking the same thing for displaying the id, name on the select box (for my create page) but when I post this to the database the only value that will be entered will be the id. I then need to be able to goto an update page (say for an order and this is the supplier) and see what the suppliers name actually is and not just the id number…

so this should be my create page script…
[php]<?php
$supplier = “SELECT name FROM fms_tbl_contacts WHERE client_id=’{$_SESSION[‘client_id_of_user’]}’ ORDER BY name ASC”;
$result = mysqli_query ($db, $supplier);

echo “”;
echo “SELECT…”;
while($r = mysqli_fetch_array($result)){
echo ‘’;
echo htmlspecialchars($r[‘name’]);
echo ‘’;
}
echo “”;
?>[/php]

and my update page script (although not tested)…
[php]<?php
$supplier = “SELECT name FROM fms_tbl_contacts WHERE client_id=’{$_SESSION[‘client_id_of_user’]}’ ORDER BY name ASC”;
$result = mysqli_query ($db, $supplier);

echo “”;
echo ‘’;

// this is the area I need to lookup what the suppliers name is from the ‘fms_tbl_contacts’ database table based on the ‘supplier_id’
$supplier_name = “SELECT name FROM fms_tbl_contacts WHERE client_id=’{$_SESSION[‘client_id_of_user’]}’ AND id=’{$row[‘supplier_id’]}’”;
$result = mysqli_query ($db, $supplier_name);
echo htmlspecialchars($row[‘supplier_name’]);
//

echo ‘’;
echo “SELECT…”;
while($r = mysqli_fetch_array($result)){
echo ‘’;
echo htmlspecialchars($r[‘name’]);
echo ‘’;
}
echo “”;
?>[/php]

Do you think this is the best way to do this?

#4

Well, Dan, not sure what you mean be a “create” and “update” page. But, normally, you handle sales carts as
a list of items linked to the one id for that cart. Each entry is one item row on the invoice. To display it for a
client, you need to loop thru the items and do other queries to retrieve the name of each item in the rows of
things they ordered. So, basically, you would use this same process for your selection of contacts. You need
to show the user only the text they need to see. You never show them id numbers as they change all of the
time and usually are not in any sort of order that makes sense to a user. (As you delete id’s in a database’s
table, they are eventually replaced with other entries and therefore are never shown to the user.) Normally,
you would sort a list by either by alpha or by date of creation. Depends on what you are doing…

Sorry if I got off track… So, if you save the id, name, etc into your database, it has already been checked for
security issues. Normally, every time you insert data into your database, you make sure it is safe before you
call the query to insert it. Therefore, you already know the data is safe. The id of a record is normally done by
the database engine as an “auto-increment INT” type of field. Therefore, it does not need “htmlspecialchars()”
function to check it’s safety. Most likely it is not needed for the name either as that is normally checked before
writing to the database.

I use id of the current user saved in a $_SESSION[] variable all the time. So, that part is good. Running a query
against the database to get a supplier’s name, or to load a long list of them to put into a SELECT clause does
not take much resource wise on the server. So, how you did it should word just fine. The only thing I see that
you might want to do is drop some of the extra echo’s you use to create the tags. It is faster to use
a concatenation “.” as needed. It is only a small amount, so not a big deal, but, why make the server work
more than it is needed. Something like this should work:
echo ‘’ . $r[‘name’] . ‘’;
You can also do it directly without the concatenation, although some like it spread out for ease of reading…
echo ‘<option value=$r[“id”]>$r[“name”]’;

Not sure if that was what you needed, but, hope it helps…
Ernie

#5

Hi Ernie,

Thanks again for your comments/feedback - I’ve started to work through and alter scripts, for some strange reason I am getting odd outputs on the update page. Everything works as it should i.e supplier id in the background but the supplier name always comes through as ‘S’. Even when I modify the supplier the name is always the same… any ideas? My code is:-

[php]<?php
echo “”;
// lookup supplier name based on supplier id
$supplier_name = “SELECT name FROM fms_tbl_contacts WHERE client_id=’{$_SESSION[‘client_id_of_user’]}’ AND id=’{$row[‘supplier’]}’”;
$result = mysqli_query ($db, $supplier_name);

echo ‘’;
echo htmlspecialchars($supplier_name[‘name’]);
echo ‘’;

echo “SELECT…”;

$supplier = “SELECT name, id FROM fms_tbl_contacts WHERE client_id=’{$_SESSION[‘client_id_of_user’]}’ ORDER BY name ASC”;
$result = mysqli_query ($db, $supplier);
while($r = mysqli_fetch_array($result)){

echo ‘’;
echo htmlspecialchars($r[‘name’]);
echo ‘’;
}
echo “”;
?> [/php]

#6

Well, in your code:

$supplier_name = "SELECT name FROM fms_tbl_contacts WHERE client_id='{$_SESSION['client_id_of_user']}' AND id='{$row['supplier']}'"; $result = mysqli_query ($db, $supplier_name);

echo ‘’;
echo htmlspecialchars($supplier_name[‘name’]);
echo ‘’;


You never create the $row[] array… You create a query, run it, but never do a “fetch” to acquire the data.
You should add something like $row=mysqli_fetch_assoc($db, $result); to actually load the array with data.
The current code also uses $row[‘supplier’], but does not load it in the query. I think you want the id there.
#7

Hi ErnieAlex,

Sorry I should have explained that i run a query to load of the order values i.e supplier, amount, address…

[php] $query = “SELECT * FROM fms_tbl_orders WHERE client_id=’{$_SESSION[‘client_id_of_user’]}’ AND client_order_no=”.$_GET[‘u’];
$result = mysqli_query($db, $query);
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);[/php]

some of the values loaded are;-
‘supplier’ (not supplier_id)
‘project_no’
‘nom_code’

Out of interest why is a ‘S’ displayed?

#8

$supplier_name is your sql query not the result set. You need to fetch those results so you will have the name to select from.

You also should be using prepared statements.

#9

Dan, in the code you posted:

// lookup supplier name based on supplier id $supplier_name = "SELECT name FROM fms_tbl_contacts WHERE client_id='{$_SESSION['client_id_of_user']}' AND id='{$row['supplier']}'"; $result = mysqli_query ($db, $supplier_name);

echo ‘’;
echo htmlspecialchars($supplier_name[‘name’]);
echo ‘’;


You do NOT create the $row[] array. You create a query named $supplier_name and run the query, but, you
never set up the FETCH, therefore the values in $row[] array are just left overs from previous queries and it
will display junk, in this case an “S”… You need to run the mysqli_fetch_assoc() function just before the echo
and after the mysqli_query…

And, as Astonechiper mentioned, using prepared statements would be a good idea. Here is a link that explains
them:
http://www.w3schools.com/php/php_mysql_prepared_statements.asp

#10

thanks AStonecipher, I’ve managed to get the code working correctly. I’m fairly new to php so prepared statements is a bit advanced for me.

my next issue is displaying the name rather than the id on a table, for instance my code;-

[php]$query = “SELECT * FROM fms_tbl_orders WHERE client_id= ‘{$_SESSION[‘client_id_of_user’]}’ ORDER BY client_order_no DESC”;
$result = mysqli_query($db, $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[‘client_order_no’] . ‘’;
echo ‘’.$row[‘nom_code’] . ‘’;
echo ‘’.$row[‘project_no’] . ‘’;
echo ‘’.$row[‘supplier’] . ‘’;
echo ‘’.$row[‘descr’] . ‘’;
echo ‘’.$row[‘raised’] . ‘’;[/php]

how would I convert the supplier from its id form to its full name? I tried this code but it only worked on the first row and didnt display any others.

[php]
echo ‘

’.$row[‘client_order_no’] . ‘’;
echo ‘’.$row[‘nom_code’] . ‘’;
echo ‘’.$row[‘project_no’] . ‘’;

$supplier_name = “SELECT name FROM fms_tbl_contacts WHERE client_id=’{$_SESSION[‘client_id_of_user’]}’ AND id=’{$row[‘supplier’]}’”;
$result = mysqli_query ($db, $supplier_name);
while($supp = mysqli_fetch_array($result)){
echo ‘

’.$supp[‘name’] . ‘’;

echo ‘

’.$row[‘descr’] . ‘’;
echo ‘’.$row[‘raised’] . ‘’;[/php]
#11

thanks ErnieAlex, our post replies crossed paths - I will read up on prepared statements

Sponsor our Newsletter | Privacy Policy | Terms of Service