Sequential MySQL with PHP/HTML fails when spaces are invloved

dogugotw · 10-23-2004, 10:20 PM

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>

boeing747fp · 10-24-2004, 01:33 AM

try sending it with + as a space and on the receiving page, convert it back to space from +

Kurashu · 10-24-2004, 02:38 AM

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.

dogugotw · 10-24-2004, 02:26 PM

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 +

dogugotw · 10-24-2004, 02:29 PM

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.

dogugotw · 10-24-2004, 02:43 PM

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

Kurashu · 10-24-2004, 08:49 PM

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

dogugotw · 10-26-2004, 12:26 AM

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