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>

I've marked this as solved because I have found more success with a different method.

I am creating a football tipping application, and I am stuck on the page where the user selects their tips (OK, so I havn't gotten very far!) For a specified round of the football season, I need the form to pair up the two teams who are playing, show details of the match and allow the user to select one of the teams with a radio button. For example: Home team | (radio select) | vs | (radio select) | Away team | Venue | Date There will be 8 lines of the above, because there are 8 matches in a round. I also need the form to pre-load with their selections. The three tables at work here are called teams, games and selections (create table code is below). The way I decided to attempt this was to only pass the teamid in the array to be processed and updated. I toyed with the idea of passing the gameid as well, but I figured this actually wasnt necessary. I've been working on the code below, and although it is imcomplete, and could be totally wrong, it might show you what I am trying to acheive. I usually don't like to post such incomplete code to a forum such as this, but in this case I'm completely stuck. Even if someone can just point me in the right direction that would be great. /*process the form - gets the team ids from the posted form and runs sql for each */ if($_POST['teams']) { foreach((array)$_POST['teams'] as $team) { $query = "Insert into selections (userid, round, team) Values ($user, '1', $team)"; $result = mysql_query($query) or die("Query failed : " . mysql_error()); } ?> <form method="post" action="round1.php"> <? $result = mysql_query("select * from games where games.round=1"); $myrow = mysql_fetch_array($result); ?> <table width="600" border="0" cellspacing="0" cellpadding="0"> <tr> <td> <? do { $home_team_id = $myrow["hometeam"]; $away_team_id = $myrow["awayteam"]; /*assign the actual team name - not sure how to handle this, should be combined with first query above?*/ /* find all records for home teams that have already been selected*/ $checkedresults_home = mysql_query("Select * from selections where userid='$user' and team=$home_team_id and round=1"); $num_home = mysql_num_rows($checkedresults_home); /* if a home team has already been checked append "checked" to the end of the input tag in the html */ if ($num_home==1) { $home_team_id = str_replace($home_team_id, "$home_team_id checked", $home_team_id); } /* find all records for away teams that have already been selected*/ $checkedresults_away = mysql_query("Select * from selections where userid='$user' and team=$away_team_id and round=1"); $num_away = mysql_num_rows($checkedresults_away); /* if an away team has already been checked append "checked" to the end of the input tag in the html */ if ($num_away==1) { $away_team_id = str_replace($away_team_id, "$away_team_id checked", $away_team_id); } /* the form with radio buttons - incomplete! */ printf ("<input type=\"radio\" name=\"teams[]\" value=...................; } while ($myrow = mysql_fetch_array($result)); ?> </td> </tr> </table> <input type="hidden" name="uid" value="<? echo $user; ?>"><br> <input type="submit" name="Submit" value="Submit"> </form> The database structure: CREATE TABLE `games` ( `id` int(11) NOT NULL auto_increment, `round` int(11) NOT NULL, `hometeam` int(11) NOT NULL, `awayteam` int(11) NOT NULL, `homescore` int(11) NOT NULL, `awayscore` int(11) NOT NULL, `homegoals` int(11) NOT NULL, `awaygoals` int(11) NOT NULL, `homepoints` int(11) NOT NULL, `awaypoints` int(11) NOT NULL, `venue` text NOT NULL, `datetime` datetime NOT NULL, PRIMARY KEY (`id`), KEY `round` (`round`), KEY `FK_hometeam` (`hometeam`), KEY `FK_awayteam` (`awayteam`), CONSTRAINT `FK_awayteam` FOREIGN KEY (`awayteam`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_games` FOREIGN KEY (`round`) REFERENCES `rounds` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_hometeam` FOREIGN KEY (`hometeam`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=177 DEFAULT CHARSET=utf8 CREATE TABLE `selections` ( `id` int(11) NOT NULL auto_increment, `bookfaceuserid` bigint(20) NOT NULL, `game` int(11) NOT NULL, `team` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `FK_selectuser` (`bookfaceuserid`), KEY `FK_game` (`game`), KEY `FK_team` (`team`), CONSTRAINT `FK_team` FOREIGN KEY (`team`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `selections_ibfk_1` FOREIGN KEY (`game`) REFERENCES `games` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `selections_ibfk_2` FOREIGN KEY (`bookfaceuserid`) REFERENCES `user` (`bookfaceuserid`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `teams` ( `id` int(11) NOT NULL auto_increment, `name` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8

Many thanks, Mad Techie. Should've realised I didnt need the regex for such a simple validation.

I have a diet related application, which has a form that asks users to enter the number of servings they eat of certain foods. The data is sent to the processing page as an array ('qty' in the code below). I need to make sure they enter a number between 1 and 999. I've been researching, and mucking around for a few days on this, and the code below is as close to working as I can get it. The first part, which checks that there are no blanks, works fine. I'm pretty sure the regex is fine as I have tested that independently. But the validation doesn't work. It seems to accept anything I enter - numbers and letters. I'm not sure if using 'implode' is the way to go, but it was the only example I could find that I could really understand. Any help greatly appreciated. Please note I am new at php, so please be gentle! # check for qty not blank? if($_POST['qty']) { foreach((array)$_POST['qty'] as $qtynull) { if (empty($qtynull)) { die('You did not enter anything. <a href="signup3.php">try again</a>.'); } } } $quantity_valid= (array)$_POST['qty']; $qv=implode(' ',$quantity_valid); if(preg_match("/[1-9]\d{0,2}$/i", $qv)){ # Any selections checked? if($_POST['qty']) { $i=0; $select = $_POST['selectionsid']; foreach((array)$_POST['qty'] as $qty) { $query = "update selections set qty='$qty' where selectionsid='$select[$i]'"; $i+=1; $result = mysql_query($query) or die("Query failed : " . mysql_error()); } } }else { echo "Incorrect format. Please enter a number between 1 and 999\n $qv"; }