changing update to insert

Justafriend · September 11, 2014

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 · September 11, 2014

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

jazzman1 · September 11, 2014

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 · September 11, 2014

$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 · September 11, 2014

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 · September 11, 2014

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 · September 11, 2014

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 · September 11, 2014

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 · September 11, 2014

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

jazzman1 · September 11, 2014

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

Justafriend · September 11, 2014

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 · September 11, 2014

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 · September 11, 2014

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 · September 11, 2014

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 · September 11, 2014

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 · September 11, 2014

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 · September 11, 2014

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

Sign In

changing update to insert

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information