SQL Select Problem, Variables with spaces or commas causing problems :(

[darthmahon]

Hey,

Ok using this drop down list to automatically change URL's. This form is the on the page "meetings-choose.php" and the output page of this data is called is "meetings.php".

<form name="areaselect">
<select name="area" onChange="location=document.areaselect.area.options[document.areaselect.area.selectedIndex].value;" value="GO">
    <?
    // Creates the Person dropdown
    while ($row = mysql_fetch_array($result))
    {
        $subarea_name = $row['subarea_name'];

    echo("<option value=meetings.php?data=$subarea_name&type=area>$subarea_name</option>\n"); 

}

?>
</select>
</form>

Now, for example, if $subarea_name was "Hello, You", when it does the query on the meetings.php page, it gives this error:

Error in query: SELECT * FROM SECTIONS WHERE id=4. You have an error in your SQL syntax near '= 'Hello,'' at line 1

This is the query code on "meetings.php", which is used above:

/* generate sql query */
$meeting = "SELECT * FROM MEETINGS WHERE $type = '$data'";

$meetingresult = mysql_query($meeting)or die ("Error in query: $query. " . mysql_error());

Any ideas? Works fine if the variable equals one word for example "Hello" but if you put a comma or space it causes problems.

Cheers,
Chris

[findapollo]

You're calling your query $meetings and probably reusing code from when it was $query, because your error is printing the wrong query. Makes it hard to debug.
I'm interested to see the actuall $meetings query, without it, it's hard to say for sure, but my first guess is that it's not recognizing the $type variable.
Are you setting it:
$type = $_GET['type']; ???

One thing about your database design too:
If you have a table that has the subarea_names and and ID, why don't you just store the subareaID in the Meetings table instead of the name?
First of all, this save size, and second, you wouldn't need to be doing selects with a string and you could use the ID

[findapollo]

Looking at it again, I think i found your problem:

echo("<option value=meetings.php?data=$subarea_name&type=area>$subarea_name</option>\n");

Because of the space you'd need to quote the value:

echo "<option value='meetings.php?data=".$subarea_name."&type=area'>".$subarea_name."</option>\n";

[darthmahon]

Excellent, works fantastic.

Yea I think I will change the name to id, seems like an easier option.

Then I have to figure out selecting the name by id which means I have to select from another table, makes lots of selects, oh well 🙂

Cheers! 😃

[findapollo]

I don't think it should be that complex...

If your options in your select have the form:
<option value="subID">subName</option>,

the meetings.php only needs one select:
select from meetings where subID=$area

I think you could make the jump-menu simpler too, here's what it would look like:

<form id="areaselect" action="message.php">
<select name="area" onchange="document.areaselect.submit();">
<option value="1">hello, you</option>
....etc....
</select></form>

Only have to loop throught the select on the table that holds the subareas to fill the options.

The ?area=subID should get appended to the URL when the form is submitted automatically.

Hope that helps simplify this for you.

[findapollo]

The hardest part will be, if you are storing the subarea by name in the messages table, to update that to an ID. (only needs to be done once though)
Once you get another table set-up for the subareas you could do something like this:

first get a result of all the ID's:

$subs = mysql_query("select * from subareas");

then do the updating:

while ($sub = mysql_fetch_assoc($subs)) {
   $update = sprintf("update messages set area=%u where area='%s'",$sub['ID'],$sub['name']);
    mysql_query ($update);
}

Now you can do as I described before:
"SELECT * FROM meetings WHERE area=$_GET['area']");

[darthmahon]

Will have a look at this later today, but you've given me some good ideas 🙂

Will let you know how I get on.

Cheers,
Chris