View original (French)

Issue with space in a select query

Solved
denirma Posted messages 7 Status Member -  
denirma Posted messages 7 Status Member -
Hello,
Sorry, but the title is not very explicit.
I am trying to build an interface that gives me access to different elements of a database. Everything is fine except when I set up filters via dropdown menus.
The filters work except when there is a space in the filter label.

For example, here is my code: 

 echo "<select name=\"letri\" id=\"letri\">"; 
 while ($donroutage = $verif_routage->fetch()){
 //echo "<li>".$doncompt['id_tcompte']." (".$doncompt['type_compte']."</li>"; 
 //echo "<option value=\"".htmlentities($donroutage['routage_element'])."\">".$donroutage['routage_element']."</option>";
 echo "<option value=\"".$donroutage['routage_element']."\">".$donroutage['routage_element']."</option>"; 
 }
 echo "</select>";

When the variable $donroutage['routage_element'] corresponds to a label with a whitespace (for example "IP fixe") and I use it to display my elements: 

$verification = $bdd->query("SELECT * FROM routage WHERE routage.nature =\"$letri\"");

I don't get any results, whereas with the same code and an element that does not have a whitespace (for example "VPN") the results come out fine.

So it's not my script that doesn't work but something that blocks when there is a whitespace.

I looked into the resulting HTML code by displaying my variable $letri (corresponding to the select) and there is no %20 as sometimes seen in HTML. And since I'm having trouble expressing the problem, Google has been of no help.

If anyone has an idea, I would be grateful.
Thanks in advance.
D.

7 answers

  1. Anonymous user
     
    Hello

    Did you display the actual executed query: 
    $requete = "SELECT * FROM routage WHERE routage.nature 
    =\"$letri\"";
    echo $requete;
    $verification = $bdd->query($requete);
    0
  2. denirma Posted messages 7 Status Member
     
    Hello,
    and thank you for your interest in the subject.

    So I had done it exclusively on the variable $letri, in both cases, the variable is correctly returned. I put: 
    $requete = "SELECT * FROM routage WHERE routage.nature 
    =\"$letri\"";
    echo "<p>Requete = ".$requete."</p>";

    And it returns to me: Requete = SELECT * FROM routage WHERE routage.nature ="IP fixe"

    Where I think it’s really related to the whitespace is that in the case where there is no space, the script works.

    If you have any other ideas, I’m open to them.
    Anyway, thank you for your help.
    Denis
    0
  3. Anonymous user
     
    If you have no error message and the query does not return any records, it means you have no line in your routing table where the nature field equals "fixed IP".
    Have you checked the spelling of "fixed IP"? (uppercase/lowercase, one or two spaces between IP and fixed...). Ideally, you should display the ASCII codes of what is in your table and what you are entering to make a proper comparison.
    Have you tried replacing your variable $letri directly with "fixed IP" in the query?

    [Edit]
    But first of all, when you say that the script does not work, do you mean that the SELECT * FROM routing WHERE routing.nature = "fixed IP" query returns no results?
    0
  4. denirma Posted messages 7 Status Member
     
    Yes, I'm indeed referring to the fact that the query returns nothing.

    For verifying how things are written, I copied and pasted to make sure it’s written the same way. And there are indeed lines on static IP.

    So I'm going to work around the problem and no longer search by the title but by the ID of the title, and I will replace the titles in the main table with the IDs from the table that specifies the elements. This complicates the queries a bit (which is always a challenge for me), but at least it should work.

    By the way (just for your information), how do we display ASCII codes?
    And thanks again for your help.
    0
  6. denirma Posted messages 7 Status Member
     
    SORRY

    The problem was elsewhere... a variable naming error!
    I'm sincerely sorry... and thank you for your help.
    0
  7. Anonymous user
     
    MySQL has never refused to do a WHERE on a field containing a space, there must be something else. It's a shame you don't want to go deeper, it probably hides another problem. It's never healthy to circumvent problems, but I know that we have to move forward anyway...

    To display the code of a character, there is the ord function in PHP. Doc, for a complete string: 
    $chn="abcd";
    for ($k=0;$k<strlen echo="" ord=""> 
    The fact that you copied and pasted the text is not proof. What did you copy: from mysql to your script, or vice versa? And I suppose you did it via phpmyadmin, which means there could be invisible characters that are not taken into account.</strlen>
    0
  8. denirma Posted messages 7 Status Member
     
    In fact, it was not an issue related to whitespace, but rather to the check of my variable that directed my request according to the filter... I had forgotten the $ before its name.
    Hence my SORRY from the previous post. It's crazy how sometimes, despite several rereads, I don't see the stupid mistake in my writing!
    So you're right, the problem did not come from the space or the query.
    Thank you for your patience and help. I will try (wishful thinking) to be more attentive next time.
    And thank you for the tip about ASCII characters... I'm sure it will be useful.
    0