sending a list from a form to a mysql table as 1 record can i

[Baxt01]

is this possible please,

 

I need to be able to allow my users to past a list into a text area on a form that looks like this

EZ_DUZ_IT_12 1415
tootie 1245
BigDuke6 1205
HOBOPEG1 1095
nearly_nuts 860
doctor9995 825
Valcar45 790
MC_Tracey 755
Da_witch 750
DJMB 710
MC_bones 710
PR_bernard6 685
CherryGarcia 680
Flag_Lady1 680
Icon_Will_ 635
Kay_ 610
Ginny 580
mooge1 570
PR_BlackSheep 520
PITA 465
bigguy5 460
MC_Fran_Tas_Tic 455
MC_ladyhawk 440
Leisure 435
debbie60fla 420
pepsimissy 420
FireryFun 400
PR_Flo 390
GalleyGal 385
MasterMariner 380
PR_pinky4103 365
sidthekid 355
MtBob 350
Musiclady333 350
PR_MonaMaggee 345
_Zingle_ 325
Sea_Ghost 325
Haleyscomet 320
__Tracey__ 290
jayco_blackie2 280
silveradodude 280
MC_Chef 265
felinepeachy 255
frogette 245
shortone989 245
MC_MuddyBoot 240
thor111 240
GENTLEBEN2 235
johnboy 235
Truegrit 230
Z3_Dar 225
MC_Oinky 220
eagle7374 215
sir_lunchalot 215
Stinger 215
dc210 210
cybillll1 205
GoofyGal 195
GoofyGuy 195
olddogyeller 195
MsDis 180
soccerfan09 180
_dust_ 175
PR_Yolande 175
Scoobie 175
cribdemon 165
PR_Lilywhite 165
digit2 160
RFTS_Moonie_ 160
larousse 155
london_wayne 155
MC_Maddy 155
wascullywabbit 155
PR_Kandy 145
Behavin_Badly 140
mitten 140
the_toaster 140
_Cat_ 135
PegginCajun 130
pops635 130
southern_storm 130
no_dice 125
PR_Candie 125
PR_Junee 125
RFTS_OldMan 125
SnowLady1 105
thekidkicks 105
Trouble_Frantic 105
jimbob57 100
RFTS__nessieee 100
RG_Terrace 100
EVIL_UNICORN 95
Just_Lindy 95
RFTS_Zap 95
PR_Black_Widow 90
RFTS_Cille 90
RFTS_Puffin 90
PR_iplay_wild 85
UBG_TAI_PAN 85
AgentOrange 80
Poet 80

 

this is a list of tournament players and their awarded points for the month the group need to be able to paste this into my web form as it is so that it saves into MySQL table then I can pick this up and re display it for the players to view when I am trying to get this to appear as a list looking like this it shows as a paragraph and not all the info is been saved and returned anyone got any ideas please

[fastsol]

You can just save it like that in the db and echo it back using the nl2br() which will redisplay it with the same line break formatting it was put in the db as.

[davidannis]

 

not all the info is been saved and returned

What is the field length/type of the field you are storing it in? Is it the data at the end that you are losing? My guess is it is being truncated because you have a lot of characters and a field that won't hold them all.

[Baxt01]

ok davidannis your answer was right on the mark with the truncate happening to the end data I do not know which setting to use to save this much data as I only know how to use varchar, INT, BIGINT, this is a little frustrating I am using a 3 field table to save this data as follows,

 

ID - AUTO_INCREMENT

player_name - INT

points - INT

 

I know the last 2 only have a 255 character support and this list holds many more characters with the line breaks

 

fastsol I like the sounds of what your saying here but have no idea what it means LOL im a complete neewb to MySQL

 

just so you know when I am working with MySQL on my domain its a dashboard console wizard system I don't get the pleasure of typing commands I log into my domain and go to a dashboard then I click on PHPMYADMIN and even setting up a table is a wizard thanks

[priyankagound]

you can use the concept of bulk insert which executes many inserts at the same time minimizing overhead of calling ExecuteNonQuery multiple times.

in MySQL this is called LOAD DATA, check here for details:

http://dev.mysql.com/doc/refman/5.5/en/load-data.html

in MS SQL Server this is called bulk insert and it's known as such, that's why I've mentioned it with this name.

[fastsol]

Are you trying to separate the list into different parts?  You list table columns that are INT type which would only work for the score of the player, not the player name.  INT will only store a number not string values.  Even if you are wanting to separate the list you are missing a crucial column of something that would relate the list to a specific game or whatever you are saving this info for.

 

So if separation is the the concept then this would be more correct columns.

ID - AUTO_INCREMENT

player_name - varchar(40)

points - INT

game_id - INT   //This being a ID value form a table with a list of games, this way all the info inserted is related to a specific game and can be referenced later easier.

 

If you wanted to do the original way I suggested then you would change the columns to something like this.

ID - AUTO_INCREMENT

game_info - TEXT

game_id - INT

Edited September 16, 2013 by fastsol

[davidannis]

If you are just storing a large blob of text use the text or blob type. http://dev.mysql.com/doc/refman/5.0/en/blob.html

[Baxt01]

after all my research and all your advice's I am now thinking maybe I should go back to my own original method of storing this list in a file on my server as in fopen in php fwrite  fclose

 

as the list I posted previous is how it needs to show on my end page if you go to this web page it will show you exactly what it is I am trying to make

http://musiccafefriends.webs.com/top100stats.htm

I did start to make this site once before using the fopen method and storing my data in a file to recall using the file include method

however my unpaid domain crashed and I lost all my work after several un kind words I started looking into MySQL I just used an online character counter and this monthly list will have approx. 1061 characters and that's just the player names this figure is variable as the players are not always the same but there will always be 100 player names per list and of course then there is the points to match the names

after viewing the link above you may then say why am I rebuilding a site that's already there and running

my answer is that its a .webs.com free domain that is several years old and editing the site has become very slow and hard work

[jcbones]

Here are a couple of questions, and we can get this sorted.

 

1. Is there a specific games that the players play, ie game specific to this week, or 1 of multiple games this week?

2. Do you want a specific players name searchable, ie shows all games and scores for individual player?

[Baxt01]

basically the group that I am making the site for host live online cribbage tournaments and that is the only game that this site is relivent from the list I posted above is how it will always appear any players that play are awarded points per tournament and those acumalatted points are totalled and the top 100 go on the site in a list

[jcbones]

So, create these tables in your database (should be able to copy paste).

-- --------------------------------------------------------

--
-- Table structure for table `players`
--

CREATE TABLE IF NOT EXISTS `players` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `player` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=0 ;

-- --------------------------------------------------------

--
-- Table structure for table `scores`
--

CREATE TABLE IF NOT EXISTS `scores` (
  `games_id` int(11) NOT NULL,
  `players_id` int(11) NOT NULL,
  `score` int(11) NOT NULL,
  KEY `games_id` (`games_id`,`players_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `games`
--

CREATE TABLE IF NOT EXISTS `games` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(40) COLLATE utf8_bin NOT NULL,
  `dateOfEvent` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=0 ;
 

Next you need a database connection script: (make sure you edit these to the proper credentials.

config.php

<?php

//Edit these values to reflect your database connection.
define('DATABASE','test_tourny');  //name of database
define('USER','root'); //name of database user
define('PASSWORD',''); //password to access database
define('HOSTNAME','localhost'); //server where database is located


//No need to edit below this line:


$db = new mysqli(HOSTNAME,USER,PASSWORD,DATABASE);

Next we need a insertion script: (this should be a members only script, so it needs to be locked like your script to save to a file.

<?php
include('config.php'); //You must have a database connection.
$date = date('Y/m/d'); //current date in Year/Month/Day format (just the way the database likes it).
if($_SERVER['REQUEST_METHOD'] == 'POST') { //if the page was POSTED to (form sent).
    $parts = explode(PHP_EOL,$_POST['text']); //break up the text area on the line breaks.
    $game = $_POST['game']; //get the game name.
    unset($_POST['text']); //free memory of the $_POST variable.
    foreach($parts as $part) { //loop the array we created.
            list($name,$score) = explode(' ',$part); //break the names from the scores.
            $name = trim($name); //get rid of whitespace.
            $score = trim($score); //get rid of whitespace.
            $names[] = sprintf("'%s'", $db->real_escape_string($name)); //escape the names for db insertion, and put them in an array.
            $scores[$name] = $score; //save scores to array, associated with the name.
    }
    //This block is for game name insertion.
    $sql = "INSERT INTO games (name,dateOfEvent) VALUES (?,?)";
    $stmt = $db->prepare($sql);
    $stmt->bind_param('ss',$game,$date);
    $stmt->execute();
    $gameID = $stmt->insert_id; //get the game ID back from the database.
    $stmt->close();
    //end game insertion.

    //insert all names into the players table.
    $sql = "INSERT INTO players (name) VALUES (" . implode('),(',$names) . ')';
    if(!$result = $db->query($sql)) { //if it fails, throw an error.
        trigger_error($db->error);
    }

    //get the id's back from the players table, since the database creates them for us.
    $sql = "SELECT id, name FROM players WHERE name IN(" . implode(',',$names) . ')';
    $names = array_flip($names); //flip the names array, so the player names are the keys.
    if($result = $db->query($sql)) { //execute the query, if it fails run the else block below, if it is successful, drop to the while statement.
        while($row = $result->fetch_assoc()) { //loop through the results, one row at a time.
            $names[$row['name']] = $row['id']; //reset the values in the names array to the ID's from the players table.
        }
    } else {
        trigger_error($db->error); //throws error if the database query fails.
    }

    foreach($scores as $name => $score) { //loop through the scores.
        $sqlPart[] = sprintf("%d,%d,%d", $gameID,$names[$name],$score); //create an array holding the values for the score insertion.
    }
    $sql = "INSERT INTO scores(games_id,players_id,score) VALUES (" . implode('),(',$sqlPart) . ')'; //string together the scores for a multi-row insert into the scores table.
    if(!$db->query($sql)) { //execute the query, if it fails.
        trigger_error($db->error); //throw an error.
    }
}
//show the form.
?>

<form action="" method="post">
    <label for="game">Game Name</label><br />
    <input name="game" id="game" type="text" /><br />
    <label for="text">Name / Score list</label><br />
    <textarea cols="50" rows="10" name="text"></textarea><br />
    <input type="submit" name="submit" value="Submit" />
</form>

Next we need a script that will search and display the results:

<?php
include('config.php'); //must have a database connection.
//main part of query string.
$sql = "SELECT g.name AS gName, p.name AS pName, s.score FROM games AS g JOIN scores AS s ON s.games_id = g.id JOIN players AS p ON p.id = s.players_id";

if(!empty($_GET['search'])) { //if a search was submitted.
    $search = trim($_GET['search']); //get rid of the whitespace.
    if(stripos($search,'game') !== false) { //if it contains the lowercase word 'game'
        list(,$search) = explode(' ',$search); //get the part that isn't 'game'
        $sql .= sprintf(" WHERE g.name LIKE '%s%%'", $db->real_escape_string($search)); //but search the database on the game name.
    } else {
        $sql .= sprintf(" WHERE p.name LIKE '%s%%'", $db->real_escape_string($search)); //else search the database for the player name.
    }
}
$sql .= " ORDER BY g.name, s.score DESC"; //add the order by, which searchs for the game name and player score DESCENDING.

if(!$result = $db->query($sql)) { //if the query fails
    trigger_error($db->error); //throw error.
}
//if the query succeeds, pring form and results.
echo '<form action="" method="get">            
            <input type="text" name="search" />
            <input type="submit" name="submit" value="Search" /><sub>case sensitive</sub>
            </form>
            <table>';
$i = 0; //control number.
while($row = $result->fetch_assoc()) { //loop through the rows in the database.
    if(++$i == 1 || (isset($lastGame) && $lastGame != $row['gName'])) { //if the incremented control number is equal to 1 or the lastGame is different from the current game.
        echo '<tr><th colspan=2>' . $row['gName'] . '</th></tr>' //echo the table headers.
                    . '<tr><th>Name</th><th>Score</th></tr>';
    }
    echo '<tr><td>' . $row['pName'] . '</td><td>' . $row['score'] . '</td></tr>'; //now echo each and every row.
    $lastGame = $row['gName']; //end of the loop, store current game name to lastGame to be checked on new row.
}
echo '</table>'; //table ends, all finished.

Thats it, You can make it more efficient, but I just had a moment to play around.  Hope it gets you close.

Edited September 17, 2013 by jcbones