Matrixkid

LOL ... yeah I guess that works. ....its going to be a long day

How would I go about executing multiple statements then? I basically have a form that has X amount of radio button groups..so its dynamic I was then doing this: foreach($results as $key => $value){ $query .= "UPDATE matchup SET winner = '".$value."' WHERE id = '$key' LIMIT 1; "; } and then just execute the $query Thanks for the help!

Hi there, I currently having an issue with certain update statements and cannot figure it out. Here are the statements: UPDATE matchups SET winner = "Erik Seidel" WHERE id = '8' LIMIT 1; UPDATE matchups SET winner = "Daniel Negreanu" WHERE id = '9' LIMIT 1; UPDATE matchups SET winner = "Jennifer Tilly" WHERE id = '10' LIMIT 1; You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE matchups SET winner = 'Daniel Negreanu' WHERE id = '9' LIMIT 1; UPDAT' at line 2 Here is the table structure: CREATE TABLE `matchups` ( `id` int(11) NOT NULL auto_increment, `event` int(11) NOT NULL, `comp1` varchar(255) collate latin1_german2_ci NOT NULL, `comp2` varchar(255) collate latin1_german2_ci NOT NULL, `winner` varchar(255) collate latin1_german2_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=11 INSERT INTO `matchups` VALUES (10, 7, 'Tom Dwan', 'Jennifer Tilly', ''); INSERT INTO `matchups` VALUES (8, 7, 'Erik Seidel', 'Ram Vaswani', ''); INSERT INTO `matchups` VALUES (9, 7, 'Amnon Filipe', 'Daniel Negreanu', '');; In phpmyadmin, If I take one of the UPDATE statements and throw explain in front of it I get an error. If I copy and paste it into phpmyadmin all the UPDATE statements, it works. Your SQL query has been executed successfully SQL query: UPDATE matchups SET winner = "Daniel Negreanu" WHERE id = '9' LIMIT 1 ;# Affected rows:1 UPDATE matchups SET winner = "Jennifer Tilly" WHERE id = '10' LIMIT 1 ;# Affected rows:1 If I run the query through php it returns an error as well. $query .= "UPDATE matchups SET winner = '".$value."' WHERE (id = $key) LIMIT 1;"; $result = mysql_query($query) or die(mysql_error()); You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE matchups SET winner = 'Daniel Negreanu' WHERE id = '9' LIMIT 1; UPDAT' at line 2 So to summarize: 1) If I copy/paste all the UPDATE statements into phpmyadmin it works 2) If I take 1 of the UPDATE statements and put explain in front of it, it doesnt work 3) Neither 1 or All of the update statements work in php any help would be appreciated. this is starting to drive me crazy. Cheers

Can you further elaborate on that? Thanks!

Hi there, Im looking at optimizing some queries I have going simply because they take way too much time the first time I run them, and I was looking to drop the query times. I have a table that I need to constantly update, every 2 hours the cron runs, with information from another table. I'll layout the tables then explain what I am doing. Table: tblFirst ID | firstname 1 JOE 2 BRETT 3 BOB 4 MIKE Table: tblInput ID firstname Date(timestamp) FirstnameID(default of 0) 1 MIKE 0000 0001 4 2 BOB 0001 0002 3 3 JOE 0001 0003 1 4 BRETT 0004 0002 2 5 CRAIG 0009 0001 0 6 CRAIG 2001 2323 0 So, this is what I am want: I need to take all the firstnames from "tblInput" and throw them into "tblFirst" if they do not exist already. Secondly, I need to go back into "tblInput" once I have updated "tblFirst" and update the FirstnameID field with the matching ID from "tblFirst" Here is what I am using right now: $table = "tblInput"; $query = mysql_query("SELECT ltrim(firstname) FROM $table WHERE FirstNameID = '0' group by ltrim(firstname) ORDER BY ltrim(firstname) ASC "); $num_rows = mysql_num_rows($query); if ($num_rows > 0) { while($row = mysql_fetch_array($query)) { $fname = strtoupper($row['ltrim(firstname)']); $query2 = mysql_query("INSERT INTO tblFirst (firstname) SELECT '$fname' FROM DUAL WHERE NOT EXISTS(SELECT firstname FROM tblFirst WHERE firstname = '$fname' LIMIT 1)") or die('Invalid query: ' . mysql_error()); } $query = mysql_query("SELECT ltrim(firstname) FROM $table WHERE FirstNameID = '0' group by ltrim(firstname) ORDER BY ltrim(firstname) ASC"); while($row = mysql_fetch_array($query)) { $fname = strtoupper($row['ltrim(firstname)']); $query4 = mysql_query("SELECT id,firstname FROM tblFirst WHERE firstname = '$fname'"); while($row = mysql_fetch_array($query4)) { $fname = ltrim($row['firstname']); $fnameid = $row['id']; $query5 = mysql_query("UPDATE $table SET FirstNameID = '$fnameid' WHERE ltrim(firstname) = '$fname' AND FirstNameID = '0'"); } } } The main issue I am having is the first time I run this on a table. It takes upwards of 200 seconds for a table of 30,000 entries. Once the table is populated with the IDs, it can make use of the ID = '0' and so then it only takes a matter of seconds to update it each time. It works. But im not happy with the initial run, so I thought I would ask to see if anyone can point me in a direction to improve it. Oh, the ltrim is on there because sometimes a whitespace appears because some of the tables have a leading blank space. thanks!

Wow. thats just dirty. never thought of doing it like that. thanks for the help! I appreciate it

Hi there, I have a column with some fields that I need to switch around the way it is stored. ex: field: id KILLERS, THE USED, THE PEOPLE, THE MUSIC, THE I want to run a query to make it into THE KILLERS THE USED THE PEOPLE THE MUSIC So far ive got this far, but just need the end part. UPDATE _testing set id = replace(id,'%, THE', I am unclear as to how to finish this statement. Thanks!

I got it. RewriteRule ^portal/([^/]+)/([^/]+)/?$ /portal.php?m=$1&id=$2 it needed a / before the page.php ...

Howzat it, Ill try and explain what I want in the end, and then show you what I have. basically I want this: http://www.example.com/portal/NEWS-ITEM-GOES-HERE/342A ideally, I would like to send 342A to {portal}.php which resides in http://www.example.com/portal.php?id=342A the issue I am having it grabbing that first bracket and making it the page. so if it was http://www.example.com/news/SOME-NEWS-HERE/A342FD42S it would be http://www.example.com/news.php?id=A342FD42S So far ive got: RewriteRule ^([^/]+)/([^/]+)/([^/]+)/?$ portal.php?id=$3 Thanks for the help! Cheers

I cant give you the correct answer, but I can share this with you: http://forums.devshed.com/showthread.php?p=1165165 One of the examples should be able to help you out. Good luck! Cheers

Nope not a problem. Thanks again!

lol you're right. too many tabs open. heres the right table info: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY music ref WeekYear WeekYear 8 const,const 1071 Using where; Using temporary; Using filesort 2 DEPENDENT SUBQUERY music range WeekYear WeekYear 4 NULL 3921 Using where and hey hey, look what we got here, 89 total, Query took 7.3527 sec) Wow. that worked nicely. A lot better than 80. lol Thanks for the help Mchl! I really appreciate it. I was just wondering if you could explain the index and how it works, and do I have to run the alter query every so often or does it update itself?

Exact same thing - still NULL id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY top40 ALL NULL NULL NULL NULL 56513 Using where; Using temporary; Using filesort 2 DEPENDENT SUBQUERY top40 ALL NULL NULL NULL NULL 56513 Using where

I did as you said but it didnt help in any way. Still takes around 80+ seconds for the query to run, and if I run more than 1 of these queries: MySQL said: Documentation #1053 - Server shutdown in progress :S Might have to start moving to two tables. Cheers!

Thanks for your considerations. Ill have to look into moving it to two tables, its just that I have over 50,000 records in this table for playing info. But its probably best to get it out of the way and start fresh and efficiently. Is there any other method possible with using only a single table? My left join statement should speed things up a lot, but I cant seem to get it to work. Also, here is the EXPLAIN query results id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY music ALL NULL NULL NULL NULL 57332 Using where; Using temporary; Using filesort 2 DEPENDENTSUBQUERY music ALL NULL NULL NULL NULL 57332 Using where

All the songs played are thrown into the table. I couldnt think of a better way for comparing data and analyzing the songs for statistics. When a song is played, it is thrown into the table, with a weeknum value(the weeknum is date("W") in php, so 1-52) and yearnum is the current year the song was played in (date("Y") in php) This way I can count songs for certain weeks(for overplaying, underplaying, ect) and also for the current year. I should add that there is also a unique time stamp for each song (it looks this this: January17.0451PM.2009)

Yep no problem. The table looks like this: Song | Weeknum | Yearnum ----------------------------------- Song 1 | 3 | 2009 Song 2 | 3 | 2009 Song 3 | 3 | 2009 Song 4 | 3 | 2009 Song 1 | 2 | 2009 Song 3 | 2 | 2009 Song 1 | 1 | 2009 Song 5 | 1 | 2009 I have left out artist as its not as important as song. Basically I have the song, a week number value, and a year number value. So using this example, Song 2 and Song 4 are new songs for the current week (we are in week 3 of the current year). This is the result that I want. Cheers!

Hi there, I am running a query right now but the database is getting too big and the query is timing out half of the times - Query took 84.7006 sec - and I have to run this query on 8 tables :-\ The query is: weeknum is dynamic and relates to the current week of the year, ex: 3, yearnum is the current year. SELECT Count( song ) AS CountSong, artist , song FROM music WHERE weeknum = ".$weekNum." AND yearnum =".$yearNum." AND song NOT IN (SELECT song FROM music WHERE yearnum = ".$yearNum." AND weeknum < ".$weekNum." ) GROUP BY song ORDER BY `CountSong` DESC Basically what this does is grab the song and artist that has not appeared in the database prior to the current week. So essentially, Im getting a list of this weeks new songs. I tried to use a left join, but I cant seem to figure it out. I get #1052 - Column 'song' in field list is ambiguous I tried: SELECT song FROM music m LEFT JOIN music as x ON m.song = x.song AND x.weeknum = $weekNum-1 WHERE m.weeknum = $weekNum AND x.song IS NULL Im just looking for some help to drop that query time from 86 seconds. lol. thanks!

I have figured out the query: SELECT Count(throws) AS CountPlayer, player FROM (SELECT throws,player FROM tablea UNION ALL SELECT throws,player from tableb) DERIVEDTBL GROUP BY player ORDER BY `CountPlayer` DESC This works on my localhost mysql query browser, but it wont work on the live server, it says SHOW TABLE STATUS LIKE 'DERIVEDTBL'; turns out mysql doesnt support derivedtbl Hmmmm.

Hi there, Im trying to take values from two tables and add them. this task is simple enough, but the problem is is that I want to get the count totals from each table first and then add them. Here is the table structure: ---TABLEA----------------------- -------------------------------- Player | Throws | Time | -------------------------------- Bob | 3 | 10-20 | Matt | 1 | 10-20 | Sam | 9 | 11-20 | Bob | 11 | 09-20 | Matt | 2 | 09-20 | Bob | 6 | 17-20 | -------------------------------- I DO NOT want the total throws. I just want the count value of how many times they show up in the table - not the sum of throws. SELECT Count(throws) AS CountPlayer, player FROM tablea GROUP BY player For TABLEA then, I am given Bob 3 Matt 2 Sam 1 ---TABLEB----------------------- -------------------------------- Player | Throws | Time | -------------------------------- Matt | 4 | 10-20 | Sam | 1 | 11-20 | Bob | 2 | 09-20 | Matt | 2 | 09-20 | Bob | 9 | 03-20 | Sam | 3 | 08-20 | Bob | 1 | 01-20 | -------------------------------- using the count method above, I am given the count values for TABLEB Bob 3 Matt 2 Sam 2 So now I want to take the count results from both tables and add them, giving me this: Bob 6 Matt 4 Sam 3 The first query is the one that gets me the closest to numbers, but it only shoots out weird results. SELECT Count(throws) AS CountPlayer, player FROM tableb GROUP BY player UNION SELECT Count(throws) AS CountPlayer, player FROM tablea GROUP BY player ORDER BY `CountPlayer` DESC SELECT (SELECT Count(throws) AS CountPlayer, player FROM tablea GROUP BY player) + (SELECT Count(throws) AS CountPlayer, player FROM tableb GROUP BY player) AS total SELECT Count(tablea.throws) AS aThrows,Count(tableb.throws) AS bThrows, (aThrows + bThrows) as abTotal from tablea,tableb left join tablea on tablea.player = tableb.player order by abTotal thanks for the help. MK

I cant edit my post, but I got the query. SELECT * FROM music WHERE song NOT IN (SELECT song FROM music WHERE week = 40) Cheers!

Thanks for the help. All I have in the table is "Song name" and "Week number" in a table called music. I tried this: SELECT * FROM music WHERE 'week' = 41 AND NOT EXISTS (SELECT * FROM music WHERE 'week' = 40) I can change the week numbers myself, its not an issue. it is error free but returns zero results.

Hi there, I am having trouble getting the results I want. I am trying to find out basically what songs I played this week but not last. Here is a table example: Song Title | Week Play ------------------------ Song 1 | 41 Song 2 | 41 Song 3 | 41 Song 4 | 41 Song 1 | 40 Song 3 | 40 So my query result would be Song 2 and Song 4 - since they were not played in week 40. ive tried: SELECT * FROM `music` WHERE 'week' = 41 AND NOT EXISTS (SELECT * FROM 'music' WHERE `week` = 40) thanks for the help. MK

everyday i update the table with the new stats, and i need past stats so i can track the users. is there another way to accomplish this? ...and...hahaha. it was the integer thing. totally forgot to set the field type. thanks for that pointer. really appreciate your help! +1

yeah, that didnt work either. lol. Some notes on the table - the query works for all the data except for Elite Mamac, and Elite Sabretooth. Here is the table: here is the result from the last suggest query: I tried removing the data from Mamac and Sabretooth fields and re-enter them into the DB, and still the problem persists. But the queries do work for the rest of the data - i am just really confused as to why it isnt working for these two specific ones. Thanks for your help. Cheers