Is Query Possible? (Newbie)
I have 2 tables used for an online calendar...
first table fields: primary_key , start_date, event_name, event_description
second table fields: primary_key, end_date
Tables fields are shortened and can't be changed.
My second table only contains events that have a end date. I want to create
a query that will take all the fields in. If no end_date exists then set to
NULL. Been playing with it all day. Hoping some advance function exists. I
thought of using a temp table but there must be a better way.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Ignore Spaces In Query (newbie)
What's the best way to run a query so that spaces in the fields are ignored? For example the following queries.... SELECT * FROM mytable WHERE username = "JohnBobJones" SELECT * FROM mytable WHERE username = "John Bob Jones" would find the following entries: John Bob Jones JohnBob Jones JohnBobJones etc I'm using Perl so removing spaces from the original query string is no problem. It's the MySQL side of things I'm not sure about.
Ignore Spaces In Query (newbie)
What's the best way to run a query so that spaces in the fields are ignored? For example the following queries.... SELECT * FROM mytable WHERE username = "JohnBobJones" SELECT * FROM mytable WHERE username = "John Bob Jones" would find the following entries: John Bob Jones JohnBob Jones JohnBobJones etc I'm using Perl so removing spaces from the original query string is no problem. It's the MySQL side of things I'm not sure about.
Newbie
I've written my site using .ASP, Javascript and an MS Access database. I'm now looking to 'upgrade' to MySql to solve any database issues I may have in the future but I haven't a clue about MySql. My questions are; Does it cost anything? What do I need? How do I transfer my tables? Will my ASP code work on MySql?
Newbie
I am a beginner to Mysql however i have worked with Oracle n i had a few questions regarding the syntax, in oracle we use the number(1) how is it when compared with mysql .I have seen that one can use int but could it be possible also to restrict the nr of digits like int(1).I have tried this out n it does not seem to work.I would also like to know the the diff in varchar n varchar2() if it is the same as in oracle
[Newbie] Localhost???
is there any way to connect to the localhost database on another server from a different server?
Newbie In MYSQL
I m a newbie in MYSQL.Would somebody pls tell me the following questions- 1. WHich is my original database after creating a Database(e.g. Student) in MySql .I mean in MSAcces DB I can find a .mdb file which is to be used.But in MySql which file to use. (If I create a database named- Student).Would I have to use the whole SQL folder including all files for a single DB. 2. I want to use that DB in MYSQL with JAVA. Which should be the driver String for MySQL. I am new pls help me. Edit/Delete Message
Please Help Newbie Upgrading From 4.1.11 To 5.0.x
I'm almost in tears here, am having a lot of trouble with the MySQL upgrade from 4.1.11 to 5.0.x. I have read Chapter 2 of the manual. I've read it more times than I care to admit. I've done Google searches. I've searched this forum as well as the usenet group. I'm stuck and I need to get this upgrade done. I must be the most stupid human on the face of the planet but I don't think the manual is very clear when it comes to explaining how to do the upgrade. Please take pity on me and help if you can, I'll be EXTREMELY grateful! My environment: Linux RHE 3, the MySQL 4.1.11 is a non-RPM installation. The software is not installed under the default directory, it's in /opt/mysql/tst/4.1.11. I have many databases and many user accounts all password-protected. Here is what I tried: 1. dumped all databases, including mysql. 2. make a backup of the whole 4.1.11 (MySQL softare) directory. Shutdown mysql 4.1.11. 3. can't tell exactly from the manual where the new software is supposed to go (is it supposed to overwrite the existing installation?). So I extracted the mysql-...tar.gz file into the /opt/mysql/tst directory. It created its own directory structure called "mysql-5.0.51a-linux-i686-glibc23". 4. I renamed the "mysql-5.0.51a-linux-i686-glibc23" directory to "5.0". 5. tried to start w/ bin/mysql_safe & and the server started and then stopped immediately. 6. Couldn't figure out what else to do and am running on the assumption that the "upgrade" is really just an "install" so I followed the installation instructions that say to run the mysql_install_db script. 7. Now I can start mysql_safe, hooray. I logged in with root, no password required. 8. I ran the mysql.sql file (the script produced when I dumped mysql out of 4.1.11) assuming this would put all the user accounts & passwords back into the new 5.0 version. 9. Logged into mysql using root and still not required to enter a password. However, am pleased to find the user accounts are in the database. What am I doing wrong? How do I install the new version of MySQL so that the user account information is preserved?
Newbie Question.
I am trying to install an accounting package called web-erp on a Linux system. Early on I need to establish a user with the name of web-erp@localhost. Unfortunately my copy of MySQL seems to balk at taking a user name with a hyphen in it,. I have tried enclosing the user name in single quotes but that generates another kind of error. Here is the exact statement: GRANT ALL PRIVILEGES ON *.* TO web-erp@localhost IDENTIFIED BY 'passname' WITH GRANT OPTION; This statement fails, but the same statement without the hyphen succeeds.
Newbie Question--please Help
I'm setting up mysql for the first time, and running on xp pro. I have no experience with mysql or databases in general, so I'm quite confused about some things--I hope someone will be able to help me. I'm reading a book on php and mysql and in the chapter on post-installation, they're showing you how to create a password for the root user. They don't really explain this well, so please bare with me: 1) the username and password for mysql refers not to the windows username, but something specific to the mysql server correct? 2) the author mentions that when you try to connect to the mysql server via the mysql monitor, you will have to put in your mysql server username and password. He says that by default, a user called root is created for you but not given a password. You should then access the mysql database (which I assume stores all your user information) with the root user as follows: mysql -u root mysql He then gives a series of commands to enter: mysql>SET PASSWORD FOR root@localhost=PASSWORD("new password"); mysql>SET PASSWORD FOR root@"%"=PASSWORD("new password"); mysql>FLUSH PRIVILEGES; the first one works fine and I assigned a password. However, the second one doesn't work--I get an error message: "error 1133: can't find any mathing row in the user table" -- I don't understand why this is happening. It is the same user root right? Incidentally, why am I entering two passwords for the same user (root)? Is this if I'm running mysql monitor client app from a remote computer on the internet? 3) do the above commands assign specific usernames and passwords to the server itself, or to a specific database within? Do all databases require a separate set of usernames and passwords or is the mysql database (which this info is being placed in) a special kind of databsee used just for storing permissions and things like that--does the server automatically know to check for a database called "mysql" in determining who has permissions for what? 4) In the line: mysql -u root mysql he says that you are accessing the mysql database. How do I know I'm in the mysql databse if the only thing the prompt just reads "mysql>" which is just the standard mysql monitor prompt right? I'm able to get into the mysql monitor without typing any username "mysql" dumps me in "mysql>" -- but I assume then that I won't be able to access any specific db info without putting in a username/password, right? HUH--I probably have a million other questions, but this is all I can think of right now. I've searched high and low on the Internet and otherwise for answers but can't seem to get a good explanation of this stuff. Will someone please explain all about usernames and passwords and permissions and stuff in mysql--thanks and sorry for the long post?
Newbie Question
I want to make a form in mysql. To explain myself further its like when you go to a used car website and it has a text box with a list of different manufacturers and you keep on filling in different text boxes until you get a specific car. Is it possible to do that in mysql??
Newbie Question
I am trying to install mysql on XP to use with PHP for a web site that I = am working on. I have done everything that the books are telling me but I can't seem to = get either PHP or Mysql to speak to each other. When I do the tests on = frontpage, it doesn't seem to do anything. On the Mysqladmin it tells me that my odbc driver 3,.51 not found is = that a problem and how do I get the driver? Also when I go to the database section and right click I don't get the = create database how do I get this?
MySQL Newbie
I am a complete newbie to MySQL. I downloaded the MySQL 4.1.1-alpha onto my windows 2000 machine and have been trying to figure it out ever since. I followed the installation directions and managed to do everything up to running mysql_install_db. cmd prompt wouldn't run the ..sh file, I don't have shell.exe on my machine and maybe that is the problem, but I couldn't find any documentation about it. Anyway, I can run the server and manipulate 'test' table, but I can't log on as a user or administrator, or perform any administrative tasks. When trying anything I get the "access denied for user ''@'localhost' " 1044 error I am stuck, because I do not entirely understand the structure of mysql (what does what exactly) and so I find it hard to try to figure out the cause for my problem
MySQL Newbie
I have started to plan a conversion of my flat file system, to one run by MySQL. If anyone would please answer these questions, it would clear up a lot of brain fogging around me and I would be very grateful. 1. I will have a table which in one of its fields, lists a series of items (alphabetical names). Where I want to retrieve only those beginning with A, can the MySQL query do this or should I bring in all the values and then process them with php/perl? 2. where I have a MySQL Db hosted on mydomain.com, can I enable other domains to read from it (to power their sites), without compromising security? (All the other sites would be on my own server).
Newbie Question
I am at the point in testing where I need to create new tables to keep things 'clean.' I have a users table that has userid in it, auto incrementing as you would expect. Now I want to add another table to deal with some other things. I have seen how to extract data using joins, but now I want to know how I can relate this new table with the users one(and put the userid into the new table)
Newbie Can't Upload Dump Pls Help If You Can
This is the error I'm getting. when trying to upload in phpmyadmin. PHP SQL query: -- phpMyAdmin SQL Dump -- version 2.8.0.2 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: May 22, 2006 at 12:48 PM -- Server version: 4.1.18 -- PHP Version: 4.4.2 -- -- Database: `askdivas_ex` -- CREATE DATABASE `askdivas_ex` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; MySQL said: Documentation #1044 - Access denied for user 'askdivas'@'localhost' to database 'askdivas_ex'
Connect One Table To Another. Newbie Here....
i´m using phpMyAdmin. Hi have two tables: cars and constructor how can i make so that when inserting values in my cars table i will have lets say a dropdown list with my constructors? Like having a products table and one table just for categories and when inserting a new product i would have the categories list to select...
Newbie: MySQL Locally?
This may be a dumb question but I am a newbie and well, don't know the answer. Is there a way to set-up/test a MySQL database locally on my machine?
Newbie: Creating Users?
I'm tring to create new users. I've read the docs and still kee recieving the error: mysql> CREATE USER joeuser IDENTIFIED BY PASSWORD somepass; ERROR 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 'USER joeuser IDENTIFIED BY PASSWORD somepass' at line 1 How can I check to see if I have access to create users? I created tables, I don't understand why I can't create users. 13.5.1.1. CREATE USER Syntax CREATE USER user [IDENTIFIED BY [PASSWORD] 'password'] [, user [IDENTIFIED BY [PASSWORD] 'password']] ... The CREATE USER statement creates new MySQL accounts. To use it, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database. For each account, CREATE USER creates a new record in the mysql.user table that has no privileges. An error occurs if the account already exists. The account can be given a password with the optional IDENTIFIED BY clause. The user value and the password are given the same way as for the GRANT statement. In particular, to specify the password in plain text, omit the PASSWORD keyword. To specify the password as the hashed value as returned by the PASSWORD() function, include the keyword PASSWORD. See Section 13.5.1.3, “GRANT and REVOKE Syntax”.
Extreme Newbie (using A Mac) Needs Basic Help
I bought the book "MySQL Tutorial" and immediately I am confused. I installed MySQL on my Mac, but immediately I am having questions regarding it's use. Am I to type these commands into the Terminal?
Newbie: Formating Data In Updates
My POS products table is formatted totally different from that of the Online products table. No problem, I've got FileMaker Pro and it is really good at changing and formating data how I want it. I can output the product records for my website one way, the output the records for Froogle in another format. The problem is that it takes and extremely long time with 70k+ records. My current scenario is outgrowing itself and I need to come up with a better solution. I need to find a quicker solution, possibly one I can automate down the road. Any advice for resources? Or examples?
Newbie With MySQL CXN Connection Problem
So, I am building a members only application and have the MySQL tables and database already set up and now having trouble connecting my application to the database and could use some help. On my line 13, I have a mysqli connect pointing to my local host (i just used the server name and then the username and database name. i hid the password in parenthesis here just for protection. PHP $cxn=mysqli_connect("h41mysql49.secureserver.net","mankatocustomers","","mankatocustomers") I also I have modified my php.ini file to include PHP extension=php_mysqli.dll .. because i saw on another form i should add this. i am using PHP Version 4.3.11 (php info file for me) i also built a members registration and members login php page you can see by clicking members registration. -------------------------- so, when you try registering or logging in (i inserted a new member, myself, using phpmyadmin and tried logging into that members registration section) and I kept getting an error like this: PHP Fatal error: Call to undefined function: mysqli_connect() in /home/content/p/e/s/peschomd/html/login/Login.php on line 14 Does anyone know whats up? I tried using "localhost" and changing the database name multiple times and looking at my two INC files and nothing changed. I always get the same errors. Anyone can help me would be greatly appreciated to this newbie to php and mysql!
Newbie - Cannot Increase Field Size?
First of all my knowledge of mysql databases is about 1%! I am learning by my mistakes thus far but have stumbled onto a bit of a problem. I am trying to fix different things in a pre-made script I was given. I have a field which is a decription field and so will be filled with quite a large amount of text from the user when submitted. The problem is its only accepting "257" characters and really this field should be unlimited! The specs of the field are: varchar(255) Null =No
Synchronizing Two Mysql Db's...(newbie)
I hope some one can point me in the right direction here... I need to synchronizing one db on a server (server A) to another db on a seprate server (server B). There both MySQL 4.1.*, is there any way to sync these from the command line (no gui tools)... and what's the easyist MySQL admin tool to help me do this.
Newbie Help - Change Datatype Of A Table
--Apple-Mail-5--1016425622 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; format=flowed how can i change the data type of a table? I'm not sure if that is my problem. If anyone is interested here is a link what I've been trying to do:
PHPMyAdmin Simple Newbie Question
I'm seeking a tool to administer a remote MySQL database from my Win2k machine. My only paradigm thusfar is MS SQL Server Enterprise Manager. This is for my own edification so I want it to be free. My search came up with these: So I guess that leaves just PHP-Nuke and phpMyAdmin. Any comments you have on these would be appreciated. I haven't figured out what PHP-Nuke is about yet, "a news automated system"??? On phpMyAdmin I have one simple question. It looks like phpMyAdmin is supposed to be installed on the remote machine then you administer the remote database with phpMyAdmin's web interface. Is this correct? Is MySQL able to be administered from a remote location like MS SQL Server Enterprise Manager, with the client software on your local machine?
Newbie Question : Database Structure
I am getting up to speed with mysql which is all new to me. The "Teach yourself in 24 hours" book worked a treat for syntax, and mechanics, but it hasnt helped me understand how to effectivelyt structure a database. I have a table for each of the following categories : Customers Names Customer Shipping Address Products I now want to create a table of orders (from which I can generate all invoices etc). As each order will consist of (at least) a customer, a shipping address, any any number of any products. How best to store the products require for each order as it is open ended? I had planned on creating an "order Item" which contains an FK for the order to which the item belongs, an FK for the product that is required, and an quantity. That table would have one entry for each item ordered, and could be query'd by OrderNumber to build a list of qty/product for that order It seems a little clunky, but is that how things like this are done? Alternatively I wan thinking about a associative array as a field in the Order table that would contain Product=>Qty pairs. I think I am more or less there for building simple apps once I figure how best to do this "undefined number of elements for field" type thing.
Newbie Seeks Help Using Backup/restore
I am attempting to bring over a MySQL database from a Windows XP server to a Sun Solaris server. I seem to be having permission problems, but I don't even know where to start. NOTE: I have root access to MySQL, but cripplingly little access to the server itself. If I knew what access to request, I could probably get it, but I don't know I was able to find the .frm files for the old database, as well as to output a .sql backup file, and to store them on the new server. I tried to restore them using the following steps: [Login to MySQL as root user and enter password, then...] CREATE DATABASE mpf1; USE mpf1; RESTORE TABLE submitted_records FROM '/var/apache2/htdocs'; (Note: htdocs is one of the few places I have permission to access.) And it said "error copying .frm file errno13" and failed to create the table. So then I tried it again with FROM '/var/apache2/htdocs/submitted_records.frm'; same error. Ditto when I went and maxed out the permissions on those ..frm files. Next, I tried creating a dummy database on the Solaris server and backing it up to see whether I was having version/migration problems. BACKUP TABLE dummy_table TO... Same error. Anyone have any ideas on where to go with this? I can ask the sysadmin (at another location) for anything I like, so long as I know why I'm asking for it
Multiple Table Selection Is This Possible - Newbie Question ?
I hope I have explained this well enough for you to understand what I am trying to do. I table A Code,CodeType depending on CodeType it needs info From Different Tables, How can I do this in SELECT statement? Select A.Code,A.CodeType,B.NameDesc,C.NameDesc,D.NAmeDesc FROM A if CodeType=1 then return B.NameDesc FROM B else if CodeType=2 then return C.NameDesc FROM C else if CodeType=3 then return D.NameDesc FROM D
Newbie Warning: Learning MySQL/Ruby, Priorities?
I've pretty much decided on learning Ruby as my first server side language, and obviously SQL to go with it. Not knowing how the two inter-operate, I'm unclear as to which would be more appropriate to learn first, for someone who is already fairly thorough in Javascript/DOM. I'm concerned that if I get into one, I'll get stuck because I don't know the other, so I'm trying to decide on how to proceed without becoming dependand on a language i am yet to learn during the process. So the question would be, MySQL or Ruby first? Or maybe a book/tutorial that nicely fits the two together from the ground up?
Some Advice For A Newbie On Big Scale Websites Like Ebay, Facebook, Youtube
I am literally newbie in database design. Just finished kevin yank's build your own database driven website using php and mysql. The book helped me to understand the imporatance of deisgn in a database driven site but I still finding it difficult to figure out the large scale websites like ebay, facebook or youtube. Does anyone know how the database might be designed for these sites. Facebook and youtube are tagbased sites where everything is connected by tags. but how are these tags organized in tables?
Using "HANDLER" Syntax Via C API ( NEWBIE)
I am looking at converting our xbase application (using open source from www.xharbour.org )from Foxpro format DBF files to mysql. I have purchased the O'Reilly book "MySQL Reference Manual" and have studied the C interface to mysql. One thing missing from the book is how to handle the version 4.0 "HANDLER" high speed access. Do I just use mysql_query(&mysql, "HANDLER myTable OPEN"); mysql_query(&mysql, "HANDLER myTable READ .....") ; .... fetch row .... process mysql_free_result(...) mysql_query(&mysql, "HANDLER myTable CLOSE"); or is there an undocumented ( in the book ) C API call to "HANDLER".
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.
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');
Simple Sql Question: Using A Query Result As A Query Variable
EDIT: it works now, I had an error in my code, not my method. I have a very simple question. I have 2 tables: 'users' and 'posts' with the following structure: users: id, username, email_address posts: id, user_id, post_title, post_text in a my own mind's mysql, I would like to: SELECT posts.id, posts.user_id, posts.post_title, posts.post_text users.username FROM users, posts WHERE posts.user_id = users.id I usually do one query for the post data, and then, based on the use_id record, do another of the users table, but today, I'm being forced to do them in one swoop.
Query Based On Results Of A Previous Query
So far I have managed to construct one query which gives me all individuals that have one of three titles. based on this I now want to find all the individuals that are affiliated to those listed in the first query ....
Big Query - Query Not Completely Stored In Memory
I have this query and when executed in mySQL query window throws error that "Big Query - Query not completely stored in memory". Also executed thru app, the program justs hangs. I have checked all indexes, they all look good.....
Pagination W/1 Query + How To Use Query With Indexes
i'm asking 2 questions in 1 thread because i don't wanna take up too much room, hopefully no one will mind. i have mysql 4.1.10 1) i want to find all the rows that were edited this month. the query i currently have ( MONTH(CURDATE()) = MONTH(date) ) doesn't use indexes. how can i manipulate it so i can take advantage of indexes. 2) this is something i've always wondered, but usually just assumed was not possible. if i am listing some results, say 20 per page, how can i get both the total number of results as well as the 20 items required for that specific page. say there are 2 million total results, so grabbing them all and showing just 20 is not an option. if this is not possible what is the most efficient way of making both queries?
Reusing A Query Output In The Same Query
I am guessing a basic question but not one I can find an obvious answer to. If I create a calculated or modified column in a query (such as a modified text string), and then want to reuse that in the same query as I need to do three or four operations on it in sequence, how do I do it in mySQL 4.1? Do I need to create a new column to store the interim result in an existing table (and then clear or alter it each time I run the query), or create a temporary table, or is there an easy way to reuse the query output in the same query (does the query have a name like a table name)? If it requires a new column or table, are there particular disciplines to ensure it is robust and self maintaining?
Create Single Query From Queries On Two Tables (was "Help With Query...")
I read from other thread that query inside loop is not good idea. May I ask some help how can I create a single query to the following code which I use loop. $sql = "SELECT * FROM mytable order by points desc limit 10"; $rec = mysql_query($sql) or die(mysql_error()); $datas = mysql_fetch_array($rec); do{ $sq = "Select * from secondtable where linkid = '$datas[id]'"; $rst = mysql_query($sq) or die(mysql_error()); $rows = mysql_fetch_array($rst); echo "$rows[somefield]"; }while($datas= mysql_fetch_array($rec)); This works perfectly but I want the second query to be out of the loop if there is a way and how.
Grab 'title' From The Table 'forum' Within The Same Query (was "Help With Query")
I have the following query for my vBulletin database: PHP $get_stats_newthreads = $db->query_read(" SELECT thread.forumid, thread.postuserid, thread.postusername, thread.threadid, thread.title, thread.lastpost, thread.forumid, thread.replycount, thread.lastposter, thread.dateline, thread.iconid, thread.views, IF(views<=replycount, replycount+1, views) AS views, thread.visible FROM " . TABLE_PREFIX . "thread AS thread WHERE NOT ISNULL(thread.threadid) AND $weekold<lastpost AND thread.visible!=0 AND (forumid=34 OR forumid=7 OR forumid=8 OR forumid=11 OR forumid=10) ORDER BY rand() DESC LIMIT 5"); and would like to grab 'title' from the table 'forum' where forum.forumid=thread.forumid
Using A Query Result In Another Query
Can I use the results of a SELECT query as a "table" in another query? I want to let my user pick a subset of the data, then refine it further. So ... do I have to repeat all the selection criteria at each step, or can I just do refer to the last query result? If so, what's the PHP syntax for this?
Run A Query On The Results Of A Query?
Lets say I have a query that searches for people living in Colorado. That results in a list on a "results.php" page. Now I want to query that result and search further for people who use Linux. I know I can do this from one query, but I would like to create several checkboxes on my results.php page where I do a further secondary query. Possible? I guess I want to query a query.
Query Inside Query?
I would like to know if its practical to Insert a Select statement inside a previous select statements (array). With that said, the 'inner' select having a WHERE statement thats dependant upon the array results...
Rewriting A Query Without A Sub Query
I've recently changed hosts and found that some of my code broke. The new host is using mysql version 4.0.25 which does not support sub-queries (and they won't upgrade). I'm trying to figure out how to rewrite the following query so it will work on 4.0.25 but not getting anywhere.....
Is It Possible To Run A Query On The Results Of A Query?
I have been trying to figure this out but no luck. Lets say I have a query that searches for people living in Colorado. That results in a list on a "results.php" page. Now I want to query that result and search further for people who use Linux. I know I can do this from one query, but I would like to create several checkboxes on my results.php page where I do a further secondary query. Possible? I guess I want to query a query.
Query 2 Tables For Query
I have 3 tables, products, order_log, and groups. Products is a list of products available, groups are groups that products are put in, and order_log is a log of the current products in an order. I need to sort the order_log by the group the products are in. order_log does not have a group_id in it, however products does. So: SELECT * FROM order_log WHERE product's group_id = 1.
|
TRACKER
