PHP/MySQL and 'space'

Butters · 27 Mar 2008 at 13:46

I'm running a query which wont give me the result because the entry in the MySQL database is "The Name", "The Car", "The House"

For single word results its fine.

I echoed the query and got this

Code:

select * from siteform where sitename ='The'

So its tripping up over the space.

Any way around this?

TIA

shine · 27 Mar 2008 at 13:49

Change the query to use LIKE:

Code:

select * from siteform where sitename LIKE 'The%'

Butters · 27 Mar 2008 at 13:55

Ahh, doesnt really work though.

Reason being, there is a page before this where all options are listed as a drop down pulled dynamically from the database. I'd end up with all the results displaying "The".

I need to remove or deal with this space earlier on.

shine · 27 Mar 2008 at 14:00

Ah - I may not be clear on what you are having problems with. Are you saying that if you try to search for "The Car" - the query ends up as:

Code:

select * from siteform where sitename ='The'

marc2003 · 27 Mar 2008 at 14:01

Code:

select * from siteform where sitename LIKE '%The %'

??

Butters · 27 Mar 2008 at 14:18

shine said:
Ah - I may not be clear on what you are having problems with. Are you saying that if you try to search for "The Car" - the query ends up as:

Code:

select * from siteform where sitename ='The'

Yes,

Marc2003, nope. But I was unfair as I missed a bit out.

Baiscally, the page before asks the user to select from a drop down list.

Based on that selection, some JS looks for the change and runs another page (site.php).

This has the query in but has an extra line at the top

Code:

<html>
<?php
$sitename=$_GET['q'];

//This is the query for the administration; 
//we are obtaining all articles that are available with all columns.
$query="select * from siteform where sitename ='" .$sitename. "'";

I think I need to use the escape function in JS.

EDIT: if I put a _ as a substitute to space it works but that means I have to change a million names.

marc2003 · 27 Mar 2008 at 14:34

have you got quotes around the option values in your html?

shine · 27 Mar 2008 at 15:22

Can you echo the sitename variable to make sure that it contains the value you expect.

Butters · 27 Mar 2008 at 16:34

marc2003 said:
have you got quotes around the option values in your html?

They are not option values as such

Code:

<form id="test">
<?php

include("connect.php"); 

$query="SELECT name FROM site ORDER BY name ASC";

/* You can add order by clause to the sql statement if the names are to be displayed in alphabetical order */

$result = mysql_query ($query);
echo "<select name=site class=st_1 id=site onchange='showUser(this.value)'>Site Name</option>";
// printing the list box select command

while($nt=mysql_fetch_array($result)){//Array or records stored in $nt
echo "<option value=$nt[name]>$nt[name]</option>";
/* Option values are added by looping through the array */
}
echo "</select>";// Closing of list box 

?><br /></br />
<form>

shine said:
Can you echo the sitename variable to make sure that it contains the value you expect.

If I Echo the sitename variable it just says "The"

marc2003 · 27 Mar 2008 at 19:33

Butters said:
They are not option values as such

Code:

echo "<option value=$nt[name]>$nt[name]</option>";

er yes that is.... :confused:

if you view the source in a browser, do you have quotes around the values? i'm guessing not.... try this instead.

Code:

echo '<option value="' . $nt[name]. '">' . $nt[name] . '</option>';

Butters · 27 Mar 2008 at 22:33

marc2003 said:
er yes that is....

if you view the source in a browser, do you have quotes around the values? i'm guessing not.... try this instead.

Code:

echo '<option value="' . $nt[name]. '">' . $nt[name] . '</option>';

Lol, what I meant was, not options like <option value="index.html">Home</option> etc.

However, you've sorted it, so thanks for that. Now working smoothly!