updating table with for loop

techker · October 5, 2019

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...

Barand · October 5, 2019

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

techker · October 6, 2019

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

Barand · October 6, 2019

What does your codes table look like?

techker · October 6, 2019

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 Updated! ";
 ++$i;
}

table code is set id value userid status dateSold

Edited October 6, 2019 by techker

Barand · October 6, 2019

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   |
+----------+--------+------+
*/

techker · October 6, 2019

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 October 6, 2019 by techker

Barand · October 6, 2019

Well, I did ask and didn't tell me so I won't waste any more of my time LOL.

techker · October 6, 2019

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;

Barand · October 6, 2019

What does a sample of your data look like before the query and what should it look like after?

I showed you mine, you show me yours.

techker · October 6, 2019

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

Barand · October 6, 2019

That is precisely what the last example query that I posted is doing (except in mine it was user 1 giving 5 codes to user 123)

techker · October 6, 2019

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

?>

Barand · October 6, 2019

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 October 6, 2019 by Barand

techker · October 6, 2019

im echoing the correct POST

credits: 2 Updated! new owner: 36 from owner: 18

techker · October 6, 2019

ok odd it seems to be working now!lol.must of been my session was expirerd?let me test to see Thx!!

techker · October 6, 2019

ya doesnt always work odd..

Barand · October 6, 2019

I have no idea what "doesn't work" means.

techker · October 6, 2019

thats odd i logout login works but after that does not work anymore..even if i logout again...unless its a cash issue..

techker · October 6, 2019

Credits stay the same even do the echo says 2 credits from to ...

Edited October 6, 2019 by techker

techker · October 7, 2019

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

techker · October 7, 2019

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 October 7, 2019 by techker

techker · October 7, 2019

even with a different approach same thing if i put 1 it updates 2

for ($h = 1; $h <= $CreditCount; $h++) echo "<option value='$h'>$h</option>";
echo "</select>";

Barand · October 7, 2019

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>

techker · October 7, 2019

would you know why it dow's one more than en what i ask?is it the select i did?that would calculate 0 as 1?

great example il try it out.again thx for the help!

Sign In

updating table with for loop

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

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