Spaces not passing in Variable

[websponge]

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>";
?>`

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.

[websponge]

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

[websponge]

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

[matrixdevuk]

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.

[almostbob]

urlencode the value

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

[matrixdevuk]

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

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.

[almostbob]

@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

[matrixdevuk]

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

[almostbob]

@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

[almostbob]

@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

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

[almostbob]

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

[websponge]

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 :(

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'.

[websponge]

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%'";

[almostbob]

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

[almostbob]

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

[almostbob]

well now, why did I do that ^^