Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New to the CF scene
    Join Date
    Oct 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sequential MySQL with PHP/HTML fails when spaces are invloved

    Noob alert.
    What I'm trying to do
    User sees a form, one field, select drop down box populated via a query against a MySQL db. This works (whoo whoo!).,
    User selects a value, a second query is run using the selected value from the first query as the WHERE clause in the 2nd query.
    If the selected value from Query1 is a single word, Query2 works like a charm.
    If the selected value from Query1 has more than one word (and spaces), $site when echoed back to the screen is just the first word and Query2 results are null.

    I know I'm missing something stupid but can't find it in any of the books, articles, forums, or googled searches I've tried. Any pointers or tips are greatly appreciated.

    Doug

    <html>

    <body>
    Select the site name from the list below<br>
    Note - if you start typing the name, you don't have to scroll to the name.<br>
    </body>
    <br>
    <form>

    <?php


    // Define variables

    $server = 'localhost';

    $username = 'web';

    $password = 'user';

    $database = 'HomeData';


    //$query = "Select site, username, password from sitelogins where site = '$site'";
    $query = "Select site from sitelogins order by site";



    // connect to mysql

    $db = mysql_connect($server, $username, $password);


    // connect to db

    mysql_select_db($database, $db);



    // run query and print results. This bit works great, list is what I expect to see.

    $result = mysql_query($query, $db);

    echo "<select name=\"site\">";
    if(!$result) die ("query failed");

    while($row = mysql_fetch_row($result)) {
    echo "<OPTION VALUE=".$row[0].">".$row[0]."</OPTION>";
    }
    echo "</select>";

    // if $site is one word, the rest of this works also.
    // if $site is two words or more, I get the first word only and the query returns null.
    echo "<br><br>The requested site is $site <br><br>";
    echo "<table border=1>\n";
    echo "<tr><td>The username is:</td><td>The password is:</td>";


    $query2 = "Select * from sitelogins where site = '$site'";
    $result2 = mysql_query($query2, $db);

    if(!$result2) die ("query failed");

    while($row = mysql_fetch_row($result2)) {
    echo "<tr><td>$row[1]</td><td>$row[2]</td></tr>";

    }

    echo "</table>";



    // close connnection

    mysql_close($db);

    ?>

    <br>

    <input type="submit" value = "Get Password">

    </form>
    </html>

  • #2
    Regular Coder
    Join Date
    Oct 2003
    Posts
    603
    Thanks
    2
    Thanked 1 Time in 1 Post
    try sending it with + as a space and on the receiving page, convert it back to space from +

  • #3
    Regular Coder
    Join Date
    Aug 2004
    Location
    The US of A
    Posts
    767
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have had a similar problem to this. I found the solution is to set the dropdown value to an unique id, and then setting the shown value as text. It could take some work for you to convert your table to this, but it is the best solution.

  • #4
    New to the CF scene
    Join Date
    Oct 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorry, when I said noob...

    I only have one page (one php file). Where and how would I convert what to what and back to what again?

    Sorry for being a bit dense here, but I'm in the learning phase of LAMP.

    Thanks for the reply.

    Doug

    Quote Originally Posted by boeing747fp
    try sending it with + as a space and on the receiving page, convert it back to space from +

  • #5
    New to the CF scene
    Join Date
    Oct 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Still a noob...

    Do you mean re-define my MySQL table with the site as a key and as text (already is text actually)?
    I'm also not sure what you mean by 'set the dropdown to unique id'. Is this something in the html/php code? If so, can you give me a hint as to what I need to set to something?

    Inch by inch... Thank you for your patience and help.

    Doug

    Quote Originally Posted by Kurashu
    I have had a similar problem to this. I found the solution is to set the dropdown value to an unique id, and then setting the shown value as text. It could take some work for you to convert your table to this, but it is the best solution.

  • #6
    New to the CF scene
    Join Date
    Oct 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Fixed the problem one way but still want to know how to work with spaces

    OK, when fine motor skills don't do the trick, hit it with a hammer.
    Did the following in MySQL
    Update sitelogins
    Set site = replace(site, " ", "_")
    Execute
    Bang! No spaces, happy happy joy joy.

    Now, I still do want to know what's up with the space issue. I know I'll run into this again and don't want to not allow spaces in MySQL data.

    TIA for tips and pointers (and feel free to point me to resources rather than solving the problem for me - I like to know how to fish, just give me enough of a pointer to find the answer).

    Doug

  • #7
    Regular Coder
    Join Date
    Aug 2004
    Location
    The US of A
    Posts
    767
    Thanks
    1
    Thanked 0 Times in 0 Posts
    There is something about SQL in general that doesn't like spaces, or at least having spaces submitted to it. What I meant when I said convert is changing this:

    Code:
    table_1
    
    text
    -----
    blah
    -----
    blah
    -----
    blah
    to

    Code:
    table_1
    
    text | id
    -----------
    blah | 1
    -----------
    blah | 2
    -----------
    blah | 3

  • #8
    New to the CF scene
    Join Date
    Oct 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Another bit of solution from a news group

    Someone just suggested I change the line that builds the option list
    from
    echo "<OPTION VALUE=".$row[0].">".$row[0]."</OPTION>";
    to
    echo "<option value='$row[0]'>$row[0]</option>";

    Works great now! Looks like the version I had, since it didn't quote the 'Value' bit resulted in just the first word being seen. With the 'to' verison, the string is fully recognized and works great.

    I'm pretty sure the form is still not completely right, no get or post method, missing bits of the form, and probably some funky logic on my part for the way I'm thinking the code is working vs the way it's actually working...
    More fun.

    Thanks again for the help.

    Doug


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •