Visit Our UNIX and Linux User Community


[MYSQL] problem with spaces in rows


 
Thread Tools Search this Thread
Top Forums Web Development [MYSQL] problem with spaces in rows
# 1  
Old 02-20-2010
[MYSQL] problem with spaces in rows

Hello.

I'm not sure how I can get around this, or what I am doing wrong, but I need some help. Smilie

I want to do an select query looking like this:

PHP Code:
SELECT venueSUMamount 
FROM IWD
WHERE venue 
'Foxy Hollow' 
Unfortunately I need to have spaces in the names in these fields,
is there a way to get around it?
THe page I am making has an drop down menu, which is beeing populated by these names, and uppon selecting one of them, the above query will be issued.

THis is the full code that I am trying to use...

PHP Code:

<?php
if (isset($_POST['venue']))
{
        
$sel_venue $_POST['venue'];
        if (
$sel_venue == "All venues")
        {
                unset(
$query);
                echo 
"<div align=\"center\">";
                echo 
"Total donations collected from each venue <br>";
                echo 
"<table width=\"398\" height=\"29\" border=\"1\">";
                
mysql_connect("localhost""user""pass") or die(mysql_error());
                
mysql_select_db("waid") or die(mysql_error());
                
$query "SELECT venue, SUM(amount) FROM IWD GROUP BY venue";
                
$result mysql_query($query) or die(mysql_error());
                while(
$row mysql_fetch_array($result))

                {
                                                echo 
"<tr>";
                                                echo 
"<th width=\"199\" height=\"25\" align=\"left\" valign=\"middle\" scope=\"col\">";
                                                echo 
$row['venue'] . ":";
                                                echo 
"</th>";
                                                echo 
"<th align=\"left\" valign=\"middle\" scope=\"col\">";
                                                echo 
"$" $row['SUM(amount)'];
                }
                echo 
"</div></th></tr></table>";
                
mysql_close();
        }
        else
        {
                unset(
$query);
                echo 
"Total donations collected from ";
                
mysql_connect("localhost""user""pass") or die(mysql_error());
                
mysql_select_db("waid") or die(mysql_error());
                
$query "SELECT venue, SUM(amount) FROM IWD WHERE venue='$sel_venue'";
                
$result mysql_query($query) or die(mysql_error());
                while(
$row mysql_fetch_array($result))
                {
                                                echo 
"<div align=\"center\">";
                                                echo 
"<table width=\"398\" height=\"29\" border=\"1\">";
                                                echo 
"<tr>";
                                                echo 
"<th width=\"199\" height=\"25\" align=\"left\" valign=\"middle\" scope=\"col\">";
                                                echo 
$sel_venue ":";
                                                echo 
"</th>";
                                                echo 
"<th align=\"left\" valign=\"middle\" scope=\"col\">";
                                                echo 
"$" $row['SUM(amount)'];
                }
                echo 
"</div></th></tr></table>";
                
mysql_close();
        }
}
?>
Everything else is working on the page.. except this. :/
# 2  
Old 02-20-2010
Possible try using:

Code:
where venue like

instead of
Code:
where venue =

# 3  
Old 02-21-2010
Sorry, I am not fully understanding the problem. Perhaps it is because I am reading this post on my mobile phone.

Are you getting an error message in the log file? If so, please post it? Or are you seeing an HTML formatting issue?

---------- Post updated at 07:56 ---------- Previous update was at 03:07 ----------

OBTW (back on my desktop computer), this statement does not look right, or it is not how I would write it:

PHP Code:
$query "SELECT venue, SUM(amount) FROM IWD WHERE venue='$sel_venue'"
I would write this:

PHP Code:
$query "SELECT venue, SUM(amount) FROM IWD WHERE venue=".$sel_venue
or

PHP Code:
$query "SELECT venue, SUM(amount) FROM IWD WHERE venue=$sel_venue"
in PHP, single quotes means "take what is inside literally and do not expand". Double quotes means "expand the variables inside". When you mix single and double quotes (inside each other) you can have huge problems in PHP.

Better for this, and considered the least confusing is:

PHP Code:
$query 'SELECT venue, SUM(amount) FROM IWD WHERE venue='.$sel_venue
The reason for this is that 'SELECT venue, SUM(amount) FROM IWD WHERE venue=' does not require any variable expansion inside the single quotes.
# 4  
Old 02-21-2010
Thank you Neo...

Unfortunately, this didn't work.. and I think the reason behind it (in the case with your code) is because there are blank steps (spaces) in the names that I am using.

Here is an example of what the database looks like:

id venue amount
1 Gaia streams office 160
2 Foxy Hollow 100
3 Foxy Hollow 160
4 Foxy Hollow 160

As you can se, the venue names "Foxy Hollow" or "Gaia Streams Office" has spaces in them, which gives me this error with your code:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Hollow' at line 1


To get back to the root of the problem, as I tried to describe it when I first wrote the post in the forum.. how do I issue the query (basically, not only in PHP, I mean, I am given the same problem when trying in the console) so that I can ask the database to sum the amounts on, for example the venue "Foxy Hollow" as neither single nor doubble quotemarks works when using " ... WHERE venue='Foxy Hollow' "
# 5  
Old 02-21-2010
Sorry, I don't think blanks in your SQL query is the problem. We send MySQL queries with blanks in the string everyday.

You have a different problem, I think. You can easily test it by using the same query and putting an entry in the DB without a space in the string.
# 6  
Old 02-21-2010
I wonder in that case what my problem is...

I issue the following query in phpMyAdmin:

PHP Code:
SELECT venueSUMamount 
FROM IWD
WHERE venue 
'Foxy Hollow' 
And get this result:
venue SUM(amount)
NULL NULL

If I take away the single quotes, I get an error:

PHP Code:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Hollow' at line 1
SELECT venueSUMamount 
FROM IWD
WHERE venue 
Foxy Hollow 
If I have entries without spaces (for example FoxyHollow) the query works like a charm..

I guess I could convert the names to replace blanks with underscores.. but I'd rather not. :/

Edit: Damn it, id Didn't work.. but, now I really don't understant this.. it worked yesterday.. I HAVE made some changes somewhere, dut damn me if I could remember what.. there IS something wrong with the query.. my apologies... now.. I just need to find out WHAT is wrong with my query.. :/

Last edited by noratx; 02-21-2010 at 06:14 AM..
# 7  
Old 02-21-2010
Also, you can try replacing the space in your query with the ASCII char for a space. I am on my mobile and don't have that char off the top of my head.

Previous Thread | Next Thread
Test Your Knowledge in Computers #399
Difficulty: Medium
The 11i v2 release of HP-UX introduced kernel-based intrusion detection, strong random number generation, stack buffer overflow protection, security partitioning, role-based access management, and various open-source security tools.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Mysql: How to update value in 27000 rows?

Hello, some member created 27000 posts in wrong section (lol :D) so i need to edit all his entries to get new section ID. SELECT * FROM `phpbb_topics` WHERE `topic_first_poster_name` LIKE "%ozerway%"; this will select all his topics... the column with forum id is named "forum_id" and... (3 Replies)
Discussion started by: postcd
3 Replies

2. Shell Programming and Scripting

MySql split rows

Dear community, I have to split string in table and list all values. I'll skip the code and jump directly to mysql query. This is the table: category title ======= ======= 7,3 title 1 1,3 title 2 1,2,3 title 3 Now, what I need is split category into single... (2 Replies)
Discussion started by: Lord Spectre
2 Replies

3. Shell Programming and Scripting

Retrieve multiple rows from mysql and automatically create a table

Hi, i want to create a table automatically based on another table (sms_key). For example; If user create a new row with sms_keyword field: IRC then a table created automatically (with some field on it, like: name, ph_number, messages). select * from sms_key; +-------------+ |... (1 Reply)
Discussion started by: jazzyzha
1 Replies

4. UNIX for Dummies Questions & Answers

[SOLVED] splitting a single column(with spaces) into multiple rows

Hi All, My requisite is to split a single column of phonemes seperated by spaces into multiple rows. my input file is: a dh u th a qn ch A v U r k my o/p should be like: adhu a dh u (3 Replies)
Discussion started by: girlofgenuine
3 Replies

5. Programming

Getting Rows from a MySQL Table with max values?

I feel stupid for asking this because it seems that MYSQL code isn't working the way that I think it should work. Basically I wrote code like this: select * from `Test_DC_Trailer` HAVING max(DR_RefKey); Where the DR_RefKey is a unique numeric field that is auto iterated (like a primary key)... (7 Replies)
Discussion started by: Astrocloud
7 Replies

6. UNIX and Linux Applications

Online insert MySQL rows by perl-script

Hello, Met a problem when I tried to insert rows to MySQL database from an old book that fits my learning level (MySQL and Perl for the Web, by Paul DuBois, 2001). First, under mysql console I created a database: webdb and the table: todo. Then I draft the perl-cgi script to have online page.... (0 Replies)
Discussion started by: yifangt
0 Replies

7. Shell Programming and Scripting

convert rows (with spaces) to columns

Hey all, I have a list in the format ; variable length with spaces more variable information some more variable information and I would like to transform that 'column' into rows ; variable length with spaces more variable information some more variable information Any... (8 Replies)
Discussion started by: TAPE
8 Replies

8. Web Development

Mysql question: Best way to update a column containing 8 million rows

Hi all, I was wondering if anyone knew a good/safe way to update a single column in a table that could contain upto 8 million rows... simple command like: UPDATE set blah=foo where bar=XXX; I will be running this on tables being written to and tables that have already been created. ... (3 Replies)
Discussion started by: muay_tb
3 Replies

9. Solaris

finding & replacing blank rows/spaces in a file

Can anyone help me find and replace blank rows in a file with a numeric value (ie blankrow=someTxtOrNumValue), the file is over 500,000 rows long so it would need to be the quickest way as I'll need to do this for multiple files...I would be greatfull for any suggestions....thanks sample file:... (2 Replies)
Discussion started by: Gerry405
2 Replies

10. UNIX for Advanced & Expert Users

MySQL problem >> missing mysql.sock

MySQL on my server is down.... I figured out that the mysqld process isn't running. When I try to run it, it says it can't find mysql.sock Any suggestions? Here's what I can't do: can't be root don't have physical access (do stuff via SSH) reinstall MySQL (need to keep the current MySQL... (8 Replies)
Discussion started by: _hp_
8 Replies

Featured Tech Videos