Jump to content

updating table with for loop


techker

Recommended Posts

Hey guys..i have an issue i can't seem to fix...

 

i have a table with codes and userid to associate  the code to a user

i need to make it that a user can give codes to another user.

i have a form .  from user to other user with amount of codes

 

so i have tried many combos..

$UserID =   mysqli_real_escape_string($DBcon, $_POST['OwnerID']);
$NewRId =   mysqli_real_escape_string($DBcon, $_POST['SubID']);
$Cr =       mysqli_real_escape_string($DBcon, $_POST['credits']);

tried with range

$t = array();
$counter = 0;
foreach (range(0, $Cr) as $t) {
	$sql = "UPDATE `Codes` SET `UserID`= '$NewRId' WHERE `UserID` = '$UserID'";
	$counter ++;
	echo $counter;
}
$result = mysqli_query($DBcon,$sql);
	if (!$result) {
	    trigger_error("UPDATE query failed: " . mysqli_error(), E_USER_ERROR);
	}
but it takes all the ocdes from user one to user two...

tried also:
 foreach ($Cr as $t) {
	$sql2 = "UPDATE `Codes` SET `UserID`= '$NewRId' WHERE `UserID` = '$UserID'";
 	if ($DBcon->query($sql2) === TRUE) {
     echo "<meta http-equiv=Refresh content=1;url=SwapC.php?success=1 >";
	} else {
		 echo "Error updating record: <br>" . $DBcon->error;
	}
 }
but does not work...

 

 

 

Link to comment
Share on other sites

In your first loop all you do is define a query string several times then execute it once.

What is $Cr?

If you never use it in a query why are you escaping it?

Much better to used prepared statements instead of escaping the data.

Not sure I really understand but it sounds like you want to do this...

$stmt = $DBCon->prepare("INSERT INTO codes (UserId, Code)
                            SELECT ?
                                 , Code
                            FROM codes
                            WHERE UserId = ?
                        ");
$stmt->bind_param('ss', $_POST['SubID'], $_POST['OwnerID']);
$stmt->execute();

 

Link to comment
Share on other sites

hey ya thx

$Cr = mysqli_real_escape_string($DBcon, $_POST['credits']);

is the number of credits the guy wants to give the other guy.

so lets say he has 20 codes and wants to give 10 to the other guy

 

in my codes table i have column codes and and column userID were i put the guys id (owner)

so now my issue is to take 10 codes out of 20 and swap his id to the new guys id

Link to comment
Share on other sites

also tried

$size = count($_POST['credits']);

$i = 0;
while ($i < $size) {
    $UserIDa= $_POST['OwnerID'][$i];
    $NewRIda = $_POST['SubID'][$i];

    $query = "UPDATE Codes SET UserID= '$UserIDa' WHERE UserID = '$NewRIda'";
    mysqli_query($DBcon,$query) or die ("Error in query: $query");
    echo "$UserIDa<br /><br /><em>Updated!</em><br /><br />";
    ++$i;
}

table code is set id value userid status dateSold

 

Edited by techker
Link to comment
Share on other sites

You seem intent on keeping the table secret so I'll guess.

/*       BEFORE 

+----------+--------+------+
| codes_id | UserId | code |
+----------+--------+------+
|        1 |      1 | aa   |
|        2 |      1 | bb   |
|        3 |      1 | cc   |
|        4 |      1 | dd   |
|        5 |      1 | ee   |
|        6 |      1 | ff   |
|        7 |      1 | gg   |
|        8 |      1 | hh   |
|        9 |      1 | jj   |
|       10 |      1 | kk   |
+----------+--------+------+   
*/

$userid = 1;
$subid  = 123;
$cr     = 5;

$stmt = $db->prepare("UPDATE codes
                            SET UserId = ?
                        WHERE userid = ?
                        LIMIT ?     
                  ");
$stmt->bind_param('iii', $subid, $userid, $cr);
$stmt->execute();


/*    AFTER

+----------+--------+------+
| codes_id | UserId | code |
+----------+--------+------+
|        1 |    123 | aa   |
|        2 |    123 | bb   |
|        3 |    123 | cc   |
|        4 |    123 | dd   |
|        5 |    123 | ee   |
|        6 |      1 | ff   |
|        7 |      1 | gg   |
|        8 |      1 | hh   |
|        9 |      1 | jj   |
|       10 |      1 | kk   |
+----------+--------+------+
*/

 

Link to comment
Share on other sites

no lol

 

CID  Value DateIn Status UserID DateSold

i tried: but stays at same..

$userid = mysqli_real_escape_string($DBcon, $_POST['OwnerID']);
$subid  = mysqli_real_escape_string($DBcon, $_POST['SubID']);
$cr     = mysqli_real_escape_string($DBcon, $_POST['credits']);

$stmt = $DBcon->prepare("UPDATE Codes
                            SET UserID = ?
                        WHERE UserID = ?
                        LIMIT ?
                  ");
$stmt->bind_param('iii', $subid, $userid, $cr);
$stmt->execute();

 

Edited by techker
Link to comment
Share on other sites

what do you need??

CREATE TABLE `Codes` (
  `CID` int(11) NOT NULL,
  `Value` varchar(100) NOT NULL,
  `DateIn` varchar(25) NOT NULL,
  `Status` varchar(25) NOT NULL,
  `UserID` varchar(25) NOT NULL,
  `DateSold` varchar(25) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Link to comment
Share on other sites

not sure how to export it..lol

 

so basically:

CID    Value         DateIn            Status .       UserID .        DateSold

1 .     24242        2019-11-11 .    0                     5                 null

2.       24243        2019-11-11 .    0                     5                 null

3 .     24244        2019-11-11 .    0                     5                 null

4 .     24245        2019-11-11 .    0                     3                 null

5 .     24246       2019-11-11 .    0                     3                null

6.     24246       2019-11-11 .    0                     4               null

7 .     24246       2019-11-11 .    0                     4                null

user          is 5 .   want to give 2 codes(value) to user 3

any value(code) 

should be :

CID    Value         DateIn            Status .       UserID .        DateSold

1 .     24242        2019-11-11 .    0                     5                 null

2.       24243        2019-11-11 .    0                     3                 null

3 .     24244        2019-11-11 .    0                    3                 null

4 .     24245        2019-11-11 .    0                     3                 null

5 .     24246       2019-11-11 .    0                     3                null

6.     24246       2019-11-11 .    0                     4               null

7 .     24246       2019-11-11 .    0                     4                null

Link to comment
Share on other sites

ya its odd..why is it not working for me....

 

ini_set("display_errors", 1);
header("Refresh: 30");
require_once 'config/dbconnect.php';

$userid = $_POST['OwnerID'];
$subid  =  $_POST['SubID'];
$cr     = $_POST['credits'];

$stmt = $DBcon->prepare("UPDATE Codes SET UserID = ? WHERE UserID = ? LIMIT ?");
$stmt->bind_param('iii', $subid, $userid, $cr);
$stmt->execute();

echo "$userid<br /><br /><em>$cr Updated! $subid</em><br /><br />";
echo "<meta http-equiv=Refresh content=1;url=SwapC.php?success=1 >";

?>

 

Link to comment
Share on other sites

Still working for me with your table format...

/*   BEFORE

+-----+-------+------------+--------+--------+----------+
| CID | Value | DateIn     | Status | UserID | DateSold |
+-----+-------+------------+--------+--------+----------+
|   1 | 24242 | 2019-11-11 |      0 |      5 | NULL     |
|   2 | 24243 | 2019-11-11 |      0 |      5 | NULL     |
|   3 | 24244 | 2019-11-11 |      0 |      5 | NULL     |
|   4 | 24245 | 2019-11-11 |      0 |      3 | NULL     |
|   5 | 24246 | 2019-11-11 |      0 |      3 | NULL     |
|   6 | 24246 | 2019-11-11 |      0 |      4 | NULL     |
|   7 | 24246 | 2019-11-11 |      0 |      4 | NULL     |
+-----+-------+------------+--------+--------+----------+
*/
            
$userid = 5;
$subid  = 3;
$cr     = 2;

$stmt = $db->prepare("UPDATE codes
                            SET UserId = ?
                        WHERE UserId = ?
                        LIMIT ?     
                  ");
$stmt->bind_param('iii', $subid, $userid, $cr);
$stmt->execute();


/*    AFTER

+-----+-------+------------+--------+--------+----------+
| CID | Value | DateIn     | Status | UserID | DateSold |
+-----+-------+------------+--------+--------+----------+
|   1 | 24242 | 2019-11-11 |      0 |      3 | NULL     |    <--  changed user 5 to user 3
|   2 | 24243 | 2019-11-11 |      0 |      3 | NULL     |    <--  changed user 5 to user 3
|   3 | 24244 | 2019-11-11 |      0 |      5 | NULL     |
|   4 | 24245 | 2019-11-11 |      0 |      3 | NULL     |
|   5 | 24246 | 2019-11-11 |      0 |      3 | NULL     |
|   6 | 24246 | 2019-11-11 |      0 |      4 | NULL     |
|   7 | 24246 | 2019-11-11 |      0 |      4 | NULL     |
+-----+-------+------------+--------+--------+----------+
*/

Are you sure your $_POST contains what you think it should contain?

Edited by Barand
Link to comment
Share on other sites

same issue very odd...it does not update at all...

require_once 'config/dbconnect.php';

  $userid = strip_tags($_GET['user']);
	$subid = strip_tags($_GET['newUser']);
  $cr = strip_tags($_GET['cr']);
	$userida = $DBcon->real_escape_string($userid);
  $subida = $DBcon->real_escape_string($subid);
  $cra = $DBcon->real_escape_string($cr);


$stmt = $DBcon->prepare("UPDATE Codes SET UserID = ? WHERE UserID = ? LIMIT ?");
$stmt->bind_param('iii', $subida, $userida, $cra);
$stmt->execute();

if(!$stmt->execute()) {
    echo "Error: " . mysqli_error($DBcon);
}else{
    echo "Success adding user : $subida with : $cra  ";
		echo "<meta http-equiv=Refresh content=1;url=Subseller.php?success=1 >";
}

 

Link to comment
Share on other sites

Uhh stupid me lol

So first issue was that i forgot to add  AND Status != 'Used' .  so it would update a used code..that is why i would not see it ..

 

But the issue i have now is that if i select 1 credit it puts 2

is it because it is like an array that 0 is 1 so it would do 0 then 1 so gives 2?

 

have a feeling it is this:

its were i add the user.it gets the amount of credits the user has(code) and puts them in a select for his to select the amount to transfer so he knows how many he can transfer..

$range = range(1,$CreditCount);
foreach ($range as $cm) {
  echo "<option value='$cm'>$cm</option>";
}
echo "</select>"; ?>

 

Edited by techker
Link to comment
Share on other sites

This is my test version (with a couple of extra displays to confirm the processing)

<?php

## connect to your DB here ##
                                
$userid = '0';
$subid  = '0';
$cr     = 0;
$max    = 0;
$confirm = '';
$before = $after = '';
//
// was data posted?
//
if ($_SERVER['REQUEST_METHOD']=='POST') {

    $userid = $_POST['userid'] ?? 0;
    $subid  = $_POST['subid'] ?? 0;
    $cr     = $_POST['credits'] ?? 0;
    
    $before = "<h3>Before</h3>" . query2HTML($db, "select * from codes where userid in ($userid, $subid)");


    $stmt = $db->prepare("UPDATE codes
                            SET UserId = ?
                            WHERE UserId = ?
                              AND status != 'Used'    
                            LIMIT ?     
                      ");
    $stmt->bind_param('iii', $subid, $userid, $cr);
    $stmt->execute();
    $howmany = $db->affected_rows;
    
    if ($cr==$howmany)  {
        $confirm = "<div style='display:inline-block; padding: 16px; background-color: #EEE; color: #000'>$howmany rows transferred</div><br>";
    } else {
        $confirm = "<div style='display:inline-block; padding: 16px; background-color: #F00; color: #FFF'>$howmany rows transferred</div><br>";
    }

    $after  = "<h3>After</h3>" . query2HTML($db, "select * from codes where userid in ($userid, $subid)");
}
//
// respond to ajax request
//
if (isset($_GET['ajax'])) {
    exit(cr_options($db, $_GET['userid'], 0));
}

/**
* create list of credit quantity options
* 
* @param mysqli $db
* @param int $user
* @param int $current     - current quantity
*/
        function cr_options(mysqli $db, $user, $current)
        {
            $stmt = $db->prepare("SELECT COUNT(*) as tot
                                  FROM codes
                                  WHERE status != 'Used'
                                  AND UserId = ?
                                 ");
            $stmt->bind_param('i', $user);
            $stmt->execute();
            $stmt->bind_result($tot);
            $stmt->fetch();
            if ($tot == 0) {
                $opts = "<option value=''>No unused codes</option>";
            }
            else {
                for($i=1; $i<=$tot; $i++) {
                    $sel = $i==$current ? 'selected' : '';
                    $opts .= "<option $sel>$i</option>";
                }
            }
            return $opts;
        }
/**
* general purpose debugging utility function to output query results
* 
* @param mysqli connection $db
* @param string $sql
* @return string         - html table
*/
        function query2HTML($db, $sql)
        {
            $output = "<table border='1' cellpadding='2' style='border-collapse:collapse'>\n";
              // Query the database
            $result = $db->query($sql);
              // check for errors
            if (!$result) return ("$db->error <pre>$sql</pre>");
            if ($result->num_rows == 0) return "No matching records";
              // get the first row and display the headings
            $row = $result->fetch_assoc();
            $output .= "<tr><th>" . join('</th><th>', array_keys($row)) . "</th></tr>\n";
            
              // display the data
            do {
               $output .= "<tr><td>" . join('</td><td>', $row) . "</td></tr>\n"; 
            } while ($row = $result->fetch_assoc());
            $output .= "</table>\n";
            return $output;
        }
?>
<html>
<head>
<meta http-equiv="content-language" content="en">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Sample Code Transfer</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script type="text/javascript">
    $().ready(function() {
        $("#userid").change( function() {
            var user = $(this).val()
            $.get(
                "",
                {"ajax":1, "userid":user },
                function(resp) {
                    $("#credits").html(resp)
                },
                "TEXT"
            )
        })
    })
</script>
<style type="text/css">
    label {display:inline-block; width: 160px; font-weight: 600;}
</style>
</head>
<body>
<form method='post'>
    <fieldset>
    <legend>Codes Transfer</legend>
        <label>From user</label><input type="text" name="userid" id="userid" value="<?=$userid?>" size="5"><br>
        <label>Credits to transfer</label><select name="credits" id="credits"><?=cr_options($db, $userid, $cr)?></select><br><br>
        <label>To user</label><input type="text" name="subid" id="subid" value="<?=$subid?>" size="5"><br>
        <label>&nbsp;</label><input type="submit" name="btnSub" value="Transfer">
    </fieldset>
</form>
<br>
<br>
<?=$before?>
<?=$confirm?>
<?=$after?>
</body>
</html>

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.