multiple entries to db using one text area

[Justafriend]

I have no idea where to go to get this  started I know how to do a basic form but my issue is I want to  take a data set that is standard in this format

A_Baddboy 1
achilleas 1
blackred 1
EU_IT_mirejo 1
Rainbow32 1
Sallyforth 1
UBG_sara1smoon 1
ADG_Romario 4
orthodion 4
TM7_CharrM5_ 4
jjaded 17
TM7_crazykarma 17
thebest7777 29
real_chainsaw 42

and put it in so each name and points beside thenm get put into data base  colums I have are id date  playername and points so that when its in there I can call on database to give me the points for each player

thank you in advance for any help

 

 

[Guber-X]

so what you are wanting to do is basically populate the list of players and be able to update the points in one form kinda thing right?

[Justafriend]

updating wouldnt work cause if i get 45 points this month they dont count for october so at that point id have to empty the table what im looking to do is have a new entry for each person  so there would be an entry in the database like this

 

id           date                          playername         points

1         todays date                  a_baddBoy             1

 

so i can do a page with get to add everytime in the month of september that you see a_baddboy it will add all his points together and spit them out  but on october first it will not look back to september results

hope this clarified it

[vinny42]

That seems simple enough, if the player names never contain spaces then this data is simple a CSV file and you can process it like you would any other CSV (including not having to paste it into a textarea, you could just upload a CSV file)

 

if you do use a testarea you can explode() the string on newlines, to get separate lines per player, and split each line around space to get the name and the points.

Simply insert the new data into the table and away you go.

[Barand]

First, create your table. ID and date fields will be populated automaticallyso yoou only need to insert the name and the points

CREATE TABLE `player_points` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_entered` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `player` varchar(45) DEFAULT NULL,
  `points` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;

This will process your text area

$db = new mysqli(HOST, USERNAME, PASSWORD, 'test' );

if (isset($_POST['points']) && !empty($_POST['points'])) {
    $entries = explode("\n", $_POST['points']);
    $insertData = array();
    foreach ($entries as $e) {
        if (trim($e) == '') continue;  // ignore empty lines
        list ($name, $pts) = explode(' ', $e);
        $insertData[] = sprintf("('%s', %d)", $db->real_escape_string($name), intval($pts));
    }
    // build multi-insert query
    $sql = "INSERT INTO player_points(player,points) VALUES " . join(',', $insertData);
    // add records
    $res = $db->query($sql);
    if ($res) {
        echo $db->affected_rows . " records added<br>";
    }
    else { 
        echo "Query failed<br>".$db->error.'<br>'.$sql;
    }
} else {
    echo "Enter players and points<br>";
}
?>
<form action="" method="post">
<textarea cols="40" rows="15" name="points"></textarea>
<input type="submit" name="btnsub" value="Submit">
</form>

You can get this month's data with this (without emptying the table and losing your data)

SELECT  player, SUM(points) as Total
FROM player_points
WHERE YEAR(date_entered) = YEAR(CURDATE()) AND MONTH(date_entered) = MONTH(CURDATE())
GROUP BY player
ORDER BY Total DESC;

Edited September 9, 2013 by Barand

[Justafriend]

ok thank you thank you in case anyone else comes across this i was getting errors and  found out what caused them

<?php
$db = new mysqli("localhost", "root","" , 'test' );

if (isset($_POST['points']) && !empty($_POST['points'])) {
    $entries = explode("\n", $_POST['points']);
    $insertData = array();
    foreach ($entries as $e) {
        if (trim($e) == '') continue;  // ignore empty lines
        list ($name, $pts) = explode(' ', $e);
        $insertData[] = sprintf("('%s', %d)", $db->real_escape_string($name), intval($pts));
    }
    // build multi-insert query
    $sql = "INSERT INTO player_points(player,points) VALUES " . join(',', $insertData);
    // add records
    $res = $db->query($sql);
    if ($res) {
        echo $db->affected_rows . " records added<br>";
    }
    else { 
        echo "Query failed<br>".$db->error.'<br>'.$sql;
    }
} else {
    echo "Enter players and points<br>";
}
?>
<form action="" method="post">
<textarea cols="40" rows="15" name="points"></textarea>
<input type="submit" name="btnsub" value="Submit">
</form>

in the reply there wasnt a php tag for it and the server username and pw needed "" i really appreciate all your work you did  and that arrow pointing me  (you doing 90 percent of my work i really appreciate it

[Justafriend]

ok i do have one question know the getting results would work as a sql query but what if i want to add it to the page and then number it i have this code but its spitting back  errors

 

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /home3/dbs/public_html/email.php on line 50

using this section of php code 

<table>

			<thead>

			<td>Place</td>

			<td>PlayerName</td>

			<td>Points</td>

			</thead>

			<?

				$i = 0;

				$d = date("Y-m-");

				$d .= "01 00:00:00";

				//$d = preg_replace("/-(\d+) /", "-01 ", $d);

				echo("Extracting data since $d<br/>");



				$r = mysql_query("select * from points where Date_Time > '$d' order by Points DESC");

				while ($v = mysql_fetch_assoc($r))

				{

					++$i;

					echo("<tr><td>$i</td><td>${v["PlayerName"]}</td><td>${v["Points"]}</td></tr>\n");

				}

			

			?>

[objnoob]

CSV / delimited file tip: enclose your strings that may contain new lines and delimiters in double quotes.

 

"jones, jimmy",2,player

"flying purple eater, one eye",3,coach

 

 

ooo... and don't explode.

 

array str_getcsv ( string $input [, string $delimiter = ',' [, string $enclosure = '"' [, string $escape = '\\' ]]] )

Edited November 23, 2013 by objnoob

[Barand]

If you are getting that error message then your query is failing. Check value of mysql_error() after running the query.

 

 

To get the last 10 days data, say, then your query would be

SELECT * FROM points WHERE DATE(Date_Time) > CURDATE() - INTERVAL 10 DAY

DATE() removes the time element from the datetime

CURDATE() returns todays date

[objnoob]

If you are getting that error message then your query is failing. Check value of mysql_error() after running the query.

 

 

To get the last 10 days data, say, then your query would be

SELECT * FROM points WHERE DATE(Date_Time) > CURDATE() - INTERVAL 10 DAY

DATE() removes the time element from the datetime

CURDATE() returns todays date

 

And, if you're doing a lot of wheres by date without time, consider separating your datetime into separate date and time columns 

[Barand]

If you need dates and times I'd recommend keeping them together. There may be an occasion when, for instance, you want all records between noon yesterday and noon today. Much simpler with the single datetime field.

[Justafriend]

the problem im having is im using a code i used way before and  have been running in brick wall after brick wall now alll i been getting is  how i got ripped off  cause the code is so insecure and how many problems are in the code  nd i have been doing my best to look through the code and  fix  it as there are so many problems with it  but its a basic thing im working with the reason the date and time are together is because when i tried to use this code  it had them like it   but even with this code im still having issues as  with the code in working i can get everything to post and run except  three problems  which is  the date to pull specific  data between 2 dates 2nd is the player names is used as id and therefore  wont allow duplicate names in the db the email list wont auto grab the most recent  itime the player name is used and the email addy with it and finally the toc  list is non alphabetical thought it was easier to get a patch job done on it until i can save up the money and  get someone to do a code that will serve the purpose i have very little php experience and am doing this not to make money or anything and already been taken 2 times but by the time i realized it it was too late

[objnoob]

If you need dates and times I'd recommend keeping them together. There may be an occasion when, for instance, you want all records between noon yesterday and noon today. Much simpler with the single datetime field.

 

Yes, there's trade offs.   Adding an additional date column in addition to datetime column is a option in that scenario.

 

But, whatever you do... you don't want to have perform functions on the data to make it usable. It should be usable out of the box, especially if your using it in a WHERE

This is where you want to be strict.  Storage is cheap.   CPU power is not.

Edited November 23, 2013 by objnoob