MYSQL :: Using IF Statements

SQL Statements
Ive got a database with the following two tables:

Person(name,address,cityname)
and
City(cityname,population)

I want to returen all Persons that live in the city of Bristol. To do that what it be:

select * from Person where name = "Bristol"
?

What about about a statement what will count the number of Persons in Bristol? Would that be:

select count (*) from Person where cityname="Bristol"
?
Finally if i wanted to setup a statement which will return the population of Jim Smith's city, would that be:

select population from City where name="Jim Smith"
?

Thanks

Using IF Statements
I have the following code:

IF (SELECT id WHERE base_narrative_id = 1) THEN
UPDATE `user_narrative`
SET `new_message` = 'You have new message'
WHERE base_narrative_id = '1'
AND user_id != '1'
ELSE
INSERT INTO user_narrative(id, base_narrative, user_id, new_message)
VALUES('', '4', '2', 'new message')
END IF;

But it doesnt work. I think the problem is with my if statement. How should it look? All i want to do is find out if there is an entry for the specified base_narrative_id, and if there is update it, if there's not create one.

SQL Statements
I'm a t a loss and maybe it can't be done, but I thought I'd ask here befor giving up. I wan to evaluate the result of a query, then run another query based on the results. For instance, let's I have:

SELECT locality.Geom As point
WHERE locality.LocalityNr='1'
IF point='point'
THEN do something
ELSE do something else;

My searches on MySQL and IF haven't yielded me much with regards to this logic.

Can You Merge These Two SQL Statements?
The first one is to get the row numbers for my paging.
The second one is what is ran for the actual paging.
I assume you can't join the two because of the LIMIT and offset values.
Am i right in thinking that?
Thanks
Matt

$query = "SELECT COUNT(ct_vehicles.id) AS numrows FROM ct_vehicles WHERE (".$where.")".$minPriceDB.$maxPriceDB." ORDER BY ct_vehicles.price;";

$search = "SELECT ct_vehicles.* FROM ct_vehicles WHERE (".$where.")".$minPriceDB.$maxPriceDB." ORDER BY ct_vehicles.price LIMIT ".$offset.", ".$rowsPerPage.";";

Grant Statements...
i understand Grant statement is used to specify access limitation to certain users in what they can do to the tables/database ~
i have a table as shown in the statement below:-

create table admin(UserID int(4) zerofill not null auto_increment,
UserName varchar(15) not null,
Password varchar(15) not null
primary key(userId)
);

i can create a set of access privileges for one particular username ~ what if there are some new users being registered into this table??does it means that i have to type the grant statement for each and every new users again and again~ to specify the limit for each new users?

Multiple SQL Statements
I wanted to know how I can make multiple select statements returin into one single row. I tried, but something funny happens:

this works, I get one row with columns currentdate and total filled with data...

select curdate() as currentdate,
sum(amount) as total from bills where date="xx-xx-xxxx"

this DOES NOT work, I expected to get one row, with column 'deposits' and column 'total' filled

select sum(amount) from deposits where date="xx-xx-xxxx",
sum(amount) as total from bills where date="xx-xx-xxxx"

using mysql 4.1

Is this a standard SQL limitation or am I not aware of something?

Importing Sql Statements
I have a text file with about 90,000 insert statements (as below). Is there an easy way I can get these into my mysql database in one fell swoop? Can anyone tell me how and perhaps that the syntax is?

Select Statements
I am trying to find all fields with a distinct value. example:

SELECT * From $Monthn With('%s') and getting error messages.

Insert Statements
I'm using bugzilla on Solaris 9. I'm trying to determine why bugzilla displays some bugs, but not others. Below I have two mysql insert statements. The first displays properly. The 2nd does not.

I wonder if someone can/would tell me why? [ Yes, I know this is a mysql forum. I would appreciate it you can tell me how these two statements differ from that degree. ]

INSERT INTO bugs VALUES (2,1,'','normal','CLOSED','2004-09-24 21:47:49',20040925181616,'TEst 2','Windows XP','P2',2,'PC',1,'1.0',2,'INVALID','--
-',0,'',0,'','2004-09-25 18:16:17',1,1,1,0.00,0.00,NULL);

INSERT INTO bugs VALUES (15,1,'','normal','NEW','2004-10-06 09:54:25',20041024210155,'Logging configuration doesn't work correctly','Windows XP
','P2',2,'PC',1,'1.0',21,'','---',0,'',0,'','2004-10-24 21:01:56',1,1,1,0.00,0.00,NULL);

Also, how would I reverse/delete either of these statements?

SELECT Statements
I have two tables. each has 60,000 entries.

Here is the structure

Table names
id
chum
lastname
firstname

Table licenses
id
chum
state
license

When I do SELECT * FROM names, licenses WHERE licenses.chum = names.chum;

It freezes.

Any ideas?

Updates And UDF Statements
I have a particular UDF function called pcd which returns the first 1
or 2 letters of a postcode (postcode area), it works fine in selects
but when i use it in a update the column being updated ends up with
the first column in the table, 10 spaces, then the 2nd column in the
table, instead of whatever the UDF function returns.

Has anybody else experienced similar problems, im losing my faith in
mysql a bit, im trying to use it for serious business applications but
might have to find an alternative. I prefer linux servers to windows,
any advice, like Max DB?

Join Statements
If I have a table full of company names, and the company can be in one or more catagories, i.e. shipper, reciever, holder, etc. and I want to pull out a recordset that shows the shipper, holder, and reciever of a product... Code:

Select Statements
I would like to know if there is a limit to the amount if columns a select statement can reference.

The situation is that there are tables with columns that have foreign keys to other tables and so on... The SELECT must result in a calculated field.

This is a problem with other databases, there are to many columns referenced. Will it be a problem for mySQL.

Prepared Statements
Is it possible to write & execute a prepared statement us MySQL Query Browser?

Prepared Statements
I am trying to prepare a dataset to use in R for statistical analysis. R is pretty rubbish on the DBMS front so I want to have everything prepared in MySQL.

I currently have a dataset (weather) showing weather data over time where there are 3 columns: "year", "month" & "total_rainfall". I would like to create a dataset showing average rainfall over a 3 month period. This is easy for one such period e.g

CREATE TABLE avgweather
SELECT Year,
AVG(total_rainfall) AS avg_rain_jan_to_mar
FROM weather
WHERE month BETWEEN 1 AND 3
GROUP BY Year
;

What I can't figure out what to do, is how to make the dataset avgweather contain lots of different 3 month periods without a lot of copying and pasting.

I would like avgweather to contain lots of columns e.g. "year", "avg_rain_jan_to_mar", avg_rain_feb_to_apr", "avg_rain_mar_to_may"......and so on, but I just can't make prepared statements cooperate!

Even better, would be if instead of the column names containing months as text, I could use the numbers that I specify in the where clause. E.g. "avg_rain_jan_to_mar" would become "avg_rain_1_3"

Passing Sql Statements To Os
just wondering, is there a way to run sql statements as a single shell command? What I mean is that I don't want to have to log in the mysql.exe util. For example, I can log into mysql and run a sql statement like this:

mysql -u root -p
...

mysql> use MyDBName
mysql> insert into table1 blah blah..

However, I would prefer if I could just skip the whole logging ing part entirely.

According to the mysql documentation, this should work:

C:mysqlin>mysql --user=root --password=rootpass DBName insert into table1 blah blah..

but that doesn't work. Is what I'm trying to achieve possible? Seems to me like it should be, but the mysql documentation isn't very clear on how to do this.

Mysql IF Statements
I looked through the mysql manual but their examples are pretty confusing
anyone think they could show me the correct way to write an IF statement inside of a query like this one?

SELECT orders.order_id, students.fname, students.lname, order_status.order_status_name, course.name AS course_name, date_format(class.startdate, '%m/%d/%Y') AS startdate, date_format(class.enddate, '%m/%d/%Y') as enddate, order_type.order_type FROM orders LEFT JOIN students ON orders.student_id = students.student_id LEFT JOIN order_status ON orders.order_status = order_status.order_status_id LEFT JOIN class ON orders.class_id = class.class_id LEFT JOIN course ON orders.course_id = course.course_id LEFT JOIN order_type ON orders.order_type = order_type.type_id IF(order_type.type_id == 1) THEN SELECT lm_number FROM lmcharge WHERE orders.order_id = lmcharge.order_id; END IF WHERE orders.order_id = '8'

PHP Multiple SQL Statements
When i try to do the following in PHP I get errors. Is this not permitted or
is this due to a setting somewhere? Can i do only one SQL statement per call
to mysql_query?

$sql = "CREATE TEMPORARY TABLE tmp
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1';
INSERT INTO tmp
SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';
SELECT * from tmp;
DROP TABLE tmp;";

$res = mysql_query($sql);

Insert Statements
INSERT INTO `products` (`product_id`, `product_name`, `level`, `gender`) VALUES
  ('1',New Balance 855,'1','men'),
  ('2',Brooks ASR2,'1','men'),
  ('3',Montrail Hurricane Ridge XCR,'1','men'),
  ('4',Spira Genesis,'1','men'),
  ('5',Spira Vortex,'1','men'),
  ('6',New Balance 587,'1','men'),
  ('7',Merrell Stormfront,'1','men'),
  ('8',Merrell Motovator,'1','men'),
  ('9',Saucony 3D Grid Hurricane 6,'1','men')

I am using a tool extract this data from a table called products to a remote server with a table with the same name.(using phpmysqladmin) For some reason in the extraction process the script created does not include all the quotes, when I place the quotes around the product name it still errors out.

#1064 - You have an error in your SQL syntax near 'Balance 855,'1','men'),
  ('2',Brooks ASR2,'1','men'),
  ('3',Montrail Hurrica' at line 2

I can't determine what's missing, especially because I am using a mysql tool to extract the data to create this script.

Case Statements
Is this a valid query in 4.1?

select
sum(amt) * case when username = 'bob' then 1 else 0 end as 'Bob',
sum(amt) * case when username = 'ted' then 1 else 0 end as 'Ted',
sum(amt) * case when username = 'alice' then 1 else 0 end as 'Alice'
from sales

This structure would work for me in Oracle but in mySQL 4.1 the result sums are wrong.

SELECT Statements
I'm trying to build a database of research publications where we might have multiple authors per papers and the order these authors are displayed is critical. Right now I've implemented a publication table that stores in pub_authors a list of author_id(s) (ie... 42,3,44,2). Based on the order of the list in publications I'd like to return from a query to the authors table a result set who's first record is id 42, second is 3... and so on.

Right now I'm using this select statement, but it returns a result set ordered ASC by author_id:
SELECT author_id, author_first, author_middle, author_last, author_institution
FROM authors
WHERE author_id
IN ( $publication[pub_authors] )

How can I accomplish this or is there a better way of doing this?

Merge Two SQL Statements
i have two sql statements, that i really need to merge into one.
here is the first one:

SELECT loanhistory.Loan_id, loanhistory.user_id, bookrecord.book_id, bookdata.title, bookdata.BookMedium FROM loanhistory, bookdata, bookrecord WHERE Date_ret IS NULL AND Date_due<'$today' AND bookrecord.book_id=loanhistory.book_id AND bookdata.ISBN=bookrecord.ISBN;

and i want to merge this one into it:

SELECT COUNT(*) as Num FROM loanhistory WHERE book_id=book_id AND loan_type=1;

Nested IF Statements
Basically i'm asking why doesn't this sql work? One If works fine but i'm not sure if you can nest them like this.. is there a better way?

This is narrowing down a products table by a variable being passed that is either subcategory, category or neither and then it narrows down by a brand if a brand is being passed.

SELECT *
FROM prodSubCat INNER JOIN (prods INNER JOIN prodCat ON prods.prodCatID = prodCat.prodCatID) ON prodSubCat.prodSubCatID = prods.prodSubCatID
WHERE (IF(subCatIDParam = 0,(IF(catIDParam = 0, prods.prodCatID LIKE '%',prods.prodCatID = catIDParam)), prods.prodSubCatID = subCatIDParam)) AND (IF(brandIDParam = 0,prods.prodBrandID LIKE '%',prods.prodBrandID = brandIDParam)

Order Of Statements
Does the order of the statements in a mysql WHERE clause have an importance of how mysql evaluates the query?

Ex: are those equal?

WHERE id=5 AND title="horse"

and

WHERE title="horse" AND id=5

thx

Prepared Statements
Prepared sql queries are nice, especially, as is often the case with websites, you have about 100 queries that are reused over and over and over again with slightly different parameters.
There is just one hitch. From what I can tell, prepared statements belong to a mysql connection. I.E. if you have a hundred open connections, you cannot share prepared queries among them. Is this true?
I use a connection pool to hand out a mysql connection to every incoming request. If prepared queries can not be shared among connections, do you think it would be a good or bad idea to prepare all 100 sql queries for all of the (possibly over 100) connections? (10,000 prepared queries)?

Online Statements.
I've been directed here b/c I work for a gym who is retooling their website. One feature we've seen on other club sites is the ability to view statements online. Is this related to MySQL? I've heard it's either this or php... We have a mysql database for our members, if that info helps any.

Prepared Statements
prepared statements are to be initialized (defined) each time you connect to database (mysql_connect) or each time mysql is started

Prepared Statements
i've got some questions:
what happens when we deallocate a prepare statement?
can we use same name for 2 prepared statements in a SP by deallocating
first
one before defining second one?
can we use a prepare statement in a loop?
can we use more than 1 prepared statement in a single SP?

Select Statements
One the the fields in that table is holding the name of the players.
How do I search for all people with the name "Andre", with the important
remark, that it should also match "André"!! So with the letter "e" with
an accent "'" on it.
In the Netherlands those names can be spelled with and without the
accent and I do not always know how the name should be spelled correctly.

The query I run at the moment is:

SELECT * FROM member WHERE name='Andre';

But that does not match the names with accents on the characters.

Getting The Create Statements
I have created some simple tables in mysql and i am wondering... is there any way of generating the create statements for the tables and saving them in a txt file.
To make sense I want to know if mysql has any automated features that can output the create statements of any given table into a txt file.

Delete Statements
How do I merge these two statements?

DELETE FROM mytable_thingy WHERE username = 'charley';

DELTE FROM mytable_thing2 WHERE username = 'charley';

Thank you for any help.

I tried:

DELETE FROM mytable_thingy, mytable_thingy2 WHERE username = 'charley';

it doesn't work.

Union Statements
I am trying to create a union statement to join two queries together.
one of the fields on one of the queries is a constant, ie because it is from the table i want it to output the letter 'B'. When i try to union this with a variable in another query, i get the error: "Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'UNION'"

MySQL Statements All Over Webpage
I have just recently installed mySQL 4.1.20 max on a Mac OS X 10.3.9 and when I view my page it has mySQL statements written all over the page. I haven't the slightest clue to what I did wrong since I am new to mySQL. Here is an example of the error...
(mysql): SELECT * FROM mdl_course WHERE id = Ƈ' LIMIT 1
(mysql): SELECT section, id, course, summary, sequence, visible FROM mdl_course_sections WHERE course = Ƈ' ORDER BY section
(mysql): SELECT * FROM mdl_course_categories WHERE parent = Ɔ' ORDER BY sortorder ASC
(mysql): SELECT COUNT(*) FROM mdl_course
(mysql): SELECT c.* ,c.sortorder FROM mdl_course c WHERE c.category = Ƈ' AND c.visible > 0 ORDER BY c.sortorder ASC
(mysql): SELECT * FROM mdl_course WHERE id = Ƈ' LIMIT 1
(mysql): SELECT * FROM mdl_cache_text WHERE md5key = ཾd39efd283de286c9192281666950b3' AND timemodified > ?' LIMIT 1
(mysql): SELECT * FROM mdl_cache_text WHERE id = '-1'
(mysql): INSERT INTO mdl_cache_text ( MD5KEY, FORMATTEDTEXT, TIMEMODIFIED ) VALUES ( ཾd39efd283de286c9192281666950b3', '', 1150385107 )
(mysql): SELECT LAST_INSERT_ID()
(mysql): SELECT * FROM mdl_event WHERE timestart + timeduration >= 1149120000 AND timestart <= 1151711999 AND ( (groupid = 0 AND courseid IN (1))) AND visible = 1 ORDER BY timestart
This is written on the page along with the regular contents of the page. It is very confusing to me. Please help if you can. TIA.

Using Regular Expression Statements.
How would I match 1 in this string 1, 10 and not 10?

Combining Two SELECT Statements
Both of these statements work separately but I can not figure out how to get them to work as a single statement.

SELECT id, hostname, partydate FROM parties WHERE id = $id

SELECT host_id, id, realname, email, comments FROM guests WHERE host_id=$id

I Think it should be something like this:

Load SQL Statements From File (PHP)
How can I load pure SQL statements from a local file through PHP (not the mysql command line)? I found LOAD DATA INFILE, but it is set up for a csv and only 1 table. My statements deal with a couple different tables and I'd hate to have to re-write and split up each table.

My file is 5.5MB also. Is that going to be a problem?

Multiple Statements In One Query
Anyone have an example of how to configure a Tomcat JNDI datasource so that you can execute multiple statements in one query string? I'm hoping there is a <parameter> of some kind that I can set for the resource in my context.xml file where I define the datasource.

Also, how to you configure the Query Browser to support multiple statements in one query string?

Columns And Grant Statements
My simple question: What statement must one use to grant a person privileges on a table EXCEPT on one column?

Revoking previously set columns dont seem to work because those prives are in mysql.tables_priv.

Inspecting Prepared Statements?
MySQL Version: 5.0.21
Java Version: 1.5.0_06
MySQL Connector/J Version: 3.1.12

I've been Googling all day and I haven't been able to find out if there is a way of seeing what statements have been prepared. I'm getting intermittent errors from my web app from some PreparedStatements with an error message of "Unknown column 'x' in 'where clause'". I'd like to verify that the PreparedStatements are getting created correctly but I don't know of a way to inspect those statements on the database server. Any ideas?

Avoiding Long SQL Statements
I am making queries from php. Some of my SQL statements are getting long. I have some that are six lines long and involve 5 tables or more. I can imagine that my queries are going to get longer. They are starting to be un maintainable. How can I improve the situation?

Cloning Prepared Statements
I have a prepared statement on a given connection. Is there an easy way to create a duplicate of the statement in an other connection?

Logging MySql Statements
My server load goes up to 100 and I don´t know why. What I know, if I
shot down the site, the server calms down and it works again.

I have figured out that i have a mysql problem but I can t figure it
out.

Is it possible to log all MySql Statements which are running on my
database ? If the server goes strange again I could check the log file
for the problem.

Nested SELECT Statements
I try to figure out how to use a nested "SELECT" statement after the
"IN" predicate. For example, when I try this code, it doesn't return
anything although it should:

SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar
ecr, auteur aut WHERE ecr.IDLivre = book.IDLivre AND aut.IDAuteur =
ecr.IDAuteur AND book.IDLivre IN ("SELECT book.IDLivre FROM livre book,
livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND
aut.Nom like '%$name%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre
= ecr.IDLivre");

So, my question is the following: How should I change syntax in order
to make this bunch of code work? I mean, under Oracle SQL, this syntax
would be legal and work perfectly, so I'm confused how to solve my
problem.

Reusable SELECT Statements
I was wondering if it is possible at all to declare and then reuse SELECT statements, as illustrated by:

<DECLARE SQL SELECT STMT> AS alias

... and then,

SELECT * FROM alias, (SELECT <something> ... FROM alias,...);

This would simplify writing complicated SQL queries/statements involving multiple tables a whole lot. Is it possible? If so, how?

How To See Sql Statements Send To Server ?
How can I see the lastest Sql Commands/statements which has been send to my mySql server? Are there Any Logfile or command to use ???

Multiple WHERE Statements In SELECT
How can I have more than one WHERE clause, like this:

SELECT
DISTINCT email FROM ehousebo WHERE
waterway="1000 Islands"
OR waterway="Alum Creek Lake"

I tried using HAVING, but that gave an "unknown column" error.

SELECT
DISTINCT email FROM ehousebo HAVING
waterway="1000 Islands"
OR waterway="Alum Creek Lake"

Invalid query: Unknown column 'waterway' in 'having clause'

Monitor SQL Statements Being Executed
How can I monitor what SQL statements are being executed on any table in realtime for any user? Example... I use Oracle Session Manager to do this with oracle. I want the same ability in MySQL 4.1.5

See Prepared Statements Parameters On The Log
Is there any (simple) way of getting a Prepared Statement *with its parameters* on the log file?

Enabling Multiple Statements
I'm trying to execute the following multiple statements using MySql w/ the ODBC connector.

INSERT INTO Sites(Hostname) values('zzz');
INSERT INTO Hosts(SiteID, Hostname) values(last_insert_id(), 'zzz');

I get the following error:

[1064] [1] [0] "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 ';
INSERT INTO SiteHosts(SiteID, Hostname)
VALUES(@@IDENTITY, 'zzz')' at line 2"

Is there something I have to do to enable multiple statements?