changing update to insert

[Justafriend]

quick question  i have located in my code

$r = mysql_query("insert into points values ('${v[0]}', ${v[1]}, '$At') on duplicate key update Points = Points + ${v[1]}");

-it is updateing the player points but i need it to do a new entry  how would i go about that i tried just taking out the on duplicate and it gives me a tstring insert error on next line.

if i leave it in i get no error

[Jacques1]

So how does your code without the ON DUPLICATE clause look like? We cannot debug code which isn't there.

[jazzman1]

Try,

$query = "insert into points values ('{$v[0]}',{$v[1]},'$At') on duplicate key update Points = Points + $v[1]";

$r = mysql_query($query) or die(mysql_error()); 

[Justafriend]

$r = mysql_query("insert into points values ('${v[0]}', ${v[1]}, '$At') on duplicate key update Points = Points + ${v[1]}");

mysql_query("insert ignore into toc values ('$p1', '$At'),('$p2', '$At'), ('$p3', '$At'), ('$p23', '$At')") or die (mysql_error());

when i use this it works  except points are being updated not  new rows the error i got is gone now i got i took out a set of "

 replaced them and page loads. 

 been staring at it too long i looked away and then went over it again

[Justafriend]

now i got a new issue and this may be a newbie question, When i got the code it made the tables and everything but i found out that  the player name was set to primary i have since changed it. Added a  primary key auto incremented and  primary now when i post into that form it whites out  do i have to tell the form that that field is there

[jazzman1]

as for the first question we need to see what value is assigned to $v[1] variable, I'm guessing it's zero or null. 

 

as for the second one, you should show us some part of your script.

[Justafriend]

ok  i have been trying to use this code i got to  learn by changing things as i need them  i have tested the code without any edits. (updating points) and its doing the same thing  so i think it has to do with  the id not being assigned but here is the code

<?php
//print_r($_SERVER);
$sn = $_SERVER['PHP_SELF'];
mysql_connect("localhost", "root", ""); //Put login and password for mysql here. Replace null with mysql server address if provided by host.
mysql_select_db("test") or die(mysql_error());//Put databasename here



mysql_query("create table if not exists tourne_report (Host TEXT, Date_Time DATETIME, PlayersCount INT, Winner TEXT, WinnerEmail TEXT, SecondPlace TEXT, SecondEmail TEXT, ThirdPlace TEXT, ThirdEmail TEXT, ThirdPlace2 TEXT, ThirdEmail2 TEXT)");

mysql_query("create table if not exists emails (PlayerName char(30) key not null, email TEXT)");

mysql_query("create table if not exists points (id INT (30), PlayerName char(30) key not null, Points INT, Date_Time DateTime)");



if (isset ($_GET["act"]))

{

if ($_GET["act"] == "erase")

{

mysql_query("drop table tourne_report");

mysql_query("drop table emails");

mysql_query("drop table points");

mysql_query("create table if not exists tourne_report (Host TEXT, Date_Time DATETIME, PlayersCount INT, Winner TEXT, WinnerEmail TEXT, SecondPlace TEXT, SecondEmail TEXT, ThirdPlace TEXT, ThirdEmail TEXT, ThirdPlace2 TEXT, ThirdEmail2 TEXT)");

mysql_query("create table if not exists emails (PlayerName char(30) key not null, email TEXT)");

mysql_query("create table if not exists points (PlayerName char(30) key not null, Points INT, Date_Time DateTime)");



}

if ($_GET["act"] == "points")

{

?>

<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");

}



?>

</table>

<?//<a href="<?echo ($sn);?><?//">Back</a>?>

<?

exit();



}

if ($_GET["act"] == "emails")

{

?>

<table>

<thead>

<td>PlayerName</td>

<td>EMail</td>

</thead>

<?



$r = mysql_query("select * from emails order by PlayerName ASC");

while ($v = mysql_fetch_assoc($r))

{

if ($v["PlayerName"] != "")

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

}



?>

</table>

<?//<a href="<?echo ($sn);?><?//">Back</a>?>

<?

exit();



}

if ($_GET["act"] == "toc")

{

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

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

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

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



?>

<h2>TOC:</h2>

<table>

<tr>

<?

$i = 0;

$all = array();

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

while ($v = mysql_fetch_assoc($r))

{



if ($v["Winner"] != "")

{

$all[] = $v["Winner"];

}

if ($v["SecondPlace"] != "")

{

$all[] = $v["SecondPlace"];

} 

if ($v["ThirdPlace"] != "")

{

$all[] = $v["ThirdPlace"];

} 

if ($v["ThirdPlace2"] != "")

{

$all[] = $v["ThirdPlace2"];

} 

}

sort($all,SORT_STRING);

$all = array_unique($all);

foreach ($all as $k)

{

echo("<td>$k</td>");

$i++;

if ($i == 3)

{

echo("</tr><tr>");

$i = 0;

}



}



while ($i != 3 && $i!=0)

{

echo("<td></td>");

++$i;

}





?>

</tr>

</table> 

<?//<a href="<?echo ($sn);?><?//">Back</a>?>

<?

exit();



}

if ($_GET["act"] == "months")

{

?>

<table>

<thead>

<td>Host</td>

<!--<td>Nick</td> -->

<td>Date_Time</td>

<td>Winner</td>

<td>#of players</td>

</thead>

<?

$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 tourne_report where Date_Time>'$d' order by Date_Time DESC");

while ($v = mysql_fetch_assoc($r))

{

echo("<tr><td>${v["Host"]}</td><td>${v["Date_Time"]}</td><td>${v["Winner"]}</td><td>${v["PlayersCount"]}</td></tr>\n");

}



?>

</table>

<?//<a href="<?echo ($sn);?><?//">Back</a>?>

<?

exit();



}



}

function not_2($var)

{

/*echo("<br><br>");

print_r($var);*/



if (count($var) != 2) return false;

else return true;

}

    if (isset($_POST["asfile"]))
    {
     //print_r($_POST);
    echo("<br>");
    $file = $_POST["file"];
    $points = strstr($file, "START POINTS");
    //print_r($points);
    $_POST["Points"] = $points;
    $file = explode("\n", $file);
     foreach ($file as $k)
    {
    $lines[] = explode(":", $k, 2);
    }
    $lines = array_filter($lines, "not_2");
    //print_r($lines);
    foreach ($lines as $value) 
    {
$value[0] = trim($value[0]);
 $value[1] = trim($value[1]);
switch (strtolower($value[0])) {
 case "date": $_POST["At"] = $value[1];
break;
case "scheduled start": $_POST["AtTime"] = $value[1];
break;
case "host": $_POST["Host"] = $value[1];
break;
case "number of players": $_POST["PC"] = $value[1];
break; 
case "1st place": $_POST["p1"] = $value[1];
break; 
case "1st place email": $_POST["e1"] = $value[1];
break; 
case "2nd place": $_POST["p2"] = $value[1];
break; 
case "2nd place email": $_POST["e2"] = $value[1];
break; 
case "3rd place":
if (isset($_POST["p3"]))
 {
 $_POST["p23"] = $value[1];
 }
 else 
{
 $_POST["p3"] = $value[1];
 }
 break; 
 case "3rd place email": 
if (isset($_POST["e3"])) $_POST["e23"] = $value[1];
else $_POST["e3"] = $value[1];
break; 
 default:
break;
 }
$_POST["send"] = 1;
}
 $vars = array("At", "AtTime", "Host", "PC", "p1", "e1", "p2", "e2", "p3", "e3", "p23", "e23");
 foreach ($vars as $k)
{
 if (!isset($_POST[$k]))
 $_POST[$k] = "";
 }
/* echo("Here:<br>");
print_r($_POST); 
echo("<br>");*/
}
if (isset ($_POST["send"]))
{
foreach ($_POST as $k=>$v)
{
$$k = $v;
}
//$d = explode(" ", $At);
 $dat = explode ("/",$At);
if (count($dat)!=3) die("Bad date format.");
 $tim = explode(":", $AtTime);
if (count($tim)<2) die("Bad time format.");
$plus = 0;
if (strstr(strtoupper($tim[1]), "PM"))
{
$plus = 12;
 }
 $h = $tim[0];
$m = explode(" ", $tim[1]);
$m = $m[0];
$h+= $plus;
 $At = "${dat[2]}-${dat[0]}-${dat[1]} $h:$m:00";
// echo("$At");
// $d = strtotime($At);
//$At = date("Y-m-d H:i:s", $d);
//echo("At: $At<br/>");
//echo("At: $At<br/>");
mysql_query("insert into tourne_report values ('$Host', '$At', $PC, '$p1', '$e1', '$p2', '$e2', '$p3', '$e3', '$p23', '$e23')") or die (mysql_error());
$Points = explode("\n", $Points);
mysql_query("insert ignore into emails values ('$p1', '$e1'),('$p2', '$e2'), ('$p3', '$e3'), ('$p23', '$e23')") or die (mysql_error());
 foreach ($Points as $v)
 {
 if ($v == "") continue;
$v = explode (" ", $v);
if (count($v) != 2)
die("Incorrect values in POINTS input (check spaces!)<br>");
if (strstr(strtolower($v[1]), 'points')) continue;
$r = mysql_query("insert into points values ('${v[0]}', ${v[1]}, '$At')");
 mysql_query("insert ignore into toc values ('$p1', '$At'),('$p2', '$At'), ('$p3', '$At'), ('$p23', '$At')") or die (mysql_error());
if (!$r) die (mysql_error());
}
//print_r($Points);
}

?>



<!--

<form method="POST" action="<?echo ($sn);?>">

<table>

<tr>

<td>

Host:</td> <td><input type = "text" name = "Host" /></td><td>Host name</td>

</tr>

<!--<tr>

<td>



Nick:</td> <td> <input type = "text" name = "Nick" /></td><td>Nick //What is it?</td> -->

<!--

<tr>

<td>



Date: </td> <td><input type = "text" name = "At" /></td><td>In "MM/DD/YYYY" format</td>

<tr>

<td>



Time: </td> <td><input type = "text" name = "AtTime" /></td><td>In "HH:MM A" format where A is either AM or PM</td>

<tr>

<td>



PlayersCount:</td> <td> <input type = "text" name = "PC" /></td><td>Integer value</td>

<tr>

<td>



Winner:</td> <td> <input type = "text" name = "p1" /></td><td>Winner name</td>

<tr>

<td>



Winner's email: </td> <td><input type = "text" name = "e1" /></td><td>Winner email</td>

<tr>

<td>



2nd place:</td> <td> <input type = "text" name = "p2" /></td><td>2nd name</td>

<tr>

<td>



2nd place email:</td> <td> <input type = "text" name = "e2" /></td><td>2nd email</td>

<tr>

<td>



3rd place:</td> <td> <input type = "text" name = "p3" /></td><td>3rd name</td>

<tr>

<td>



3rd place email:</td> <td> <input type = "text" name = "e3" /></td><td>3rd email</td>

<tr>

<td>



2_3rd place:</td> <td> <input type = "text" name = "p23" /></td><td>Second third place :-)</td>

<tr>

<td>



2_3rd place email:</td> <td> <input type = "text" name = "e23" /></td><td>Second third place email</td>

</table>

Points:<br>

<textarea name="Points" cols="30" rows="7"></textarea>

<br/>

<input type = "submit" name="send" value="Send results"/>

</form>

-->

<img src = "/Posting%20pOints.jpg" /> 

<form method="POST" action="<?echo ($sn);?>">

File contents:<br/>

<textarea name="file" cols="100" rows="20"></textarea><br/>

<input type="submit" name="asfile" value="Send a file" />

</form>

<a href = "<?echo ($sn);?>?act=points">Points</a><br>

<a href = "<?echo ($sn);?>?act=toc">Toc qualifiers</a><br>

<a href = "<?echo ($sn);?>?act=emails">Emails</a><br>

<a href = "<?echo ($sn);?>?act=months">Monthly report</a><br>

<?//<a href = "<?echo ($sn);?><?//?act=erase">ERASE</a><br>

?>

And before i get told i know its terrible code but to rewrite it is way out of my level of  learning. right now i need to get it working  then i can take the time to learn all about mysqli

[jazzman1]

It's huge I am not able to help without copying/past the script onto my IDE. So, I need to see what result you've got below.

 

Instead yours,  

$r = mysql_query("insert into points values ('${v[0]}', ${v[1]}, '$At') on duplicate key update Points = Points + ${v[1]}");

// do this 
$query = "insert into points values ('{$v[0]}',{$v[1]},'$At') on duplicate key update Points = Points + $v[1]";

echo $query; exit; 

$r = mysql_query($query) or die(mysql_error());

[Justafriend]

insert into points values ('afred',1 ,'2014-09-01 13:30:00') on duplicate key update Points = Points + 1

[jazzman1]

so you don't specify on which exactly duplicated key you want to update points column.

[Justafriend]

i dont want it added so for each entry  from each player thats entered it creates a new record using the id date  player points

the id wasnt there. i changed it figuring maybe the  reason it wouldnt post  removing the  duplicate  part was because the player name was set to primary. 

so i added a id auto increment primary column and now it wont post into the database at all it freezes and the page just shows blank, Why i was wondering if i needed to tell it theres an id field and if i do how do i do that

[jazzman1]

Ah...I see, you've got an redesigned database.I don't know how to help you...b/s a lot of queries should be fixed, I think.  

[Justafriend]

why i was just  asking if when the querry is submitted do i have to tell php that theres an id  column even if its auto increment

[jcbones]

No, the auto increment field will populate itself, BUT you will get errors if you don't follow a couple of rules.

 

1. If you run an insert query, and don't specify column names, you must provide a value for each and every column.

That would change your query to: based off of adding one auto increment column at the BEGINNING of the table.

 

$r = mysql_query("insert into points values ('','${v[0]}', ${v[1]}, '$At') on duplicate key update Points = Points + ${v[1]}");

2. If you specify the column names, you can skip the columns that are auto populated.

OR column names are made up, and probably do not reflect your column names.

 

$r = mysql_query("insert into points(Name,Points,Location) values ('${v[0]}', ${v[1]}, '$At') on duplicate key update Points = Points + ${v[1]}");

[mac_gyver]

jazzman, you and the OP are both connecting using ISP's in Toronto.

 

i think the OP intendeds to insert one row of data for each player, per event/match, with the point result for that event/match (or whatever the granularity of the information is for.) if this is the case, there's no 'on duplicate update ...' part needed.

[jazzman1]

jazzman, you and the OP are both connecting using ISP's in Toronto.

 

 

Usually, I am using my centos machine and firefox through a tor proxy server with German exit nodes, these replies were wrote from my daughter's fedora laptop.

It's nice to hear, that there are more Canadians around me

[Justafriend]

Thank you very much for all your help today that trick did the job. I really appreciated  it.