this is not a mysql specific question but more of a general development question so its a bit OT We use SVN for
because MySQL cannot handle four byte UTF-8 characters, is there any way to get it to error when such a character is inserted into the database?
instead of just silently truncating?
harveyd, hehe, thanks for the tip
hey there, can someone please help me out with something?
its somewhat off topic, but i dont know where else to ask
well
it’s what we’re here to do.
im have to design a program for an assignment, part of this assignment is to make a structured diagram
but im not sure if my logic is correct
http://s192.photobucket.com/albums/z101/goneburger/
the above link is my structured diagram
could you pretty please take a look and tell me what you think?
next time link us directly to the pics, not everyone wants to look at your personal pictures 
oh ok, sorry
http://i192.photobucket.com/albums/z101/goneburger/structureddiagram-1.jpg
thats the one
the hexagons are the decisions, circles are loops, and squares are processe’s
EXIT
forgot to mention, its not complete as yet
what do you thing Gargantua?
does it look about right?
i mean logic wise?
oh could someone please help? im really stuck, ive got logic block lol
boh could someone please help? im really stuck, ive got logic block lol/b
Hello
anybody of you migrated from mysql to mssql2000
hello
can i ask a question related to phpadmin and inodb?
Rinner no, that would be the fool’s choice – use sql host 2005 instead
i am from argentina… please sorry for my bad english
adaptr, i want to connect sql enterprise manager with my sql 2000
sql 2005 comes with a ..slightly better product
that also handles all mssql 200 installations
adapt i see.
adapt, for some reason i am not able to connect to the sql hosting database server, i did not create it as an instance , normal default
can one have multible values for SET in one query?
humm newbie question here.. I have the error : #1040 – Too many connections while trying to connect to phpmyadmin or any of my sites.. what could be the cause of that? My server does not have more than 2000 unique visits per day, so I doubt it’S traffic related… any ideas on what/how to
check??
How can I add more connections to the db??
according to mysql manual I can add more connections to mysql.. how can I restart mysql with more connections??
anyone here uses phpMyDamin with non-english mwsql databases? i have a question
hi guys, I wonder if you wouldn’t mind me quizing your collective expert brains?! I am trying to get to the bottom of how wordpress works. See the following diagram, please. The comments table has a column of comment_post_ID INT(10) and posts table has ID BIGINT(19). I know that the
application somehow inserts the correct post id to the comment table, despite them being different data types. Does anyone have any idea h
ow and why this is done? http://img482.imageshack.us/my.php?image=wordpressmodelsj7.gif
ie how and why the data types can/are be different
most likely a mistake made made by the WordPress folks.
hal; The data types to not differ, both are INTEGER, just the *size* of them differs.
it’s unlikely you’ll have more than 65k comments for a post, or more than 2+ billion blog postings. Nothing wrong with those types.
and the numbers in parens have no effect on storage size, just diplay, and only when used with zerofill.
I think his issue is the “mismatch” of datatype size for the foreign key reference.
they’re integers, as you pointed out. No mismatch.
Technically he is correct INT !== BIGINT
like varchar != char? ;^)
to do any comparison MySQL needs to cast INT to a BIGINT. Would be better to have them match.
No, those are much greater in difference.
More like VARCHAR !== VARCHAR2
stabilize your connection please.
[RainMkr], if I only could…
ISP issues?
I gotta mow the lawn before the temp hits 100. later.
I’ll quit FreeNode, I guess, I’m only talking on QNet right now
weather kcha
3 AM EDT / 2007.08.11 1353 UTC Wind: from the NE (040 degrees) at 10 MPH (9 KT):0 Visibility: 10 mile(s):0 Sky conditions: clear Dew Point: 68.0 F (20.0 C) Relative Humidity:
Living in the midwest?
[RainMkr], yeah, it seems like it’s my ISP
[RainMkr], threnody thank you for the information. I agree that the datatypes should match, even though it is unlikely to cause an issue. In this instance, the engine for both tables is MyISAM. Would mysql allow this if they were InnoDB?
sure. Just about any RDBMS would.
I have a table called `banned_words`. It has only one column, `word` (VARCHAR(32)). I have banned words in it. It works great, but in one situation, I want to do a wildcard, like “fuck*you” or “fuck%you”. I am checking the table with the query: “SELECT COUNT(*) FROM `banned_words` WHERE
LOCATE(`word`, ‘hello everyone lala blabla i am a nice cat fuck, you and i eat grass’)”. What can I do to have LOCATE understand the w
[RainMkr], really? Are you sure? Surely it should check that the types match before allowing it?
Well, postgres and oracle allow it.
I don’t have windows where, let alone MS SQL Server, so I can’t speak to that.
[RainMkr], well, I didn’t expect that! OK, thank you. I am not sure I understand the numbers in parenthesis though. What did threnody mean when he said it only effects display? Displaying where?
One index cannot have multiple “kinds”, right? Like both PRIMARY AND UNIQUE?
it’s kind of inclusive
a primary key is by definition unique
It is not…
(Is it?)
Hrm. You’re right.
if the ZEROFILL attribute is given and and the value of 123 is stored in an INT(10), when you issue a SELECT you will get back: 0000000123
I have a table called `banned_words`. It has only one column, `word` (VARCHAR(32)). I have banned words in it. It works great, but in one situation, I want to do a wildcard, like “fuck*you” or “fuck%you”. I am checking the table with the query: “SELECT COUNT(*) FROM `banned_words` WHERE
LOCATE(`word`, ‘hello everyone lala blabla i am a nice cat fuck, you and i eat grass’)”. What can I do to have LOCATE understand the
And my ISP is probably going down again any minute.
if it was INT(5) You would get: 00123
[RainMkr], and that is the only instance when it is used?
basically, yes.
Am I still on-line?
[RainMkr], great! Thank you!
REGEXP()
not != ?
dunno
sorry, vice versa.
oops
existed before !=; that said php mysql web hosting support !=
Soon I’m going with a shotgun to my ISP’s office.
What the hell are those incompetent idiots doing?
[RainMkr]: Yes, but where? Around `word`?
[RainMkr], ah, so MySQL supports != ?
Sounds silly to support two different syntaxes.
Yes, but I would suggest not using it and sticking with
why not?
[RainMkr]: If you know, please reply before my connection dies again. :/
It’s non-standard.
oh
I’d have to look up the regexp() function.
so are there versions of MySQL that don’t support it or something?
The computer world is fully of boogy-traps in the form of non-standards syntax.
No, != is not part of the SQL:2003 standard for testing inequality.
I’m confused, but what do I know, you’re the proffessionals
[RainMkr], it was interesting what you said about data types and the storage they use up. Is there a chart that you know of that summarises this for all possible mysql data types?
[RainMkr]: Did you reply?
Sorry. Somebody is remotely pulling the plug repeatedly.
I am a nice cat fuck, you and I eat grass.
!m regexp
regexp see http://dev.mysql.com/doc/refman/5.0/en/.html
Huh?
that insight into your inner dialogue was very revealing.
And as usual, the manual shows nothing related to what one searched for.
the manual does, the bot doesn’t.
http://www.mysql.com/regexp
LOCATE(REGEXP(`word`), … doesn’t seem to work.
it’s obvious you didn’t read the manual.
“23.2. MySQL C API”
[RainMkr], ah, I have found it http://dev.mysql.com/doc/refman/4.1/en/storage-requirements.html
Why the hell doesn’t REGEXP work?
And why does the manual not have a single useful example?
And zero info about the function?
….
no one knows!
i’m learning sql and puzzling about the union operator. as i understood it, the union-operation is for joining 2 tables with the same number of attributes and its type equal to each other, right?
Am I supposed to sit here and GUESS what its nonsense error message means?
With no manual to speak of…
….try using the man :p
As I already mentioned, the manual doesn’t have any useful info on this.
Or most other things, for that matter.
Seems like this manual was scanned in from old printed docs.
google it?
which mysql hosting function should i be using if i want to find rows in a table where a certain date field is at least 6 months old?
crolle17, yes, except the word “join” is a bit overloaded in sql context
crolle17, think of union in set theory
WHERE datecol NOW() – INTERVAL 6 MONTH
From the looks of it, it’s not even POSSIBLE to use REGEXP inside LOCATE(). Is this the case?
gnari, o.k. but in a good db-schema there shouldn’t be data in different tables, which are equal (for example table1 for addresses in city1 and table2 for addresses in city2). i suppose these data belong together into the same table?
Hello?
gnari, so i suppose the union operator is quite useless in good db-schema, right?
crolle17, usually not, but remember that the “tables” being unioned may be derived, that is not really tables, but the result of some other queries
hi all,which mysql engine is better for web app? myisam or innodb?
RainChen, depends on whether you care for yur data
gnari, do you have a wise example? (because in google there a only senseless example like the mentioned)
are
examples
crolle17, i admit that i tend to normalize my tables to an anal degree, and seldom need the UNION operator
some articles said that the innodb sucks on count()
gnari, yes that’s what i was assuming. so to me there are no case for use.
gnari, is it correctly saying that the use of union-operator is a sign of bad normalized db?
crolle17, but a classic example is historic data. say you have a table with 30 years worth of data, but you normally only want to query the last month. it can be useful to split the table in 2, one large mostly readonly, and one small current with lots of updates. for queries needing
historical data , yu would query on the union of the 2
gnari, ah.didn’t know that it is possible to make such split into a part of for updating and another one which is readonly. how to set a part of data readonly?
data
gnari, ah you mean splitting the data into two tables?
crolle17, yes. this is called partitioning
gnari, so having knowingly not normalized data?
crolle17, yes, exactly
gnari, interesting
normalize till it hurts, denormalize till it works
gnari, but then a trigger would be needed for transferring data into the history-table from time to time, right?
gnari, funny saying.
crolle17, or regular maintenance jobs, like automatic first-of-the month cron jobs
crolle17, have to go, be back in 10 …
gnari, bye
I have a C# program that writes values to a MySQL db on a remote machine
the timeout period elapsed prior to obtaining a connection from the tho pool.
it also says all pooled connections were in use and max pool sixe was reached?
I know nothing about any pool
adam300, you need to increase the size of your connection pool. what function call are you using to get a connection ?
I create a new MySQLConnection object
I got the library from MySQL.com
adam300, is there no configuration with that?
no just a connection string
I thought that it was a MySQL error but if it’s a C# i need to look at that documentation
reduce the timeouts for mysql connections
that is definitely a dotnet error, yes
alright looking in the object broswer now for connection timeout and connection pool
adam300, i think you can set pool size in the connection string
client pool size, yes, which defaults to 100 AFAIK
so is it POOLSIZE=some big number?
adam300, i know nothing about C#, but are you leaving your connections open a lot?
no I close them right after I am done
that’s why I thought it was a MySQL error
adam300, and are you using a lot of concurrent connection?
only 2 per minute max
and it’s very small stuff
an insert with like 10 paramiters
gnari dotnet uses objects you create, when the code leaves scope the object is destructed and any connections are closed
adam300, if the default pool size is 100 (and you have not changed that), then i would guess there is a connection leak somewhere in your app
can I paste in here?
gnari so you define an object with all conneciton info, and on initialising an instance, it opens the socket, creates the connection, and logs in to mysql – all at once
NO
adam300, try first explicitly setting a large pool size, and come back if the problem persists
obviously
pasting is what you do in a pastebin
gnari there is little room for luser error *if* dotnet is used correctly…
http://pastebin.com/m5dff8b85
it’s pretty simplistic
adam300, are thereother applications using this database host server?
yes, but nothing big
phpbb3 with like 12-14 users
and SVN
with 3-4 users
adam300, is it possible that these other applications are hogging all of mysql’s connections?
From the looks of it, it’s not even POSSIBLE to use REGEXP inside LOCATE(). Is this the case?
i suppose it is, if for instance they are not closing connections……
Can I see open connections in MySQL on the server host side?
\q
sorry wrong window
weird there is no record of that error in mysqld.log
From the looks of it, it’s not even POSSIBLE to use REGEXP inside LOCATE(). Is this the case?
Catnip96_, i cannot be bothered to trowl through the logs, so please re-state your problem for me (clearly)
I am trying to use REGEXP inside LOCATE(), quite simply.
And my connection dies ever 2 minutes because my ISP is incompetent.
Catnip96_, REGEXP returns a boolean. which argument of locate did you want to supply with a boolean?
hello; how can I dump just one column of a table using update querries? Would I use mysql dump? If so, what arguments should i give it?
No, youcan’t dump just a single column of a table. Moreover, what would dumping a single column achieve? With only one column, you will have either no primary key, or just a primary key.
i have one table that has good values that i want to apply to another table
gvcoman, select you column into a TEMP TABLE, and dump that
gvcoman, another table in same database?
no, in a different database
it’s the same table, just that one has updated values for one column
gvcoman, i would imagine you would need more that one column, in most cases
Why not dump and restore the entire table then?
because i only want that column updated
don’t want to risk other columns incase the data is different
gvcoman, how would you know in what row of the destination table each of your columns row should go ?
the ids are the same
gvcoman, the id are in another column, no ?
UPDATE table_name SET col = value WHERE entry = N;
is that possible with mysqldump?
or another utility
gvcoman, what is the type of your column ?
float
gvcoman, select concat(‘UPDATE dest_table set col=’,col,’ where id=’,id) as upd from source_table;
i am unfamiliar with that syntax
what does that do?
ah, perfect
thank you so much gnari
gvcoman, seriously, what you should do is select the id column, and your datacolumn into a temporary table with 2 columns, dump that, restore it on the other server, and update the destination table using a join with the temporary table
gvcoman, the query i gave you earlier, will just generate update statements. you can then use cut and paste or any other methot to get them to the other database
yes, i ran it and it was exactly what i was looking for
thanks again
ok
gvcoman, there may be problems. for example if there is a row in source_table with no equivalent id in destination, that update will just generate an error
gvcoman, sorry, not an error, but will update 0 rows, so the data will be lost
No, it won’t, it will make an update which doesn’t update anything
MarkR42, i corrected myself
grari, the id is unique and is a primary key
all rows should have one
gvcoman, if id 42 in destination table has been deleted, for example
gvcoman, it may or may not be a problem for you, i just wanted to mention it
in that case then the update just won’t do anything; however all other updates will work, right?
gvcoman, yes
ya, that’s ok
guys, is it possible to use two WHERE clause?
‘$fid’
Gargantua, no. usually one uses a AND
like that^^ ?
Gargantua, what was the problem?
‘3” at line 1
Gargantua, is id a varchar?
You have some kind of escaping problem. Convert something which should be an int into an int first, ideally use prepared queries if your language / api supports them.
id is an int
Gargantua, a good debugging technique is to have your application language display the sql you make before sending it to the database
O’right, I’ll do that.
You can also enable the mysql general query log if you like. But don’t do this in production (normally)
Gargantua, it would be much easier for us to spot your problem if you could show us tha actual sql , after $id and $fname has been injected
well
‘3′
and don’t worry, the ‘ in the name value is in html
Gargantua, no AND there
pardon?
Gargantua, sorry, misread
I understand, you’re probably used to reading queries in caps
Gargantua, ok. first thing is to make sure your ‘ was quoted properly. can you try again with a $fname that does not contain quotes?
sure
ok, I changed it to the string “fname”.
still same error.
Gargantua, what was the error exacly now? (hopefully no mention of Paradise)
never mind guys, it was just a case of repeated code.
thanks for the tips, appericate it.
Hi All. If I have data histories with each data has its created datetime record. How do I group each item with the latest created datetime ?
dinochopins, you bastard
join, ask, and part?
yep
besides, there was no way he can group that way
let me put some thought into the answer, and then i realize he is gone
not without using temporary tables, anyway
select item,max(created) group by item
right, of course
but apart from that, there was no way
I was referring to a recent issue of mine, where I had to group by date
and month
having only a full datetime field to group on
thumbs, you can allways GROUP BY MONTH(datetimecol)
can you?
yes
thumbs, you can grup by any expression
that’d be swell, let me try
hi all
$req = ” SELECT profiles.*,user_extra.profilePic FROM profiles,user_extra WHERE profiles.nickname LIKE ‘%”.$s.”%’ ” ;
in my profiles table, I have “nas” and “nasky”. if i search “nas”, the script finds out “nas” and “nasky” but 6 times each one !
when i list results i have “nasky nasky nasky…. nas nas nas…”
why ?
You are doing a cartesian product instead of a join
has anyone got an idea how to start with selecting the nth row from a table
Tables aren’t inherently ordered
So you can’t.
MarkR42, uh I never used “JOIN”. now it’s the time ?
You can only skip *results* of a query.
from an ordered query
I don’t know how to use it
You can use “LIMIT” to limit the results of a query, but it happens after the query is evaluated
If you don’t join the tables, it multiples them together
with every possible combination of rows
this is a cartesian product
it is usually not what you want
SELECT * FROM table WHERE rownum % 5 = 0 ORDER BY id DESC
but in mysql
MarkR42, could u show me how to do with JOIN pls ?
No, read the docs, or just learn some SQL.
how do you go about creating entries in a new table using data from another? I basically have around 200 records in one table and I want to use one field from them to populate another field in the new table. Any ideas?
it’s an ID rather than data
can do a subquery INSERT INTO table VALUES (SELECT * FROM bleh LIMIT 1)
but theres probably a better way
probably
see SELECT INTO in the manual
cheers!
yeh that was the better way
http://dev.mysql.com/doc/refman/4.1/en/ansi-diff-select-into-table.html
better yet, insert … select. http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
is that what you mean?
the mysql version of select into is the insert … select thingee.
ah right, just testing it out now
very cool! Worked right off! Cheers guys
so my was was best
yay
hello
load data infile is ignoring the first row of a csv because the primary key is 0… can anybody offer help¿
anyone know where i can find a cheet sheet for php query quoting rules
you need to escape quotes, not much else to it
mysql_real_escape_string()
im talking when to “ or ‘ ‘ or ” “
what do you mean by “when to”
do you mean mysql_query(“”); or mysql_query(”);
because thats irrelevant, it just sends a string, just a matter or constructing a valid string using either method
no if i call a straight query like SELECT * FROM table its all good but if i run SELECT * FROM $table i have to put `$table`
i much prefer using ” ” because you dont need to escape it to insert variables, and use ‘ ‘ to quote columns
” SELECT * FROM $table; ” will work fine, replacing $table with its value
you would think but doesnt seem to in php5
“INSERT INTO table(bleh) VALUE(‘$bleh’); ” works fine also
http://www.php.net/manual/en/language.types.string.php#language.types.string.parsing
I didnt know echo “A banana is {$fruits['banana']}.”; worked, thats pretty handy
anyway to tell load data to not ignore a 0 key value (im not saving the 0, but i need to operate with it)
are md5 passwords stored as binary or text?
?
SELECT t1.* FROM mytable AS t1 INNER JOIN mytable AS t2 ON t2.id=t1.id GROUP BY t1.id HAVING COUNT(*) % 7 =0;
woohoo
oki, say I allow users to register, but I require the account to be validated by the admin before they can login. And then the user logs in before its validated. How do I cause PHP to load the page that tells them their account has not yet been activated?
not a mysql question. try ##php
Hey
oops
lol
i thought i was in #php
someone told me a long time ago that the “count” command for mysql is quite slow and uses lots of memory, that I should user “select” instead. Is that true?
Aquaman, usually they are used together
SELECT COUNT(id) FROM users WHERE user = ‘”.$user.”‘
I got that
but I just remembered what that guy told me about using count
so, is it ok? or should I use any other thing instead?
I need to get the result of the query as fast as I can
Aquaman, if you need the count, use count()
Aquaman, in that query, it seems to me that an index on users should make it quick
for example making the id field an index?
`user`
Hello. I can’t find the list of all supported options (that can go into a my cnf file) in the docs.
I’m particularly looking for something to tell the libmysql to bind to a particular interface (source ip).
Any tips?
bind-address will tell the server to binding to an interface
server being mysqld
I need it for the client.
Do you know of a LD_PRELOAD wrapper to do it?
Why does a client need to bind to an interface?
Because this particular machine has many ip’s, one for each user, and mysqld is blocking access from the ip that all users are currently sharing for outgoing connections. fun.
So I’d like to use my incoming ip also for outgoing connections, so that mysqld doesn’t block me.
Hmm.. Well, I don’t know how to do that, but in the mean time you can probably set up an ssh tunnel
(..if ssh supports binding the source ip. Checking.)
yep it does, options -b.
Nice tip.
Thanks.
oh, it’s socks
heh
hi
when using fulltext search is it possible to return which column the term was found in?
I don’t think so
bummer
have u ever seen in phpmyadmin how it puts a border around the column that match the search?
Have you ever seen the code that powers phpMA?
nope
is that good or bad?
Hi, I need a way to check if a value allready exists, before it is getting inserted again.
or better said if a row exists.
I have already had that problem.
okay?
INSERT IF NOT EXISTS …
ah, perfect, I knew that this is possible
another thing I would like so solve very hard, I find and mega slow, when i do it with php
and multiple selects, would you like to try yourself?
i despise php. sorry.
isnt that what a primary key prevents?
so right now to complete a task, i have a loop inside of a loop (php) to first look up which modules are to be displayed on the main page, and then run a loop within the module class to display the news in the order and format based on what the module row says. can i do this in one mysql
query/
i’m sorry, imMute. what do you use then?
PietroGiovanni, yes, it should, but as I do inserts without a primary key, I cant really choose
i see…
Here is the function which is way to slow:
http://rafb.net/p/JyCmJY40.html
I am importing geoinformations from geonames.org
I allready downloaded the database and tried to import the informations as I needed them
but its ten times faster, when I do the import directly from the geonames.org REST Service
But its still taking 47 hours, so it would be cool, if I could figure out a faster solution.
Perl
what scale does MySQL Administrator display the “data length” at?
what is the difference between max_connections and max_user_connections ?
the former is max over the whole server, the latter is max per user
squitta, wouldnt bulk insert be faster?
Hello. is it safe to replace mysql db with another from a previous install, as a way to restore users ?
Hello. is it safe to replace the mysql db (i’m not talkin about the the rdbms here) with another from a previous install, as a way to restore users ?
also, is there life on #mysql ?
one could do that, and then run mysql-upgrade or whatever it’s called (if the old db version was older)
no same version
better would be to recreate the users with new grant statements.
then you won’t need mysql-upgrade
they’re the same v, but why better to do it manually ?
(last sentence was a reply to your last statemetn)
(last question was a reply to your last statement)
well, you wouldn’t have to stop the server
that’s no prblm
Sounds like you know what you’re doing
i’m its only user
no i dont”
then start by backing up your …/data/mysql directory
that’s why im asking
thanks
stop the server, copy over the data/mysql/ dir and contents, and restart the server
I’d use rsync to move over the mysql data files
but start with a backup so you can restore in case of catastrophy
ok thanks a lot centosian
surely
that’s very helpful advice
mksm what is a bulk insert?
read on up load data infile in the manual
err, s/on up/up on/
hi, does anyone have a good site that has a collection of example of using mysql command line ?
the mysql docs?
a_meteorite , mysql docs too much for me as a beginner, im looking for a short collection of mysql command
!m cyzie tutorial
cyzie see http://dev.mysql.com/doc/refman/5.0/en/tutorial.html
/usr/bin/mysqladmin -u root -h opensuse.site password ‘NewPassword’ ?
i mean is it same password as the one set with /usr/bin/mysqladmin -u root password ‘NewPassword’ ?
sounds daft, but when you specify a length for an integer field it’s the number of digits right?
hmm fount it out, those are two different passwords, and changed them after loging in set password = password(“thereispassword”);
locellcount, i would not bother specifying integer length
if do changes on the grant, do i need to execute the flush priviledges and why ?
okey doke
the_wench, report
the_wench, tutorial
!report
is there a better way to write select * from t1 where t2_id not in (select id from t2); ?
better = more efficient
left join… where field_from_t2 IS NULL
that was for you
thanks
select * from t1 left join t2 where t2_id is null – ?
select * from t1 left join t2 on t1.t2_id = t2.id where t2.id is null ?
fetch rows from the modules table, and then fetch rows from the news table based on what the modules table says. the modules need to be split up in as many different styles as i like with news rows belonging to eacb module as i specify in the modules table. is there a way to do this in one
query?
Hello
I replaced the mysql/mysql database folder with an older one ,but now can’t shutdown server because debian-sys-maint can’t access the database . The folder’s owner and group are mysql, and i had updated debian-sys-maint’s password in the mysql.user table .
now i can’t restart the server
‘Access denied for user ‘debian-sys-maint’@'localhost’ (using password: YES)
guys, what is MySQLi?
php.net/mysqli
can someone pleasae help ?
‘Access denied for user ‘debian-sys-maint’@'localhost’ (using password: YES)
hello, do you know any software (linux) that is able to draw a diagram of the selected database?
there is DBDesigner, but there are very opensource-type problems with compatibility (can’t run it)
SELECT `Tag`.`id`, `Tag`.`name` FROM `tags` AS `Tag` WHERE `id` IN (82, 63, 62, 72) and I would like to know how can I perserve the order in which I am looking for them?
I would like id 82 to be the first one.
order by
desc
^ ^
google order by desc
i may be wrong on th e syntax but its something like order by id DESC;
folks I need help :
stumbler, I don’t think that will work for him
why ?
If he wants to preserve the exact order he put them in
Since they aren’t perfectly descending
ah
how do I update user info in the mysql.user (or mysql db) from another older mysql db , without touching a specific user ?
….
62 ORDER BY desc won’t work
SELECT `Tag`.`id`, `Tag`.`name` FROM `tags` AS `Tag` WHERE `id` IN (82, 63, 62, 72) and I would like to know how can I perserve the order in which I am looking for them?
notice that ^
i don’t know
hm this sucks
!man grant
see http://dev.mysql.com/doc/refman/5.0/en/grant.html
there isn’t a way is there…
^^
thanks
but i don’t remember all the passwords of the old users
that’s the thing
you can’t, n this context
Yeah Delixe, I was curious and have been looking and can’t see a way right away besides sorting it later in whatever language you’re using.
yeah there’s gotta be a logic to your “sort” so use it in the code
heh yeah looks like it
unless,
Because I have my tags in one table and then their ids in another
you do a compound query
Well, I know nothing about using expressions in the order by
Then I SORT the ids in second table
And want them to come out nicely in the first.
bah
You may be able to find more info and put a expression in the order by.
MySQL has too limited functionality.
It sucks
Well, it’s not oracle.
and how can you do that in MSSQl or Oracle ?
no, your issue is a table misdesign
and how can you do that in MSSQl or Oracle ?
he can’t
he did a poor design with his database
yeah Thumbs is prolly right. Generally when you can’t achieve something, and if it’s that important, it means poor design.
Actually my table is designed very well.
then order by another field
i’d like to know the logic behind the order you want
exactly
I have a table that will be updated a lot. I’m thinking about using innodb so I can take advantage of row level locking so multiple people can update different rows. Is this a sane plan? What drawbacks am I not aware of by going with innodb?
It looks like I’ll just have to do it code wise, thanks.
Delixe, still there man?
yes.
Delixe, I think I have your solution, but you might not like it.
it if requires table redesign don’t bother
Nope…
alright what’s up
Delixe, create a temporary table with those 4 values you’re trying to select, with each one having a second field that is a “sortby” field of sorts, 1 for the first, 2 for the second, 3 for the third, 4 for the 4th, and then join it with your select above and sort by the temporary table’s
sortby field.
Depending on your experience with temporary tables, that might be nasty and new for you. But a good learning experience, heh
It’s probably just easier doing it through code
Exactly,
And faster!
yeah it’s already 90% coded anyay
lo
lol*
yep done.
lol
lol.
I swear one day I am making my own database software
MySQL sucks balls.
how is mysql not fulfilling your requrements, exactly?
select tm_club_name,tm_club_district, tm_club_url from clubs where tm_club_url is not null order by tm_club_district; – how can I print a subtotal of rows for each tm_club_district ?
It needs a recursive feature, deep searching based on a field.
Something like categories of categories.
Luckily for me, my framework that I code on takes care of it for me.
MySQL needs to be able to do multiple inserts in a single query, and multiple counts
well as far as 1) is concerned, it sounds like a terrile idea where performance is concerned
mysql CAN do multiple inserts in one query.
oh it can?
insert into tbl (f1, f2) VALUES (1, 2), (3, 4), (5, 6)
ah
now, define ‘deep’ searching
I guess I came to the wrong place. Sorry.
IF you have categories and want to find categories under that category and categories under that category for instance.
huh?
thumbs, I asked a question but was ignored, it seems.
subtotal of rows?
that sounds like you need table normalisation
number of rows returned for each tm_club-district
You mean I need another table saying what categories are attached to exactly what parents? That’s absurd legin
select count(id), f1, f2, f3 FROM …. GROUP BY f1, f2, f3
hmm
legin?
lol oops
That’s absurd thumbs*
not really. Table normalisation allows for cleaner queries, and more efficient data access, at the expense of insertion time.
Not quite sure that’s what I am after.
oh well, forget I asked.
perhaps you can ask your question more clearly, then?
SQL_CALC_FOUND_ROWS might work nelgin?
yeah I agree with thumbs.
thumbs, so for every child, I need a table that lists its parents and its parents parents?
if you design your table properly, a single table can serve that purpose.
Hi, I’m installed mysql 5, its running but I couldn’t add users… how I do that? I used mysql -u root
OK, when I run the query, I get a number of rows, ordered by tm_club_district
that sucks thumbs
and then, GRANT command
Hence why MySQL needs to be made better
Yes Neglin, so what do you want?
!man grant
see http://dev.mysql.com/doc/refman/5.0/en/grant.html
^^
I want the number of rows for each district after it has displayed the rows.
I SAW
hrhr
for each district
yes, grouping will do that.
AloBlumenau, read it again then.
de4dpixel, group by
yep grouping will do that
mysql.com/doc, start reading
read my initial response again
Delixe
tizag.com – Pretty good guide.
huh?
nice help
Delixe = Delixe.
I tried it and it didn’t appear to work, maybe your response was as clear as my quetion
well you must read the documentation, and tell us EXACTLY what you tried
then, we’ll tell you what you did wrong.
You’re going to have research GROUP BBY
GRANT ALL PRIVILEGES ON *.* TO ‘london’@'localhost’ IDENTIFIED BY ‘pass’ WITH GRANT OPTION
Group by doesn’t print all the rows though, right?
nothing happened before I use it
nelgin, the docs tell it all
no, it grouips them.
groups, too
and when I try access using phpmyadmin, londo user, I got an error my pass is wrong
OK, well I must have missed something then.
#1045 – Access denied for user ‘london’@'localhost’
AloBlumenau, other users work fine from PMA?
no, there isnt anyone
nelgin, yeah, you missed reading the freaking manuals
AloBlumenau, not even root?
using root I got same error
tibyke, why don’t you shut the fuck up?
so what do you think then? do you think at all?
Geez.
nelgin, i would, but you just keep asking rtfm issues.
If I could get it to work, I wouldn’t be asking for fukcing help would I?
nelgin, so just start freaking reading before asking
http://www.tizag.com/mysqlTutorial/mysqlgroupby.php
hi, can someone help with just an create table syntax i´ve got the error 1005 ?
!perror 1005
grr
sorry??
pastebin your statement, please
Guest_666, look up error 1005 in the manual, but it must be some sort of syntax error
paste here?
pastebin
try http://pastebin.ca or http://pastebin.mysql-es.org
no in a bin
after you create a user, try to log in using the mysql console
RIght, given that example, it prints one line per “type”
correct.
Hence, I am getting one line per district, not 10 lines
ok
ah
ok tnx
same error
So, as far as I can tell, group by isn’t going to do what I need.
Access denied for user ‘london’@'localhost’ (using password: YES)
nelgin, why don’t you give us an example then.
give us a couple of sample rows, and how you want them arranged
perhaps then we’ll understand what you need.
guys, is it really true that mysql 4.1.20 does not have a boolean data type?
OK, here’s an example.
select tm_club_district from clubs where tm_club_url is not null order by district
This returns 4967.
hal, it has
5[04thumbs5]1: http://pastebin.ca/653891
how to list mysql users?
do not use colours in here, please
tibyke, that’s silly, isn’t it?!
hal, what? why?
The first 26 rows contain “01″. I want it to print the 26 rows then tell me it printed 26 rows. Then it’ll print 58 rows for “02″ then I want it to tell me it printed 58 rows etc
sorry
AloBlumenau, select whateer from mysql.user;
ie, I want it to print all the rows for district 01 and then tell me how many rows it printed, then do the same for district 02, then 03, etc.
there should be an way of interpretting 1/0 yes/no true/false all as the same
do you want to print the count on EACH line?
hal, and yes, there is
No, not on each line.
hal, mind the fine manual at mysql.com/doc
and if it output nothhing
?
AloBlumenau, on what query?
ok then. Where exactly?
Something like
01
3 rows
02
2 rows
if you wrote the ouput on a pastebin, this would be MUCH easier.
*sigh*
lol
thumbs. probably.
I’ll do it after dinner. bbl
“here, guess what I’m trying to output, and write the query for me”
sure!
tibyke, there is no way of doing it is there?
hal, what? are you in write-only mode, or sometimes you read answers to your questions?
tibyke, does not compute. Not enough data. Please reboot
hal, ok
hi, can someone help with just an create table syntax i´ve got the error 1005 ? http://pastebin.ca/653891
tibyke, well, I feel the boolean functionality could be better implemented, but I guess we have to be grateful for what the lord provides to us, eh?! 
Guest_666, you forgot to paste the error msg you get
hal, yes
sorry i´ll update
* Checking for corrupt, not cleanly closed and upgrade needing tables.
what does it means?
AloBlumenau, which part cant you understand?
is it an error?
or just a checking?
no, its not
just read it.
[tibyke]: http://pastebin.ca/653908 there is
I cant login even if root user
I am investigating a database at the moment, and there is a row that contains the following data. Can anyone offer any suggestions on how it may be used? Are the colons or semicolons the delimiters, do you think? a:1:{s:10:”subscriber”;b:1;}
I installed kmysqladmin
and same error
AloBlumenau, check the presence of your mysql database files in your datadir
hi guys I have a problem with a corrupt table
hal, its a seraialized php array
Kalavera, good for you
how I do that? I installed mysql from synapitc
Table ‘traffic’ is marked as crashed and last (automatic?) repair failed
AloBlumenau, contact your system adminstrator
tibyke, thank you
Kalavera, mysql.com/repair
fuck U asshole
are you stupid
tibyke, Repair does not work
why are you here?
I try it a lot of times
Guest_666, ls -l and df -h
AloBlumenau u´r from brazil man?
AloBlumenau, stay cool.
if you don’t have something to say, shut up
how did you try to repair it, exactly?
tibyke, thank you very much – I am reading up on that now
tibyke sorry man i don´t get it?
yes I am
REPAIR TABLE table_name;
Guest_666, check permissions and disk space
and mysqlrepair table_name
and mysqlcheck -r table_name
those should work.
do you have a backup?
well it doesn’t work
mmmm I don’t think so , well is a mysar table
[AloBlumenau]: intaum para de ficar sujando os brazileiro aki cara fica xingando num leva a nada na boa
mysar?
Guest_666, english ffs!
omg, even more morons here than on ircnet
thumbs, yeah! is a log analyzer for squid
[tibyke]: i say to AloBlumenau to stop fighting and saying shit around
mysql based
it has a traffic table and this table does not work
Guest_666, thats right, but stick to english, please
regardless, do you keep backups?
well hold on
Kalavera, you can try myisamchk
[tibyke]: ok sorry and about the permissions and disk space, it´s all ok
yeah I try that first
fuck you too
I paste the result in a pastebin
as patient as we were, I find your attitude counter-productive
[AloBlumenau]: got the message, stick to english, congrags!
http://www.pastebin.ca/653916 – that is
you could try dropping the index
no
I don’t know if this table has an index
the error message suggest it
http://www.pastebin.ca/653921
yeah It has indexes
how to drop it ? and what index I need to drop?
um, I am not sure.
tibyke, thanks for the pointer on serialized arrays – I am more clued up on it now, as there is plenty of info in the php manual. Appreciated
http://www.pastebin.ca/653924
this is an explain for this table
hal, yw
any idea?
not really. Sorry.
perhaps someone else can lend a hand?
no problem thumbs thankyou at all
XD maybe, I think to drop all table and redo
well, do you want to salvage the data?
if not, simply drop the db
This DB only has access_log information from squid
and I see the problem came from two months ago
Yeahh drop all database is an option in this case, is not critical
drop database mysar;
Query OK, 6 rows affected (2.92 sec)
mysql
that’s it thank you
great
in the future, please make backups.
TNHX eveybody
yeah , usually i do that but this is not a case I learn about it
you can usually repair most tables
but nothing is better than solid backups.
Hi guys
Hello
I need some help to solve a problem with mysql
Go on…
googd
hi henaro
mysql, mysqladmin, and so on a message appears
Floating point exception
what does it means?
I’m not too sure . _ .
what dist? what mysql?
ok
Rhel 5 + MySQL 5
from rpm?
yes
rpm -qa | grep -i mysql | xargs rpm -V
UPS!
Unsatisfied dependencies for MySQL-client-community-5.0.45-0.rhel5.i386: libc.so.6(GLIBC_2.4), rtld(GNU_HASH)
hrhr
you are missing something, or you forced/nodeps-ed an install/upgrade
and now?
it is not a mysql issue
I seem to be getting an error when connecting to MySQL ._.
it’s a red hat issue
install the mysql packages properly.
ok thanks
henaro, fascinating
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
henaro, very nice!
!perror 2002
make sure mysqld is running first
I do
I want to replace values in MySQL, e.g. 5 to 10, 10 to 4, 4 to 5, etc. How to do it?
mysql.com/update
I can’t make 5 UPDATE queries.
CASE
!man case
see http://dev.mysql.com/doc/refman/5.0/en/case-statement.html
\o/
So has anyone encountered that error before? OR know how to make it not error?
But if MySQL update 5 to 10, won’t it update 10 to 5 again?
google for it, you’ll get 1000s of hits
which os?
Ubuntu 7.04
ps -aux | grep mysql
I googled and tried everything I saw
is it running?
*sigh*
Yeah
0 grep
that’s your grep command
henaro, damnit, your mysqld is not running
not the server
omfg
Well it won’t start
mysqld –console –user=mysql
then why the hell you say its running?
it does not update over and over again – only once
8 [ERROR] Do you already have another mysqld server running on port: 3306
Because of that
so kill all mysqld processes first (if you cant stop the server)
it checks the current value in the field and returns another value. then it moves in to the next row
okay
killall mysqld_multi; killall mysqld
however, don’t i need stored procedure?
no process killed
My script has to be capable with SQLite – i wonder if it has such syntax.
henaro, fuser -n tcp 3306
hey, if i create a column ‘`id` INT( 255 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,’ what is the biggest id ?
Didn’t do anything . _ .
henaro, then just start the bitch with the ubuntu init script
what’s that fuser supposed to return?
2147483647
thanks
flupps|US, pid of process using tcp port 3306
* Starting MySQL database server mysqld [fail]
D:
Hey all.
Hello sir
flupps@piglet:~$ fuser -n tcp 3306
3306
UPDATE albums_serialized SET other_discs=” WHERE blz_id IN (‘a’, ‘b’, ‘c’);
or:
UPDATE albums_serialized SET other_discs=” WHERE blz_id IN (‘a’), (‘b’), (‘c’);
Or are neither of those correct?
eyeRmonkey, php.net/update
i mean mysql.com/update, read it
php.net?
Hehe.
SettlerX, yes
oops
flupps|US, prolly you have an older version of fuser
i can’t find any information on how to use “IN” with static values (as opposed to using a subquery).
eyeRmonkey, the one that does not generate a syntax error is the correct one
Hey
is there any way to do something along the lines of this (in PHP) SELECT * FROM foo WHERE strtolower(bar) = ‘baz’?
http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html
Thanks.
lower
eyeRmonkey, if you are still looking for it, http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_in
thanks. i still was.
UPDATE albums_serialized SET other_discs=NULL WHERE blz_id IN (‘0-20501′,’00-01′);
is it possible to specify distinct in a join? in order to help mysql know where the redundancy is going to occur
johnjosephbachir, probably not the way you think
how could I get my db from a drive.. i cant chroot and use mysqldump .. ?
a what from what?
i managed to boot up in a live cd and mount my root where my db is.. seems the fs is messed up and wont boot and i dont know how to fix it.. i just need to get the db
recover from daily backup
:-/
stealth, what os, distribution ?
Gentoo Linux
stealth, do you think the data directories are ok ?
one day i tried to access my server and I got nothin but input/ouput error.. all i could do was hard reboot with a livecd.. i managed to use testdisk and find a superblock and rebuild the fs.. it mounts now and everything looks to be there.. (it wont boot though, keeps saying fs has errors
and runs fsck)
does your livecd have mysql installed?
yea
stealth, and you cannot chroot to the mounted disk?
im trying.. used a old dsl cd was layin around and was 2.4 kernel and said was too old.. got a new knoppix booting now..
if the mysql installed in your livecd is same version as you had on your hd, you might try to use that one, but the version probably needs to be exactly the same
stealth, best solution is to try to get chroot to work, and use your original mysqldump to dump the database
yea i know eee
stealth, another option is to install same gentoo on another disk, and after mysql install symlink your original datadirectories into /var/lib/mysql or wherever gentoo puts it
stealth, problem with gentoo, is that it is difficult to get the exact same setup, due to all the build options
i should have my make.conf somewhere.. i do have everything backed up (somewhat) .. just faster to get it off the disk
gnari, i got in the chroot, but mysql cant run in the chroot… i get the .sock error
from mysqldump
stealth, did you start mysqld ?
yea in the chroot.. /etc/init.d/mysql start but i get .. /sbin/functions.sh: line 286: /dev/stderr: No such file or directory
stealth, does not sound good
stealth, sorry, this has gone out of my expertise. you need a real mysql hacker
theres no file to just pull it off of?
oohh..i found my all_databases.sql ahh geeze wheww… mainly from here i suppose its just learning would love to know how far the system is really screwed .. i did learn quite a bit about ext3 and recovery… suppose ill start using my old tape drive more now
well the data directories (/var/lib/mysql on my non-gentoo machine) could be copied into equivalent directory of a working mysql setup, but you would have to make sure the build options are as similar as possible, and the same mysql version of course
ah ill copy that, imma rebulid the machine with another hard drive and the same make.conf…
i still dont get what happend.. the drive seems fine
stealth, ah well, shit happens.
yea indeed
hi guys
just little confirmation before going foward
we can JOIN between two databases rights ?
database is just a directory with tables it doesn’t slow down the server ?
it’s like joining two table from the same DB right ?
Hiya! I’m using MySQL and I have an images table, a user table, and an image_votes table. I’m trying to say “Give me an image from the images table, but only if the user hasn’t yet voted on it”
I’m not sure how to phrase that.
the image_votes table has user_id and image_id columns, and the vote column.
would any nice soul know how to accomplish that?
That just requires a SELECT images left join image_votes ON … WHERE image_votes.primary_key IS NULL …
“SELECT images.* FROM images LEFT OUTER JOIN image_votes ON image_votes.image_id = images.id WHERE images.parent_id IS NULL AND (image_votes.user_id IS NULL OR image_votes.user_id != #{authed_user.id})
is what I came up with
I’ve left out the SELECT list and the FROM keyword.
it seems to work.. but I’m not fully sure I totally understand i t
Ooops. I didn’t leave that out.
That’s correct.
Cool. I’m not sure exactly why I need to specify: image_votes.user_id IS NULL OR
image_votes.user_id != any_integer_here should return true if user_id is NULL right?
You can simplify your solution.
I don’t know what images.parent_is is null means or why that would be needed for this.
You want to move any image_votes tests into the ON clause.
well, that’s an implementation specific thing.. if parent_id is NULL it means that image is not a thumbnail
which is the only kind of image I want to vote on
ugly I know. :/
hi,all. how do i get the length of a string?
len / strlen seems not working
Are you checking only for one user?
DavidLeon, every tried mysql.com/doc?
Thank you for your help.. I have to go, sorry.
But yes, only one user
string functions
Just trying to say “return the images the user has not voted on yet”
SELECT images.* FROM images LEFT OUTER JOIN image_votes ON image_votes.image_id = images.id AND image_votes.user_id = ‘thisuser’ WHERE image_votes.user_id IS NULL;
oh thx
That’ll find only images that haven’t been voted on by this user.
Add the parent_id logic as well to handle the thumbnails.
SELECT images.* FROM images LEFT OUTER JOIN image_votes ON image_votes.image_id = images.id AND image_votes.user_id = ‘thisuser’ WHERE image_votes.user_id IS NULL AND images.parent_id IS NULL;
could numbers implicitly be cast to any other types?
yes
where does mysql saves the user&password?
in $DATADIR/mysql
bofh
hey guys, i have a mysql dump file and im trying to add an id field as the primary key to it, but all the entries are seperated by just a comma so when i add a field i get an error of column count
derek, add it in the db then
derekj212, i mean
what do you mean
If you can generate the dump with an insert field list for each insert, you can add the column without that problem.
i only have the dump, i dont have access to the orignal db
Your problem is without the field list the values list much contain all the fields ni the table, which you are changing.
That’s why it fails.
you can redump, or use an awk/perl/sed oneliner
yeah i get that, i just dont know what to do to fix it
Then import the data and alter to add teh column later.
Just add the ALTER TABLE statement to the end of the dump file.
if i add an id column later can i get it to become the primary key, autoincrement and populate it?
Yes.
how can i do that?
i already have a db loaded with the data
alter table your_table add id int auto_increment primary key;
and that will populate the id?
Yes.
oh wow way easier than i thought
So you just need to add that to the end of your dump file, if you’re still asking the original question.
can i just alter the table if i already dumped it without the id?
You can add the alter to the dump file or perform the alter manually after importing.
The moment you alter the table in the DB, the dump file is out of date.
the dump file is just one that has ZIP code info i downloaded, so im not worried about it
If we’re ignoring the dump file, yes, you can always just add a new primary key with the above form.
Assuming you don’t have a primary key for this table.
If you have one, you’d have to drop the key first.
right now the primary is the zip but its a string which is screwing things up
so i needed to either switch it to an integer and save the data or just add a new column to use
ALTER TABLE to drop the primary key (not the string column, just the key) and then alter table to add the new primary key.
but the string column IS the key i think
If you want to toss the old key AND column, you can just drop the column.
The key and the column are separate constructs.
oh ok, no i cant drop the whole column
but ill drop the key
then add the id field as primary key
Right. That was my point.
yeah just making sure i got it right
so i need to specify primary key, autoincrement and anything else
?
Just as I typed it.
oh ok awesome thanks
Whether you use a signed int or unsigned int is up to you.
its just going to be 1….n
so unsigned would prob make more sense
alter table your_table add id int unsigned auto_increment primary key;
ok ill try it now
Perfect! That worked, thank you
thanks it works
Xgc’s just helping everybody.
Lucky guess.
Xgc is an AI, a local instance of Yahweh, hand coded by William Gibson and Neal Stephenson…
i’d like to return the minutes/hours days since a timestamp from now() using sql, can anyone point me to some help?
really just so people can see how long it’s been since a post has been submitted.
no?
just ping me if you want to help.
have a table with 3+ fields, addr1, addr2 and timestamp, want to select rows based on distinct addr1 with the most recent timestamp, what is the simplest way to do this with a select statement?
maybe the TO_DAYS will help
znoG im trying timestampdiff; in a sec
this is not a mysql specific question but more of a general development question so its a bit OT We use SVN for its intended use, but I always have an issue with the DB part of things. If I change the DB structure, I have to let all the other developers know and they made have made changes
themselves which makes syncing a bit of a nightmare. What do you guys use?
ideally we should share the same DB, but using port forward of 3306 (through SSH) makes things a bit slow
is it slow cause it’s saturday night?
figured it out, select distinct(addr1),addr2,max(timestamp) from table group by addr1
maybe? select distinct(addr1) from table orderby timestamp asc
you might be adding superflous functions, or im mis understanding what you’re doing.
arg..could someone explain the difference to me for these two queries:
SELECT images.* FROM images LEFT OUTER JOIN image_votes ON image_votes.image_id = images.id AND image_votes.user_id = 1 WHERE image_votes.user_id IS NULL AND images.parent_id IS NULL AND image_votes.skip IS NULL AND (image_votes.ip IS NULL OR image_votes.ip != 416841577) LIMIT 0, 1;
SELECT * FROM image_votes WHERE image_votes.ip IS NULL OR image_votes.ip != 416841577;
the first one returns an image, the second one does not
however, they both should not return an image.
it’s something weird about that “image_votes.ip IS NULL OR”
if I have in the above query, the above query fails to work.
The difference is the LEFT JOIN (which is an outer join).
Right.. I was trying to isolate the SQL
the behavior I’m seeing is the difference between
SELECT images.* FROM images LEFT OUTER JOIN image_votes ON image_votes.image_id = images.id AND image_votes.user_id = 1 WHERE image_votes.user_id IS NULL AND images.parent_id IS NULL AND image_votes.skip IS NULL AND (image_votes.ip IS NULL OR image_votes.ip != 416841577) LIMIT 0, 1;
and
SELECT images.* FROM images LEFT OUTER JOIN image_votes ON image_votes.image_id = images.id AND image_votes.user_id = 1 WHERE image_votes.user_id IS NULL AND images.parent_id IS NULL AND image_votes.skip IS NULL AND (image_votes.ip != 416841577) LIMIT 0, 1;
is different.
even though “ip” is not null
(the only thing that’s changed is at the very end.. I removed “image_votes.ip IS NULL OR”
okay… Now I can’t even reference “image_votes.ip” in any way and have the query succeed. Gah.
okay
moving it into the “ON” clause appears to have fixed it
so weird..
Ohhh!
I can’t reference anything about the image_votes in the WHERE clause
well..no..
not sure what goes into the ON clause then. Hrm.
Criteria in the WHERE clause and criteria in the ON clause of an outer join behaves much differently.
yes.. I’m trying to learn now.
That’s the point of the outer join.
Failure of the test in the ON clause does not remove any rows of the left table frm the result. Failure of the same test in the WHERE clause removes the corresponding left table rows from the result set.
I see
what does failure of the ON clause do?
For an outer join the left table rows remain and the right table row where a match could not be found, the columns return null.
That’s for a LEFT OUTER JOIN.
okay
I’m getting the feeling that whever I test in the ON clause needs to exist in both tables
is that correct?
or..
No. That’s the point of the left outer join. If there are matching rows in both tables, there’s no need for an outer join.
matching columns?
LEFT OUTER JOIN is used when you want all the rows of the left table plus rows of the right table (where matches are found) and null otherwise.
but if I’m doing SELECT images.* FROM images…. won’t only images.* be returned?
When I talk about the result of the JOIN, I’m not atrlking about what you have in the select list. That’s immaterial.
Oh
so internally it returns both the left and the right table columns together.
A JOIN produces a table. You can select whatever you want from that resulting table.
