Hello, I am relatively new to MYSQL and have had an issue that has been bugging me for a while. I've tried googling all over the place for the answer, but have unable to find an acceptable solution as of yet. Here is the query I am running currently to find the best possible match for a given search term: $query="SELECT * from `vocabu...
I have a mysql table called RATING with this structure: ID, USERNAME, RATING, RATER, MONTH I want to be able to show a league table of users ordered by their average rating for the current month. For example: 1, Bob, 10, Rita, JUL 2, Bob, 8, Sue, JUL 3, Rita, 9, Bob, JUL 4, Sue, 4, Rita, JUL 5, Rita, 10, Sue, JUL I want a SQL qu...
I have a search box that can contain multiple values using a comma, eg Pasta, tuna, eggs Im using FULLTEXT mysql search but I need to use some kind of preg_replace to turn Pasta, tuna, eggs into 'Pasta','tuna','eggs' If I enter this 'Pasta','tuna','eggs' into the search box the results are correct. Thanks people ...
Hey! I am trying to find the fastest way to insert data into a table (data from a select) I always clear the table: TRUNCATE TABLE table; Then I do this to insert the data: INSERT INTO table(id,total) (SELECT id, COUNT(id) AS Total FROM table2 GROUP BY id); Someone told me I shouldn't do this. He said this would be much faster: C...
User has pets, pets may have pictures One picture may be set as mainpicture (mainpic=0 or mainpic=1) I need to get all the pet data from the pets table and I also need the picture id where mainpic=1 or first picture or null if he has no picture in the picture table. I need to get the pet data even if it has no picture at all. Currently...
Hi I'm trying to use c++ in cgi programs and everything is ok if i don't use mysql. Without mysql my site is generated in about 1-2 ms, but if I add only one variable or function from mysql++ library, site is generated in ~35 ms! (with php it's about 15 - 25 ms all time) I suppose it's a problem with dll loading ( there are two, and mys...
Say I have multiple servers in multiple locations and I want to use MySQL's datetime type for the field date and I always want to have the field date have the UTC timestamp so I would execute a UTC_TIMESTAMP() when I add it to the database. Now say I want to have MySQL output the UNIX TIMESTAMP for it. When I do this on Server A I get t...
My SQL query selects some columns from from a view with an optional sort field. Basically, my view concatenates a number of fields into a single address string, so that I end up with something like 123 Sesame Street Birdtown in the address column. I want the search to be case-insensitive (it isn't by default), so I tried this: SELE...
Is there a Java equivalent to PHP's mysql_real_escape_string() ? This is to escape SQL injection attempts before passing them to Statement.execute(). I know I can use PreparedStatement instead, but let's assume these are one shot statements so preparing them will result in lower performance. I've already changed the code to use Prepare...
Hello Can anyone tell me which is he better appraoch in MySQL whin it comes to finding the row count of a table: is it better to do SELECT COUNT(*) FROM TABLE_NAME or to look up the row count in the table TABLE in the INFORMATION_SCHEMA? This would be a frequent operation for calculating pagination counts for pages? I should add t...
ok here is my php and mysql code: where it is bold i wanted to the the uid from the online table and if it in there where online.uid = '' i needed so it put the uid in there. $sql = ("select accounts.id, accounts.tgid, accounts.lastactivity, cometchat_status.message, ...
I have one table spread across two servers running MySql 4. I need to merge these into one server for our test environment. These tables literally have millions of records each, and the reason they are on two servers is because of how huge they are. Any altering and paging of the tables will give us too huge of a performance hit. Becau...
For simplicity, assume all relevant fields are NOT NULL. You can do: SELECT table1.this, table2.that, table2.somethingelse FROM table1, table2 WHERE table1.foreignkey = table2.primarykey AND (some other conditions) Or else: SELECT table1.this, table2.that, table2.somethingelse FROM table1 INNER JOIN table2 ...
I have tabulated data in an Excel spreadsheet (file size will likely never be larger than 1 mb). I want to use PHP to parse the data and insert in to a MySQL database. Is there any advantage to keeping the file as an .xls/.xlsx and parsing it using a PHP Excel Parsing Library? If so, what are some good libraries to use? Obviuously,...
I have a table with various VARCHAR fields in MySQL. I would like to insert some user data from a form via PHP. Obviously if I know the field lengths in PHP, I can limit the data length there with substr(). But that sort of violates DRY (field length stored in MySQL and as a constant in my PHP script). Is there a way for me to configure ...
is there a way to use PHP+PDO to query a MySQL database and find out the column widths of some VARCHAR fields? I don't want to hardcode them into my PHP file if possible. (purpose: determine maximum lengths of strings to be either inserted into these columns, or compared against existing data in the columns.) ...
I'm runnning MySQL 5.0.45/ PHP Version 5.2.6 / CentOS with Plesk. I get occasionaly a burst of "Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug" Global memory is not the issue since I have about 620M free. Anyone know what could be the cause for t...
Hi folks, I has a fairly simple site getting 200K hits/day (3-5 hits/sec) using a LAMP configuration. The host is somewhat flaky (read: cheap), so we want to add a 2nd host for guaranteed uptime (I wouldn't call this mission critical, but more annoyance-prevention). MySQL is idling at about 200 qps, spiking for 450 at times. I have s...
Flickr's Cal Henderson gave the keynote address for DjangoCon 2008. He briefly touched on the Django framework's use of table joins in queries, asserting, "you don't use joins when you get to a certain scale." I expect that Henderson knows this stuff to the bone, but does anyone know what the likely reasoning for his claim is? ...
I have an issue in mysql that i have a field id which is auto increment and some other fields. where the id field should not be autoincremented while enterting the null values and should be autoincremented while entering values insert values in the same row while giving not null values . ...