Using IF Statement In MySQL Query
I have the following code in one of my scripts.
PHP
$result = @mysql_query("SELECT * FROM Table WHERE id='$id'");
$row = mysql_fetch_array($result);
if ($row['name'] != "") {/* do nothing here */}
elseif ($row['email'] != "") {$row['name'] = $row['email'];}
else {$row['name'] = "<anonymous>";}
Like you can see I'm using an IF statement outside the MySQL query to define the variable $row['name']. But I know that I can use an IF statement inside the query instead of outside it.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Mysql Query Browser...how Do I Ignore " Within A SET Statement?
I'm trying run this in mySQL query browser Update xcart_classes x Set classtext = "5 Year Protection <a href="javascript: void(0);" onclick="javascript: window.open('etp.htm','HELP_POPUP','width=406,height=261,toolbar=no,status=no,scrollbars=yes,resizable=no,menubar=no,location=no,direction=no');" class="PopupHelpLink">Learn More</a>" where Classtext = "Add 5-Year Extended Protection"; Obviously it fails, and I'm pretty sure it's because of the double quotes on my SET statement...any idea on how to avoid that?
Query Statement
I am actually using this for a different database, but don't know what I am getting wrong with my syntax. select * From rents where id = 119,505 order by Town I keep getting an error message. Can anyone see where I am wrong?
Statement In A Query
Is it possible to do an if statement in a query? or something like it? I have a schedule in a table. For the time, I need it to output local time and eastern time. I have the local date and time in a "datetime" column. So depending on where the event is, I need too add to the time in order to produce an eastern time as well. Right now I'm using this: DATE_FORMAT(DATE_ADD(sked.date, INTERVAL +2 HOUR),'%l:%i%p') AS eastt But I need to change the interval number depending on where the event is being held. Does anyone know how? or is there an easier way to accomplish this task?
Technically An If Statement In A Query?
My heirachy is Forum connected to thread via forum_id and then Post connected via thread_id. Some forum rows have require_access set to 1, if that is the case then user must have a row in user_data with a key of ForumAccess, the value as the forum id and the user_id as there user id. I am retrieving a list of threads from the entire forum, however I need to make sure users can't see threads which are in forums they cant access. Here is my query so far. MySQL SELECT `thread_id` as `id` FROM `Post` p JOIN `Thread` t ON (t.`id` = p.`thread_id`) JOIN `Forum` f ON (f.`id` = t.`forum_id`) LEFT JOIN `User_Data` u ON (u.`user_id` = p.`user_id`) WHERE u.`key` = 'ForumAccess' AND u.`value` = f.`id` AND f.`require_access` = 1 GROUP BY p.`thread_id` ORDER BY max(p.`updated`) DESC LIMIT $from, $to I'm unsure on what route I should be going, as essentially I need some sort of if(f.require_access = 1){check user_data row exists if it does then return this thread}
QUERY One Table And Update Another In ONE Statement?
ZIP_CODE: zip_code latitude longitude customers: customer_id ... zip_code latitude longitude The lat and long in the customers table is blank. I want to lookup customers' zip code in the ZIP_CODE, get the related lat & long and update the customers table with that information. The result being that after this query is run, the customers table now has lat & long data.
Help With MySQL Statement
PHP $query = "SELECT * FROM officemap WHERE upper(fname||' '||lname) LIKE upper('%".$search."%') or upper(username) LIKE upper('%".$search."%')";
MySQL IF Statement?
I have a query formatted roughly as below: - SELECT table1.field, table2.field FROM table1, table2 WHERE id = table1.id AND id = table2.id The query returns a list of products, the "id = table2.id" condition refers to a product's review. Some products do not have reviews, but I still want these to be listed? The problem with this query is that they are not. Is is possible to have some sort of an IF statement to fix this?
MYSQL IF Statement
I have mySQL version 4.1.20 I have a statement SELECT UserAccount.UserName, UserAccount.FirstName, UserAccount.LastName, UserChannel.ChannelGK, UserChannel.SubChannelGK, Channels.ChanName FROM UserAccount, UserChannel, Channels WHERE UserChannel.AccountID = UserAccount.AccountID I need to get the Channels.ChanName but it would be an if statement. If(UserChannel.SubChannelGK = 0) THEN I need this in my WHERE clause: UserChannel.SubChannelGK = Channels.ChannelGK Otherwise the Where clause would just need to be this: UserChannel.ChannelGK = Channels.ChannelGK Is there a way to make this work?
MySQL Statement.
I need to have a statement that I can pull data from a database and put it in an excel worksheet. I have already done all of the connecting and pulled some data but now I want to pull it in a differant way. I have 3 tables that all have a column with a unique id called project_seq. These tables contain differant data and I want to bring some columns of data from the tables but not all of the columns and have it matched up with project_seq. Code:
If Statement On Mysql 4.1.8
I'm trying to do a DTS to replicate a msSQL table (let's call it table1) on a mySQL one (let's call it table2). I bring the data from msSQL to a table table2_temp in mySQL and I need to check if there are more rows in the table2_temp than in table2. If that's so, I'll delete all the data from the old table and copy the data from table2_temp to table2. I tried to do this: select @count_ori := count(id) from table2; select @count_temp := count(id) from table2_temp; if @count_ori <= @count_temp then #delete and copy data; end if; It didn't work. mySQL says that I have an error near if @count_ori <= @count_temp then..... I tried simpler ifs (i.e. if 1>0 then select @count_ori; end if;) and it didn't work either ¿Does the if statement work in this version of the server???
Mysql WHERE Statement
I am having a problem with a mysql where statement, using a PHP page I have 3 drop down boxes, each with a SELECT DISTINCT statement in them, all these work ok in selecting the distinct value. I have set in each of these an initially selected label of "All", with a value of %. On my results page I have the following code :- Code: $colpeg_rsName = "1"; if (isset($_POST['gkpeg'])) { $colpeg_rsName = (get_magic_quotes_gpc())? $_POST['gkpeg'] : addslashes($_POST['gkpeg']); } $colspecie_rsName = "1"; if (isset($_POST['gkspecie'])) { $colspecie_rsName = (get_magic_quotes_gpc())? $_POST['gkspecie'] : addslashes($_POST['gkspecie']); } $colname_rsName = "1"; if (isset($_POST['gkname'])) { $colname_rsName = (get_magic_quotes_gpc())? $_POST['gkname'] : addslashes($_POST['gkname']); } mysql_select_db($database_roughamlake, $roughamlake); $query_rsName = sprintf("SELECT * FROM catches WHERE name = '%s' AND pegno = '%s' AND specie = '%s' ORDER BY name ASC", $colname_rsName,$colpeg_rsName,$colspecie_rsName); $rsName = mysql_query($query_rsName, $roughamlake) or die(mysql_error()); $row_rsName = mysql_fetch_assoc($rsName); $totalRows_rsName = mysql_num_rows($rsName); ?> 'colname' , 'colpeg' , 'colspecie' are my 3 variables from the previous page, and I am using the $_POST[colname], $_POST[colpeg] and $_POST[colspecie] for my variables. If I select for each of the 3 search variables a value which matches in the database to ALL three items, it selects Ok, but I need the "All" option to work on the 3 drop down boxes. Example, select "Andy" in box 1, but leave the other to as "All", the give all records with "Andy" in them.
MySQL If Statement
I am trying to update an existing record or Insert a new line into a MySQL database. I can not seem to get this statement to run. I keep receiving the following message.What am I missing? 'Error in your SQL syntax near 'IF first_name LIKE '.%' AND year_of_grad = '1900' THEN UPDATE Members SET fir' at line 1' Independently the UPDATE and INSERT statements work just fine. IF first_name LIKE '.%'AND year_of_grad='1900' THEN UPDATE Members SET first_name='TOM', last_name='LastName', year_of_grad='1900', date_of_membership=CURDATE() WHERE first_name LIKE '.%'AND year_of_grad='1900' END ELSE INSERT INTO Members SET first_name='Dick', last_name='LastName', year_of_grad='1900', date_of_membership=CURDATE() END;
Error With SQL Statement When Using MySQL 5.0.24 (Works Fine In MySQL 4.0)
Here is my query that worked in mySQL 4.0 but not in MySQL 5.0.24: INSERT INTO magtallynew SET magempnum=?', magcentre='YVRRC', entrydate=��-08-15', empnum=?', empcentre='YVRRC', connection='Rockwell', service='Express', region='dom', callertype='CSR', otherdept='', transferredcall='No', directrespdept='No', station='Not Applicable', withoutmagassist='Yes', category='Accounting', issue='Advised On Contact', comments='test' Here is the error message I get: #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 'connection='Rockwell', service='Express', region='dom', callertype='CSR', otherd' at line 1
What Is Wrong With This Mysql Statement?
What is wrong with this mysql statement? PHP SELECT * FROM authorize AS u_one INNER JOIN authorize AS u_all ON u_all.department IN ( u_one.department , u_one.department2 , u_one.department3 , u_one.department4 , u_one.department5 , u_one.department6 ) WHERE u_one.username = '$_SESSION[user_name]' AND NOT u_all.lockedstatus = 'LOCKED' SELECT * FROM here LEFT JOIN authorize ON here.username=authorize.username WHERE authorize.username IS NOT NULL;
How Do I Make This Mysql Statement?
PHP $sql = "SELECT * FROM authorize AS u_one INNER JOIN authorize AS u_all ON u_all.department IN ( u_one.department , u_one.department2 , u_one.department3 , u_one.department4 , u_one.department5 , u_one.department6 ) WHERE u_one.username = '$_SESSION[user_name]' AND NOT u_all.lockedstatus = 'LOCKED'"; I want to add to it and say "and also if they exist in table timesheet, display username, and various other informations from that table"
SHAPE Statement In MYSQL ?
Has anyone tried (and succeded?) in specifying the appropriate connect string for a hierarchical dataset using MyODBC? It's where you specify MSDATASHAPE as your "Provider" but then need to designate MyODBC as the "Data" Provider.
Efficiency Using Or Within An Sql Statement On Mysql
I have done a lot of experimentations using several "or" predicates within an sql select statements on mysql. My only conclusion so far is that whatever syntax or order I use it is always slower than doing seperate sql statements (one for each or) and programaticaly adding them to a vector. The performance of any "or" statement is really really bad. does anyone out there manages to get resonable results out of an or in an sql statements? If you do can you point me to what rules you use to get the best out of mysql. Code:
MySQL Database LIKE Statement
I'm trying to compare a longer statement with a shorter one. The shorter statement is located in my database while the longer one is acquired from the user. Here is the statement I am trying to do. SELECT * from SearchEngines WHERE 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)' like UserAgent UserAgent is the info stored in my database. What is stored there is Mozilla, which is the start of the user entered statement. How could I get this to work?
MySQL/PHP Select Statement
I'm trying to use this select statement: $result = mysql_query('SELECT * FROM Accounts WHERE user LIKE $user'); for a login script. but i get keep getting this error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/traumati/public_html/six/conntemp.php on line 17 can anyone tell me what i'm doing wrong? i asume it's something wrong with the syntax..
Not Sure How To Implement Mysql If-else Statement
I have three tables in my test db.we will call them,"category","item" and "data".What I want to do it have a delete statement that will remove the selected "category" ID then look for "items" or "data" that are associated with the "category" being deleted. I am not sure how to do this, but I believe it's possible. the "category" ID is also refered to in the "item" table but the "data" table does not have a "category" ID, rather the "data" table has an "item" ID referenced to the "item" table.An illustration of sorts: Category catid catname Item itemid itemname catid Data dataid dataname itemid Anyone know how to properly set it up to determine if there is any data in "item" or "data" to be deleted once the "category" table is set for deletion?
Mysql Insert Statement
I was a foxpro AP for 12 years and you get very confident when writing the select or insert statements you have to be. I can't find any consistancy with mysql 3.23.58 Lets take an example CODE INSERT INTO `sledgerdet`(cno, invno, row, unit, desc, unitprice)VALUES ($mast_cno, $invno, $invrow, $unit, $description, $unitprice)
Statement Translation For Pre 4.1 MySQL
I need this statement for pre 4.1 mysql... I can't seem to get this to work with joins. SELECT md5_hash FROM file_table WHERE md5_hash NOT IN (SELECT md5_hash FROM ownership_table where owner_name='john');
MySQL Statement Length
I'm not finding what the maximum number of characters that a statement can be. Anyone know where I can find that spec?
Mysql DISTINCT Statement
I cant seem to get this going the way that i want it.... pseudo-kinda?: Code: SELECT * FROM table_1 WHERE column_name = DISTINCT(column_name) something like that, i just need to return the row if a specific column are "unique".... should'nt be more difficult than this but apparently it is
MySQL/PHP - SQL UPDATE Statement
I am trying to update one field in my table in MySQL. I cannot get this field to update. Can someone possibly tell me what is wrong with my SQL statement? $SQL="UPDATE umcritsurv SET IQ_Use_Q9='$IQ_Use_Q9' WHERE fldAuto='54'";
MYSQL Statement Not Returning Data...
Is there something I am doing wrong in the following query: SELECT * FROM 'photos' WHERE approval='NULL' ORDER BY 'reg_date' ASC LIMIT $page, $limit". It doesn't seem to target the approval field in the photos table in which all current values are NULL... or am I targeting NULL incorrectly? The field is a tinyint(1) so it can be 0 for NO, 1 for YES, or NULL so that I can target photos that have not been checked yet. It runs fine but returns no results when it should return all current rows in the table.
Join 2 Tables In SUM IF Statement-mysql
I am trying to join 2 tables with in a SUM IF statement. the code is as under: SELECT prod_code, description, description1, quant, SUM(IF(newsales.location = '05-P', newsales.quant,''))P, SUM(IF(newsales.location = '10-L', newsales.quant,'')) L, SUM(IF(newsales.location = '12-M', newsales.quant,'')) M, SUM(IF(newsales.location = '11-BI', newsales.quant,'')) Bi, SUM(IF(newsales.location = '14-BR', newsales.quant,'')) Br from newsales, stock where newsales.prod_code = stock.prod group by prod_code But it gives an incorrect output, but if I romove the join statement, it gives accurate resuly. How to join the 2 tables ie newsles and stock so that the output remains right.
MySQL AB: MySQL - Use A Out Parameter In An Insert Into Statement
I have a stored procedure that inserts a new passenger into a database. The fields are name and email. The passengerID is a auto increment. The database I am using is mySQL. The problem is that I want to return the passengerID when the stored procedure is called. I am not sure if that is possible. Here is my code: Store procedure in mysql CODEDELIMITER $$ DROP PROCEDURE IF EXISTS `flightdb`.`spInsertNewPassenger`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `spInsertNewPassenger`(_name varchar(50), _email varchar(5) ) BEGIN INSERT INTO Passengers ( name, email ) VALUES ( _name, _email ); END$$ DELIMITER ;
MySQL Equivalent For Oracle DECODE Statement?
Is there a MySQL equivalent for Oracle's DECODE statement ? I am looking for code that will allow me to transform columns/rows within the SQL query. My query returns data like this: Date | Indicator | Name ---------------|----------------|--------------------------- 01/01/2004| 1 | Mr Smith 01/01/2004| 2 | Mr Brown 02/01/2004| 1 | Mr Jones 02/01/2004| 2 | Mr White 02/01/2004| 3 | Mr Black I would like to transform this to return as: Date | 1 | 2 | 3 --------------- |--------------|--------------|-------------- 01/01/2004 | Mr Smith | Mr Brown | 02/01/2004 | Mr Jones | Mr White | Mr Black i.e. the values in the indicator column become the column headers.
Can't Execute LOCK TABLES Statement From Mysql++?
When I execute "LOCK TABLES 'ABC_0' WRITE" from control center, it is successfull, but when I execute this same query from MySql++ client I get errror exception which says "error near 'ABC_0' WRITE", i don't know where is problem in query. My Environment. MySQL ++ (MySQL++ 1.7.1 for VS by lazyfox),Windows/Linux OS clien/server.
MySQL Error With Select Statement In Procedure
I am receiving an error when running a stored procedure with the following select statement: SELECT count(pass_num) INTO count FROM eco_class_t; The first time I run the procedure everything runs fine. Each time after that I get the following error: ERROR 1172 (42000): Result consisted of more than one row If I remove the select statement the proc works fine. Could someone explain to me what I am doing wrong?
Mysql Sql Access 2 Servers In Single Statement
Is it possble to access databases in 2 server simultaneously in sql using the mysql client? I can use sql to access multiple dabases on the same host1 server when I run 'mysql -h host1' but I cannot find a syntax to access databases on another server, which is supported on other db software.
MYSQL SELECT Statement Depening On Search Results
I have a search form that contains 3 dropdown boxes: - search_bedrooms - search_price - search_area Each of these boxes contain values which will get info from a database when submitted, but they also have a value called [no preference], this is selected when the user doesn't want to search for any particular value. When submitted it goes through to a page which has a MYSQL SELECT statement: $query = "SELECT * FROM property_details WHERE area = '$search_area' AND price = '$search_price' AND bedrooms = '$search_bedrooms'"; What I need to know is if the user selects [no preference] on any one of the dropdowns, how can I make the above SELECT statement not include that in the search, but still search on the other criterias. (e.g. if in the search_bedrooms dropdown I selected [no preference], how could it be excluded from the search, but still include area and price?) I know it can be achieved with multiple SELECT statements,
Mysql C Prepared Statement API Sending Incorrect Data
I am having a problem with the C api with prepared statements, the data recorded in the database does not match the data I am sending. It seems to be some sort of bit shifted version of the data, and I have no idea why. I am including code I have been using to test this, followed by the output in the database. I am using mysql version 4.1.12, I have tried this on multiple servers, remote and local. Code:
MySql + VB6: Pass Keyword As Fieldname Within Insert Statement
I successfully migrated from access to mysql. The original database had a column name "group"..When i try to pass/insert the value from the form into this field, in the table, i get an error. I believe group is also a keyword/part of mysql and its causing the error. Is there a way to pass this keyword as the field? Such as wrap it in something (like brackets) to pass the info as a field without changing the column name? for example the correct form (if it wasn't a field name) would be: "insert into tbl (group) value('" & groupnum.text & "')" so how can i pass this "keyword" into the mysql database as a field name?
How To Create Efficient MySQL Query From A Pseudo Query
I'm trying to build a webapplication where users can search for a person having a particular preference for color and material. To store this information I use the following structure (a MySQL dump can be found at the end of this post): *table person with fields: -persid: autoincrement id -name: name of the person *table material with fields: -materialid: autoincrement id -material: name of the material eg "wood" *table color with fields: -colorid: autoincrement id -color: name of the color eg "green" *table persmaterial with fields: -persmatid: autoincrement id -persid: link to table person -materialid: link to table material *table perscolor with fields: -perscolorid: autoincrement id -persid: link to table person -colorid: link to table color In the webapplication the search can be entered by the users as a kind of pseudo query: (color=red OR color=blue) AND color=green AND material=iron My question is: how can I automatically transform this pseudo query into an efficient MySQL query? I have tried out some different options: Option 1: (SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=1 OR pc.colorid=2) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING (count(DISTINCT pc.colorid)=2 AND count(DISTINCT pm.materialid)=1)) UNION (SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=2 OR pc.colorid=3) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING (count(DISTINCT pc.colorid)=2 AND count(DISTINCT pm.materialid)=1)) Remarks: *I do not see how to turn a general pseudo query into a query like the one in option 1, except for turning the pseudo query into a sum of products form where the sulms would correspond to the UNIONs. IS there a clever way to obtain such a sum of products form from an arbitrary pseudo query? Option 2: SELECT persid FROM person p WHERE (EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=1 AND p.persid=pc.persid) OR EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=3 AND p.persid=pc.persid)) AND EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=2 AND p.persid=pc.persid) AND EXISTS(SELECT * FROM persmaterial pm WHERE pm.materialid=2 AND p.persid=pm.persid) Remarks: *very easy to get from pseudo query to MySQL query but what about performance? Option 3: SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=1 OR pc.colorid=2 OR pc.colorid=3) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING sum(case when pc.colorid in (Ƈ',Ɖ') then 1 else 0 end) >= 1 AND sum(case when pc.colorid=ƈ' then 1 else 0 end)>=1 AND sum(case when pm.materialid=ƈ' then 1 else 0 end)>=1 Remarks: *this option requires the pseudo query to be turned into a product of sums form; again is their a clever way to obtain such a form; Option 4 SELECT DISTINCT pc1.persid FROM perscolor pc1 INNER JOIN perscolor pc2 ON pc1.persid=pc2.persid AND pc2.colorid=2 INNER JOIN persmaterial pm1 ON pc1.persid=pm1.persid AND pm1.materialid=2 LEFT OUTER JOIN perscolor pc3 ON pc1.persid=pc3.persid AND pc3.colorid=1 LEFT OUTER JOIN perscolor pc4 ON pc1.persid=pc4.persid AND pc4.colorid=3 WHERE COALESCE(pc3.persid,pc4.persid) IS NOT NULL Remarks: *this option requires the pseudo query to be turned into a product of sums form Option 5: SELECT p.persid FROM person p, persmaterial pm,perscolor pc1,perscolor pc2,perscolor pc3 WHERE p.persid=pm.persid AND p.persid=pc1.persid AND p.persid=pc2.persid AND p.persid=pc3.persid AND (pc1.colorid=1 OR pc2.colorid=3) AND pc3.colorid=2 AND pm.materialid=2 GROUP BY p.persid Remarks: *very easy to get from pseudo query to MySQL query but what about performance? -- phpMyAdmin SQL Dump -- version 2.6.1 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Oct 19, 2006 at 01:13 PM -- Server version: 4.1.9 -- PHP Version: 4.3.10 -- -- Database: `aston` -- -- -------------------------------------------------------- -- -- Table structure for table `color` -- CREATE TABLE `color` ( `colorid` int(11) NOT NULL auto_increment, `color` varchar(30) NOT NULL default '', PRIMARY KEY (`colorid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `color` -- INSERT INTO `color` VALUES (1, 'red'); INSERT INTO `color` VALUES (2, 'green'); INSERT INTO `color` VALUES (3, 'blue'); INSERT INTO `color` VALUES (4, 'yellow'); -- -------------------------------------------------------- -- -- Table structure for table `material` -- CREATE TABLE `material` ( `materialid` int(11) NOT NULL auto_increment, `material` varchar(30) NOT NULL default '', PRIMARY KEY (`materialid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; -- -- Dumping data for table `material` -- INSERT INTO `material` VALUES (1, 'wood'); INSERT INTO `material` VALUES (2, 'iron'); -- -------------------------------------------------------- -- -- Table structure for table `perscolor` -- CREATE TABLE `perscolor` ( `perscolorid` int(11) NOT NULL auto_increment, `persid` int(11) NOT NULL default Ɔ', `colorid` int(11) NOT NULL default Ɔ', PRIMARY KEY (`perscolorid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; -- -- Dumping data for table `perscolor` -- INSERT INTO `perscolor` VALUES (1, 1, 1); INSERT INTO `perscolor` VALUES (2, 1, 2); INSERT INTO `perscolor` VALUES (3, 2, 1); INSERT INTO `perscolor` VALUES (5, 3, 3); INSERT INTO `perscolor` VALUES (6, 3, 2); -- -------------------------------------------------------- -- -- Table structure for table `persmaterial` -- CREATE TABLE `persmaterial` ( `persmatid` int(11) NOT NULL auto_increment, `persid` int(11) NOT NULL default Ɔ', `materialid` int(11) NOT NULL default Ɔ', PRIMARY KEY (`persmatid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; -- -- Dumping data for table `persmaterial` -- INSERT INTO `persmaterial` VALUES (1, 1, 1); INSERT INTO `persmaterial` VALUES (2, 1, 2); INSERT INTO `persmaterial` VALUES (3, 2, 1); INSERT INTO `persmaterial` VALUES (5, 3, 2); -- -------------------------------------------------------- -- -- Table structure for table `person` -- CREATE TABLE `person` ( `persid` int(11) NOT NULL auto_increment, `name` varchar(30) NOT NULL default '', PRIMARY KEY (`persid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ; -- -- Dumping data for table `person` -- INSERT INTO `person` VALUES (1, 'john'); INSERT INTO `person` VALUES (2, 'emily'); INSERT INTO `person` VALUES (3, 'liz');
MySql Update Or Insert Statement To Modify A Word In All Caps
I have a field in my database called Subtopic. In this field I have a word LITERATURE in all caps. I need to change the word LITERATURE to LITERATURE followed by a colon then an empty space. Can someone give me the MySql statement I can paste into phpMyAdmin to make this change? Note: The Subtopic field contains the word "literature" but not in all caps because the Subtopic field is part the database that is an encyclopedia. Therefore, I want to change ONLY the word LITERATURE if the word LITERATURE is in all caps.
How Can I Make A Query Like Microsoft Access, And A Query From A Query
I am new to MYSQL and am trying to understand how to make queries... I am moving from Microsoft Access where it is GUI driven and easy! I can make a simple single query using MYSQL Query Browser, say: qry1: SELECT ID, Area FROM data GROUP BY Area How can I store this as a query inside MYSQL, rather than having to code it each time? In Microsoft Access I could enter a variable ($VARIABLE) and then pass by code to the query: qry2: SELECT ID, $VARIABLE FROM data GROUP BY $VARIABLE How can I store this as a query and then pass the variable from code? In Microsoft Access I could base a query on the results of another query, so following example above: qry3: SELECT qry1.Area, data.ID FROM qry1 INNER JOIN data ON qry1.Area = data.Area; How can I store this as a query in MYSQL.
Different Results In Mysql & Mysql Query Browser?
I've come across an extremely strange problem. The exact same query in both mysql command line client, and mysql query browser gives entirely different results. I was hoping someone out there could shed some light. Ok, the query (I've stripped it bare, the real query is a bit more complex)... Each person in the people table has an associated 'place', which is an integer that maps onto a suburb in the suburbs table. The 'place' CAN be NULL. so, for all intensive purposes, the schema is roughly:
Help With This SQL Statement.
I have a list of employees with titles and their office locations. What I want to do is to count the number of senior engineers out of the total number of regular and associate engineers at the location. All I can figure out is how to get the total number of engineers per locatoin. What is the SQL statment that will give me the total number of "Senior Engineers" and "Total Number of Engineers" per "office"? Here is my attempt which is totally wrong: SELECT COUNT ( title = "Senior Engineer" ), COUNT ( title = "Senior Engineer" + title = "Engineer" + title = "Associate Engineer" ), office FROM employees WHERE title = "Associate Engineer" OR title = "Engineer" OR title = "Senior Engineer" GROUP BY office I want to the output to be like: Number Senior Engineers, Total Engineers, Office 4, 20, Oregon 1, 10, Texas
IF Statement
I have read http://dev.mysql.com/doc/refman/5.0/...statement.html but unable to understand it. Can someone give a simple example? I tried this but it gives me the error "You have an error in your SQL syntax; check ..... right syntax to use near 'IF 10>5 THEN ...'" MySQL IF 10>5 THEN SELECT 'Greater'ELSE SELECT 'Smaller'END IF; What I am doing wrong? I am on MySQL 5.0.20, WinXP.
Can This Be Done In One SQL Statement?
My Roster table contains rosterId as the key, userName as the field I want. My Article table contains articleId as the key, rosterId as a foreign key, synopsis, article and posted as the fields I want My Comments table contains id as the key, rosterId and articleId as foreign keys and posted as the field I want. I want only one row returned that Given the aritcleId Contains the Roster.userName for the Article, the Article.synopsis, the article.posted (date), the Roster.userName for the Comments and the Comments.posted where 1) The Comments row contains the latest posted (date) for the rows the Comments.articleId that matches the articleId above 2) uses the Comments.rosterId from the Comments table to extract the Roster.userName (for the Comment) AND 3) uses the articleId to obtain the Article.rosterId (which will be different than the Comments.rosterId) to get the Roster.username (for the article). All tables use the innodb engine. I understand how to do this in multiple selects, but suspect it could be done in one. Yes? No?
SQL Statement
I am using the following statement to pull data from the table for any data older than a certain date but I would also like to immediately delete the data the I just pulled from the table into the dump file from the table. Can anyone help me out? I am new to MySQL. SELECT * FROM `radacct` WHERE `AcctStartTime` < "2005-06-01 00:00:00" INTO OUTFILE '/usr/backups/radacct_output.txt' fields terminated by '|';
SQL Statement
I would like to ask you what to read to understand this "<> for queries. SELECT polltext, votecount FROM poll_data WHERE pollid = '$pollid' AND polltext <> '' What does polltext <> '' do?
IF STATEMENT
I am trying to create a statement that checks a table to see if an entry exists and if it does run one select statement and if the the check comes back with 0 then run another select statement This is what I have but it doesn't seem to run ? Am I way off ? I can post the exact detailed code but since I am a new to mySQL syntax I am sure it is something basic. IF (SELECT COUNT(*) FROM table_Y WHERE field_X = 'string') = 1 THEN SELECT ......... FROM ........... WHERE ........... ELSE SELECT ......... FROM ........... WHERE .......... END IF
|
TRACKER
