damo87

Thanks for the 'select into outfile' suggestion. Would have been perfect except my database user doesnt have the permissions to use this. I tried granting permision, ie GRANT FILE ON *.* TO 'dbuser' @ 'localhost'; but permission was always denied. After trying other solutions (query tools, a separate php page) I realised that there was a simple way to sidestep the problem in phpmyadmin. I simply created a temporary table and populated that with the query results: INSERT INTO round_odds_temp (user, sumodds) SELECT user, SUM(odds) FROM choices WHERE round=1 GROUP BY user limit 0, 10000; I can then export the new table into a csv file.

Yes, the recordset is correct. I have to spend about an hour copying the results and pasting them into excel. I received some advice from the SourceForge.net help forum to upgrade to phpMyAdmin 3.3.1 or at least 2.11, but this isn't an option for me because I am on a shared server. Is there another place (other than phpMyAdmin) I can run the query and copy/export the results?

I can usually export the results of my queries in the format 'CSV for Microsoft Excel' in PHPMyAdmin, but when attempting to do this with the following query: SELECT user, SUM(odds) FROM choices WHERE round=1 GROUP BY user limit 0, 10000; I can only get an xml file to be exported, which doesn't contain the results of the query. I suspect it is the SUM and/or GROUP BY function that is the major difference between the queries I can export successfully, and this one. Any suggestions greatly appreciated. Thanks. --Versions: MySQL - 5.0.90; phpMyAdmin - 2.8.2.4--

Thanks for the response, ocpaul20. I'm not familiar with 'date_default_timezone_set' so Ill have to do my research to fully understand how it could help. In the mean time, I have simply created a new date/time column in the database So I have one datetime column that is used to display the date on the page (which shows the game in the local time of the game location), while the other datetime column (which is the server time of the game start time) which is used to do other stuff, such as lock down any further tipping once the game has started. I really have no idea about whether this is a better solution than manipulating the dates with php . I suppose it is a better solution for me right now because it is the only way I know to get it to work.

I am displaying a table of football matches, which includes columns for venue and date. The complete code is quite long, so I'll just show the relevant parts: $query="SELECT venue, date_format(datetime, '%M %e, %Y, %l:%i%p') as newdate,..... Then I define some variables from the query: while($nt=mysql_fetch_array($result)){//Array or records stored in $nt $datedisplay = $nt[newdate]; Then display the results in a table: echo "<tr><td class=right>$nt[hometeamname] $homefinalscore</td>.......<td class=center>$nt[venue]</td><td>$datedisplay</td>.......</tr>"; So I get my date/time displayed in the table like this: April 1, 2010, 7:40pm This is all working fine. All the dates are stored on the database in a particular time zone, which is the same time zone as my server time, which makes it easy to do things such as restrict changing selections for a game that has already started. What I would like to change though, is to display the game date/time in the local time for that game location. There are only two venues where the time zone is different, so I imagine I would need to do something like "if venue = S, $datedisplay=$datedisplay-2" (if the venue 'S' is two hours behind the server time.) Just to clarify, I am not looking to change the date displayed based on my users' location. Everyone, no matter their actual location should see the same game time displayed. Thanks in advance for any suggestions.

Got it working like this: // check the server time $mysqltime = date ("Y-m-d H:i:s"); $query = "DELETE FROM selected WHERE selected.round=$round and user=$user and EXISTS (select distinct game.home_team from game where selected.team=game.home_team and game.round=$round and datetime >'$mysqltime')";

Im my football tipping application, I would like for users to be able to update their tips during the weekend's round of play, even if some games have already been played (the usual scenario). So when users submit their tips, I need my SQL statement to delete their tips only for the games that havnt already started. (I insert their new tips for those games in the next block of code, but that is not the issue here). The problem I am having is getting the SQL to ignore the games that have started. I have tried this: DELETE FROM selected WHERE selected.round=$round and user=$user and EXISTS (select distinct game.home_team from game where selected.team=game.home_team and game.round=$round and datetime >NOW()) This seems to work in all situations except where the datetime (which is an actual datetime field) and now() times have the same morning hour, eg: now time: 2010-03-25 04:39:46 game time: 2010-03-25 04:37:00 The game started a few minutes ago, so it shouldnt delete it - but it does! I have also tried: $datetime = date('Y-m-d h:m:s'); $datetime = strtotime($datetime); DELETE FROM selected WHERE selected.round=$round and user=$user and EXISTS (select distinct game.home_team from game where selected.team=game.home_team and game.round=$round and UNIX_TIMESTAMP(datetime) >= '$datetime')" ...but this has the same outcome. Any Ideas greatly appreciated. Versions: MySQL - 5.0.90 PHP Version 5.2.13

Thanks for the push in the right direction. Got it to work like this: $queryrounds = "Select number from ...........rest of query here"; $resultrounds = mysql_query($queryrounds) or die (mysql_error()); while($row = mysql_fetch_object($resultrounds)){ $rounds_array[] = $row->number; } $rounds_list = implode(", ", $rounds_array); echo "Hello: $rounds_list";

My query in the code below returns two rows (from my MySQL database), which it should do. If I loop through the results, I can disply the two rows. What I need to do is these two to be in a new variable, comma separated so that I can use them in an insert statement. What I get with the code below is only the first row. If I print the array before I try to implode it, I get: Array ( [number] => 1 ) So it looks like I'm not setting up the array correctly because the '1' is the value of the first row, but the value of the second row is missing. Any suggestions for getting both values into the array, and them imploding them would be greatly appreciated! $query= "Select round from rounds where rounds.round<=$roundineditable and rounds.round not in (select x from margin where user=$user and round<=$roundineditable)"; $result = mysql_query($query) or die("Query failed : " . mysql_error()); $row = mysql_fetch_assoc($result) or die(mysql_error()); $comma_separated = implode(",", $row); echo "<p>$comma_separated</p>";

Yes, if it wasn't a multi dimensional array. Imploding my array just gives me the word 'array' rather than the user id's I need.

OK, I think I've sorted this one out. The output of the first SQL statement results in this array: Array ( [0] => Array ( [uid] => 1234 ) [1] => Array ( [uid] => 4321 ) ) So I need to turn the array values into comma separated values, and remove the last comma (note - because it is a multidimensional array, the implode function will not work): $uid = ''; foreach($resultarray as $v1) { $uid .= $v1["uid"] . "," ; } $uid=substr($uid, 0, -2); Then insert into the next statement: $query = "select * from user where userid IN($uid)"; $result = mysql_query($query) or die("Query failed : " . mysql_error()); Which gives me an array with the data I need.

Sorry, should have mentioned that it is MySQL.

I have an sql statement that returns a list of user ids from one database. I then need to run some SQL on a different database for each of those user ids. The tricky thing for me here is that they are two different databases, so I assume I can't get all the data I need in one SQL statement. I really have no idea how to start on this, so at this stage even a push in the right direction, ie. a description of generally what I need to do/what to google on, would be helpful. Thanks!

Thanks for putting me on the right track. I've tweaked things a bit, so here are the bits that are different from the original code: $gameid = $nt[id]; echo "<input type = 'radio' name='radioarray[$gameid]' value=$nt[hometeam]>$nt[hometeamname]<input type = 'radio' name='radioarray[$gameid]' value=$nt[awayteam]>$nt[awayteamname]<p>"; and then in the processing part: if($_POST['radioarray']) { foreach((array)$_POST['radioarray'] as $team) { Thanks again for you help.

Note: I posted a topic earlier today on this (array and radio buttons for football tipping), but because I have moved on to a different method, with almost completely different code I thought I'd start a new topic. I am creating a football tipping application. For a specified round of the football season, I need a form to pair up each set of two teams who are playing, show details of the match and allow the user to select one of the teams with a radio button. There are three tables involved, and I have included the create table scripts at the end of this post. I have that part of things working. What I am having trouble with is processing the data from the form. The data that is sent to be processed consists of 8 different arrays. This makes sense to me because there are 8 games each week, therefore 8 sets of radio buttons. In the first output below, the '[17]' is the id of the game, and the "14" is the teamid that has been selected: [17]=> string(2) "14" [18]=> string(2) "16" [19]=> string(2) "12" [20]=> string(2) "13" [21]=> string(1) "4" [22]=> string(1) "2" [23]=> string(2) "17" [24]=> string(1) "8" I just need to insert the teamid into the selections table (along with the userid and roundid, which I don't need from the array). I have hardcoded the ['17'] in the code below to check if the insert statement actually inserts into the database as expected, and it does. But obviously I need it to dynamically loop through all eight arrays, and I am really unsure how to go about that. Or is there a better way to set up the radio buttons, so that the data is sent as one array?? If anyone could suggest how to fix this, either at the form or at the processing stage I would be very grateful. Thanks. if($_POST['17']) { foreach((array)$_POST['17'] as $team) { $query = "Insert into selections (bookfaceuserid, round, team) Values ($user, '3', $team)"; $result = mysql_query($query) or die("Query failed : " . mysql_error()); } } $query="SELECT games.id, hometeam, awayteam, teams.name AS 'hometeamname', teams2.name AS 'awayteamname' FROM games, teams, teams AS teams2 WHERE games.round =3 AND (games.hometeam = teams.id AND games.awayteam = teams2.id)"; ?> <form action="round3.php" method="post"> <input type="hidden" name="add" value="true" /> <input type="hidden" name="userid" value="<?php echo $user ?>" /> <? // printing the radio buttons select command $result = mysql_query ($query); while($nt=mysql_fetch_array($result)){//Array or records stored in $nt echo "<input type = 'radio' name=$nt[id] value=$nt[hometeam]>$nt[hometeamname]<input type = 'radio' name=$nt[id] value=$nt[awayteam]>$nt[awayteamname]<p>"; /* Option values are added by looping through the array */ } ?> <br><br> <input type="submit" name="submit" value="Submit" /> </form>