0

I have a select box that looks up values from my database, and then runs a jquery function to display the results, function is working fine, but some of the values in the database have spaces i.e "BT Global" the results are returning everything that starts with "BT"

any help appreciated.

<?php
$sql = 'SELECT customer FROM device GROUP BY customer';
if(!$result = $db->query($sql)){
    die('There was an error running the query [' . $db->error . ']');
}
echo "<select class='form-control' id='fwname' onchange='getfirewall();'>";
echo "<option selected='Choose Customer'>Choose Customer</option>";
while($row = $result->fetch_assoc()){
$thevalue = $row['customer'];
echo "<option value=".$thevalue.">";
echo  $thevalue;  
echo "</option>";
}
echo "</select>";
?>`
4
Contributors
19
Replies
62
Views
2 Years
Discussion Span
Last Post by almostbob
Featured Replies
  • 2
    diafol 3,720   2 Years Ago

    >I am using name, because there are multiple entries, so cannot just return the row ID I'm not sure that I follow, but you may find using row IDs a lot simpler. This is the usual (accepted?) way of doing things. Having names in the values can lead to all … Read More

  • @matrixdevuk If they were called "Smith & Smith & Smith" each of those ampersands would appear as the beginning of another variable my paranoia comes out and always *plan for the worst case* you can never be sure what someone will enter as text Read More

0

You should be getting/seeting the customer id in the dropdown value attribute no the actual name. So your HTML would look like...

<option value="2">BT Global</option>

To create would be something like...

<option> value="<?= $row['id'] ?>"><?= $row['customer'] ?></option>

in your select loop. You then pluck the id value and use that in your DB actions.

0

The value in the dropdown, is the name of the vlaue, not the ID, but, I see what you are saying...

0

I am using name, because there are multiple entries, so cannot just return the row ID

0

Just a tip, you might want to lookup how to properly name variables. :P
$thevalue would confuse a crapload of developers for a moment or two if they looked at your code. I'd try something like $customer.

0

urlencode the value

echo "<option value=".urlencode($thevalue).">";
echo  $thevalue;  
echo "</option>";

Edited by almostbob

0

What @almostbob should do the trick; but I would probably recommend doing str_replace(" ", "_", $thevalue); — which would change "Hello World" to "Hello_World".

2

I am using name, because there are multiple entries, so cannot just return the row ID

I'm not sure that I follow, but you may find using row IDs a lot simpler. This is the usual (accepted?) way of doing things. Having names in the values can lead to all sorts of complications, as you've experienced already. But, each to his own. Good luck with it.

Votes + Comments
me too
Yup; this is how I'd do it.
0

@matrixdevuk

str_replace() changes the submitted value and ;
would need to be reprocessed at the server and ;
does not fix the many proscribed characters that may be in the data and ;
would screw up if the name contained '_' a valid character

urlencode() does not change the submitted value, and fixes other characters not allowed in the posted data, permitted in text fields
eg ><[]{}/\,+;&

value with spaces : urlencodes to ;
value%20with%20spaces : and arrives at the server as ;
value with spaces

Edited by almostbob

1

I was assuming that $thevalue was mad up of alphanumeric with spaces.

Votes + Comments
+rep for the WCS in later comment - new one on me - like it
0

@diafol
from the code supplied the DB is not normalised, there does not seem to be a unique id so they are selecting by text representation of the customer name

2

@matrixdevuk
If they were called "Smith & Smith & Smith" each of those ampersands would appear as the beginning of another variable

my paranoia comes out and always plan for the worst case you can never be sure what someone will enter as text

Votes + Comments
Yeah; never thought about that. :P +1 for thinking about WCS.
0

from the code supplied the DB is not normalised, ...

Ah. I see - well that's something else the OP should address. That is probably bad for peformance, let alone duplication and all the multiple points of failure.

@AB - yep WCS are always the way to go. Not telling what some fruitloop will inject into your carefully crafted code. +1

0

for the purpose of the OP selecting on non-unique columns
In my mind the op selects a customer and then is displayed all the devices for that customer
at this time the sql selects all customers and displays duplicates in the dropdown
as there must be later processing to select the devices for this customer it is redundant and bandwidth and processor hog to select and display all
perhaps $sql = 'select distinct customer from device order by customer'; may help,
its faster by a factor of 10 than group by and
displays every customer :once:

consider also, redesigning the DB to normalise it
proper indexing, unique keys between tables, reduces error

Edited by almostbob

0

Thanks everyone, No idea what you mean by normalise, sorry!
you are correct almostbob, there are multiple entries for each customer, so cannot just return the rowid, I need to see all devices where the customer is "The Customer" but alas I have other customers that start with "The " for example.

matrixdevuk, I used $thevalue as a quick reference only to post here ;)

urlencode did not work either :(

0

Normalizing in your specifi case would be something like this:

customer table
id | customername | ...other fields...

devices
id | devicename | ...other fields...

Depending on the type of relationship, you may need another table. If it is a ONE TO MANY e.g. One customer can have many devices - you'd place a customer_id field int he devices table. Likewise if One device could have many customers, then you'd place a device_id field in the customers table.

However, I assume that many customers can have many devices. This is known as a MANY TO MANY relationship and demands a separate table, possibly with the following structure:

customer_devices
id | customer_id | device_id

Note that all these fields are integer type, so just by looking at it you'd have absolutely no idea what the underlying data was pertaining to. This is where JOINS (usually INNER JOIN for this) come into play.

If you wanted a list of customers that has a particular device...

SELECT c.customername FROM customers AS c INNER JOIN customerdevices AS cd ON c.id = cd.customer_id WHERE cd.device_id = 3

That could be expanded to include all three tables if you wanted data from the devices table as well.

The danger with using fulltext as you seem to have done is that you are wide open to errors. For example say that you had "BT Global" and then "BT Gloabl" - obviously the same company, but a typo. This will appear as two separate companies with your setup. If you started to normalize your tables, this shouldn't really happen as you enter the customer (company) name just once and re-use the 'id'.

0

Ah ok, I get that. the database table was imported from an existing spreadsheet, as its quite a lot of data.. thats why its not normalised. thank you

However its still picking up anything that starts with "BT " rather than the actual name. the SQL for the jquery is this:

$fwname = urlencode($_POST["fwname"]);
$sql = "SELECT * FROM device WHERE customer LIKE '$fwname%'";
0

the keyword 'like' will do that,, it is an approximate match
where you are populating <select> from the db, like is unneccessary and counter productive, an exact match will return correct answers
where customer = '$fwname' should work
It is fairly simple to normalise the db, using sql, if the data is to be retained. More secure, faster, smaller, simpler to access, is the result.
If this is a one shot, may not be worth the time needed

Edited by almostbob

0

websponge, urlencode() ONLY applies to the html <option value=''> text
not to the sql, it will return errors in the sql

Edited by almostbob

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.