Home > Archive > PHP Programming > October 2004 > Double query form, result of 1st query drops anything after a space
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Double query form, result of 1st query drops anything after a space
|
|
|
| Noob alert.
Code is below.
File is saved as a .php.
What I'm trying to do:
User uses 'select' box drop down list to pick a value.
Value ($site) is derived from a db query. This works fine.
Value selected is used as the 'where' clause of the 2nd query.
If $site is a single word, the 2nd query works like a charm.
If $site is more than one word (has spaces), the query returns a null
because $site is trimmed back to just the first word (I can tell that
because I echo the value of $site.
I've poked around here and googled but no joy. Any tips are
appreciated. Soooo close...
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 populate the select box - this bit works great.
// >>>>> note, if I use \"$site\" below, I get nothing. using site as
the name seems to work.
$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>";
// >>>>> next line - if the value of $site is something like 'fred joe',
the echo $site prints as 'fred' and the 2nd 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>
| |
|
| dogu <dfinnerathome@netscape.net> wrote in news:gbudnWqdxpwKLefcRVn-
sQ@metrocastcablevision.com:
> echo "<select name=\"site\">";
I think that would produce <select name=site>, which isnt right. right?
try echo "<select name='$site'>";
| |
|
| dogu <dfinnerathome@netscape.net> wrote in news:gbudnWqdxpwKLefcRVn-
sQ@metrocastcablevision.com:
> <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 populate the select box - this bit works
great.
> // >>>>> note, if I use \"$site\" below, I get nothing. using site as
> the name seems to work.
>
> $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>";
>
> // >>>>> next line - if the value of $site is something like 'fred
joe',
> the echo $site prints as 'fred' and the 2nd 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>
>
Two things, where do you assign $site a value before you actually use it?
second, you are using the $db link instead of the connect link for your
querys (chose something other than result so you dont overwrite it).
And no I didnt catch that immediately :P
| |
| Geoff Muldoon 2004-10-24, 8:55 pm |
| > dogu <dfinnerathome@netscape.net> wrote
> echo "<select name=\"site\">";
Try:
echo '<select name="'.$site.'">';
Geoff M
| |
| Michael Fesser 2004-10-25, 8:55 am |
| .oO(dogu)
>User uses 'select' box drop down list to pick a value.
>Value ($site) is derived from a db query. This works fine.
>Value selected is used as the 'where' clause of the 2nd query.
>If $site is a single word, the 2nd query works like a charm.
>If $site is more than one word (has spaces), the query returns a null
>because $site is trimmed back to just the first word (I can tell that
>because I echo the value of $site.
The reason is the way how you build your select box. Have a look at the
generated HTML code or even better run it through the W3 validator.
><form>
Where are the required form attribtues method and action?
>// >>>>> run query and populate the select box - this bit works great.
>// >>>>> note, if I use \"$site\" below, I get nothing. using site as
>the name seems to work.
Sure, because you don't want to print out the value of the (undefined)
variable $site, but the literal string 'site'.
>$result = mysql_query($query, $db);
>echo "<select name=\"site\">";
echo '<select name="site">';
>if(!$result) die ("query failed");
> while($row = mysql_fetch_row($result)) {
>echo "<OPTION VALUE=".$row[0].">".$row[0]."</OPTION>";
And here's the problem. Assuming $row[0] contains 'foo bar', the result
will look like this:
<OPTION VALUE=foo bar>foo bar</OPTION>
Got it? Not try it this way:
echo "<option value='$row[0]'>$row[0]</option>";
The result will be:
<option value='foo bar'>foo bar</option>
But why not simply use a kind of ID for the values instead of a complete
site name? Would avoid lots of problems.
>$query2 = "Select * from sitelogins where site = '$site'";
First: You want to use $_GET['site'] or $_POST['site'] (dependent on the
used submission method) instead of just $site.
http://www.php.net/manual/en/security.globals.php
Second: You want to use at least mysql_escape_string() before using a
user submitted string in a query. Check for magic quotes first.
http://www.php.net/manual/en/securi...l-injection.php
http://www.php.net/manual/en/functi...cape-string.php
http://www.php.net/manual/en/function.addslashes.php
http://www.php.net/manual/en/functi...-quotes-gpc.php
HTH
Micha
| |
| Michael Fesser 2004-10-25, 8:55 pm |
| .oO(dogu)
>Michael Fesser wrote:
>What I appear to have created is a form that doesn't go anywhere. I'm
>not calling a different PHP file to process the data, it's all in the
>same file/form/code/whatever it's called.
Quite usual, but nevertheless the browser has to know where to send the
data.
>Set a value one place on the
>form and another bit of the form uses the input. Not sure if this is
>supposed to work, but when I hit my 'get password' button, stuff happens
>and results pop into a table.
>Should I be doing a 'call to self' (not sure of the format but know I've
>seen something like that soemwhere)?
Yep, at least the action-attribute is required. Use $_SERVER['PHP_SELF']
for its value. The method-attribute is not required (defaults to 'get'),
but IMHO makes the code more readable:
<form action="<?php print $_SERVER['PHP_SELF']?>" method="get">
>WHOA! It works like magic! I need to play with the logic of my 'bad'
>version and yours to understand the difference. Is this a magic quote
>thing?
Nope. The answer is much simpler. Without quotes in
<option value=foo bar>foo bar</option>
only 'foo' is seen as the attribute's value, 'bar' is considered as
another (undefined) attribute, because in HTML attributes are separated
by blanks. So to tell the browser, that all the words belong to the one
attribute, just put quotes around them. BTW it's a good idea to always
quote attribute values (with single or double quotes), this avoids such
errors.
>Two reasons. For the particular design I'm working with, I want the
>choice displayed to the user derived directly from the db AND be human
>readable.
No problem so far, you could use an ID for the internal value and the
human readable stuff for the display. Assuming your records in the
database look like this ...
ID | site
----+----------------
1 | This is foo
2 | This is bar
42 | Nothing special
your select box could look like this ...
<select name="site">
<option value="1">This is foo</option>
<option value="2">This is bar</option>
<option value="42">Nothing special</option>
</select>
IMHO this would also make the querying of the DB easier and more
reliable. You don't have to deal with quoting, escaping and probably
encoding stuff anymore. You just have to do the "standard check" if a
value was submitted at all and use its integer-value in the query:
// This makes sure that $site always contains a numeric value, in case
// of an error it is set to zero
$site = isset($_GET['site']) ? intval($_GET['site']) : 0;
$query = "SELECT ... FROM ... WHERE site = $site";
Or both together in one statement using sprintf():
// %u is a placeholder for an unsigned integer
$query = sprintf('SELECT ... FROM ... WHERE site = %u',
isset($_GET['site']) ? $_GET['site'] : 0);
Just some ideas.
>Second, I know I'll run into this kind of problem again (spaces in
>strings) and I want to be sure I know how to deal with them.
OK.
But as said earlier: In case of problems have a look at the generated
HTML-code (with an editor capable of syntax highlighting if available)
and use the W3 validator. It will complain about such errors.
>
>See discussion further up about the form actions. I'm not using a post
>or get, just running code inside one php file
Sure, but the browser has to submit the form first, before you can
process its data. Even if you send it to the same file, you have to
choose between get (default) or post. But what I was referring to was
the register_globals thing. On recent PHP installations with default
configuration the variable $site would be undefined, the correct way to
access its value is to use one of the superglobal arrays $_GET or
$_POST. This will work on all systems, regardless of the configuration.
>Need to read up on these things.
Do a Google on (Advanced) SQL Injection. When working with scripts and
especially databases you should know about some of the dangers and risks
that exist there and how to secure your scripts. The WWW is no play-
ground, it's a battlefield with thousands of crackers, script kiddies
and other parasites being your enemies.
Micha
| |
|
| dogu <dfinnerathome@netscape.net> wrote in
news:dcKdnTrmGZERG-DcRVn-oQ@metrocastcablevision.com:
> Theo wrote:
>
> Lost again...
>
> What I thought I was doing was creating the variable in the line that
> creates the drop down select box, echo "<select name=\"site\">";
> That's where I thought the variable name was created. If it gets
> created somewhere else, I don't have a clue where.
>
> Still lost. Every example of php connecting to MySQL uses the same
> format as my code.
>
> $db = mysql_connect($server, $username, $password);
> mysql_select_db($database, $db);
> Isn't $db the connect link? Can't I use it throughout the code?
> Are you referring to my $result2? Do I need to create something like
> a second connect als $db2 = mysql_connect($server, $username,
> $password)?
>
> I know I'm getting trapped in some kind of circular logic hell.
> Everything I've used for references either has good HTML examples with
> no PHP/MySQL, or good PHP with limited HTML or simple HTML form
> creation with no clever modifications (ie programmatic population of
> lists) or... but never a fully built example of the whole thing.
>
> Once all the pieces come together, this'll be easy. I'm just not
> seeing the solution. Sorry for my slowness and thank you for your
> patience.
>
> Doug
>
ok... first
you are submitting a query before you assign $site a value. if you do a
value check before submitting the query you will see that it is a null
value.
The line you commented out...
//$query = "Select site, username, password from sitelogins where site =
'$site'";
wont work because $site is null. So the question is, what value do you
want to assign to it, and where is it coming from... assuming its not the
same every time?
----
when checking values add a line like
print "my value is $value"; exit();
before you use it. so you can see what the value is at that point. If you
get something unexpected, or get 'my value is' and then nothing
afterwards, you need to check how you are assigning your values.
---
for the other point, youre right I got that backwards. Sorry bout that.
:-)
| |
| Michael Fesser 2004-10-26, 3:55 am |
| .oO(dogu)
>Man I love the internet. I cannot thank you enough for your help.
You're welcome.
Micha
|
|
|
|
|