Hi all Im trying to change the default value of a column and I cant seem to figure out the query Im using –
can REPLACE() take regexps or is there some replace function that will do regexps?
hi, can you help me pls with this query? INSERT INTO bans (ip) VALUES '195.138.78.40 ','193.17.208.229' (You have an error in your sql host syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''195.138.78.40
','193.17.208.229'' at line 1)
INSERT INTO bans (ip) VALUES ('195.138.78.40 ','193.17.208.229'); perhaps.
INSERT INTO bans (ip) VALUES ('195.138.78.40 ','193.17.208.229') - Column count doesn't match value count at row 1
my bad
you will have to split your query into two inserts.
ok, thx
no problem.
no
just chain values()
separate them with comas
how? how I have it done?
Hey guys, got a Q. (Could you guess?)
go for it
Will do.
I've got this query that calls up all job IDs that are attached to a particular time from a relational database ("SELECT jid FROM job_time_r WHERE tid=$currTime") but I want the times themselves to be sorted by…. wait a moment…
It just struck me. I'm looking at the wrong query. 
Nvm guys, I'm all set. 
dd
testing….
completed.
initiating spamming process
Nek se ovaj vijek gordi nad svijema vjekovima,
on e era biti straana ljudskijema koljenima.
U nj se osam blizanacah u jedan mah iznjihaae
iz kolevke Belonine, i na zemlji pokazaae:
Napoleon, Karlo, Bliher, knez Velington i Suvorov.
Karaore, bi tirjanah, i `varcenberg i Kutuzov.
Arei je, strava zemna, slavom bojnom njih opio
i zemlju im za popriate, da se bore, nazna io.
Iz grmena velikoga lafu iza trudno nije,
u velikim narodima geniju se gnj'jezdo vije:
ovde mu je pogotovu materijal k slavnom djelu
i trijumfa di ni v'jenac, da mu krasi glavu smjelu.
Al' heroju topolskome, Karaoru besmrtnome,
sve prepone na put bjehu, k cilju dospje velikome:
di~e narod, krsti zemlju, a varvarske lance sruai,
iz mrtvijeh Srba dozva, dunu ~ivot srpskoj duai.
dade Srbu stalne grudi;
od viteatva odviknuta u njim lafska srca budi.
Faraona isto noga pred orem se mrznu sile,
orem su se srpske miace sa viteatvom opojile!
Od ora se Stambol trese, krvo~edni otac kuge,
sabljom mu se Turci kunu - kletve u njih nema druge.
. . . . . . . . . . . . . . . . . . . .
u . . . . . . . . . . . . . . . . . . . ./u
Da, viteza sustopice tragi eski konac prati:
tvojoj glavi bi sueno za v'jenac se svoj prodati!
. . . . . . . . . . . . . . . . . . . .
u . . . . . . . . . . . . . . . . . . . ./u
Pokoljenja djela sude, ato je ije daju svjema!
Na Borise, Vukaaine, opata grmi anatema,
gadno ime Pizonovo ne sm'je kaljat mjesecoslov,
za Egista uprav sli i grom nebesni, sud Orestov.
.. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
Nad svijetlim tvojim grobom zloba grdna bljuva tmuae,
al nebesnu silnu zraku ato ' ugasit tvoje duae?
Pla ne, grdne pomr ine - mogu l' one svjetlost kriti?
Svjetlosti se one kriju, one e je raspaliti.
Plam e, vje no ~ivotvorni, blistat Srbu tvoje zublje,
sve e sjajni i udesni u vjekove bivat dublje.
.. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
Zna Duaana rodit Srpka, zna dojiti Obilie,
al heroje ka Po~arske, divotnike i plemie,
gle, Srpkinje sada rau… Blagorodstvom Srpstvo diae…
Bje~i, grdna kletvo, s roda - zavjet Srbi ispuniae!
U Be u na Novo ljeto 1847. goda
So initelj
Lica
Vladika Danilo
Iguman Stefan
Serdar Janko uraakovi
Serdar Radonja
Serdar Vukota
Serdar Ivan Petrovi
Knez Rade, brat vladike Danila
Knez Bajko
Knez Rogan
Knez Janko
Knez Nikola
Vojvoda Draako
Vojvoda Milija
Vojvoda Stanko (Ljub.)
Vojvoda Batri
Tomaa Martinovi
Obrad
Vuk Raslap evi
Vukota Mrvaljevi
Vuk Tomanovi
Mnozina
Bogdan uraakovi
Vuk Miunovi
Vuk Manduai
Vuk Ljeaevostupac
Pop Mio
Sestra Batrieva
Had~i-Ali Medovi, kadija
Skender-Aga
Mustaj-Kadija
Arslan-Aga Muhadinovi
Kavazbaaa Ferat Za ir
Rid~al Osman
Jedna baba
Lica koja pjesnik nije bio unio u spisak:
Vuk Markovi, jedan Cuca, jedan vojnik, drugi vojnik,
svat Crnogorac, svat Tur in, a e, aci.
Skupatina uo i Troji ina dne na Lovenu
Gluho doba noi, svak spava.
Vladika Danilo (sam sobom)
Vii vraga su sedam binjiaah,
su dva ma a a su dvije krune,
praunuka Turkova s Koranom!
Za njim jata prokletoga kota,
da opuste zemlju svukoliku
ka skakavac ato polja opusti!
Francuskoga da ne bi brijega,
aravijsko more sve potopi!
San pakleni okruni Osmana,
darova mu lunu ka jabuku.
Zloga gosta Evropi Orkana!
Vizantija sada nije drugo
no prija mlade Teodore;
zvijezda je crne sudbe nad njom.
Paleolog poziva Murata
da zakopa Grke sa Srbima.
Svoju misli Brankovi s Gertukom.
Muhamede, to je za Gertuku!
Sjem Azije, e im je gnjijezdo,
test
worked
..
why did that
that
spam bot thing
come in here
re initiating spam process!
..
no
man
you're doing it all wrong
you need to secure webmail web hosting affiliate links
ah
you're not making any money
what a fucking retard.
no
man
it's not pwn
nothing is pwn here
you're a loser
Nek se ovaj vijek gordi nad svijema vjekovima,
on e era biti straana ljudskijema koljenima.
U nj se osam blizanacah u jedan mah iznjihaae
iz kolevke Belonine, i na zemlji pokazaae:
Napoleon, Karlo, Bliher, knez Velington i Suvorov.
Karaore, bi tirjanah, i `varcenberg i Kutuzov.
alright good going
Arei je, strava zemna, slavom bojnom njih opio
i zemlju im za popriate, da se bore, nazna io.
Iz grmena velikoga lafu iza trudno nije,
u velikim narodima geniju se gnj'jezdo vije:
ovde mu je pogotovu materijal k slavnom djelu
i trijumfa di ni v'jenac, da mu krasi glavu smjelu.
Al' heroju topolskome, Karaoru besmrtnome,
sve prepone na put bjehu, k cilju dospje velikome:
di~e narod, krsti zemlju, a varvarske lance sruai,
iz mrtvijeh Srba dozva, dunu ~ivot srpskoj duai.
dade Srbu stalne grudi;
od viteatva odviknuta u njim lafska srca budi.
/ignore loserOfTheDay
Faraona isto noga pred orem se mrznu sile,
orem su se srpske miace sa viteatvom opojile!
easy as that
Od ora se Stambol trese, krvo~edni otac kuge,
sabljom mu se Turci kunu - kletve u njih nema druge.
.. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
Da, viteza sustopice tragi eski konac prati:
tvojoj glavi bi sueno za v'jenac se svoj prodati!
.. . . . . . . . . . . . . . . . . . . .
or /ignore *@71-32-160-196.desm.qwest.net
. . . . . . . . . . . . . . . . . . . .
Pokoljenja djela sude, ato je ije daju svjema!
Na Borise, Vukaaine, opata grmi anatema,
gadno ime Pizonovo ne sm'je kaljat mjesecoslov,
yes!
thank you, HarrisonF !
+o
what's that?
operator
means he can ban and kick people
why not ban?
5 ! loserOfTheDay was kicked from #mysql by HarrisonF (stop) that was the
er
operator! exit!
no, that was the kick
4 ~ HarrisonF set +b *!*@71-32-160-196.desm.qwest.net on
that was the ban
yes i know
obviously struggling with my cut'n'paste skills
with all the power computing technologies can give even regular people, cut'n'paste is still the most confounding application
hardly
it's just my basic motor skills (and the poor-ish UI of this program) which seem to have failed
it's just my basic motor skills (and the poor-ish UI of this program) which seem to have failed
hi, how can I do that INSERT query on duplicate key do nothing pls?
Did I miss something?
qaws, it's hard to understand what you are asking
qaws, if you have a primary key set, and you've told it to auto-increment, there shouldn't be any duplicates of that key.
qaws, assuming you have a unique key, then the INSERT query will indeed do nothing on duplicate key
i have a table with unique col (ip) and i want to do, if I will try to insert the same ip, that it should do nothing
but it returns mysql_error()
qaws, right, so if you've set up your table correctly, you'll be all set
qaws, what's the error?
Duplicate entry '66.232.97.225' for key 2
qaws, OK
qaws, http://dev.mysql.com/doc/refman/5.0/en/create-table.html
qaws, What's your query?
INSERT INTO bans SET `ip`='66.232.97.225'
qaws, aha
qaws, so, since you have that IP already in there, it gives you an error.
yeah
qaws, your program should recognize there's a duplicate IP, and respond accordingly…
qaws, I'm not sure what the problem is - mysql is telling you you've got an IP of that name already.
qaws, so essentially MySQL has done nothing except return an error
qaws, there may be a way to force MySQL to be silent on error, if that's what you are really after
OK, then I will try to ignore it and it will go
qaws, but personally I wouldn't do that - I'd rather have my program check to see that the error is really duplicate key first before discarding it.
qaws, to be sure, run that above test query, and then run…
ok
SELECT ip address host FROM bans WHERE ip='66.232.97.225'
I highly doubt (99.9% kind of a doubt) that you'll get more than one result in the SELECT query after running that INSERT query you gave me.
use inet_aton and store ip numbers as an integer unsigned not null
wot programming language is mysql written in
?
stevedtrm, just a wild guess, but I'd say some C variant.
C and C++
k thx
Ha!
:P
the C++ features are relatively limited
pretty much objects and templates are the only things used from C++
why is almost every commercial app programmed in c or c++?
why not vb or java or whatever
or is it sort of legacy code?
things like RDBMS need to be quite optimized, so that is why C and C++ are chosen
Because C and C++ are more powerful and run more lower-level. There's probably more reasons, but those are a few
same with OS's
Power, actually, is arguable
But the low-level bit (speed, ability to interact with hardware better) is definitely on C's side
a lot of other programs aren't in C or C++ without you even knowing
hrm
hi, i have a few sets of options to store in a column in mysql. what is the suitable type for this ?
You could use the SET type
It's not in normal form, technically, but it is sometimes quite convenient
ok, thanks
but i have look into enum
what are the different ?
enum you can only put one value in the column (which is technically first normal form)
WHEREAS set allows you to put several values in the same row in the same column
how do I know which columns in a table have an index?
doesn't seem to list them all.
in particular, it seems if there's a multi-column index, only the first column would be listed as having an index.
hey when I dod flush tables with read lock, then do a show master status, the log pos still counts up, is that correct?
show create table tablename;
hi guys, what is the best to change the document root for the data folder where the DB's are located in on Linux ? Just make a symlink ?
make changes in my.cnf and restart?
thanks
np
that's restart mysqld, not the computer.
threnody, yep I know that but I was thinking what was the most clear way
hello, how do i stored options e.g. a this is cat b this is dog c this is mouse d this is flower inside mysql table ? waht type can i use ?
0 and subtract an hour from
uh, why not use two columns … ?
or, one column and join …
Dossy_, any example ?
do you have a better description of what you're actually trying to accomplish?
ok, i have stored questions with type longtext
now i woiuld liek to stored answers in mysql table
but i dont knwo which type suit my criterias
i want to stored those answers in tables
how do I tell if I have a read tock on tables?
Hi, all, I'm trying to change the default value of a column, and I can't seem to figure out the query. I'm using — alter table prefs alter column theme { set default "clouds" }; — can anyone tell me what's wrong?
I did a read lock to do a dump, then I unlocked tables, but my log position hasn't moved yet.. any idea?
what's the shape of the answer?
shape?
what does an example answer look like?
a this is cat b this is dog c this is mouse d this is flower
you can check to see if a read lock is held by connecting a new session and trying to write? perhaps?
having a "SHOW LOCKS" would be really useful, huh? 
what's with the curly braces?
ALTER TABLE prefs MODIFY COLUMN theme DEFAULT "clouds";
not sure i saw it somewhere
but thanks
0 and subtract an hour from
Dossy_, any idea?
that whole string is the answer? TEXT, maybe.
depends on how long the answers can be.
how about date_sub()?
mmmm
lol
date_sub doesn't seem to like a time in the format 2007-08-25 10:30:00
it works fine if I use CURRENT_TIMESTAMP though
how would I see the server-id on mysql from the command shell?
is it a variable?
hello
how can i change the mysql root user password if i don't know what it is ?
password
rename and recreate the mysql.user table
? password
grep server-id my.cnf
Dossy_, how would i do that ?
http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html
ignore Dossy_ He's just being troublesome.
Dossy_, is there somethign that have (1,65536) where 1 is the a,b,c or d and 65536 is for the possible answers?
Troublesome? It works just fine.
3', interval 30
sure… if you want all your users to be mangled.
just temporary disabled.
3', interval 1
The following procedure is for Windows systems. The procedure for Unix systems is given later in this section. i can't find the one for unix though … :/
why not use two columns?
reset root
!m EvaLuaTe reset root
Sorry - I have no idea what function you're talking about! but try http://dev.mysql.com/reset root
mmmm
I'm sure that's it… thanks
Restart the MySQL server with the special –init-file=~/mysql-init option:
that is such a huge honking security hole. wow.
Dossy_, 2 column? how do u tie up the a,b,c,d and the answers?
on the same row.
i dont undrstand. can u give example ?
create table foo (x int, y varchar(255));
insert into foo (x, y) values (1, 'the lazy brown fox …');
most people find more success using the directions that start at 'Alternatively, on any platform, you can set the new password using the php mysql web hosting client(but this approach is less secure):'
how is that a security hole?
I guess it's of limited importance/risk, since with the off-the-shelf storage engines, all the data is in the clear, anyway.
You'll need to be 'root' or 'mysql' user to do that anyway
to set –init-file.
Dossy_, i think longtexxt suit the best
thanks!
WHERE User='root'' at line 2
and i typed in this command:
UPDATE mysql.user SET Password=PASSWORD('newpwd')
WHERE User='root';
what's that "" doing in there
yeah. don't include the -
well, that was on the site :/
you only type the blue text.
ok sir
EvaLuaTe pls don't update the mysql.user table directly
use the command — SET PASSWORD for user@host = password('newpwd');
well, i already updated ti :/
it*
anyway, my site still doesn't work, i hate this
if you update the mysql.user table you must issue FLUSH PRIVILEGES for the grants table to be re-read into to cache
using SET PASSWORD… will update the cache and the table at the same time.
it's safer and easier.
ok, thanks guys, it seems to work
and, should i be able to connect to 'localhost' if the site is located on the same machine ?
Yes, use "localhost" always in preference to the local host name
Hey guys I was wondering if there is a quick and simple way of creating a copy of one database in another. Not a backup but I have a mydb_dev that I've been messing about with and I want to create a mydb_prod that is an exact copy of mydb_dev.
what's the command to create a new database ? :/
create database database_name
don't forget the semicolon
;
Why not just dump and restore the database? I don't understand why you can't do that.
Can I change the name of the database while doing that?
no need
you simply create a new db, then restore the backup it in the new db.
If you invoke mysqldump on a single database, it does not put the db name in the file, it can be restored on to anything
Mark, I've never actually restored a backup . I only do backups and have the sql files on archive.
Can you guys tell me what the restore command is so I can test it out right now? Thanks.
you can try mysqlimport dbname filename
You can import a dump using mysql client, as described in the manual,
look in the backing up section
Alright thanks guys.
How can I SELECT all fields FROM table foos EXCEPT field bar?
Wow, this is the second time in two days someone has asked that. Why does it matter?
Is column bar really large?
exactly.
the short answer is, list all column names except the columns you want to exclude.
query with bar: 2.8 seconds, query without bar: 0.2 seconds
Index issue? What's the datatype of column bar? BLOB?
TEXT
migrate that column to a separate table and join on a compact key like INT
how would that help?
the query parameter doesn't depend on bar
well, if you wanted an easy way to query excluding that column … just move it out to another table
your first suggestion seems much easier than splitting tables…
it sure is
yeah, thanks for it
but, you already knew it
it would be an interesting sql extension … "EXCEPT"
SELECT * EXCEPT col1, col2 FROM table1 …
It's not really that important
if it really, really bothers you, create a view which lists all the columns except for the blob
Very little is actually important, but lots of things are convenient.
So, what is everyone working on today?
getting vhcs to work … :/
Hey, how would I remove a slave from starting up one restart of mysql? it was a slave now a master, I dont want it to slave, at all
mikefoo, is it shutdown or running?
well I dont have it in my.cnf, but when I restart it, starts slave up
master.info file need ot be deleted?
I dotn want the slave to ever start up..
on restart.. how would I do this?
Anyone?
if the server is running, then you can just run SLAVE STOP; RESET SLAVE;
if the server is shutdown, then you can just remove the *.info and *relay*
so reset slave will make it no startup again, right?
correct, it permanently removes replication
Anyone provide me a little assistance setting up mySql on a non-server, windows OS for local testing purposes?
Anyone? Yes no maybe so?
Just uh, run the exe/msi?
You don't need anyone to hold your hand, unless your a hot chick
you're…
Not a hot chick, but the configuration after I run the .exe isn't able to start the services.
I've uninstalled it, but I'm going back through the "Typical" setup (first time I used custom and was changing some of the settings)
"Could not start the service MySQL. Error: 0"
I think after you uninstall it, the service somehow remains. You have to go into Services and disable it or something. Try that?
what does the mysql error log tell you, exactly?
where is that log located?
in /var/log/mysql?
No such directory
what distro are you using?
Not linux..Windows, a non server-version (XP Pro, to be precise)
oh, I don't know about that OS, sorry
I don't run it
I only need it for local testing (localhost access only)
*shrug*
sorry
np
the mySql services are indeed still in the services tab of msconfig, but disabling them has no effect (and even the first time I tried to start the service, it gave me the same error)
Today is the day we help you learn Linux!
Did you try XAMPP? Or do you just need MySQL?
Disable it and reboot perhaps.
I only need mySql
Alright, I'll brb.
I've read through the docs on how to change the 'import' upload size under php.ini upload_max_filesize, memory_limit and post_max_size but its still showing in phpmyadmin as the default as 16,384KiB any ideas on how to up the limit?
Check MySQL's max packet size
I don't know if phpMA checks that var though
Alrighty, I'm back.
error 0
7Salut à tous!!! :p
if I forgot the Root password for mysql is there a way I can change it?
you can reset it
look it up at mysql.com/doc
ah, late
hello folks
Dont forget the bots!
i'm trying to compare two tables and select all rows from one of them that has a matching field from another, is that possible?
the_wench lol
!m join
join see http://dev.mysql.com/doc/refman/5.0/en/.html
Dossy is that what i want to use?
!m robboplus join
robboplus see http://dev.mysql.com/doc/refman/5.0/en/join.html
botsnack
Dossy thank you.. it's time to learn joining it seems..
yeah, I would say that.
hi. I have a req to do and the order is RAND(). But the result I have, don't seem to be really random, something exist to shuffle more the result of the req ? (I'm not a programmer don't be rought )
hello
hey
having a problem setting up WAMP + MYSQL well not WAMP,
its working + so is php
but mysql wont load
i did install "mysql-essential-4.1.12a-win32.msi. "
and?
let me get my crystal ball
mysqld.exe won't start?
erm..
i got the white + yellow meter in corner
one sec…
oky
btw.. why are you using 4.1.12a ?!
thats wat i read to use on a setup guide
crap guide then i guess
Current GA s 5.0.45
Server 4.1 is at 4.1.22
ive just removed MYSQL from the WAMP menu
what should i do nxt ? :s
4.1.12 was probably current when that guide was written (+2 yrs ago.)
this is why i hate the net, contrast of info
end up with more problems than i started with
get http://dev.mysql.com/downloads/mysql/5.0.html#win32
shall do
learn to go to the canonical source for information
the net is a great resource, IF you use primary sources
shit let me see wat version of php i downloaded + installed
php-5.2.3-Win32.zip
how dated is this ?
php5 stable is 5.2.3
good good
atleast i got something right
gettin new version of mysql
hopefully this works
installing now
Alrighty, I've gotten mySQL 4.1 installed onto Apache in a Windows XP env for local testing purposes ONLY.
PHP MyAdmin is also installed, but it cannot connect to the mySql database hosting (I tried localhost and mysql.localhost as the server addresses but to no help)
Any help?
is php web hosting built with the mysqli extension?
how can I check that?
make a php page with a function phpinfo(); in it.
this will print out all the php modules settings and variables.
phpinfo(); ?
searching for "mysqli" returns nothing
is 'mysql' there?
mysqli is the 'improved' mysql libs.
yes
mysql is there
'mysql' module should show up if you're not using the current incarnation of libs
and it has a section showing the version, and everything.
probably something like 3.23.x
the mysql version is old, I know that…because I couldn't get the most recent version to install correctly
so I installed the 4.1 version instead, it worked find.
fine*
wait a second though, it says API version is 5.0.37
inside the MySQL Command Line Client, the "Server Version" is 4.1.22-community-nt
thats the php client library
that should be ok anyway
Haha, I fixed it.
that API version is backward compatible with 4.1
IDK how I did it exactly, but i went through the reconfiguration for mySQL
and allowed TCP/IP connections on the default port (I had previously unchecked it, since I'm only using it for localhost)
anyone from UK here?
Then went back into the config.inc.php and changed server to localhost and it worked.
I think what you did with that check box was enable/disable –skip-networking
you'd need to use the local socket file to connect if you have done that. That doesn't work in windows though.
Kermy, most probably a lot of people
ive jus been on fasthosts.co.uk
it looks like oneandone.co.uk
any connection between the two?
Kermy, this is #mysql. rings a bell?
yea
still trying to get it to work
cool
5.0 should be installed by now… any problems?
jus had to ininstall 4 lol
1 sec
when i select wamp then MYSQL it gives me the option to install or remove it, and not start :s
jus running the config
i dbout its gonna work ive dont something wrong
and stupid mirc keeps scrolling up ARRR
god
security settings cud not be applied
great cant even finish the auto config
may aswell uninstall WAMP
and start all over
but shud i install the latest MYSQL ? since WAMP installs a version ? :s
—-5-5-5-5-5-5-5-5-5-5-2—2—2—2 —2—9–3—3—3—3————–
———–5-5-5-5-5——-9-9-9—2-
–2—3-3-4—4—bbb1—334-2—2—
oops sorry
\quit
hrm… I'm familiar with WAMP as Windows Apache MySQL + PHP/Perl but I don't know about any installer program that handles loading them all.
does it matter if i use SYSDATE instead of SYSDATE() or are both formats the same?
they're both the same.
thanks
however SYSDATE and NOW aren't the same.
they used to be.
i know
am gonna start over
install WAMP
SYSDATE behaves exactly like it should behave
wamp it up
PHP5 + that mysql latest
eventhough am on vist is this doc, sufficent to set up wamp + latest mysql SUCCESSfully http://www.daholygoat.com/xpguide.html
the problem with wamp is the w.
have you tried XAMPP ??
nope
on my pc i have outdated wamp but i set it up along time ago ok
looking at WAMP it installs MySQL 5.0.41 and PHP 5.2.3 not sure how you ended up with 4.1
just for testing php + stuff not for people to access
oh god wat an idiot ive installed an older version over it
so no need to install php5 to cphp
just use wats already set with latest wamp ?
XAMPP is good, it has an installer and will take care of all you hassels
wats the difference ? from wamp ?
used wamp in past so once its working id feel more comfortable using this
gotta look into setting up secure payment for a friend when this is done
not lookin forward to it since i am not qualified for it,
XAMPP provides many more components much more complex too.
starting over, driving me mad
uninstall wamp
anyone running mysql cluster on amazon ec2? anyone know of an ec2/s3/sqs (amazon web services) irc channel?
cluster = NDB ?
100000 GROUP BY region
Hi
ndb = the in memory newness that came out in 5.x
Hvae a little question
you want HAVING instead of WHERE
oh!!!
syntax error HAVING
normally insall wamp to cwamp this recommended?
code town_name last_date time
JOIn table
yes, It's the in memory (been around since before 4.1) hotness.
and I need to get for every town last date and time
I tried some slections but I couldn't get it
TodoInTX do u know an easy to follow *upto date site talking me through the complete wamp setup, passwords etc..
oh… well … ya , that… im looking into using amazons ec2 for some new projects and wanted to get some feedback if anyone has exp with that setup
HAVING is a group by modifyer.
http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html
I only succed to get only for every town the last date
10000
oh NOoooooooooo
just reinstalled wamp and the mysql still isnt loading, still half yellow meter
please stop saying wamp
it sounds stupid.
:o
WAMP is a installer script too
lol
People, could somebody help me?
that doesn't make it sound any better
ask and someone can try IcoBgr
but ok
i'll zip my lip then
no idea about this "yellow meter" either.
help you how?
I asked
maybe it's something with Apache?
1000000
am i right?
wat should the path in Envionment Values be set to?
give some example data that you're talking about
Can I copied here?
5 rows
code town_name last_date time
1 Lom 2007.08.24 17:01:34
3 Lom 2007.08.24 20:55:0
4 Lom 2007.08.25 08:02:05
5 Lom 2007.08.25 15:22:44
2 Kutlovica 2007.08.24 18:34:08
that seems to work thanks.. but why can't i use totalpopulation instead of sum(population) after HAVING ??
TIAS
The result wich I search are :
5 Lom 2007.08.25 15:22:44
2 Kutlovica 2007.08.24 18:34:08
The first column is not important
I forget why exactly.
This is JOIN tables
in future, use something like pastebin.com for multi-line pastes, thanks
let's say i want to sort the results by population
In 5.0, if I execute a stored procedure on the master, does the stored procedure call itself get replicated, or does the SQL output of the stored procedure get replicated?
hmm, you want MAX(last_date) …
1000000 ORDER BY totalpopulation …
what should i do?
depends….
grouped by town_name
depends on how?
1000000 ORDER BY sum(population) DESC;
that's weird
I tried this, but I didn't get the last time for the date
right, there's a reason for that, I can't remember why.
basically any aggregate function's results cannot be aliased in the GROUP, HAVING, ORDER clauses.
ic..
ok.. just to make sure i'm using the correct convention or practise to do this query
can you show us the actual query you're trying to use?
http://pastebin.com/d3233c169
http://pastebin.com/d66e94c13
I do not get the last time for the day
pls can someone help…. am tryin to remove the latest version php from c and reset my Environment Variables Path … ?
Are you there?
any ideas?
MAX(Times.Time) as last_time
Hi
you might be better served asking that question in #php
already there
searchin for default path now
cos i reinstall WAMP + mysql still didnt work :s
This will not retur that what I want
This returns the max time for all days
not last time for MAX(Date)
someone on Vista, able to check a "Environment Variables" "Path" for me pls
yeah, your schema is a little … strange
oh, oh, try–
Hello All
Can anyone point me to some source of information on the performance hits/benefits (if any) of using foreign keys?
What is strange?
why do you have these "Dates" and "Times" tables?
n Dates table I get all days for which I have queries
In Times table I have town, date and time for the query
seems like a really poor schema design, unless this is read-optimized for data warehousing
anyhow, try the query I just sent to pastebin
This returned 3 rows
what were they?
how large are these tables right now? are you still in development
hi, does it makes any differenzes if i change Kollation from latin1_swedish_ci to latin1_german_ci ?
yes, presumably the collation order is different
when i want to store german text it's better to set it to german or ?
a collation does not affect storage,
only index creation
so you can change it easily from latin1_swedish_ci to latin1_german1_ci
could someone with vista help me
this are all data http://pastebin.com/d398774f4
From this data I neede for Lom and Kutlovica last date and time
try adding "HAVING last_date = MAX(Dates.date)" after the GROUP BY clause
that may not do what I'm hoping, but it's worth a try
if you can give me a mysqldump of those 3 tables (Times, Dates, Gradove) I'll test the query on my side
someone must be on vista
not me
people seriously use Vista?
yea sadly
jus cos its easier to use than uninstall on laptop
heh, on my winxp laptop, I run linux under VMware on it
this way all the oddball laptop hardware has device drivers, but I get to do my dev work in Linux
a mate is willing to pay for secure payment online, anywhere u can recommend?
paypal?
there using it atm
he wants 1 on his site
i can do him a database with availablitity
but he wants payment
sorry, I'm not understanding what you're getting at.
wats required is:
database for hotel availability, he is using paypal for payment but he wants a secure CC payment on his site
Hi! Anyone knows some documentation or paper about optimizing a keyword based search?
fulltext search?
oh
We have a large image database, and images have keywords…
fulltext?
I have tried fulltext on a coma-separated list of keywords
but is reeeally slow…
http://dev.mysql.com/doc/en/Fulltext_Search.html
works fine here
(even if relevance doesn't 
)
select *, MATCH(`sky`) AGAINST ('cielo' IN BOOLEAN MODE) from imgs_keysastext WHERE MATCH(`sky`) AGAINST ('cielo' IN BOOLEAN MODE)0 ORDER BY MATCH(`keys`) AGAINST ('sky' IN BOOLEAN MODE) desc
is the dump good?
takes 3 minutes…
mysql.com internasly using mysql fulltext?
yeah… but I have 750K rows…
maybe is that… 
yes i'm using the internally fulltext search
The explains tell me that is using the fulltext index…
hm
you shouldn't work with select * imo :-p
hm, maybe all indexes must be in memory. … it's possible get fulltext indexes into memory
?
what do you mean with "must be in memory"?
preloading? caching?
yes
How could I check that? probably it isn't…
maybe this is problem with 750k rows
select img, MATCH(`keys`) takes also long… the * doesn't seem to be the problem…
i have experiences only with classical indexes
the index is just being rebuilt on INSERT or UPDATE as far as I know
If the keywords were no so many, I thought on the posibility of using a SET() value
so caching wouldn't help I think
but is not practical…
yes, but can by casched in any buffer
Are you there?
and adding a new keyword kills the db while rebuilding indexes…
yes, maybe try query cache?
so when searching for the same tag, the cache will be used
Yeah, of course I'm using that
caching is helping in thypical cases of myisam, innodb, integer, char indexes, why not with fulltext?
but the problem is that is a website for searching images
and still that slow?
not query cache
and if the first search takes so long…
i mean chace for indexes
how you do that?
if you have indexes and first search takes too long, it's mean index is moving into memory first
ups, cache, not chace
I have been trying a img-key table, with numerical ids (is faster than using text, the relationship table has 20 million rows)
is the second search faster then the first one?
but the problem is that I have to calculate the image score on code…
So it's faster, but becomes too slow (30-50 secs) with widely used keywords…
yes.. integer should be better then text, blob etc
sure… if I repeat the same search, is cached on mysql…
So I'm on a dead end…
but if similar search?
if similar search and if is also faster, it's about key cache, not query cache 
I'm using prepared queries all the time, but it doesn't help
i also read on dev.mysql.com about prepared queries, and also doe's not see satisfactions!
If I change the keyword it takes again a lot of time…
did you try sphinx or something similiar?
aha, it's only query cache probably
try mysqlshow variables like 'key_buffer_size';
the table with 20M rows + numerical indexes is bloody fast
because mysql fulltextsearch is always slow :p
but I must calculate scores by hand on code…
sphinx?
key_buffer_size | 50331648
I have bumped it up
i 'm planing also use for tests sphinx or lucene…but somebody must install it
sphinx?
hummm
c++ compiler must be available
and show table status; and size of indexes in your table?
7 | NULL | latin1_swedish_ci | NULL | NULL |
ok, not the greatest query (reorganizing the tables would help) but it does what you want — http://pastebin.com/d6bb9b754
now, I'm going to step away for a bit–bbl
you need key_buffer_size = 300MB , not 48MB, look at config file and try it
http://phpfi.com/258736 - how to optimize this query?
Do you know any way of adding scores of temporary tables??? If I manage to use the int-int index table to search for images and add scores without retrieving all the rows, It will be fast…
ID; unknown data: lft, rgt
The problem right now with the int-int table is to retrieve 40K rows and add the scores of several keyword matches…
I'm going to set that key_buffer_size and try again…
but try several times
can someone help me with optimizing?
Whoa!
yees, put off subselects
Seems to be reasonably fast now with that key cache
hehe
can I do everything in one query or must I get "lft" and "rgt" first to PHP?
i think can by all in one sql
so you just changed the buffer size ?
but how to do it without subselects?
should i store PRICE and AMOUNT fields in DECIMAL or float?
or DECIMAL(10,2)?
hm, i think can by, but i don't know …yet
….
1 key is fast… 2 secs, 2 keys ~6 secs, 3keys ~13 secs, 4 keys 20 secs…
dammmm
4gb, you should use the latest version of mysql
where is a good site where i can search on more help on SQL queries?
we have key_buffer 1GB without problems
as far as I can remember there was a bug in earlier versions. at the moments its a limitation I think
dev.mysql.com, google
yes, it is documented
But the int-int approach is now 9 secs for 40K images!
It seems that the problem was the search indexes, not retrieving & processing the images scores…
thanks
)) I'm a happy man now
gratulations
thanks to you
Now I need more memory at the server…
oh, I forgot the con*, hehe :p
i can tell result's of changes after hundreds, thousands of queries on both configurations, not 10 make a program for your queries and try it
:-D
when i create a file for creating a table, is there a differenze between CREATE TABLE `name` or CREATE TABLE 'name' (` or ')
Anyway, you know how to sum up images scores of records from several tables?
hehe, yes, memory is cheaper problem for us
I would want to try the temporary table approach
phpMyAdmin does show me the commands with an `
sum() ?
Yeah, but the images doesn't need to appear on all temporary tables, thats the problem…
or…
what will run is GOOD!
you can add rows to a temporary table using insert … select …?
yes
so you mean there is no real differenze
is difference in php you will see
!tell rocketmagnet about quotes
rocketmagnet Use ` around identifiers (database/table/column/alias names) and ' around strings and dates. MySQL does allow " for strings, but ANSI standard uses " for identifiers (which you can enable with ANSI QUOTES option) and dont quote numbers
ty!!
!tell me about sphinx
me
:-)
in php “ is for executing system commands if i remember correctly
thanks wench
It's been a pleasure serving you, threnody.
hehe
!tell roman3x about sphinx
roman3x
thanks threnody
:-) competition
!tell roman3x about fulltext
roman3x
can i make comments inside a .sql file ?
yes
use /* fdhft */
ty
ah i see - i love vim
anyone has made experiences with mysql fulltext search and relevance? the field contains "message 1", when searching for "message" results in a relevance about 64%. field contains "computer", searching for "message" brings up a relevance of 100%
-when :p
I'm reading about temporary tables… Then I could do something like:
CREATE TEMPORARY TABLE search SELECT 1.5 as score, etc images for the first keyword
And SELECT INTO TEMPORARY TABLE search …. for the second score?
I don't unsertand very well the temporary tables…
hm, sphinx have API for Ruby and not for PHP
http://www.ibm.com/developerworks/opensource/library/os-php-sphinxsearch/
Sphinx looks really nice
# apt-cache search Sphinx
libsphinx2-dev - speech recognition library - development kit
libsphinx2g0 - speech recognition library
sphinx2-bin - speech recognition utilities
I think it's not the same XDDD
hehehe
thanks
no problem
how much should a mysql idleing doing nothing cost ?
ram wise
it depends on many factors.
!man tuning
see http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html
i'm going mainteance operations in my head fro next ~ 8 hours
for
rebuild indexes or for what???
why head or body need sleep?
update .. set ..=NOW()
update .. set ..=".date(…)
do you think there's any speed difference?
installing mysql onto debian
default
w/e
idleing not using a thing
The first doen't have to analyze the time format, so probably is faster…
more people is here - do you know how to optimize it - http://phpfi.com/258736 ?? We don't know LFT and RGT parameters.
But on several websites I have content that should be activated on certain time
both seem to be equal (14/13ms)
On that cases is much much better to do queries like select * from articles where publish_date$date…
And make $date contain only hours… that way the query will be totally cached for all queries during 1 hour…
how much should i set mysql to cache ?
You can reduce it to 5 minutes or what you need, but definitely faster for high volume websites
for a low to medium traffic site ?
it depends.
right now mysql is idleing and using 120 mb
ok.
do you want to increase that amount? reduce it?
are you running out of memory? are you using swap?
Mine is now using 500 Mb… XD
im saying can i set the mysql cache to like 32 mb ?
i mean shit i am only going ot use 1 table and it will be used only on orders
test it
tweak mysql down, and see if the memory usage grows as you run simultaneous queries
then find a reasonable value.
its a small little query
i mean
would 15 mb be ebough ? lol
it depends
there is no magic bullet.
you need to perform tests
anyone knows how to change date format in access???
access as in Microsoft Access?
on averag though a few small queries takes what ?
it depends
there is no magic bullet
thumbs yeah
I need to do a query on listing on a certian date
I have no idea. I don't use that OS.
SELECT VALUE FROM X WHERE DATE = '2007-12-29'
but in access it is in 12/29/2007
ok.
whichever way, it doesn't hit and comes out an critical error
date comparisons should work in a sane dbengine
DATE type mismatch in critical expression
perhaps you could ask in #microsoft or #access or #msaccess?
ok
but let's say
where is the config file file that reserved 120 mb etc
if u do a normal query like what i did,
on a normal sql database
is it correct format?
my.conf
yes, it would work on a sane db engine, as I pointed out earlier
where is it on the system usually ?
in /etc?
perhaps you could consult a basic linux guide too.
It would tell you where config files are usually located, amongst other things
Well, this certainly is cool.
Hi, I'm looking for something like phpmyadmin, except not a web application… just a regular app for linux, preferably GTK. It's a bit tedious to start apache everytime I need to do some dbing you see
I see one of my users did a DELETE * FROM mysql WHERE User="root";
anyone have any ideas?
Under –skip-grant-tables, it doesn't appear that I can add a new root user.
What are my options at this stage? I'm kinda stumped.
can't you use the mysql console?
and it succeeded?
It did.
reset root
See http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html
i really need help on querying on date
sigh
in access?
Er, so it's not as simple as adding a new root account eh?
There is no root *account* anymore, that's the issue at hand I suppose.
try = , if you have time components stored
or =
well, you could recreate the initial tables, and restore the rest
nm, I see the instructions there.
I'm such a noob… I'm from the windows generation, I like it graphical
I could. Damnit.
I promise that I am going to take time to learn to use it, though
gui
just, not now
GUI tools can be found at http://dev.mysql.com/downloads/gui-tools/5.0.html phpMyAdmin at http://www.phpmyadmin.net/ and even navicat or http://www.webyog.com/en/
read those
i'll check that out, thanks
Well. F. Going to have to reinit the privilege tables;
That sucks.
Does MySQL5 support multiple unbuffered queries? I know MySQL4 doesn't support it but how is it with 5?
hi… i don't have time component.. in my date column, i keyed in 1998-07-23 , which access automatically change to 7/23/1998
ok.
i not sure how i should do my query on date
but I know nothing about access.
this is #mysql
how should the standard query be like?
ok one more time
WHERE `datefield` = '2007-08-24'
how much does your guys idle mysql take up ?
or and date format
mysql will convert it automatically.
it dependws
SELECT name FROM x WHERE date = '1998-07-23' ? ok
sure.
Guys I've got a server I'm trying to install MySQL 4 on top of 5. Both servers are running but only one is listening on an IP.
skip-networking is not enabled on the mysql 4 …
Did you specify different socket files?
yep.
netstat shows both sock files … but only one listening IP.
and you've skip-networking on one but not the other?
What do you expect to happen?
skip-networking is disabled on both.
Did you specify different ports?
mysql 4 is set to listen on port 4444
If you disable mysql 5 for a moment, and start up 4 - does it go back to 3306 or 4444?
Don't try if mysql 5 is being used… :s
new server it's ok. One moment.
still not listening on an IP.
www.pastebin.com your my.cnf
on it…
do you guys know of a really fast full text engine that works with mysql?
MyISAM?
fulltext myisam will _not_ work for my needs
it's too slow
sphinx?
pretty standard … just a basic copy of my-medium.cnf
well, it's pretty much what you've got for MySQL, are you sure your queries are doing everything 100% a.o.k?
yeah, i've done 1 table search, i partitioned it across multiple tables, etc.. unfortunately it just won't work … are there oss options for external full text engines?
hmmm, are there any command line options? Are you 100% certain the database is starting up and is able to run queries etc?
well if I change to /usr/local/mysql/ and run bin/mysql -u root -p I can connect to the 4 server
hmmm, this is weird
not looked into it, sorry.
np
!man multiple servers
see http://dev.mysql.com/doc/refman/5.0/en/multiple-servers.html
I/whois icobgr
Is there an SQL operation to get the difference ( in minutes ) between 2 TIME fields?
oh wow it's called TIMEDIFF()
good to know
does anyone knows how to do a query on DATE on a msaccess 2003?
Heeeeloo #mysql. Query: UPDATE cats a, cats b SET a.position = b.position, b.position = a.position WHERE a.id = 1 AND a.position b.position limit 1
Incorrect usage of UPDATE and LIMIT
en?
em?
You're in #mysql
UPDATE `cats` SET …
UPDATE [your tables go here, not fields]
cats a, cats b
For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used.
(
hello please can someone tell me how to give a varchar field the default value of a blank string?
nm go it
got it*
ALTER TABLE `table` CHANGE `field` `field` VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT ''
So if I'm changing the character set of a table, I still need to CONVERT() the data contained within the tables don't I?
i've `created_at` column and `user_id` i want to get last created_at date which should be max(created_at) for this specific user and match it againest week( now(), 5)
select max(created_at) from properties where week(created_at,5) != week(n
ow(), 5);
sorry
but this return the max(created_at) from last week which is out of this week
oDesk, what does " which is out of this week" mean ?
i figured it out,, so rather i do != i should do = so it returns empty or with rows
and i removed max()
Win XP and MySQL 5.1. I'm getting Error 1568 — Cannot proceed because the system tables used by Event Scheduler were found damaged at server start — any ideas? Can I fix that one table? I never use event scheduling, but Navicat won't open without scanning that table first
hey, is there any way to get a given row's rank in an "order by" query?
fclub, you mean the order in which the records appear? starting from 1?
i can order a table's row by a column fine
but i want to know the number of the a given row in that order
number as in the position of a row in the sorted order
fclub, SET @x:=0; SELECT @x:=(@x+1) as rank FROM table ORDER BY ..
what's the best way to move a HUGE innodb database to a new server?
best way? i don't know. exporting as individual .sql files for each table would probably work well
SHut old server off, scp the DB directory to new server, turn on, run mysqlcheck.
with innodb i don't think that will work
the only lead i have, other than mysqldump, is ibbackup
No? With the source server turned off it *should* work, shouldn't it?
the source server is a development server, so shutting it down isn't a problem.
Oh, set the new server up as slave and make the db replicate to it; I'm pretty sure that's doable (though I don't know how)
cafuego i remember having tons of problems trying to do that with innodb, something about how it writes the transaction log and the particular settings in my.cnf, something seemed to vary server-to-server enough to break it after the transfer
i suppose it's worth a try though
Ah ok, ouch.
mysqldump seems the most reliable option…
that would be many GB in this case, though
Yes, mysqldump will work; it's not quick though.
i'd like to find a better option
try the 'load data from master' method kris mentioned
sounds like no matter what you will be waiting some time for the data to transfer though
how big is the db?
my sign in script was working fine until I changed it a bit, but I can't figure out why it isn't working - any advice? http://pastebin.ca/670724
swimrr, when you use a table alias you need to use that alias to prefix fields with
Bah.
Is there any type of 'repair' that can be done on Innodb ib* files?
for every field?
dan, not that I know of. when i've messed up ib* files i've had to start from scratch haha
two tables have 11M records.
They're there
just corrupt as all fsck
did you change the innodb settings in my.cnf?
last i tried, it doesn't work too well after the ib* files have been created
Nope.
swimrr yes, it's a very good practice.. you don't absolutely need to unless there are fields with the same name in more than one table
Although I know what to, and not to change, per my.cnf when running innodb heh
Lesson learned many years ago, unfortunatey.
Unfortunately, too!
that's what I thought, because on my previous sites I haven't needed to
if only one field needs it you should do it on all the fields?
swimrr yes. anytime you use a table alias you should do it. when you post your sql in here we don't know the structure of those tables or whether there are duplicate field names or what fields you are selecting from what table
for debugging sql it's so much easier
thanks for the tip
does it make sense from the code I gave you that is the problem?
swimrr, anyway, it looks like the inner join may be your problem. if there isn't a corresponding row in userimages you will get an empty result set. use a LEFT JOIN if there "may or may not be" a corresponding userimages record
I'm confused because i'm not getting any error messages, just a blank page
aha….thats it
thanks
0 InnoDB: Page dump in ascii and hex (16384
See, that's real cool.
I'm not really sure on how inner, left and right vary
Awesome.
swimrr you're welcome. inner join requires there to be a row in the table you're joining in order to return a result set
trix, what is the diff between left and right? just where it adds it?
swimrr, i'm not too sure actually, i'm so set in my ways i only use left joins and inner joins
left join, all the rows from the first named table, any of the rows that match from the second named table.
thank you
trix, thanks again for your help…that inner join was the problem
you're welcome
save yourself some headache later on though, and prefix those fields with the aliases. maybe later down the road you'll add a field in table #2 that's already in table #1.. and then your query would stop working
good advice, thanks
np
i am creating a new table which has 3 columns, two usernames (VARCHAR) and a timestamp
(INT)
and i am wondering how i should set my indexes…
each pair of users will be unique, but there might be repeats in each column
What sort of queries will you be running on the table?
there might be occationally a query requesting a pair of users to see if it exists, but typically my queries will be more like:
You probably want PRIMARY KEY (user1, user2), at least (if MyISAM; use AUTOINCREMENT instead for InnoDB, for preference, and have that UNIQUE KEY).
SELECT * FROM thistable WHERE user1='someone' OR user2='someone'
i am using myISAM
so a primary key on the pair?
Hmm.
What kind of data does the table store? I.e., what's the implied relationship?
what about an index on each column separatly?
Slight simplification: WHERE 'someone' IN (user1, user2)
yep, thats exactly it, its a table of relationships between users, a friends table
Xgc, ok thanks, does that run faster?
Probably not.
ok, well its neat to learn that method too
Not likely. But you really don't want to duplicate the 'username' in the SQL if not necessary.
SELECT * FROM thistable WHERE user1='someone' UNION SELECT * FROM thistable WHERE user2='someone' might, though. Do EXPLAINs for all three once you have the keys set up.
PRIMARY KEY (user1, user2), KEY (user2).
but anyways, since 99% of my queries will be on each column individually, might just a normal index on each column be good?…
(user1, user2) can be used the same as (user1), so that's basically what you have.
oh ok
and it'll handle 3 cases, not just 2.
Why would you want a mixed-up list of the people the user was chosen as a friend by and the people the user has chosen as friends?
so, … PRIMARY KEY (user1, user2) will speed up requests for the pair or for user1?
Is the operation intended to be symmetric? I.e., is my making you my friend the same as you making me your friend?
_Quintus_, it will work for either.
and then adding KEY(user2) adds an index on the second user?…
_Quintus_, yes.
Simetrical, yes, i am your friend = you are my friend, in this case but there is another table for handling how both people agree on that….
With "REPEATABLE READ" isolcation what happens if I insert rows with conflicting keys in diffrent transactions?
I feel as though there should be a better way of expressing a symmetric relationship. But I can't think what.
ok, so, to clarify, i want a primary key on the pair of users and just a normal index on the second users, and that will speed up both types of queries…
_Quintus_, yes. It will also ensure the uniqueness of pairs.
one of them will encounter a commit conflect
if [x,y] implies [y,x], then always insert [a,b] such that ab. That'll eliminate having to worry about duplicates.
It will not, however, ensure that you don't have a pair of entries like ('user1', 'user2') and ('user2', 'user1').
Simetrical, ok, thanks a bunch for your help…
That will happen at commit time?
Right, what Xgc says is a good idea.
ok
You can force that with a trigger.
In 5.0+, that is.
Xgc, im not sure i understand that
its a little more complicated, because inserting to this table is dependent on another table
A trigger can take any insert [a,b] and swap them such that user1 is guaranteed user2.
but yes, i will check that the pair does not exist in this table before setting up the other table in such a way that it might lead to a duplicate in this table…
swap, if necessary, that is.
Xgc, ok, is that to help with speed?
_Quintus_, the idea is that if Alice and Bob are friends, store them in the order 'Alice', 'Bob' (alphabetically) instead of 'Bob', 'Alice'.
No, it won't help with speed, just with validity (uniqueness).
Xgc, why do i want user1 user2? I assume that refers to alphabetical order?….
That way you won't have both 'Alice','Bob' and 'Bob','Alice' ever stored separately.
So that there's no change of both ['bob','alice'] and ['alice','bob'] being in the table.
ok, well, that sounds like a good idea, but i need to make another query in an entirely different script which will end up taking care of uniqueness anyways
and i would like to have user1 be the initiator
No. Don't force that.
not as a total requirement of the table, but it might help in the future
It should matter who initiates. you always want ['bob', 'alice'] … user1 = 'bob'.
Sorry. I meant Alice.
Assuming you want user1 to be user2.
ok… why is that exactly…
yeah, but i dont want user1 user2
that doesn't matter to me
It doesn't make a big difference.
To prevent unique but logical duplicates.
Xgc, ohhhh
He'll handle that at the scripting level. Should work okay in practice.
Although it's purer to do it with triggers or whatever.
Xgc, ok, that makes a lot of sense, i am still probably going to do it at the scripting level as simetrical says, but not i understand why user1 user2 would be good
Xgc, they could be unique but equivilant, that makes sense
Xgc + Simetrical, well, thanks a ton for your help on this!
hey
how would i select all rows with comment_stamp within the last 2 weeks?
comment_stamp is TIMESTAMP
anyone?
!man interval
see http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html
thx
hmm thats not right
lol yeah doesnt look like it
!man date and time
see http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
ty
this doesnt seem to be working, i get 'unknown error' :-/
SELECT photo.*,AVG(2*photo_comment.comment_rating) AS photo_rating FROM photo,photo_comment WHERE DATEDIFF(NOW(), photo_comment.comment_stamp) 14
You need a GROUP BY clause.
hm?
I think you do, anyway.
ah got it
Not to mention, you have no join clause, so you're doing a Cartesian product.
yeah i figured it out
just needed to specifiy which comments i wanted
all right.. anyone know how to select the most popular content from the database, based on number of comments (different table…) and date… :-/
What's the database schema?
InnoDB
woah shit brb'
InnoDB is the storage engine. The database schema is a list of what tables there are and what columns are in each table.
The query you want will vary depending on how the tables are laid out.
sry :-/ thought my sump pump was failing..
basically, photo table has id, timestamp, and photo_comment has rating and timestamp
and id of course
ideally it would be based on rating and # of comments, but just high ratings would be good too i guess
can you left join on a select statement?
